Mysql server optimization for InnoDB and MyISAM
Optimizing mysql for InnoDB storage engine
With the latest mysql version 5.5 the default mysql storage engine is now InnoDB, this has been worked on a lot more now and performance on this engine is now really good, if not impressive!
You do need to keep some track of a few important things about it, by default it is not optimized and you should at least give enough innodb storage, although this is design to grow as much as needed even if its hitting the limit set. But its better to know what you’ve set then finding out your disk is full.
Ok, so InnoDB settings you need to make sure are optimized properly are:
|
1 2 3 4 5 |
innodb_buffer_pool_size = 1024M innodb_additional_mem_pool_size = 8M innodb_log_buffer_size = 8M innodb_write_io_threads=4 innodb_read_io_threads=4 |
innodb_buffer_pool_size — This one is easy to guess, the size of the InnoDB storage
innodb_additional_mem_pool_size — This is the thing I said that if you are hitting the limit for your pool size, it will start increasing so that mysql will still work as normal.
innodb_log_buffer_size — This is basically a size that will always buffer when writing to a log file
innodb_write_io_threads and innodb_read_io_threads — This is the number of threads used to read and write data from your InnoDB pool size. Default is set to 4. Normally you don’t need more.
Now that’s for InnoDB, mysql optimization does not start there. For example if you have different services running on the same server, you may want mysql to have top priority over all, so you can maybe use option nice to give it top priority. This is done by adding the below values to your [mysqld_safe] section:
|
1 2 3 |
[mysqld_safe] nice = -20 # [ ... ] and other settings |
This will get mysql up and running with high priority over all services that are running.
You may want to skip the following ones as you probably don’t use them:
|
1 2 |
skip-name-resolve skip-federated |
That’s all for Innodb storage engine that you need be careful of. Its not really that hard, right? Default settings would suffice.
But that’s not just it, the ram usage that is being used by some application may need adjusted a bit more.
Optimizing Mysql for MyISAM storage engine.
MyISAM is basically using your ram memory to execute sql queries and cache them for speed overall. What I found you need to make sure you keep track is the bellow ones:
- Buffer settings for MyISAM
|
1 2 3 4 5 6 7 8 9 10 11 |
# Number of CPU cores * 2 thread_concurrency=4 key_buffer_size = 16M max_allowed_packet = 1M table_open_cache = 4 sort_buffer_size = 64K read_buffer_size = 256K read_rnd_buffer_size = 256K net_buffer_length = 2K thread_stack = 128K |
- Cache settings for MyISAM
|
1 2 3 4 5 6 |
query_cache_limit = 1M query_cache_size = 16M thread_cache_size = 8M table_cache = 16M tmp_table_size = 24M max_heap_table_size = 24M |
- Disable what you don’t need
|
1 2 3 4 5 |
skip-external-locking skip-networking skip-name-resolve skip-federated log-error = /dev/null |
There are a few more things that could still be done here, but most them have been explained already. If you need anything else specific, just comment below and if possible I’ll help out. If anything you see is mistyped or wrong, please do note and let me know for me to update.
That’s it for today, hope you enjoyed it, see next time!
Pages: 1 2
