MySQL Tuning

[Volion]

Looking for feedback, suggestions for best performance for the innodb settings for 4GB - 6 GB of ram with Xenforo.

innodb_data_home_dir = /var/lib/mysql/
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/
innodb_buffer_pool_size = 64M
innodb_additional_mem_pool_size = 10M
innodb_log_file_size = 32M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50

Thanks in advance.

 

[Brent W]

This would give you a few good recommendations: http://mysqltuner.pl/mysqltuner.pl

 

[Volion]

Thanks, but I was hoping for hands on advice, experience.

 

[Brent W]

Thanks, but I was hoping for hands on advice, experience.

I understand, just wanted to throw it out there.

This is in my.cnf FWIW. (2GB Dedicated MySQL server)

max_connections = 150
max_allowed_packet= 32M
innodb_buffer_pool_size= 1G
thread_cache_size = 4
key_buffer_size = 768M
table_cache = 8192
max_heap_table_size = 256M
tmp_table_size = 256M

 

[Volion]

I understand, just wanted to throw it out there.

This is in my.cnf FWIW. (2GB Dedicated MySQL server)

max_connections = 150
max_allowed_packet= 32M
innodb_buffer_pool_size= 1G
thread_cache_size = 4
key_buffer_size = 768M
table_cache = 8192
max_heap_table_size = 256M
tmp_table_size = 256M

Interesting, I've seen a few people set the innodb_buffer_pool_size= to 1G what do you have your log_file_size set to?

 

[Ghan_04]

The most important setting when using innodb tables is probably going to be the buffer pool. That's basically where MySQL will cache data related to indexing on all innodb tables on the server.
That being said, you should still also pay attention to the key_buffer because Xenforo's search index is still a MyISAM table regardless due to the fulltext indexing. You optimally want to have a key_buffer that is large enough to hold all the indices of your search index table for higher search performance. max_heap and tmp_table sizes should be large enough for MySQL to use in-memory temp tables for as much as possible (try some different values and check the tuning script to see what % of your temp tables are being created in memory to get a good feel for it).

Keep in mind that all of this depends on how big your database is, so there is no set of magical numbers that will get you going, but it sounds like you have enough RAM to do what you need.

 

[Volion]

The most important setting when using innodb tables is probably going to be the buffer pool. That's basically where MySQL will cache data related to indexing on all innodb tables on the server.
That being said, you should still also pay attention to the key_buffer because Xenforo's search index is still a MyISAM table regardless due to the fulltext indexing. You optimally want to have a key_buffer that is large enough to hold all the indices of your search index table for higher search performance. max_heap and tmp_table sizes should be large enough for MySQL to use in-memory temp tables for as much as possible (try some different values and check the tuning script to see what % of your temp tables are being created in memory to get a good feel for it).

Keep in mind that all of this depends on how big your database is, so there is no set of magical numbers that will get you going, but it sounds like you have enough RAM to do what you need.

Thanks, yeah I have those all set large enough. Changed the innodb_buffer_pool_size= to 1G , still no noticeable difference in performance. I'm going to have to break down and use the tuning script.

 

[craigiri]

Performance issues are hard to suss out unless you have a lot of people beating on the server. PhpMyAdmin also gives some tuning advice...or at least shows if anything is terribly wrong.

Also, chances are that cache and other such issues (apache tuning, etc.) can help as much or more than mysql tunings.

I guess the first questions are:
Is there a problem with speed or response you are trying to fix?
What is the amount of data you are dealing with (posts, members, size of xf db) - and is there anything else of note on the server.

 

[Ghan_04]

I have noticed huge performance improvements from using the standard MySQL query cache. If you don't already have it set, try that. This is the setting:

query_cache_size = 64M

 

[Volion]

Performance issues are hard to suss out unless you have a lot of people beating on the server. PhpMyAdmin also gives some tuning advice...or at least shows if anything is terribly wrong.

Also, chances are that cache and other such issues (apache tuning, etc.) can help as much or more than mysql tunings.

I guess the first questions are:
Is there a problem with speed or response you are trying to fix?
What is the amount of data you are dealing with (posts, members, size of xf db) - and is there anything else of note on the server.

Speed and response times are all over the place (pingdom report) the site actually seems to run faster when we have 400+ online, rather then the normal 200 or so.
Relatively new site ( 2 months old ) 63k, 3000+, 25m.

I have noticed huge performance improvements from using the standard MySQL query cache. If you don't already have it set, try that. This is the setting:

query_cache_size = 64M

Here is my updated .cnf - mySQLtuner reported no suggestions, will recheck in 48 hours.

character-set-server=utf8
collation-server=utf8_general_ci
innodb_file_per_table
skip-external-locking
key_buffer_size = 500M
max_allowed_packet = 16M
table_open_cache = 4096
sort_buffer_size = 4M
read_buffer_size = 2M
join_buffer_size = 2M
myisam_sort_buffer_size = 32M
thread_cache_size = 64
query_cache_limit = 5M
query_cache_size = 128M
query_cache_type = 1
thread_concurrency = 4
tmp_table_size = 256M
max_heap_table_size = 256M
server-id = 1
slow_query_log
slow_query_log_file=/var/log/slow-queries.log
long_query_time=4
 
max_connections = 384
max_user_connections = 100
interactive_timeout = 30
wait_timeout = 300
connect_timeout = 10
max_connect_errors = 5
 
 
innodb_buffer_pool_size=1G
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 32M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit=2
innodb_lock_wait_timeout = 50
innodb_flush_method=O_DIRECT
 
[mysqldump]
quick
max_allowed_packet = 32M

 

[craigiri]

That is a relatively small db size so speed should not be a problem.
For comparisons sake, we have a 2 gig db and 1.2 million posts with about the same settings in mysql and it's running quick. Server load has not gone over .5 since we've had XF running (it's our off-season, so only about 200 online at one time).

If you get slowdowns at this point, I'd look closer at other things.......

All your RAM (or most) should be getting used - check the top command
(Nix uses RAM as cache/buffers by default)
Apache or whichever web server you use should be set to spawn plenty of servers and children
You can set the basic front and back end caches in XF if you like

Of course, it can also be your hosts network, but regular ping tests should tell you about some of that.

Good Luck!

 

[simbolo]

Since you reference pingdom, are you sure the slow responses are due to db issues and not network/web server/etc?

 

[Volion]

Since you reference pingdom, are you sure the slow responses are due to db issues and not network/web server/etc?

Looking into that for a few days now, very well could be.