Mysql cpu load to high

atlabs

• April 14, 2014 15:14

Hello, We have problem with mysql cpu load ( we don't know if is mysql bad config or bad queryies) VPS Configuration: 1 Cpu E5 2407 4 cores / 400 gb ssd raid 10 / 15 gb of ram cpu load varies under 100% - 200% mysql config: [mysqld] query_cache_size=512M table_cache=72324 query_cache_limit=3M query_cache_min_res_unit=1024 read_rnd_buffer_size=1M max_heap_table_size=1024M query_cache_type=1 port=3306 thread_cache_size=128 myisam_sort_buffer_size=8M key_buffer_size=256M net_buffer_length=8K sort_buffer_size=512K open_files_limit=50000 skip-external-locking tmp_table_size=1024M max_allowed_packet=1M read_buffer_size=256K join_buffer_size=3M default-storage-engine=MyISAM innodb_buffer_pool_size=128M table_definition_cache=16000
Tunner results: server2:/# ./usr/local/cpanel/bin/mysqltuner.pl >> MySQLTuner 1.2.0_1 - Major Hayden >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.5.35-cll [!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 3G (Tables: 18778) [--] Data in InnoDB tables: 732M (Tables: 5821) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [--] Data in MEMORY tables: 0B (Tables: 123) [!!] Total fragmented tables: 1923 -------- Performance Metrics ------------------------------------------------- [--] Up for: 1d 3h 3m 49s (80M q [102K qps], 105K conn, TX: 480B, RX: 12B) [--] Reads / Writes: 95% / 5% [--] Total buffers: 1.9G global + 4.9M per thread (151 max threads) [!!] Allocating > 2GB RAM on 32-bit systems can cause system instability [!!] Maximum possible memory usage: 2.6G (17% of installed RAM) [OK] Slow queries: 0% (17/44M) [OK] Highest usage of available connections: 47% (72/151) [OK] Key buffer size / total MyISAM indexes: 256.0M/821.2M [OK] Key buffer hit rate: 100.0% (172M cached / 55K reads) [OK] Query cache efficiency: 98.7% (43M cached / 43M selects) [!!] Query cache prunes per day: 1674273 [OK] Sorts requiring temporary tables: 0% (38 temp sorts / 80K sorts) [!!] Joins performed without indexes: 37809 [OK] Temporary tables created on disk: 5% (18K on disk / 336K total) [OK] Thread cache hit rate: 99% (72 created / 55K connections) [!!] Table cache hit rate: 7% (25K open / 347K opened) [OK] Open file limit used: 26% (37K/144K) [OK] Table locks acquired immediately: 99% (890K immediate / 891K locks) [!!] InnoDB data size / buffer pool: 732.0M/128.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Enable the slow query log to troubleshoot bad queries Increasing the query_cache size over 128M may reduce performance Adjust your join queries to always utilize indexes Increase table_cache gradually to avoid file descriptor limits Variables to adjust: query_cache_size (> 512M) [see warning above] join_buffer_size (> 3.0M, or always use indexes with joins) table_cache (> 72324) innodb_buffer_pool_size (>= 732M)
Thank you!

• 

  cPanelMichael

  • April 15, 2014 13:47

  Hello :) You may also want to use a command such as "mysqladmin processlist" when the CPU usage is at it's peak to see if you notice any particular database using an excessive amount of resources. Thank you.

  0
• 

  atlabs

  • April 15, 2014 15:05

  TOP results PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 1277 mysql 15 0 2182m 1.5g 6684 S 103.3 10.2 302:59.23 mysqld 23053 nobody 16 0 99576 71m 24m R 33.2 0.5 0:14.22 php 23068 nobody 16 0 74288 46m 23m R 30.9 0.3 0:17.93 php 22655 nobody 16 0 79388 54m 23m R 24.3 0.4 0:19.32 php 23243 nobody 16 0 94892 62m 23m R 24.3 0.4 0:13.05 php 23466 nobody 15 0 56652 16m 9432 S 22.9 0.1 0:00.69 php 21510 nobody 16 0 78864 50m 24m S 16.3 0.3 0:29.92 php 23467 nobody 15 0 56648 15m 8248 S 15.6 0.1 0:00.47 php 21827 nobody 16 0 91184 62m 23m R 11.6 0.4 0:23.87 php 23478 nobody 15 0 68788 39m 22m R 11.6 0.3 0:00.35 php 21971 nobody 15 0 77932 49m 24m S 7.0 0.3 0:28.38 php 21970 nobody 15 0 76160 48m 24m S 6.3 0.3 0:26.84 php 23475 nobody 17 0 55288 23m 17m S 6.0 0.2 0:00.18 php 21823 nobody 16 0 84156 55m 23m R 5.0 0.4 0:27.29 php 21829 nobody 15 0 79424 51m 23m S 5.0 0.3 0:32.90 php 23423 nobody 18 0 143m 14m 1960 S 4.3 0.1 0:00.20 httpd 23219 nobody 18 0 145m 15m 1976 S 3.3 0.1 0:01.05 httpd 23452 nobody 18 0 142m 13m 1944 S 1.3 0.1 0:00.04 httpd 22682 nobody 18 0 145m 15m 1984 S 1.0 0.1 0:02.08 httpd
+--------+-----------------+---------------------+-----------------+---------+-------+-------------------------------+------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +--------+-----------------+---------------------+-----------------+---------+-------+-------------------------------+------------------------------------------------------------------------+ | 188483 | tor_upload | ...:53676 | tor_mrex | Sleep | 15 | | 199105 | leechprotect | localhost | leechprotect | Sleep | 10459 | 334642 | infomanu_mrexv3 | localhost | infomanu_mrexv3 | Query | 0 | Waiting for query cache lock | SELECT cod_categorie,vizibil FROM mv_categorii WHERE cod="320" LIMIT 1 | | 334648 | gotica_gotica | localhost | gotica_mrexv3 | Sleep | 0 | | 334649 | brico_casa | localhost | brico_casa | Query | 0 | storing result in query cache | SELECT * FROM mv_produse WHERE cod="630" | | 334650 | caiete_mrex | localhost | caiete_mrexv3 | Sleep | 0 | | 334652 | evo_mrex | localhost | evo_mrex | Query | 0 | Waiting for query cache lock | SELECT cod FROM mv_produse WHERE cod_categorie="307" | | 334653 | root | localhost | | Query | 0 | | show processlist | +--------+-----------------+---------------------+-----------------+---------+-------+-------------------------------+------------------------------------------------------------------------+
  Any sugests?

  0
• 

  thinkbot

  • April 16, 2014 21:32

  adjust and restart query_cache_size = 150M table_definition_cache = 7000 key_buffer_size=1000M innodb_buffer_pool_size = 1000M remove those: net_buffer_length=8K query_cache_min_res_unit=1024 table_cache=72324 [COLOR="silver">- - - Updated - - - 1. make sure PHP Opcache like apc/eaccelerator is installed 2. table - query infomanu_mrexv3 - SELECT cod_categorie,vizibil FROM mv_categorii WHERE cod="320" LIMIT 1 make sure there is index on column cod brico_casa - SELECT * FROM mv_produse WHERE cod="630" make cure there is index on column cod evo_mrex - SELECT cod FROM mv_produse WHERE cod_categorie="307" make sure there is index on column cod_categorie

  0
• 

  atlabs

  • April 17, 2014 19:26

  # Query_time: 14.547095 Lock_time: 0.000087 Rows_sent: 177 Rows_examined: 411880 SET timestamp=1397552166; SELECT c.cod,c.denumire,c.ordine,( SELECT COUNT(p.cod) FROM mv_produse p WHERE CONCAT('|',p.cod_categorie,'|') LIKE CONCAT('%|',c.cod,'|%') AND p.vizibil=1) AS cateProduse FROM mv_categorii c WHERE vizibil=1;
  You are right - no indexes Thank you a lot!

  0

Please sign in to leave a comment.