Especially the one that defines how MySQL is gonna use server's CPU. I'd like to boost performance. Also, what are other ways to boost MySQL performance? (Maybe some code optimization hints?) Anyway. Any information will be useful. Thanks in advance.
2 Answers
Use InnoDB tables and configure it properly. This site is a great resource:
There's no way to magically "boost performance", but there are ways to better tune your MySQL to fit your application needs. Not knowing your application requirements means this is hard to answer in general terms.
If you're using InnoDB, and you really should be, there are several configuration variables relating to that which should be adjusted.
The primary boost comes from the generic buffer pool:
[mysqld]
innodb_buffer_pool_size=2048M
This should be set somewhere around 40% of system memory for a dedicated database server in order to leave sufficient system memory over for OS filesystem caching and other processes. You can increase this factor on systems with more memory. For instance, on a 12GB system you might allocate 8GB to this pool.
As always, read the documentation on your chosen engine very carefully and benchmark your application against any settings you try. There is no one-size-fits-all solution here. If you're using SSD you would tune quite differently from regular disks, and even then differently for specific RAID configurations.
