My Sql tuning questions - temporary tables, key_buffer_size

MrC

Active member
I am using mysqltuner, tuning-primer for Mysql tuning. Everything seems fine (according to the scripts) except the following:

1. Temporary tables created on disk is always around 40-45% no matter what values I chose for tmp_table_size and max_heap_table_size. Currently, they are at 256M, mysqltuner suggested to reduce the values, tuning-primer suggested to increase the values. I tried both, still got 40-45%. Total fragmented tables is also quite high: 170.

2. I have key_buffer_size=128M, I saw some people got it as high as 768M. But tuning-primer suggested to lower the value. Should I trust tuning-primer? What are the reasons for setting key_buffer_size value high? I am using Enhanced Search if that matters.
 
Enhanced search should be saving you mysql queries.....

All this stuff depends on how big your forum is, how often it is used, etc. etc.
How much RAM you have to work with also can determine your settings.

XF is mostly innodb, so the innodb buffer pool is the most important setting. Most forums should run fine with somewhat standard settings.

PhpMYadmin also gives a good overview of the various parts of the mysql statuses.

Do you have a problem with server load? Does the top or other command show mysql being fairly normal - that is, the CPU use relatively low and the process not taking up too much memory?

Some hints on innodb settings:
http://dev.mysql.com/doc/refman/5.1/en/innodb-configuration.html

Example:
# Set buffer pool size to 50-80% of your computer's memory,
# but make sure on Linux x86 total memory usage is < 2GB
innodb_buffer_pool_size=1G
innodb_additional_mem_pool_size=20M
 
  • Like
Reactions: MrC
I have 4GB Ram, innodb_buffer_pool_size=1.5G. The top command give loads: 0.xx, 0.17, 0.16 and it's roughly like that every time I checked. I got around 100qps in peak time.

Google site speed gives an average of 3.38 sec for the last 15 days or so. I just moved server. Yesterday it was 2.48 sec.

So the settings are probably OK. I am just wondering if there is something I could do to solve the warnings.

Based on your suggestion, I think key_buffer_size=128M should be fine (cause I am using ES). The first problem may be due to something like BLOB and TEXT...
 
Do you have the slow query log turned on? You can use that to see what queries are actually taking the most time. Ultimately, it doesn't matter if temp tables aren't that efficient if the queries are still pretty quick.
Also, make sure you have the query cache turned on. That can help a lot as well.

Something other than MySQL you could do is install memcached and set up Xenforo to use it.
 
I have slow query log turned on. I just restarted mysql about 2,3 hours ago, so it might not be accurate. But tuning-primer.sh gives: "you have 1 out of 1037515 take take longer than 2 sec to complete". I set slow_query_log_file="/var/log/slow_queries.log", slow_query_log=1 but I couldn't find the slow_queries.log file in the directory. I am not sure what I have done wrong.

I tried to install memcached. But I could not find a repository which works with the latest php version that I am using ( I am also using Centos 6.2). I am giving up on memcached for now. May be I will look at it again later.
 
No, I am only having an issue with tables created on disk. At the moment, it's 40%. mysqltuner.pl suggested to decrease tmp_table_size, tuning-primer.sh suggested the opposite.

For slow queries, tuning-primer.sh gave "You have 1 out of 2493647 that take longer than 2 sec to complete".

According to mysqltuner.pl, I have: Data in MyISAM tables 90M, Data in Innodb tables 608 Mb.

Edit: I have query cache at 64M. I converted from VB to XF sometimes ago, if that makes any difference.
 
Did you see this note in tuningprimer.sh?

rich said:
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.

XF uses blobs so you're always going to see a reasonable level of temp tables created on disk - my server runs quite fast and I've spent a long time optimising MySQL and I still have 47% of temp tables created on disk. Nothing to worry about and raising the temp values won't make a difference. You'll probably be fine with 64/128M temp table values.

key_buffer_size is for the myisam engine/tables and since XF primarily uses InnoDB adjusting this value won't impact too much; however if you're running other sites/databases you might want to try a value of 512M and see how you go. Lower it if you find you're using up all of your available system memory.

Cheers,
Shaun :D
 
Could you post a copy of your my.cnf file contents - there may be other InnoDB settings that might benefit your setup. (y)
 
Did you see this note in tuningprimer.sh?
Yes, I saw the note.
XF uses blobs so you're always going to see a reasonable level of temp tables created on disk - my server runs quite fast and I've spent a long time optimising MySQL and I still have 47% of temp tables created on disk. Nothing to worry about and raising the temp values won't make a difference. You'll probably be fine with 64/128M temp table values.
I tried to lower the value and both mysqltuner.pl and tuning-primer.sh gave warnings to increase the value. But I think you are right, there was no performance issues when I lowered the value.
key_buffer_size is for the myisam engine/tables and since XF primarily uses InnoDB adjusting this value won't impact too much; however if you're running other sites/databases you might want to try a value of 512M and see how you go. Lower it if you find you're using up all of your available system memory.
Cheers,
Shaun :D
I only have a small wordpress blog (in addition to XF) on the server, which I am going to move to RM when it 's out.

Here is my my.cnf
Code:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
port=3306
user=mysql
#MrC add
ft_min_word_len=3
#skip-name-resolve
back_log=50
max_connections=60
#max_user_connections=100
low_priority_updates=1
concurrent_insert=ALWAYS
#log_queries-not-using-indexes=1
key_buffer_size=128M
myisam_sort_buffer_size=64M
myisam_max_sort_file_size=2048M
read_buffer_size=2M
sort_buffer_size=2M
join_buffer_size=4M
read_rnd_buffer_size=2M
table_open_cache=1024
table_definition_cache=1024
thread_cache_size=16
long_query_time=2
#wait_timeout=30
interactive_timeout=60
connect_timeout=30
tmp_table_size=256M
max_heap_table_size=256M
max_allowed_packet=64M
max_seeks_for_key=1000
group_concat_max_len=1024
max_length_for_sort_data=1024
net_buffer_length=16384
max_connect_errors=100
#bulk_insert_buffer_size=8M
query_cache_limit=1M
query_cache_size=64M
query_cache_type=1
#query_prealloc_size=262114
#query_alloc_block_size=65536
#range_alloc_block_size=4096
#transaction_alloc_block_size=8192
#transaction_prealloc_size=4096
slow_query_log=1
slow_query_log_file="/var/log/slow_queries.log"
max_write_lock_count=4
#Innodb
innodb_data_home_dir= /var/lib/mysql/
innodb_open_files=500
innodb_data_file_path=ibdata1:10M:autoextend
#innodb_log_group_home_dir=/var/lib/mysql
#innodb_log_arch_dir=/var/lib/mysql
innodb_buffer_pool_size=1536M
innodb_additional_mem_pool_size=20M
innodb_log_file_size=400M
innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=2
innodb_thread_concurrency=16
innodb_flush_method=O_DIRECT
innodb_log_files_in_group=2
innodb_lock_wait_timeout=300
#default-storage-engine=InnoDB
# Disabling symbolic-links is recommended to prevent assorted security risks
#symbolic-links=0
[mysqldump]
quick
max_allowed_packet =128M
 
[mysql]
no-auto-rehash
 
[isamchk]
key_buffer=128M
sort_buffer_size=128M
read_buffer=2M
write_buffer=2M
 
[myisamchk]
#ft_min_word_len=3
key_buffer_size=128M
sort_buffer_size=128M
read_buffer=2M
write_buffer=2M
 
[mysqlhotcopy]
interactive-timeout
 
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
 
Are you using the built in front and back end XF cache?

You have more memory set for innodb than I do, and my db is larger, so I don't think you have a problem there. Does your site seem to load quickly? If that is the case, I would not chase performance through goog webmaster tools, as it seems to be wrong (sometimes) to me.

Is your apache conf set to spawn enough children and servers?

Post your URL and we can see if it seems quick.
 
I have 4GB Ram, innodb_buffer_pool_size=1.5G. The top command give loads: 0.xx, 0.17, 0.16 and it's roughly like that eve..

Your server load is extremely low! I think you'd be best off waiting until it got high before tuning too far....that is, if the site seems relatively quick.
 
Are you using the built in front and back end XF cache?

You have more memory set for innodb than I do, and my db is larger, so I don't think you have a problem there. Does your site seem to load quickly? If that is the case, I would not chase performance through goog webmaster tools, as it seems to be wrong (sometimes) to me.

Is your apache conf set to spawn enough children and servers?

Post your URL and we can see if it seems quick.
I am using APC for front end and back end. I am sending you the URL, I prefer to keep it non public :D.
Your server load is extremely low! I think you'd be best off waiting until it got high before tuning too far....that is, if the site seems relatively quick.
It is higher now, but still at 0.xx, 0.27, 0.23. I installed Taigachat yesterday, so it might increase the load a bit.
 
Anything below 1.00 is great, especially on systems with multiple cores. I wouldn't worry at all with a load that low.
 
  • Like
Reactions: MrC
Site is very quick!
There is nothing wrong with server loads much higher than one - especially if you have multiple core CPU's. Some people say that it is approx "1" per core, so a dual core CPU at a load of 2 would have full utilization.

Also, servers can run very well with small wait periods, so the server with a dual CPU may run fine even if it hits much higher loads occasionally. I would only start to worry if the site starting getting very sluggish.

On my old forum with a dual core CPU, that would happen at about a server load of 4+. Once it got above 6 or so, it would be very slow, but still working. Even at that load, I did not get a lot of complaints from members.
 
  • Like
Reactions: MrC
I have 4 cores. After fixing an issue with Enhanced Search, tuning-primers suggested to increase key_buffer_size to >295M. So I set it =384M for now.
 
Top Bottom