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 ...
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
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
Hi,
My Drupal(CMS) site consume lots of memory.
RAM : 1GB
OS : CentOS release 5.2
mysql : 5.0.45
My current my.cnf file.
*************************
# Example MySQL config file for very large systems.
#
# This is for a large system with memory of 1G-2G where the system runs mainly
# MySQL.
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /var/lib/mysql) or
# ~/.my.cnf to set user-specific options.
#
# In this file, you can use all long options that a program supports.
# If you want to know which options a program supports, run the program
# with the “–help” option.
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /var/lib/mysql/mysql.sock
# Here follows entries for some specific programs
# The MySQL server
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
key_buffer = 128M
max_allowed_packet = 8M
table_cache = 1024
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 16M
myisam_sort_buffer_size = 64M
thread_cache_size = 16
query_cache_size = 32M
# Try number of CPU’s*2 for thread_concurrency
thread_concurrency = 8
# Don’t listen on a TCP/IP port at all. This can be a security enhancement,
# if all processes that need to connect to mysqld run on the same host.
# All interaction with mysqld must be made via Unix sockets or named pipes.
# Note that using this option without enabling named pipes on Windows
# (via the “enable-named-pipe” option) will render mysqld useless!
#
#skip-networking
# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin
#required unique id between 1 and 2^32 – 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1
# Replication Slave (comment out master section to use this)
#
# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) –
# the syntax is:
#
# CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,
# MASTER_USER=, MASTER_PASSWORD= ;
#
# where you replace , , by quoted strings and
# by the master’s port number (3306 by default).
#
# Example:
#
# CHANGE MASTER TO MASTER_HOST=’125.564.12.1′, MASTER_PORT=3306,
# MASTER_USER=’joe’, MASTER_PASSWORD=’secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method, then
# start replication for the first time (even unsuccessfully, for example
# if you mistyped the password in master-password and the slave fails to
# connect), the slave will create a master.info file, and any later
# change in this file to the variables’ values below will be ignored and
# overridden by the content of the master.info file, unless you shutdown
# the slave server, delete master.info and restart the slaver server.
# For that reason, you may want to leave the lines below untouched
# (commented) and instead use CHANGE MASTER TO (see above)
#
# required unique id between 2 and 2^32 – 1
# (and different from the master)
# defaults to 2 if master-host is set
# but will not function as a slave if omitted
#server-id = 2
#
# The replication master for this slave – required
#master-host =
#
# The username the slave will use for authentication when connecting
# to the master – required
#master-user =
#
# The password the slave will authenticate with when connecting to
# the master – required
#master-password =
#
# The port the master is listening on.
# optional – defaults to 3306
#master-port =
#
# binary logging – not required for slaves, but recommended
#log-bin=mysql-bin
# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname
# Uncomment the following if you are using BDB tables
#bdb_cache_size = 384M
#bdb_max_lock = 100000
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 – 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 384M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 100M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout
*************************
My server goes down when we add/update the content(means database operations).
Thanks in Advance.
Tejas
How would one take advantage of your free MySQL configuration advice?