CiviCRM Community Forums (archive)

So I'm just wondering what others use for their mysql my.cnf settings to tune their DB?

I've got a large DB which has slowed down considerably since I imported 550,000 contacts. I had not adjusted my.cnf and I'm just playing around with the settings. I've got a VPS with 8G of RAM at the moment and wondering how to best allocate it.

I'm new to this, so I've been playing around - but others insights would be great.

Brian

Thanks Jake. I'll give that a try. I'd be interested in what your my.cnf settings ended up being. I'll post mine when I'm done, although I am sure it will be very specific it would be interesting to see.

Brian

Sorry for the slow response time Brian... Been diving deeper into civicrm MySQL/InnoDB optimization- I can tell you that MySQLtuner will falsely report that almost all InnoDB tables are corrupted, at least on MySQL 5.1+, confirmed by running analyze on my civicrm DB via PHPMyAdmin. And, OPTIMIZE TABLE does not work on InnoDB as with MyISAM- if you run this, MySQL will automatically try to perform a "re-create + analyze" instead. I use this command from root to optimize all DB tables after running mysqltuner-
 
mysqlcheck --optimize -A (-u username -ppassword if not logged in as "root"), can also use short-hand like, mysqlcheck -o -A

Don't forget to make backups first.

This is where I started this journey- dev.mysql.com/doc/refman/5.1/en/innodb-configuration.html

I pretty much have InnoDB setup/config for civicrm down now- I have overcome too many issues to list them all here. If anybody is having difficulties, post your question and I will be more than happy to help. I can especially help if you are using CPanel/WHM + VPS.

Jake

Lobo,

I wish I had more time to do this right now, I know it would be very helpful to others (would have saved me a month!). However, I will start assembling my many notes into an easy to read post of what I've learned along the way about optimizing MySQL/InnoDB for civicrm. This will take me a little while, given my current workload- If someone needs help in the meantime, don't hesitate to ask.

I was actually surprised to find very little on this subject here in the forum... To the best of your knowledge, is there any official civicrm documentation regarding this MySQL/InnoDB optimization?

That is a good idea, but my notes are rather cryptic- meaning I'm usually the only one that can make sense of them

I forgot about this CPanel help video I found the other day- After I already figured everything out the hard way, of course. This video pretty much sums up the basics for MySQL optimization, at least for CPanel/WHM users. Starts with a proper my.cnf with must have variables, and covers the the many helpful tools as well. This is not InnoDB specific, but will be a good staring point. Even covers a mess up I made- Using the included mysql my.cnf templates - Don't do it, it will cause many issues.

http://www.cpanel.net/videos/mysql-optimization/

I would like to mention that after getting my.cnf past the initial setup, installing MySQLtuner, and any other tools you may choose to use- you should leave all InnoDB variables left to your system defaults, or commented out until mysqltuner has sufficient time to gather MySQL performance data. Please wait at least 24 hours before making any changes suggested by mysqltuner

I created this my.cnf as my base from which to build on (Note: for CPanel/WHM my.cnf config). :
**Make a backup of your original my.cnf 1st**
cd /etc/
cp /etc/ my.cnf my.cnf.orig (I use ".orig" instead of ".bak" for original files)
---------------------------------------------------------------------------------------------------------------------------------------------------------------------

# The MySQL server
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0


# Logging and Variables new to MySQL 5.1 - Be careful, general log can grow very quickly
skip-external-locking
slow_query_log
general_log
expire_logs_days = 0

# Cache - Must have variables
thread_cache_size=4
table_open_cache=64
query_cache_limit=1M
query_cache_size=8M
query_cache_type=1

# Buffer - Must have variables
key_buffer_size=48M
read_buffer_size=128K
join_buffer_size=128K

# Other must have variables
connect_timeout=10
max_connect_errors=10
max_allowed_packet=1M
tmp_table_size=16M

# Uncomment the following for InnoDB tables-Use mysqltuner 1st
#innodb_data_home_dir = /var/lib/mysql
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_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 = 256M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 64M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
#innodb_force_recovery = 4



[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

---------------------------------------------------------------------------------------------------------------------------------------------------------------------
A few things to be mindful of- mysql-bin logging (used for replication), innodb_force_recovery (used for repairing corrupted InnoDB DB's), always monitor the logs, look for mysqld errors like this- InnoDB: Error: page log sequence number InnoDB: is in the future!

Anybody having issues with any of the above, post here- I'll do my best to answer as quickly as possible.

Hopefully, this will save others some time and effort (and head-slapping!).