(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.