1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

My Sql tuning questions - temporary tables, key_buffer_size

Discussion in 'Server Configuration and Hosting' started by MrC, Jun 10, 2012.

  1. MrC

    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.
     
  2. craigiri

    craigiri Well-Known Member

    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
     
    MrC likes this.
  3. MrC

    MrC Active Member

    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...
     
  4. Ghan_04

    Ghan_04 Active Member

    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.
     
  5. MrC

    MrC Active Member

    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.
     
  6. Ghan_04

    Ghan_04 Active Member

    So are you having any issues other than the tmp_table thing?
    Also, how large is your database?
     
  7. MrC

    MrC Active Member

    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.
     
  8. CyclingTribe

    CyclingTribe Well-Known Member

    Did you see this note in tuningprimer.sh?

    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
     
  9. CyclingTribe

    CyclingTribe Well-Known Member

    Could you post a copy of your my.cnf file contents - there may be other InnoDB settings that might benefit your setup. (y)
     
  10. MrC

    MrC Active Member

    Yes, I saw the note.
    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.
    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
    
     
  11. craigiri

    craigiri Well-Known Member

    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.
     
  12. craigiri

    craigiri Well-Known Member

    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.
     
  13. MrC

    MrC Active Member

    I am using APC for front end and back end. I am sending you the URL, I prefer to keep it non public :D.
    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.
     
  14. Ghan_04

    Ghan_04 Active Member

    Anything below 1.00 is great, especially on systems with multiple cores. I wouldn't worry at all with a load that low.
     
    MrC likes this.
  15. craigiri

    craigiri Well-Known Member

    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.
     
    MrC likes this.
  16. MrC

    MrC Active Member

    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.
     

Share This Page