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.

Very slow mysql queryies

Discussion in 'Workarounds and Optimization' started by mlody69, Feb 9, 2016.

  1. mlody69

    mlody69 Registered

    Joined:
    Feb 9, 2016
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    World
    cPanel Access Level:
    Root Administrator
    Hello,
    I have a problem with very slow mysql insert queries. Inserting 10k record takes serveral minuts.
    My specification:

    • Intel Xeon E3 1231v3
      4 8- 3,4 GHz
    • 32 GB RAM
      DDR3 ECC 1600 MHz
    • 2x2TB SOFT
    • CENTOS 7.2 x86_64 standard – ns3032115
    • WHM 11.52.3 (build 1)
    • MySQL 5.6

      I'm using default apache,php and mysql settings. What is wrong?
     
    #1 mlody69, Feb 9, 2016
    Last edited by a moderator: Feb 9, 2016
  2. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,697
    Likes Received:
    657
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
  3. mlody69

    mlody69 Registered

    Joined:
    Feb 9, 2016
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    World
    cPanel Access Level:
    Root Administrator
    On my previous virtual server, this operation dealt up to several seconds.


    Code:
    Report Generated:
    Tue Feb 9 23:45:33 CET 2016
    
    -------------------------------------------------
    mysqltuner output
    -------------------------------------------------
    mysqltuner.pl [found]
    >>  MySQLTuner 1.6.4 - Major Hayden <major@mhtx.net>
    >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
    >>  Modified by George Liu (eva2000) at http://vbtechsupport.com/
    >>  Run with '--help' for additional options and output filtering
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.6.28
    [OK] Operating on 64-bit architecture
    
    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
    [--] Data in MyISAM tables: 274K (Tables: 18)
    [--] Data in InnoDB tables: 8M (Tables: 32)
    [!!] Total fragmented tables: 4
    
    -------- Security Recommendations  -------------------------------------------
    [OK] There are no anonymous accounts for any database users
    [OK] All database users have passwords assigned
    [!!] There is no basic password file list!
    
    -------- CVE Security Recommendations  ---------------------------------------
    [--] Skipped due to --cvefile option undefined
    
    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 5h 27m 54s (47K q [2.399 qps], 5K conn, TX: 511M, RX: 2M)
    [--] Reads / Writes: 30% / 70%
    [--] Binary logging is disabled
    [--] Total buffers: 169.0M global + 1.1M per thread (151 max threads)
    [OK] Maximum reached memory usage: 173.5M (0.54% of installed RAM)
    [OK] Maximum possible memory usage: 338.9M (1.06% of installed RAM)
    [OK] Slow queries: 0% (0/47K)
    [OK] Highest usage of available connections: 2% (4/151)
    [OK] Aborted connections: 1.07%  (56/5228)
    [!!] Query cache is disabled
    [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts)
    [OK] Temporary tables created on disk: 2% (51 on disk / 2K total)
    [OK] Thread cache hit rate: 99% (4 created / 5K connections)
    [OK] Table cache hit rate: 92% (138 open / 149 opened)
    [OK] Open file limit used: 0% (88/65K)
    [OK] Table locks acquired immediately: 100% (28K immediate / 28K locks)
    
    -------- MyISAM Metrics ------------------------------------------------------
    [!!] Key buffer used: 19.8% (1M used / 8M cache)
    [OK] Key buffer size / total MyISAM indexes: 8.0M/196.0K
    [OK] Read Key buffer hit rate: 97.0% (3K cached / 98 reads)
    [!!] Write Key buffer hit rate: 1.5% (1K cached / 1K writes)
    
    -------- InnoDB Metrics ------------------------------------------------------
    [--] InnoDB is enabled.
    [OK] InnoDB buffer pool / data size: 128.0M/8.1M
    [!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1).
    [!!] InnoDB Used buffer: 9.62% (788 used/ 8192 total)
    [OK] InnoDB Read buffer efficiency: 99.98% (4715933 hits/ 4716717 total)
    [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
    [OK] InnoDB log waits: 0.00% (0 waits / 19035 writes)
    
    -------- ThreadPool Metrics --------------------------------------------------
    [--] ThreadPool stat is disabled.
    
    -------- AriaDB Metrics ------------------------------------------------------
    [--] AriaDB is disabled.
    
    -------- TokuDB Metrics ------------------------------------------------------
    [--] TokuDB is disabled.
    
    -------- Galera Metrics ------------------------------------------------------
    [--] Galera is disabled.
    
    -------- Replication Metrics -------------------------------------------------
    [--] No replication slave(s) for this server.
    [--] This is a standalone server..
    
    -------- Recommendations -----------------------------------------------------
    General recommendations:
        Run OPTIMIZE TABLE to defragment tables for better performance
        MySQL started within last 24 hours - recommendations may be inaccurate
    Variables to adjust:
        query_cache_type (=1)
        innodb_buffer_pool_instances (=1)
    
    Report Complete:
    Tue Feb 9 23:45:34 CET 2016
    
     
  4. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,697
    Likes Received:
    657
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    It's generally a good idea to let MySQL run for at least 24 hours before running the tuner to obtain more accurate results. You can try adjusting the variables listed under the "Variables to adjust" results and then test again.

    Thank you.
     
  5. mlody69

    mlody69 Registered

    Joined:
    Feb 9, 2016
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    1
    Location:
    World
    cPanel Access Level:
    Root Administrator
    Hello, this problem is solved.
    I noticed that my sites do not work the for few minutes when traffic traffic is higher. How to check why? Which parameters should be increased?
     
  6. cPanelMichael

    cPanelMichael Forums Analyst
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    30,697
    Likes Received:
    657
    Trophy Points:
    113
    cPanel Access Level:
    Root Administrator
    There's no way to know which specific parameters to alter. I suggest contacting a qualified system administrator for assistance if you are unable to receive enough user-feedback:

    System Administration Services

    Thank you.
     
Loading...

Share This Page