Today, while on IRC in #pentaho I came across a discussion and a published my.cnf. In this configuration I found some grossly incorrect values for per session buffers (see below).
It doesn’t take a MySQL expert to spot the issues, however there is plenty of bad information available on the Internet and developers not knowing MySQL well can easily be mislead. This has spurred me to create a program to rid the world of bad MySQL configuration. While my task is potential infinite, it will enable me to give back and hopefully do a small amount of good. You never know, saving those CPU cycles may save energy and help the planet.
Stay tuned for more details of my program.
[mysqld] ... sort_buffer_size = 6144K myisam_sort_buffer_size = 1G join_buffer_size = 1G bulk_insert_buffer_size = 1G read_buffer_size = 6144K read_rnd_buffer_size = 6144K key_buffer_size = 1024M max_allowed_packet = 32M thread_stack = 192K thread_cache_size = 256 query_cache_limit = 512M query_cache_size = 512M ...
Tags: free advice, my.cnf, mysql configuration
Truly a noble effort, especially as most of the information out there is either misleading or vague, or with a generally unhelpful “it depends on the situation”.
Something that could lay out steps or outright analyze various “situations” based on tuning parameters and such would be amazing. Even as simple as an extended version of (http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html) with more detailed performance implications and dependencies of any given variable. The closest thing I have found to what you’re suggesting is a “tuning primer script” by Matthew Montgomery, and probably worth taking a look at if you haven’t seen previously: http://www.day32.com/MySQL/
Hi Erick,
Yes, I know Matt well and while I’ve not looked at his script in several years I know people reference it.
It’s difficult to tune a my.cnf without additional key information. This includes
* System Memory, and available memory for MySQL
* Database size/Growth
* Storage Engines
* SQL paths
* Query volume
It is however very easy to identify errors, omissions and outrageous values, and my first goal will be to at least identify these.
So for your critical review.
Redhat 5.2 x64
8G memory
80DBs, Largest ~72Bytes x 32,000,000 rows
All MyISAM with ~15 user connects and 2,000 SQL/s
MySQL 5.1.37-SP1 Enterprise x64
query_cache_limit = 16M # Don’t cache results that are larger than this number of bytes.
query_cache_size = 512M # caching query Sizes > 128M may reduce performance
query_cache_type = 1 # 1=ON – 2=OFF – 3=DEMAND
thread_cache_size = 128 # How many threads the server should cache for reuse
key_buffer = 32M # 64M for 1GB, 128M for 2GB, 256 for 4GB
key_buffer_size = 4096M # Should be some less then 75% of total RAM
join_buffer_size = 8M # 1M for 1GB, 2M for 2GB, 4M for 4GB
max_allowed_packet = 32M # maximum size of one packet or any generated/intermediate string
table_cache = 2048 # number of open tables for all threads
sort_buffer_size = 1M # 1M for 1GB, 2M for 2GB, 4M for 4GB
read_buffer_size = 1M # 1M for 1GB, 2M for 2GB, 4M for 4GB
read_rnd_buffer_size = 768K # 768K for 1GB, 1536K for 2GB, 3072K for 4GB
myisam_sort_buffer_size = 16M # 32M for 1GB, 64M for 2GB, 128 for 4GB
table_definition_cache = 768 # number of table definitions that can be stored in the definition
[...] part of my upcoming FREE my.cnf check advice I first need to ask people to provide the current MySQL configuration file commonly found as a file [...]
I currently use Tuning Primer or Mysql Report to configure buffer sizes. But I agree that there is A LOT of misinformation out there and my.cnf configurations. As a general rule of thumb I use this formula to calculate mysql memory usage: memory=key_buffer+(sort_buffer_size+read_buffer_size)*max_connections