How to optimize MYSQL for 360MB of RAM?
I would need some advice if this is right or wrong. It is for a Drupal site. And I would like to have Innodb too for other pages. Thank you very much.
skip-external-locking
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
#skip-innodb
innodb_data_file_path=ibdata1:10M:autoextend:max:500M
# Set buffer pool size to 50-80% of your computer's memory
innodb_buffer_pool_size=150M
innodb_additional_mem_pool_size=20M
# Set the log file size to about 25% of the buffer pool size
innodb_log_file_size=38M
innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1
# Set key_buffer to 5 - 50% of your RAM depending on how much
# you use MyISAM tables, but keep key_buffer_size + InnoDB
# buffer pool size < 80% of your RAM
key_buffer_size=20M
key_buffer = 16K
max_allowed_packet = 8M
thread_stack = 64K
thread_cache_size = 4
query_cache_limit = 1M
query_cache_size = 4M
net_buffer_length = 2K
max_connections=100
read_buffer_size=1M
sort_buffer_size=1M
#table_cache = 64
#thread_concurrency = 10
2 Replies
It's a script that "looks" at your MySQL server and suggests some optimizations. Besides learning quite a bit about MySQL performance tuning (because I didn't just blindly make every suggested change but instead looked up what the config option was about and its effects), the script also did make my server run smoother.
As for Drupal, if your site's visitors are mostly anonymous, I strongly recommend the Boost module:
It basically creates a cache of static html pages that get served instead of hitting the MySQL server to create the page dynamically (or even fetching the page from Drupal's cache). In other words, it reduces the load on MySQL a lot.
Other than that, I suggest you take a look at placing varnish in front of your webserver for serving static files and, if you use Apache, look at using nginx or lighttpd instead.
@hedac:
Innodb too for other pages.