The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

MySql Optimization Help

Discussion in 'Workarounds and Optimization' started by Kenpachi, Nov 28, 2010.

  1. Kenpachi

    Kenpachi Member

    Joined:
    Nov 28, 2010
    Messages:
    22
    Likes Received:
    0
    Trophy Points:
    1
    Hello everybody I need help to optimize MySQL

    VPS

    Intel(R) Xeon(R) CPU E5520 Quad Core / 50 GB HDD / 500 GB Bandwidth / 1GB Memory

    my.cnf variables

    Code:
    [mysqld]
    datadir = /var/lib/mysql
    socket = /var/lib/mysql/mysql.sock
    log-slow-queries=/var/lib/mysql/slow.log
    user=mysql
    #skip-name-resolve
    safe-show-database
    old_passwords
    back_log = 50
    skip-innodb
    max_connections = 250
    key_buffer_size = 32M
    myisam_sort_buffer_size = 64M
    join_buffer_size = 1M
    read_buffer_size = 1M
    sort_buffer_size = 2M
    table_cache = 4000
    thread_cache_size = 144
    wait_timeout = 20
    interactive_timeout = 10
    connect_timeout = 10
    tmp_table_size = 32M
    max_heap_table_size = 32M
    max_allowed_packet = 32M
    net_buffer_length = 16384
    max_connect_errors = 100000
    thread_concurrency = 16
    concurrent_insert = 2
    table_lock_wait_timeout = 30
    read_rnd_buffer_size = 786432
    bulk_insert_buffer_size = 8M
    query_cache_limit = 3M
    query_cache_size = 32M
    query_cache_type = 1
    query_prealloc_size = 262144
    query_alloc_block_size = 65536
    transaction_alloc_block_size = 8192
    transaction_prealloc_size = 4096
    default-storage-engine = MyISAM
    max_write_lock_count = 4
    
    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
    nice = -5
    open_files_limit = 8192
    
    [mysqldump]
    quick
    max_allowed_packet = 16M
    
    [myisamchk]
    key_buffer_size = 32M
    sort_buffer_size = 16M
    read_buffer_size = 16M
    write_buffer_size = 16M
    
    [mysqlhotcopy]
    interactive-timeout
    Code:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.0.91-community-log
    [OK] Operating on 32-bit architecture with less than 2GB RAM
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 444M (Tables: 729)
    [--] Data in MEMORY tables: 2M (Tables: 6)
    [!!] Total fragmented tables: 16
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 16h 45m 53s (1M q [16.927 qps], 48K conn, TX: 313M, RX: 329M)
    [--] Reads / Writes: 59% / 41%
    [--] Total buffers: 106.0M global + 4.9M per thread (250 max threads)
    [!!] Maximum possible memory usage: 1.3G (130% of installed RAM)
    [OK] Slow queries: 0% (68/1M)
    [OK] Highest usage of available connections: 16% (41/250)
    [OK] Key buffer size / total MyISAM indexes: 32.0M/213.5M
    [OK] Key buffer hit rate: 100.0% (3B cached / 56K reads)
    [OK] Query cache efficiency: 59.5% (370K cached / 622K selects)
    [!!] Query cache prunes per day: 2145
    [!!] Sorts requiring temporary tables: 12% (13K temp sorts / 104K sorts)
    [!!] Joins performed without indexes: 541
    [OK] Temporary tables created on disk: 7% (1K on disk / 16K total)
    [OK] Thread cache hit rate: 99% (41 created / 48K connections)
    [OK] Table cache hit rate: 87% (1K open / 1K opened)
    [OK] Open file limit used: 21% (1K/8K)
    [OK] Table locks acquired immediately: 99% (738K immediate / 740K locks)
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Reduce your overall MySQL memory footprint for system stability
        Adjust your join queries to always utilize indexes
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        query_cache_size (> 32M)
        sort_buffer_size (> 2M)
        read_rnd_buffer_size (> 768K)
        join_buffer_size (> 1.0M, or always use indexes with joins)
    
    thank you to everyone who can help
     
  2. GaryT

    GaryT Well-Known Member

    Joined:
    May 19, 2010
    Messages:
    321
    Likes Received:
    3
    Trophy Points:
    16
    Well you have some heavily modified my.cnf considering you need help on this ? I can only assume you copied / pasted from another post rather than building one yourself.

    to make the changes what the tuner says replace with this:

     
  3. Kenpachi

    Kenpachi Member

    Joined:
    Nov 28, 2010
    Messages:
    22
    Likes Received:
    0
    Trophy Points:
    1
    Thanks garry, i got this config from eva000 from vbulletin but my license is expired now, so can't open any new ticket regarding that.. i'll post the result after changing the variables :)
     
  4. Kenpachi

    Kenpachi Member

    Joined:
    Nov 28, 2010
    Messages:
    22
    Likes Received:
    0
    Trophy Points:
    1
    Code:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.0.91-community-log
    [OK] Operating on 32-bit architecture with less than 2GB RAM
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 444M (Tables: 729)
    [--] Data in MEMORY tables: 1M (Tables: 6)
    [!!] Total fragmented tables: 20
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 43s (848 q [19.721 qps], 45 conn, TX: 5M, RX: 301K)
    [--] Reads / Writes: 68% / 32%
    [--] Total buffers: 122.0M global + 7.2M per thread (250 max threads)
    [!!] Maximum possible memory usage: 1.9G (187% of installed RAM)
    [OK] Slow queries: 0% (0/848)
    [OK] Highest usage of available connections: 2% (7/250)
    [OK] Key buffer size / total MyISAM indexes: 32.0M/214.1M
    [OK] Key buffer hit rate: 99.9% (5M cached / 6K reads)
    [OK] Query cache efficiency: 45.8% (237 cached / 518 selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 7% (7 temp sorts / 95 sorts)
    [!!] Joins performed without indexes: 2
    [OK] Temporary tables created on disk: 5% (1 on disk / 18 total)
    [OK] Thread cache hit rate: 84% (7 created / 45 connections)
    [OK] Table cache hit rate: 93% (93 open / 99 opened)
    [OK] Open file limit used: 1% (164/8K)
    [OK] Table locks acquired immediately: 99% (697 immediate / 698 locks)
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
        Reduce your overall MySQL memory footprint for system stability
        Adjust your join queries to always utilize indexes
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        join_buffer_size (> 2.0M, or always use indexes with joins)
    
     
  5. RCraft

    RCraft Well-Known Member

    Joined:
    Nov 7, 2010
    Messages:
    52
    Likes Received:
    0
    Trophy Points:
    6
    Ease off the caching a bit as you're risking an OOM (out of memory) with only having 1 Gb of ram.

    I would start off with something more vanilla. These settings are generally pretty good for about 1 Gb of ram:

    query_cache_limit = 1M
    query_cache_size = 32M

    key_buffer_size = 64M

    max_heap_table_size = 256M
    tmp_table_size = 256M

    thread_cache = 128

    The idea is to improve the speed of MySQL without allowing it to consume all of the available memory on the server (I've seen MySQL using 42% of the available memory by itself on a poorly optimized server).

    Run it for 24 hours and then check with mysqltuner again to see how it's performing. Also, run a free -m right after restarting mysql and before you start mysqltuner to see how much of your memory is available after a fresh start of mysql and after 24 hours of having it running.

    Note that optimization is no substitute for having enough resources. In some cases, it may be simply that you don't have enough resources and will need to add more ram or cpu power depending on the situation.
     
  6. Kenpachi

    Kenpachi Member

    Joined:
    Nov 28, 2010
    Messages:
    22
    Likes Received:
    0
    Trophy Points:
    1
    thanks Rcraft, i'll do the changes and post the results after 24Hrs :)
     
  7. Kenpachi

    Kenpachi Member

    Joined:
    Nov 28, 2010
    Messages:
    22
    Likes Received:
    0
    Trophy Points:
    1
    After applying the above settings .. the cpu load shoots upto 100.0 - 150.0 during peak hours .. i currently run a vB forum with 300 avg users and 500-600 during peak time. mysql started 10 hours ago so i'll post the report after some more time.

    btw, after chaning the values, here's the result from free -m

    Code:
    root@vps50 [~]# free -m
                 total       used       free     shared    buffers     cached
    Mem:          1024        784        239          0          0          0
    -/+ buffers/cache:        784        239
    Swap:            0          0          0
    
     
    #7 Kenpachi, Dec 2, 2010
    Last edited: Dec 2, 2010
  8. sodepdotvn

    sodepdotvn Registered

    Joined:
    Dec 2, 2010
    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    1
    i need help , cpu over 60%
     
  9. syslint

    syslint Well-Known Member

    Joined:
    Oct 9, 2006
    Messages:
    249
    Likes Received:
    6
    Trophy Points:
    18
    Location:
    India
    cPanel Access Level:
    Root Administrator
    Twitter:
    Hello,

    It seems you are on an openvz vps. This issue may be due to the following reason too ,
    - limitation in cpu cycles for your vps
    - overloaded hardware node.

    If so you may probably need to move to a dedicated server
     
  10. Kenpachi

    Kenpachi Member

    Joined:
    Nov 28, 2010
    Messages:
    22
    Likes Received:
    0
    Trophy Points:
    1
    Hi

    again today when i tried to run mysqltuner, to my surprise mysql started automatically during high load again .. its goes upto 70.0 - 100.0..so don't know what is causing this high load peak & that too not even in peak hours.

    should i ask my host regarding this ?

    well after looking what might be the possible reason behind the load spike, i found quite bit of slow quires which is causing mysql to load the cpu power. im in process of fixing those quires and will post the result of mysqltuner later.
     
    #10 Kenpachi, Dec 3, 2010
    Last edited: Dec 3, 2010
  11. Kenpachi

    Kenpachi Member

    Joined:
    Nov 28, 2010
    Messages:
    22
    Likes Received:
    0
    Trophy Points:
    1
    Here are the results from mysqltuner & tuning primer respectively.

    Code:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.0.91-community-log
    [OK] Operating on 32-bit architecture with less than 2GB RAM
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 685M (Tables: 454)
    [--] Data in MEMORY tables: 1M (Tables: 4)
    [!!] Total fragmented tables: 17
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 0h 38m 26s (3M q [44.879 qps], 73K conn, TX: 1B, RX: 1B)
    [--] Reads / Writes: 11% / 89%
    [--] Total buffers: 378.0M global + 7.2M per thread (200 max threads)
    [!!] Maximum possible memory usage: 1.8G (177% of installed RAM)
    [OK] Slow queries: 0% (479/3M)
    [OK] Highest usage of available connections: 77% (155/200)
    [OK] Key buffer size / total MyISAM indexes: 64.0M/336.5M
    [OK] Key buffer hit rate: 99.9% (108M cached / 85K reads)
    [OK] Query cache efficiency: 59.6% (517K cached / 868K selects)
    [!!] Query cache prunes per day: 2244
    [OK] Sorts requiring temporary tables: 4% (5K temp sorts / 140K sorts)
    [!!] Joins performed without indexes: 1136
    [OK] Temporary tables created on disk: 14% (2K on disk / 18K total)
    [OK] Thread cache hit rate: 99% (155 created / 73K connections)
    [OK] Table cache hit rate: 43% (1K open / 3K opened)
    [OK] Open file limit used: 22% (1K/8K)
    [OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Reduce your overall MySQL memory footprint for system stability
        Adjust your join queries to always utilize indexes
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        query_cache_size (> 48M)
        join_buffer_size (> 2.0M, or always use indexes with joins)


    Code:
    -- MYSQL PERFORMANCE TUNING PRIMER --
                 - By: Matthew Montgomery -
    
    MySQL Version 5.0.91-community-log i686
    
    ./tuning-primer.sh: line 497: bc: command not found
    ./tuning-primer.sh: line 498: bc: command not found
    ./tuning-primer.sh: line 499: bc: command not found
    ./tuning-primer.sh: line 500: bc: command not found
    ./tuning-primer.sh: line 501: bc: command not found
    ./tuning-primer.sh: line 502: bc: command not found
    Uptime =  days  hrs  min  sec
    Avg. qps = 44
    Total Questions = 3983117
    Threads Connected = 9
    
    Warning: Server has not been running for at least 48hrs.
    It may not be safe to use these recommendations
    
    To find out more information on how each of these
    runtime variables effects performance visit:
    http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
    Visit http://www.mysql.com/products/enterprise/advisors.html
    for info about MySQL's Enterprise Monitoring and Advisory Service
    
    SLOW QUERIES
    The slow query log is enabled.
    Current long_query_time = 10 sec.
    You have 479 out of 3983141 that take longer than 10 sec. to complete
    ./tuning-primer.sh: line 403: bc: command not found
    ./tuning-primer.sh: line 606: [: -gt: unary operator expected
    Your long_query_time seems to be fine
    
    BINARY UPDATE LOG
    The binary update log is NOT enabled.
    You will not be able to do point in time recovery
    See http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html
    
    WORKER THREADS
    Current thread_cache_size = 128
    Current threads_cached = 120
    Current threads_per_sec = 0
    Historic threads_per_sec = 0
    Your thread_cache_size is fine
    
    MAX CONNECTIONS
    Current max_connections = 200
    Current threads_connected = 9
    Historic max_used_connections = 155
    The number of used connections is 77% of the configured maximum.
    Your max_connections variable seems to be fine.
    
    No InnoDB Support Enabled!
    
    MEMORY USAGE
    ./tuning-primer.sh: line 1321: bc: command not found
    ./tuning-primer.sh: line 1322: bc: command not found
    ./tuning-primer.sh: line 1346: bc: command not found
    ./tuning-primer.sh: line 1349: bc: command not found
    ./tuning-primer.sh: line 1350: bc: command not found
    ./tuning-primer.sh: line 1352: bc: command not found
    ./tuning-primer.sh: line 1354: [: -gt: unary operator expected
    ./tuning-primer.sh: line 459: [: max_memoryHR: integer expression expected
    ./tuning-primer.sh: line 465: [: max_memoryHR: integer expression expected
    ./tuning-primer.sh: line 471: [: max_memoryHR: integer expression expected
    ./tuning-primer.sh: line 478: export: `=max_memoryHR': not a valid identifier
    Max Memory Ever Allocated :  bytes
    ./tuning-primer.sh: line 459: [: per_thread_buffersHR: integer expression expected
    ./tuning-primer.sh: line 465: [: per_thread_buffersHR: integer expression expected
    ./tuning-primer.sh: line 471: [: per_thread_buffersHR: integer expression expected
    ./tuning-primer.sh: line 478: export: `=per_thread_buffersHR': not a valid identifier
    Configured Max Per-thread Buffers :  bytes
    ./tuning-primer.sh: line 459: [: global_buffersHR: integer expression expected
    ./tuning-primer.sh: line 465: [: global_buffersHR: integer expression expected
    ./tuning-primer.sh: line 471: [: global_buffersHR: integer expression expected
    ./tuning-primer.sh: line 478: export: `=global_buffersHR': not a valid identifier
    Configured Max Global Buffers :  bytes
    ./tuning-primer.sh: line 459: [: total_memoryHR: integer expression expected
    ./tuning-primer.sh: line 465: [: total_memoryHR: integer expression expected
    ./tuning-primer.sh: line 471: [: total_memoryHR: integer expression expected
    ./tuning-primer.sh: line 478: export: `=total_memoryHR': not a valid identifier
    Configured Max Memory Limit :  bytes
    ./tuning-primer.sh: line 440: bc: command not found
    Physical Memory :  G
    Max memory limit seem to be within acceptable norms
    
    KEY BUFFER
    ./tuning-primer.sh: line 754: bc: command not found
    ./tuning-primer.sh: line 755: bc: command not found
    ./tuning-primer.sh: line 440: bc: command not found
    Current MyISAM index space =  M
    ./tuning-primer.sh: line 440: bc: command not found
    Current key_buffer_size =  M
    Key cache miss rate is 1 : 1268
    Key buffer free ratio =  %
    ./tuning-primer.sh: line 792: [: -le: unary operator expected
    ./tuning-primer.sh: line 796: [: -le: unary operator expected
    Your key_buffer_size seems to be fine
    
    QUERY CACHE
    ./tuning-primer.sh: line 827: bc: command not found
    ./tuning-primer.sh: line 828: bc: command not found
    Query cache is enabled
    ./tuning-primer.sh: line 440: bc: command not found
    Current query_cache_size =  M
    ./tuning-primer.sh: line 440: bc: command not found
    Current query_cache_used =  M
    ./tuning-primer.sh: line 440: bc: command not found
    Current query_cache_limit =  M
    Current Query cache Memory fill ratio =  %
    ./tuning-primer.sh: line 440: bc: command not found
    Current query_cache_min_res_unit =  K
    ./tuning-primer.sh: line 845: bc: command not found
    ./tuning-primer.sh: line 846: bc: command not found
    ./tuning-primer.sh: line 847: [: -gt: unary operator expected
    ./tuning-primer.sh: line 854: [: -le: unary operator expected
    ./tuning-primer.sh: line 858: [: -ge: unary operator expected
    MySQL won't cache query results that are larger than query_cache_limit in size
    
    SORT OPERATIONS
    ./tuning-primer.sh: line 440: bc: command not found
    Current sort_buffer_size =  M
    ./tuning-primer.sh: line 440: bc: command not found
    Current read_rnd_buffer_size =  M
    Sort buffer seems to be fine
    
    JOINS
    ./tuning-primer.sh: line 440: bc: command not found
    Current join_buffer_size =  M
    You have had 1137 queries where a join could not use an index properly
    You should enable "log-queries-not-using-indexes"
    Then look for non indexed joins in the slow query log.
    If you are unable to optimize your queries you may want to increase your
    join_buffer_size to accommodate larger joins in one pass.
    
    Note! This script will still suggest raising the join_buffer_size when
    ANY joins not using indexes are found.
    
    OPEN FILES LIMIT
    Current open_files_limit = 8210 files
    The open_files_limit should typically be set to at least 2x-3x
    that of table_cache if you have heavy MyISAM usage.
    Your open_files_limit value seems to be fine
    
    TABLE CACHE
    Current table_cache value = 4000 tables
    You have a total of 475 tables
    You have 1380 open tables.
    The table_cache value seems to be fine
    
    TEMP TABLES
    ./tuning-primer.sh: line 440: bc: command not found
    Current max_heap_table_size =  M
    ./tuning-primer.sh: line 440: bc: command not found
    Current tmp_table_size =  M
    Of 15732 temp tables, 14% were created on disk
    Created disk tmp tables ratio seems fine
    
    TABLE SCANS
    ./tuning-primer.sh: line 440: bc: command not found
    Current read_buffer_size =  M
    Current table scan ratio = 957 : 1
    read_buffer_size seems to be fine
    
    TABLE LOCKING
    Current Lock Wait ratio = 1 : 2405
    You may benefit from selective use of InnoDB.
    If you have long running SELECT's against MyISAM tables and perform
    frequent updates consider setting 'low_priority_updates=1'
     
  12. GaryT

    GaryT Well-Known Member

    Joined:
    May 19, 2010
    Messages:
    321
    Likes Received:
    3
    Trophy Points:
    16
    Assuming you stuck by the last config:

    I made changes to what Tuner says:

    Why you use this silly values ? I can only guess you copied from another or someone elses my.cnf ? :rolleyes:

    Thats just over the top, Try 10*

    Lower this, Your bassicly giving your mysql 4 times as much mem as your server has.



    Try: table_cache = 512

    Already as default on MySql so no need for these entries.
    This does not exist on linux, Also just cause your maching shows this, It does not mean your VPS is allocated to use them all, You will be limited.

    Now, See your MySql, You are asking for help, Yet in your config you have some settings which only people would add on the basis that they know what there doing, Take not offence on that ! , Now when making the my.cnf you start from the basics then work up, In which your doing but you already have such large values.

    Now with the changes made and what I suggest, You should think about trying the my.cnf going of the tuner and the server information you provided.

     
  13. Kenpachi

    Kenpachi Member

    Joined:
    Nov 28, 2010
    Messages:
    22
    Likes Received:
    0
    Trophy Points:
    1
    Those values are given by vbulletin staff member here as i run a similar forum with similar config.

    will do the changes, as im on learning process in managing the vps :)




    ok sir will replace the my.cnf file with this one and will wait for 2-3 days to see the result of mysqltuner output.
     
  14. GaryT

    GaryT Well-Known Member

    Joined:
    May 19, 2010
    Messages:
    321
    Likes Received:
    3
    Trophy Points:
    16
    You can post the results 24 hours after the change.

    Those original values are wrong for the VPS your on. Allocating to much and pointless things.

    You may not notice but if someone asks for mysql help on here, The staff usually leave it to the one thats helping and nobody tends to butt in as it can cause confusion, The best thing for you and your server it to keep everything in this thread and do nothing else, Then you and myself know whats been changed and what the affects of it were.
     
  15. Kenpachi

    Kenpachi Member

    Joined:
    Nov 28, 2010
    Messages:
    22
    Likes Received:
    0
    Trophy Points:
    1
    yea i can understand the prob of confusion :p

    well here are the results

    Mysqltuner

    Code:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.0.91-community-log
    [OK] Operating on 32-bit architecture with less than 2GB RAM
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 357M (Tables: 270)
    [--] Data in MEMORY tables: 1M (Tables: 2)
    [!!] Total fragmented tables: 14
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 0h 13m 24s (1M q [21.063 qps], 93K conn, TX: 742M, RX: 517M)
    [--] Reads / Writes: 55% / 45%
    [--] Total buffers: 138.0M global + 8.2M per thread (250 max threads)
    [!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
    [!!] Maximum possible memory usage: 2.1G (213% of installed RAM)
    [OK] Slow queries: 0% (116/1M)
    [OK] Highest usage of available connections: 22% (55/250)
    [OK] Key buffer size / total MyISAM indexes: 32.0M/167.1M
    [OK] Key buffer hit rate: 100.0% (1B cached / 134K reads)
    [OK] Query cache efficiency: 62.3% (674K cached / 1M selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 4% (8K temp sorts / 178K sorts)
    [!!] Joins performed without indexes: 1268
    [!!] Temporary tables created on disk: 32% (11K on disk / 35K total)
    [OK] Thread cache hit rate: 99% (55 created / 93K connections)
    [!!] Table cache hit rate: 19% (510 open / 2K opened)
    [OK] Open file limit used: 19% (790/4K)
    [OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
    
    -------- 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
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        join_buffer_size (> 3.0M, or always use indexes with joins)
        tmp_table_size (> 32M)
        max_heap_table_size (> 32M)
        table_cache (> 512)
    

    Tuning PRimer

    Code:
     -- MYSQL PERFORMANCE TUNING PRIMER --
                 - By: Matthew Montgomery -
    
    MySQL Version 5.0.91-community-log i686
    
    ./tuning-primer.sh: line 497: bc: command not found
    ./tuning-primer.sh: line 498: bc: command not found
    ./tuning-primer.sh: line 499: bc: command not found
    ./tuning-primer.sh: line 500: bc: command not found
    ./tuning-primer.sh: line 501: bc: command not found
    ./tuning-primer.sh: line 502: bc: command not found
    Uptime =  days  hrs  min  sec
    Avg. qps = 21
    Total Questions = 1838057
    Threads Connected = 4
    
    Warning: Server has not been running for at least 48hrs.
    It may not be safe to use these recommendations
    
    To find out more information on how each of these
    runtime variables effects performance visit:
    http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
    Visit http://www.mysql.com/products/enterprise/advisors.html
    for info about MySQL's Enterprise Monitoring and Advisory Service
    
    SLOW QUERIES
    The slow query log is enabled.
    Current long_query_time = 10 sec.
    You have 116 out of 1838092 that take longer than 10 sec. to complete
    ./tuning-primer.sh: line 403: bc: command not found
    ./tuning-primer.sh: line 606: [: -gt: unary operator expected
    Your long_query_time seems to be fine
    
    BINARY UPDATE LOG
    The binary update log is NOT enabled.
    You will not be able to do point in time recovery
    See http://dev.mysql.com/doc/refman/5.0/en/point-in-time-recovery.html
    
    WORKER THREADS
    Current thread_cache_size = 144
    Current threads_cached = 51
    Current threads_per_sec = 0
    Historic threads_per_sec = 0
    Your thread_cache_size is fine
    
    MAX CONNECTIONS
    Current max_connections = 250
    Current threads_connected = 4
    Historic max_used_connections = 55
    The number of used connections is 22% of the configured maximum.
    Your max_connections variable seems to be fine.
    
    No InnoDB Support Enabled!
    
    MEMORY USAGE
    ./tuning-primer.sh: line 1321: bc: command not found
    ./tuning-primer.sh: line 1322: bc: command not found
    ./tuning-primer.sh: line 1346: bc: command not found
    ./tuning-primer.sh: line 1349: bc: command not found
    ./tuning-primer.sh: line 1350: bc: command not found
    ./tuning-primer.sh: line 1352: bc: command not found
    ./tuning-primer.sh: line 1354: [: -gt: unary operator expected
    ./tuning-primer.sh: line 459: [: max_memoryHR: integer expression expected
    ./tuning-primer.sh: line 465: [: max_memoryHR: integer expression expected
    ./tuning-primer.sh: line 471: [: max_memoryHR: integer expression expected
    ./tuning-primer.sh: line 478: export: `=max_memoryHR': not a valid identifier
    Max Memory Ever Allocated :  bytes
    ./tuning-primer.sh: line 459: [: per_thread_buffersHR: integer expression expected
    ./tuning-primer.sh: line 465: [: per_thread_buffersHR: integer expression expected
    ./tuning-primer.sh: line 471: [: per_thread_buffersHR: integer expression expected
    ./tuning-primer.sh: line 478: export: `=per_thread_buffersHR': not a valid identifier
    Configured Max Per-thread Buffers :  bytes
    ./tuning-primer.sh: line 459: [: global_buffersHR: integer expression expected
    ./tuning-primer.sh: line 465: [: global_buffersHR: integer expression expected
    ./tuning-primer.sh: line 471: [: global_buffersHR: integer expression expected
    ./tuning-primer.sh: line 478: export: `=global_buffersHR': not a valid identifier
    Configured Max Global Buffers :  bytes
    ./tuning-primer.sh: line 459: [: total_memoryHR: integer expression expected
    ./tuning-primer.sh: line 465: [: total_memoryHR: integer expression expected
    ./tuning-primer.sh: line 471: [: total_memoryHR: integer expression expected
    ./tuning-primer.sh: line 478: export: `=total_memoryHR': not a valid identifier
    Configured Max Memory Limit :  bytes
    ./tuning-primer.sh: line 440: bc: command not found
    Physical Memory :  G
    Max memory limit seem to be within acceptable norms
    
    KEY BUFFER
    ./tuning-primer.sh: line 754: bc: command not found
    ./tuning-primer.sh: line 755: bc: command not found
    ./tuning-primer.sh: line 440: bc: command not found
    Current MyISAM index space =  M
    ./tuning-primer.sh: line 440: bc: command not found
    Current key_buffer_size =  M
    Key cache miss rate is 1 : 11655
    Key buffer free ratio =  %
    ./tuning-primer.sh: line 792: [: -le: unary operator expected
    Your key_buffer_size seems to be too high.
    Perhaps you can use these resources elsewhere
    
    QUERY CACHE
    ./tuning-primer.sh: line 827: bc: command not found
    ./tuning-primer.sh: line 828: bc: command not found
    Query cache is enabled
    ./tuning-primer.sh: line 440: bc: command not found
    Current query_cache_size =  M
    ./tuning-primer.sh: line 440: bc: command not found
    Current query_cache_used =  M
    ./tuning-primer.sh: line 440: bc: command not found
    Current query_cache_limit =  M
    Current Query cache Memory fill ratio =  %
    ./tuning-primer.sh: line 440: bc: command not found
    Current query_cache_min_res_unit =  K
    ./tuning-primer.sh: line 845: bc: command not found
    ./tuning-primer.sh: line 846: bc: command not found
    ./tuning-primer.sh: line 847: [: -gt: unary operator expected
    ./tuning-primer.sh: line 854: [: -le: unary operator expected
    MySQL won't cache query results that are larger than query_cache_limit in size
    
    SORT OPERATIONS
    ./tuning-primer.sh: line 440: bc: command not found
    Current sort_buffer_size =  M
    ./tuning-primer.sh: line 440: bc: command not found
    Current read_rnd_buffer_size =  M
    Sort buffer seems to be fine
    
    JOINS
    ./tuning-primer.sh: line 440: bc: command not found
    Current join_buffer_size =  M
    You have had 1268 queries where a join could not use an index properly
    You should enable "log-queries-not-using-indexes"
    Then look for non indexed joins in the slow query log.
    If you are unable to optimize your queries you may want to increase your
    join_buffer_size to accommodate larger joins in one pass.
    
    Note! This script will still suggest raising the join_buffer_size when
    ANY joins not using indexes are found.
    
    OPEN FILES LIMIT
    Current open_files_limit = 4096 files
    The open_files_limit should typically be set to at least 2x-3x
    that of table_cache if you have heavy MyISAM usage.
    Your open_files_limit value seems to be fine
    
    TABLE CACHE
    Current table_cache value = 512 tables
    You have a total of 289 tables
    You have 511 open tables.
    Current table_cache hit rate is 19%
    , while 99% of your table cache is in use
    You should probably increase your table_cache
    
    TEMP TABLES
    ./tuning-primer.sh: line 440: bc: command not found
    Current max_heap_table_size =  M
    ./tuning-primer.sh: line 440: bc: command not found
    Current tmp_table_size =  M
    Of 23858 temp tables, 32% were created on disk
    Perhaps you should increase your tmp_table_size and/or max_heap_table_size
    to reduce the number of disk-based temporary tables
    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.
    
    TABLE SCANS
    ./tuning-primer.sh: line 440: bc: command not found
    Current read_buffer_size =  M
    Current table scan ratio = 1043 : 1
    read_buffer_size seems to be fine
    
    TABLE LOCKING
    Current Lock Wait ratio = 1 : 564
    You may benefit from selective use of InnoDB.
    If you have long running SELECT's against MyISAM tables and perform
    frequent updates consider setting 'low_priority_updates=1'
    If you have a high concurrency of inserts on Dynamic row-length tables
    consider setting 'concurrent_insert=2'.
    
     
  16. GaryT

    GaryT Well-Known Member

    Joined:
    May 19, 2010
    Messages:
    321
    Likes Received:
    3
    Trophy Points:
    16
    Change to this then restart mysql.

     
  17. Kenpachi

    Kenpachi Member

    Joined:
    Nov 28, 2010
    Messages:
    22
    Likes Received:
    0
    Trophy Points:
    1
    Okay, will see the performance for another day how it goes
     
  18. Kenpachi

    Kenpachi Member

    Joined:
    Nov 28, 2010
    Messages:
    22
    Likes Received:
    0
    Trophy Points:
    1
    I've been monitoring the vps for last cpl days and mysqltuner is suggesting me to increase the tmp table size & join buffer, so i've adjusted it accordingly.

    here is the rerport after another day.

    Code:
    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.0.91-community-log
    [OK] Operating on 32-bit architecture with less than 2GB RAM
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 372M (Tables: 270)
    [--] Data in MEMORY tables: 1M (Tables: 2)
    [!!] Total fragmented tables: 4
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 2h 48m 16s (2M q [23.318 qps], 102K conn, TX: 2B, RX: 626M)
    [--] Reads / Writes: 53% / 47%
    [--] Total buffers: 362.0M global + 11.2M per thread (100 max threads)
    [!!] Maximum possible memory usage: 1.4G (144% of installed RAM)
    [OK] Slow queries: 0% (226/2M)
    [!!] Highest connection usage: 100%  (101/100)
    [OK] Key buffer size / total MyISAM indexes: 32.0M/166.8M
    [OK] Key buffer hit rate: 100.0% (3B cached / 167K reads)
    [OK] Query cache efficiency: 65.5% (893K cached / 1M selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 3% (8K temp sorts / 218K sorts)
    [!!] Joins performed without indexes: 1223
    [!!] Temporary tables created on disk: 39% (24K on disk / 61K total)
    [OK] Thread cache hit rate: 99% (101 created / 102K connections)
    [!!] Table cache hit rate: 14% (283 open / 1K opened)
    [OK] Open file limit used: 8% (336/4K)
    [OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Reduce your overall MySQL memory footprint for system stability
        Reduce or eliminate persistent connections to reduce connection usage
        Adjust your join queries to always utilize indexes
        Reduce your SELECT DISTINCT queries without LIMIT clauses
        Increase table_cache gradually to avoid file descriptor limits
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        max_connections (> 100)
        wait_timeout (< 20)
        interactive_timeout (< 10)
        join_buffer_size (> 6.0M, or always use indexes with joins)
        table_cache (> 640)
    
     
  19. polkocholo

    polkocholo Active Member

    Joined:
    Nov 22, 2010
    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    6
  20. Kenpachi

    Kenpachi Member

    Joined:
    Nov 28, 2010
    Messages:
    22
    Likes Received:
    0
    Trophy Points:
    1
    I recently got a email from my hosting provider abt high cpu usage.
    Code:
    Please be informed that your VPS is overloading our hardware node by constantly using more than 10% CPU power of the hardware node CPU POWER.
    
    You must take immediate action to reduce your server load as it is started to affect other accounts hosted in the same hardware node. 
    A quick check on server top processes shows that apache and mysql services are utilizing high CPU power.

    Looking for any kind of help regarding high load issue.
     
Loading...

Share This Page