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

Mysql optimizing

Discussion in 'Server Configuration and Hosting' started by Will, Jun 27, 2012.

  1. Will

    Will Active Member

    Just looking for some tips on improving my mysql settings.

    my.cnf
    Code:
    [mysqld]
    innodb_additional_mem_pool_size=16M
    innodb_buffer_pool_size=800M
    innodb_file_per_table=1
    innodb_log_buffer_size=4M
    innodb_flush_log_at_trx_commit=2
    myisam_sort_buffer_size=16M
    expire_logs_days=7
    query_cache_size=32M
    thread_cache_size=16K
    max_allowed_packet=16M
    local-infile=0
    table_open_cache=2K
    table_definition_cache=3K
    open_files_limit=2K
    max_connections=100
    wait_timeout=30
    interactive_timeout=60
    connect_timeout=10
    read_buffer_size=2M
    slow_query_log=1
    slow_query_log_file="/var/log/slow_queries.log"
    sort_buffer_size=2M
    join_buffer_size=4M
    key_buffer_size=128M
    query_cache_limit=4M
    query_cache_type=1
    long_query_time=5
    tmp_table_size=128M
    max_heap_table_size=128M
     
    [myisamchk]
    read_buffer=2M
    key_buffer=256M
    sort_buffer_size=256M
    write_buffer=2M
     
    [mysqldump]
    max_allowed_packet=16M
    quick
     
    [mysql]
    no-auto-rehash
     
    [mysqlhotcopy]
    interactive-timeout
    mysqltuner
    Code:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.24-1~dotdeb.1-log
    [OK] Operating on 64-bit architecture
     
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 37M (Tables: 93)
    [--] Data in InnoDB tables: 300M (Tables: 321)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 5M (Tables: 8)
    [!!] Total fragmented tables: 183
     
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
     
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 9d 3h 16m 24s (59M q [75.936 qps], 4M conn, TX: 578B, RX: 8B)
    [--] Reads / Writes: 84% / 16%
    [--] Total buffers: 1.1G global + 8.5M per thread (100 max threads)
    [OK] Maximum possible memory usage: 1.9G (76% of installed RAM)
    [OK] Slow queries: 0% (142/59M)
    [OK] Highest usage of available connections: 10% (10/100)
    [OK] Key buffer size / total MyISAM indexes: 128.0M/22.4M
    [OK] Key buffer hit rate: 100.0% (43M cached / 12K reads)
    [OK] Query cache efficiency: 54.6% (19M cached / 34M selects)
    [!!] Query cache prunes per day: 53192
    [OK] Sorts requiring temporary tables: 0% (1 temp sorts / 430K sorts)
    [!!] Joins performed without indexes: 19730
    [!!] Temporary tables created on disk: 49% (309K on disk / 622K total)
    [OK] Thread cache hit rate: 99% (10 created / 4M connections)
    [!!] Table cache hit rate: 14% (762 open / 5K opened)
    [OK] Open file limit used: 6% (275/4K)
    [OK] Table locks acquired immediately: 99% (20M immediate / 20M locks)
    [!!] Connections aborted: 8%
    [OK] InnoDB data size / buffer pool: 300.7M/800.0M
     
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Adjust your join queries to always utilize indexes
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Increase table_cache gradually to avoid file descriptor limits
        Your applications are not closing MySQL connections properly
    Variables to adjust:
        query_cache_size (> 32M)
        join_buffer_size (> 4.0M, or always use indexes with joins)
        tmp_table_size (> 128M)
        max_heap_table_size (> 128M)
        table_cache (> 2048)
    Running a VPS with 2.5GB RAM. Any suggestions would be appreciated. :)
     
  2. Ghan_04

    Ghan_04 Active Member

    Here are some of my suggestions based on the data:

    - Lower max_connections to around 25
    - Lower key_buffer_size to 32M
    - Increase query_cache_size to around 64M
    - Increase table_cache to 4096 or 8192

    tmp_table_size is probably fine where it is. It's hard to say because most of those temp tables are likely due to fulltext searching, and those will never use in-memory temp tables, meaning that increasing the value would not help those. Your database seems relatively small, so 128 MB should be plenty. Indeed, if this is all that is running on your VPS, 2.5 GB of RAM is probably unneeded. You won't be able to use all of it. More RAM is never a bad thing in terms of performance, though you might be able to cut costs if you downsize (depending on current VPS plan, provider, costs, etc)
     
    Will likes this.
  3. Will

    Will Active Member

    Thanks, I will try your suggestions. The only thing is the max_connections. The number should be somewhere around the amount of users are online, correct?

    To be clear about the server, it is actually 1GB dedicated and 2.5 burst. It's an unmanaged VPS so it's only like $18 a month so not a huge deal. :)
     
  4. Ghan_04

    Ghan_04 Active Member

    > The number should be somewhere around the amount of users are online, correct?

    Not really. This is the number of concurrent MySQL connections being made to the MySQL server. Since most of the time spent online is not actually time spent doing MySQL queries, this is likely going to be much lower than the number of users online.
    User online != active MySQL connection

    Here is the pertinent line:

    [OK] Highest usage of available connections: 10% (10/100)

    In 9 days of being up, the server has only hit 10 connections in use at once. That's why I suggested 25. Still plenty of room to grow.
     
    Will likes this.
  5. MrC

    MrC Active Member

    Even at 500 online users, you probably don't need more than 50 max_connections.
     
    Will likes this.
  6. Will

    Will Active Member

    Thanks for the clarification.
     
  7. CyclingTribe

    CyclingTribe Well-Known Member

    I would suggest trying these lower values too:

    PHP:
    read_buffer_size=1M
    sort_buffer_size
    =1M
    join_buffer_size
    =1M
     
  8. Will

    Will Active Member

    I made these suggested changes.

    new my.cnf
    Code:
    [mysqld]
    innodb_additional_mem_pool_size=16M
    innodb_buffer_pool_size=800M
    innodb_file_per_table=1
    innodb_log_buffer_size=4M
    innodb_flush_log_at_trx_commit=2
    myisam_sort_buffer_size=16M
    expire_logs_days=7
    query_cache_size=64M
    thread_cache_size=16K
    max_allowed_packet=16M
    local-infile=0
    table_open_cache=8K
    table_definition_cache=3K
    open_files_limit=2K
    max_connections=25
    wait_timeout=30
    interactive_timeout=60
    connect_timeout=10
    read_buffer_size=1M
    slow_query_log=1
    slow_query_log_file="/var/log/slow_queries.log"
    sort_buffer_size=1M
    join_buffer_size=1M
    key_buffer_size=32M
    query_cache_limit=4M
    query_cache_type=1
    long_query_time=5
    tmp_table_size=128M
    max_heap_table_size=128M
     
    [myisamchk]
    read_buffer=2M
    key_buffer=256M
    sort_buffer_size=256M
    write_buffer=2M
     
    [mysqldump]
    max_allowed_packet=16M
    quick
     
    [mysql]
    no-auto-rehash
     
    [mysqlhotcopy]
    interactive-timeout
    mysqltuner output
    Code:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.24-1~dotdeb.1-log
    [OK] Operating on 64-bit architecture
     
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 38M (Tables: 93)
    [--] Data in InnoDB tables: 306M (Tables: 322)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 2M (Tables: 8)
    [!!] Total fragmented tables: 186
     
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
     
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 22h 0m 36s (9M q [59.762 qps], 731K conn, TX: 93B, RX: 1B)
    [--] Reads / Writes: 82% / 18%
    [--] Total buffers: 1.0G global + 3.5M per thread (25 max threads)
    [OK] Maximum possible memory usage: 1.1G (44% of installed RAM)
    [OK] Slow queries: 0% (36/9M)
    [OK] Highest usage of available connections: 40% (10/25)
    [OK] Key buffer size / total MyISAM indexes: 32.0M/22.6M
    [OK] Key buffer hit rate: 99.9% (5M cached / 3K reads)
    [OK] Query cache efficiency: 63.4% (3M cached / 6M selects)
    [!!] Query cache prunes per day: 20882
    [OK] Sorts requiring temporary tables: 0% (4 temp sorts / 64K sorts)
    [!!] Joins performed without indexes: 3519
    [!!] Temporary tables created on disk: 49% (48K on disk / 97K total)
    [OK] Thread cache hit rate: 99% (10 created / 731K connections)
    [OK] Table cache hit rate: 21% (535 open / 2K opened)
    [OK] Open file limit used: 1% (246/16K)
    [OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
    [!!] Connections aborted: 11%
    [OK] InnoDB data size / buffer pool: 306.6M/800.0M
     
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Adjust your join queries to always utilize indexes
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Your applications are not closing MySQL connections properly
    Variables to adjust:
        query_cache_size (> 64M)
        join_buffer_size (> 1.0M, or always use indexes with joins)
        tmp_table_size (> 128M)
        max_heap_table_size (> 128M)
    Are there any major issues that stand out here?
     
    Puntocom likes this.
  9. Ghan_04

    Ghan_04 Active Member

    It looks like you could increase the query cache some more. Perhaps to 128M. Other than that, most things seem pretty good.
     
  10. Will

    Will Active Member

    Just made that change. I was looking at one thing in the recommendations.

    Your applications are not closing MySQL connections properly

    Should I worry about this? If so, how could I fix it?
     
  11. Walter

    Walter Well-Known Member

    Never saw this with Xenforo. Do you have addons installed?
    Is your site very slow so it times out a lot?
     
  12. Will

    Will Active Member

    Didn't see it on the server I was previously on. Yes, I have addons installed.
    To me the site doesn't seem slow but I can't be online 24/7 so I can't be entirely sure. I did have one member just last night say that they receive this error a lot now. "The following error occurred: The server did not respond in time. Please try again."
     
  13. CyclingTribe

    CyclingTribe Well-Known Member

    If you've enabled error logging and slow query logging check the logs, they might give you a clue as to what is causing the disconnects. ;)
     
    gordy likes this.
  14. Will

    Will Active Member

    I'll check this out and see if I can figure out the issue.
     
  15. CyclingTribe

    CyclingTribe Well-Known Member

    Just a thought, looking at your [wait_timeout=30] - try increasing it to 45 (seconds) and see if you still get disconnects?

    You might find that search queries from your XF site search (which can be fairly intensive at times) are exceeding the MySQL wait timeout and causing the disconnects. I'm not saying that is the case, it could be an add-on, a query from the ACP or something else, but extending the timeout might eradicate the problem for you by giving the query extra time to complete.

    Cheers,
    Shaun :D
     
  16. Will

    Will Active Member

    I have noticed the last few days that the load has been over 1 consistently when it usually doesn't really go over .8 at all. Top command says that mysqld is using over 80% cpu. My my.cnf hasn't changed much except for the suggestions made here. Any ideas?
     
  17. Ghan_04

    Ghan_04 Active Member

    I would guess it's caused by the leap second that we had recently. I had one machine where MySQL did that. I rebooted the server and it was fine.
     
  18. Puntocom

    Puntocom Well-Known Member

    Thank you! now I can rebuild caches :)
     

Share This Page