I’ve been screwing up mysql configurations left and right. Nothing really harmful just not efficient. Not enough ram allocated to the right configurations.
http://www.mysqlperformanceblog.com/2014/01/28/10-mysql-settings-to-tune-after-installation/
http://mysql.rjweb.org/doc.php/memory
Two great resources for reading about how to properly configure mysql.
Checking how much ram you need simple. You just need a few mysql commands to get you the numbers.
SELECT ENGINE, ROUND(SUM(data_length) /1024/1024, 1) AS “Data MB”, ROUND(SUM(index_length)/1024/1024, 1) AS “Index MB”, ROUND(SUM(data_length + index_length)/1024/1024, 1) AS “Total MB”, COUNT(*) “Num Tables” FROM INFORMATION_SCHEMA.TABLES WHERE table_schema not in (“information_schema”, “performance_schema”) GROUP BY ENGINE;(this command wont work pasted in as it is posted here, its right but wordpress formats it incorrectly, get the command from the link I posted above)
Thats the most imporant. This command shows you what you should be setting your two more important settings to. .
“Add up Data_length + Index_length for all the InnoDB tables.Set innodb_buffer_pool_size to no more than 110% of that total.
Add up Index_length for all the MyISAM tables.Set key_buffer_size no larger than that size.”
These settings control how the engines will interact with the ram and hard disk. Innodb needs more ram than my ISAM. So if you have 200mb of myisam index and 200 total mb of innodb then they would require similar amounts of ram. This would probably mean myisam is doing more total work though. Its important to know which engine is being used more.
”
SHOW GLOBAL STATUS LIKE ‘Key%’;
then calculate Key_read_requests / Key_reads If it is high (say, over 10), then the key_buffer is big enough.
”
”
If using just MyISAM, set key_buffer_size to 20% of _available_ RAM.(Plus innodb_buffer_pool_size=0)
If using just InnoDB, set innodb_buffer_pool_size to 70% of _available_ RAM.(Plus key_buffer_size = 10M, small, but not zero.)
“
This is for dedicated MySQL servers. If you have a webserver running together with MySQL in one box then consider lowering these values. Thats why you check to see how large the tables are for each engine so you can set the buffers accordingly.
Query Cache
Short answer: query_cache_type = OFF and query_cache_size = 0
To see how well your QC is performing,SHOW GLOBAL STATUS LIKE ‘Qc%’;then compute the read hit rate:Qcache_hits / Qcache_insertsIf it is over, say, 5, the QC might be worth keeping.ENGINE=MEMORY
This is a little-used alternative to MyISAM and InnoDB. It has limited uses. The size of a MEMORY table is limited to max_heap_table_size, which defaults to 16MB. I mention it in case you have changed the value to something huge; this would stealing from other possible uses of RAM.
You can see how well your system is performing via
SHOW GLOBAL STATUS;
or
SHOW /*!50002 GLOBAL */ STATUS;
and computing the opens/second via
Opened_files / Uptime If this is more than, say, 5, table_cache should be increased.If it is less than, say, 1, you might get improvement by decreasing table_cache.
InnoDB only: innodb_buffer_pool_size = 70% of RAM If you have lots of RAM and are using 5.5 (or later), then considerhaving multiple pools.Recommend 1-16 innodb_buffer_pool_instances, such that each one is no smaller than 1GB.(Sorry, no metric on how much this will help; probably not a lot.)
Meanwhile, set key_buffer_size = 20M (tiny, but non-zero)
If you have a mixture of engines, lower both numbers.
Alot of this is copy pasta from those articles. You should read those articles if you want to understand this configuration for MySQL.
max_allowed_packet=2M
This setting is like php memory_limit. It is the limit on each query. If you start seeing out of memory errors from the mysql error log then you should raise this value. Otherwise 2M is probably plenty and most querys will not be this large.
wait_timeout=12
This is how long the connection will stay open after a query has been received. It usually takes a second or two to send a query.
“The client had been sleeping more than wait_timeout or interactive_timeout seconds without issuing any requests to the server. See Section 5.1.4, “Server System Variables”. ”
12 is probably too high but its better than 6. If you’re having issues reaching max connections maybe try 8 or 10.
Having it too low could cause Aborted_clients. But there are plenty of other reasons a client could abort.
http://dev.mysql.com/doc/refman/5.0/en/communication-errors.html
” Other reasons for problems with aborted clients or aborted connections:
The max_allowed_packet variable value is too small or queries require more memory than you have allocated for mysqld. See Section B.5.2.10, “Packet Too Large”.
If you start the server with the –log-warnings option, you might find messages like this in your error log:
If a client successfully connects but later disconnects improperly or is terminated, the server increments the Aborted_clients status variable, and logs an Aborted connection message to the error log.”
innodb_file_per_table=1
skip-external-locking
http://doc.ctrlaltdel.ch/database/mysql/manual_Performance.html
So stop messing up those configurations already jeeeeeeezzzzz.