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.

sql optimization help needed

Discussion in 'Workarounds and Optimization' started by zkhanseo, May 25, 2014.

  1. zkhanseo

    zkhanseo Registered

    Joined:
    May 25, 2014
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Forgive me for being a non-admin kind of guy.

    Code:
     >>  MySQLTuner 1.3.0 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
     >>  Run with '--help' for additional options and output filtering
    [OK] Currently running supported MySQL version 5.5.36-cll
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 85M (Tables: 26)
    [--] Data in InnoDB tables: 5G (Tables: 443)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [--] Data in MEMORY tables: 975K (Tables: 17)
    [!!] Total fragmented tables: 33
    
    -------- Security Recommendations  -------------------------------------------
    [OK] All database users have passwords assigned
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 1d 4h 17m 34s (13M q [131.096 qps], 4K conn, TX: 8B, RX: 4B)
    [--] Reads / Writes: 59% / 41%
    [--] Total buffers: 1.2G global + 24.4M per thread (3056 max threads)
    [!!] Maximum possible memory usage: 74.0G (970% of installed RAM)
    [OK] Slow queries: 0% (624/13M)
    [OK] Highest usage of available connections: 0% (19/3056)
    [OK] Key buffer size / total MyISAM indexes: 1.0G/99.9M
    [!!] Key buffer hit rate: 7.6% (110K cached / 102K reads)
    [!!] Query cache efficiency: 2.3% (185K cached / 8M selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 2% (566 temp sorts / 26K sorts)
    [!!] Temporary tables created on disk: 28% (9K on disk / 33K total)
    [OK] Thread cache hit rate: 99% (19 created / 4K connections)
    [OK] Table cache hit rate: 24% (626 open / 2K opened)
    [OK] Open file limit used: 0% (97/15K)
    [OK] Table locks acquired immediately: 100% (13M immediate / 13M locks)
    [!!] InnoDB  buffer pool / data size: 128.0M/5.2G
    [OK] InnoDB log waits: 0
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        Reduce your overall MySQL memory footprint for system stability
        Enable the slow query log to troubleshoot bad queries
        When making adjustments, make tmp_table_size/max_heap_table_size equal
        Reduce your SELECT DISTINCT queries without LIMIT clauses
    Variables to adjust:
      *** MySQL's maximum memory usage is dangerously high ***
      *** Add RAM before increasing MySQL buffer variables ***
        query_cache_limit (> 1M, or use smaller result sets)
        tmp_table_size (> 16M)
        max_heap_table_size (> 16M)
        innodb_buffer_pool_size (>= 5G)
    
    
    It is a Intel Xeon 5506 (Quad Core) with
    8 GB DDR2 Memory.

    Please suggest how to fix the above points as the database is really slow, when trying to search for any product.
     
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    651
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    Could you provide some more details about the database itself? For instance, how large is the database, and what's your server load and disk I/O (iostat) when you search for products?

    Thank you.
     
  3. zkhanseo

    zkhanseo Registered

    Joined:
    May 25, 2014
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    the database contains 1 million products.

    May I know how to check server load & iostat in WHM ?

    Thanks in advance.
     
    #3 zkhanseo, May 26, 2014
    Last edited by a moderator: May 26, 2014
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,678
    Likes Received:
    651
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    This thread will help you with commands/utilities on checking the load and disk activity:

    Troubleshooting High Loads On Linux Systems

    Thank you.
     
  5. zkhanseo

    zkhanseo Registered

    Joined:
    May 25, 2014
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Website Owner
    Code:
    # sar -d
    Linux 2.6.32-431.17.1.el6.x86_64 (server1.cheapesale.com)       05/27/2014      _x86_64_        (4 CPU)
    
    12:00:01 AM       DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util
    12:10:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    12:10:01 AM    dev8-0      3.27      8.65    177.57     57.01      0.03     10.63      5.53      1.81
    12:20:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    12:20:01 AM    dev8-0      3.30      3.01    178.47     54.96      0.03      9.17      5.46      1.80
    12:30:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    12:30:01 AM    dev8-0      3.40      2.52    172.46     51.54      0.03     10.20      5.68      1.93
    12:40:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    12:40:01 AM    dev8-0      4.41     38.17    182.25     49.94      0.04     10.10      5.60      2.47
    12:50:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    12:50:01 AM    dev8-0      4.95     25.79    200.78     45.77      0.06     12.52      5.02      2.48
    01:00:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    01:00:01 AM    dev8-0      7.25     24.09    182.12     28.44      0.12     16.84      5.80      4.21
    01:10:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    01:10:01 AM    dev8-0     16.15     40.94    467.20     31.46      0.31     19.23      5.38      8.68
    
    01:10:01 AM       DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util
    01:20:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    01:20:01 AM    dev8-0      6.01     12.51    160.93     28.84      0.10     16.03      5.29      3.18
    01:30:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    01:30:01 AM    dev8-0      4.39     40.32    106.15     33.34      0.07     16.21      4.87      2.14
    01:40:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    01:40:01 AM    dev8-0      2.67      2.25     80.61     30.98      0.04     14.31      3.96      1.06
    01:50:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    01:50:01 AM    dev8-0      3.07      2.84     80.78     27.28      0.05     15.93      4.26      1.31
    02:00:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    02:00:01 AM    dev8-0      3.27      2.65     84.73     26.70      0.07     19.88      4.98      1.63
    02:10:02 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    02:10:02 AM    dev8-0      3.46     15.49     88.64     30.06      0.06     17.70      5.90      2.05
    02:20:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    02:20:01 AM    dev8-0      4.65     22.97    115.56     29.76      0.09     18.47      4.99      2.32
    
    02:20:01 AM       DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util
    02:30:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    02:30:01 AM    dev8-0      2.89      1.22     78.69     27.65      0.06     19.40      4.80      1.39
    02:40:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    02:40:01 AM    dev8-0      4.01      3.85    103.72     26.80      0.07     17.56      6.14      2.46
    02:50:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    02:50:01 AM    dev8-0      3.12      2.98     81.98     27.21      0.08     24.81      6.39      1.99
    03:00:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    03:00:01 AM    dev8-0      3.31      9.70     83.91     28.27      0.08     22.66      5.09      1.69
    03:10:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    03:10:01 AM    dev8-0      3.09      1.11     83.38     27.36      0.06     19.18      4.91      1.52
    03:20:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    03:20:01 AM    dev8-0      3.03      5.25     81.00     28.51      0.05     17.49      5.20      1.57
    03:30:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    03:30:01 AM    dev8-0     35.19    661.78    274.60     26.61      0.16      4.64      1.43      5.04
    
    03:30:01 AM       DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util
    03:40:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    03:40:01 AM    dev8-0      3.11      1.43     90.41     29.54      0.05     14.66      5.70      1.77
    03:50:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    03:50:01 AM    dev8-0      2.82      1.08     81.90     29.42      0.04     13.53      4.71      1.33
    04:00:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    04:00:01 AM    dev8-0      3.13      1.06     96.21     31.04      0.04     13.66      4.72      1.48
    04:10:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    04:10:01 AM    dev8-0      2.51      1.27    131.09     52.73      0.04     16.67      4.69      1.18
    04:20:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    04:20:01 AM    dev8-0      3.40      8.87    132.81     41.61      0.06     17.02      4.36      1.48
    04:30:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    04:30:01 AM    dev8-0      3.99      9.06     97.13     26.62      0.10     23.92      5.32      2.12
    04:40:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    04:40:01 AM    dev8-0      5.58     78.11    100.57     32.01      0.14     25.66      5.06      2.82
    
    04:40:01 AM       DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util
    04:50:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    04:50:01 AM    dev8-0      4.25      6.74    105.14     26.30      0.10     23.54      4.87      2.07
    05:00:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    05:00:01 AM    dev8-0    131.22   4097.33    289.79     33.43      0.41      3.09      1.17     15.32
    05:10:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    05:10:01 AM    dev8-0      4.63     74.57     97.75     37.21      0.13     28.17      4.97      2.30
    05:20:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    05:20:01 AM    dev8-0      3.88     10.50    101.85     28.93      0.11     28.48      5.19      2.02
    05:30:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    05:30:01 AM    dev8-0      2.88     35.99    169.02     71.29      0.03     11.92      4.67      1.34
    05:40:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    05:40:01 AM    dev8-0     19.58   3520.78    146.79    187.31      0.08      3.84      2.02      3.95
    05:50:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    05:50:01 AM    dev8-0      2.50      9.71    159.22     67.53      0.02      9.70      4.77      1.19
    
    05:50:01 AM       DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util
    06:00:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    06:00:01 AM    dev8-0      2.65      4.78    173.24     67.07      0.04     15.32      5.40      1.43
    06:10:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    06:10:01 AM    dev8-0      2.31      0.64    155.56     67.54      0.03     13.77      4.81      1.11
    06:20:02 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    06:20:02 AM    dev8-0      3.53      5.62    184.56     53.91      0.03      9.92      4.77      1.68
    06:30:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    06:30:01 AM    dev8-0      2.89      3.98    149.13     53.00      0.03      9.91      4.83      1.39
    06:40:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    06:40:01 AM    dev8-0      2.67      6.38    147.77     57.67      0.03      9.58      4.68      1.25
    06:50:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    06:50:01 AM    dev8-0      2.87      6.13    154.28     55.81      0.03     11.33      4.82      1.39
    07:00:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    07:00:01 AM    dev8-0      3.05      2.14    164.51     54.56      0.02      8.10      5.00      1.53
    
    07:00:01 AM       DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util
    07:10:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    07:10:01 AM    dev8-0      4.42     11.55    278.78     65.70      0.04      8.82      5.68      2.51
    07:20:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    07:20:01 AM    dev8-0      5.19     44.36    393.67     84.44      0.06     11.24      5.16      2.68
    07:30:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    07:30:01 AM    dev8-0      3.76     11.51    124.11     36.07      0.06     16.42      4.91      1.85
    07:40:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    07:40:01 AM    dev8-0      2.99      3.28     98.76     34.18      0.04     14.33      4.53      1.35
    07:50:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    07:50:01 AM    dev8-0      3.60     16.82    110.92     35.45      0.07     19.04      5.07      1.83
    08:00:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    08:00:01 AM    dev8-0      2.97      9.05     95.83     35.26      0.04     14.94      5.12      1.52
    08:10:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    08:10:01 AM    dev8-0      3.48      6.48    104.90     31.99      0.07     20.17      5.68      1.98
    
    08:10:01 AM       DEV       tps  rd_sec/s  wr_sec/s  avgrq-sz  avgqu-sz     await     svctm     %util
    08:20:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    08:20:01 AM    dev8-0      9.18     54.94    218.05     29.75      0.16     17.26      6.29      5.77
    08:30:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    08:30:01 AM    dev8-0      7.44     14.10    195.63     28.19      0.13     17.15      6.06      4.51
    08:40:02 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    08:40:02 AM    dev8-0      6.52    182.60    122.95     46.86      0.06      8.62      3.77      2.46
    08:50:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    08:50:01 AM    dev8-0      2.67      1.44    137.80     52.08      0.04     14.52      5.88      1.57
    09:00:01 AM   dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    09:00:01 AM    dev8-0      3.26      7.40    110.00     36.04      0.06     17.30      5.91      1.92
    Average:      dev8-16      0.00      0.00      0.00      0.00      0.00      0.00      0.00      0.00
    Average:       dev8-0      7.30    170.19    147.87     43.56      0.07     10.27      3.37      2.46
    root@server1 [~]#
     
  6. storminternet

    storminternet Well-Known Member

    Joined:
    Nov 2, 2011
    Messages:
    462
    Likes Received:
    0
    Trophy Points:
    16
    cPanel Access Level:
    Root Administrator
    Your database has one million products that means it should be large in size. While /searching/creating records it may use disk that lead to high disk I/O. I suggest you to use tuning-primer' a third party script to optimize your mysql database.
    tuning-primer has support for mysql 5.5 and 5.6
     
Loading...

Share This Page