2

(Before you will call me an idiot or start laughing, please remember it's a real deal on my side and I cannot cancel this assignment as it is a part of a bigger project)

I have a database server that is crazy big in size and is running on crazy small server, ie. 32GB RAM, 24 cores. During the weekend we are going to add 18GB of RAM to the machine to make it itsy bitsy more capable (server load during the day jumps up to average 80!!) until we will sort out performance issues with the application using this database.

I suspect that the load is caused by DB is swapping because of lack of free RAM (about 5GB always in SWAP).

This database has multiple small-ish tables and one huge table which is receiving GPS and IO data at rate around 350,000 records / h and this table is partitioned by a partition/month.

Mysqltuner suggests running optimize table on all tables but I have had read that doing so on InnoDB tables is useless and also it would take long hours.

Here is a snip from the script output:

General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
  query_cache_limit (> 16M, or use smaller result sets)
  join_buffer_size (> 2.0M, or always use indexes with joins)
  tmp_table_size (> 16M)
  max_heap_table_size (> 16M)
  table_cache (> 4096)
  innodb_buffer_pool_size (>= 1726G)

Should I blindly trust it or is there another way to do this?

I know the first thing is to reconfigure the DB after the upgrade, what else should I be aware of and what should I try? Downtime should be as always as short as possible so I really don't want to run a query which would run for 5h straight.

  • 1
    Nobody should call you things for this question! It is very reasonable, although difficult to answer remotely. – jynus Jul 10 '14 at 19:35
5

Forget about mysqltuner and check for human advice. This tool tells you general recommendations that may be useless and even hurtful in some cases. Optimize table is probably going to be useless, but it locks your tables for writes. A consultant may save you time and money in the long run.

Swapping should be a no-go for MySQL. Make sure to tune your innodb_buffer_pool_size to maximize memory usage without swapping. Please make sure you understand that there may be other things using memory: filesystem cache, needed for logging and other operations, and per-client used memory, like the join buffer and sort buffers. All together should be less than the available physical memory, as MySQL/InnoDB does its own way of buffering, and if it uses virtual memory that in reality it is on disk, you are going to have a bad time.

It is difficult to provide foolproof "general advice" without a full review of your server. But in my experience, 90% of the problems are with the queries and the database design, not with the server configuration.

However, here you have a good introduction to MySQL/Innodb configuration optimization by a good human (Peter Zaitsev). Hope it helps.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.