Very slow mysql queryies

[mlody69]

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?

 

[cPanelMichael]

Hello

Have you been able to quickly insert that many records in the past on other servers? The first place to begin is often with a MySQL tuning application:

mysqlmymonlite.sh server stats gathering tool for cPanel Server

Thank you.

 

[mlody69]

On my previous virtual server, this operation dealt up to several seconds.

Report Generated:
Tue Feb 9 23:45:33 CET 2016

-------------------------------------------------
mysqltuner output
-------------------------------------------------
mysqltuner.pl [found]
>>  MySQLTuner 1.6.4 - Major Hayden <[email protected]>
>>  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

 

[cPanelMichael]

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.

 

[mlody69]

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.

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?

 

[cPanelMichael]

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?

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.