Search code examples
mysqlperformanceinnodbdatabase-optimization

How mysql/innodb database size impacts performance?


I have a 30 GB large mysql database where all tables use innodb engine. Almost all of this space is taken by one large table (about 25 GB) where I store large texts in binary format, but 99% of the queries use other, smaller, tables.

My server has 16 GB ram and system is very short on memory, always uses swap and gives my users a lot of pain. I have my innodb_pool_buffer_size set to 6G.

I am thinking of solving this performance problem by moving those texts from database to filesystem.

Will moving 25 GB of texts to filesystem solve this problem, if most queries do not use this large table? Does database size impacts server performance even if largest innodb table is not used for queries too much?


Solution

  • There's simply not enough information in your question to provide an answer.

    Before applying possible solutions to your problem, you need to take a systematic approach to find the problem. Otherwise, you're prescribing random drugs, for an unknown ailment. You're as likely to make the problem worse as you are to make it better, or have no effect.

    Start by running EXPLAIN on your slow queries, to see what they're doing. Make sure you have your table(s) properly indexed to improve those queries (if they aren't already). And then, if you're still stuck, post your slowest query, and the output of EXPLAIN as a new question here, and you'll get much more useful answers.