Re: CPU usage 99% - and query very slow - help
Hello Rick!
today my pid is loading to 400%...
There are same more info what you are asked;
##################### SHOW VARIABLES LIKE '%buffer%'
Variable_name Value
bulk_insert_buffer_size 8388608
innodb_buffer_pool_awe_mem_mb 0
innodb_buffer_pool_size 8388608
innodb_log_buffer_size 1048576
join_buffer_size 16777216
key_buffer_size 576716800
myisam_sort_buffer_size 16777216
net_buffer_length 16384
preload_buffer_size 32768
read_buffer_size 3145728
read_rnd_buffer_size 3145728
sort_buffer_size 5242880
##################### SHOW INDEX FROM table_fx
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment
table_fx 0 PRIMARY 1 id A 1932758 NULL NULL BTREE
table_fx 0 dupe_filter 1 dupe_hash A 1932758 NULL NULL BTREE
table_fx 1 search_TT 1 search_TT A 1932758 NULL NULL BTREE
table_fx 1 brand 1 brand A 32758 NULL NULL BTREE
table_fx 1 merchant 1 shop A 889 NULL NULL BTREE
table_fx 1 id_shop 1 id_shop A 40265 NULL NULL BTREE
table_fx 1 price 1 price A 77310 NULL NULL BTREE
table_fx 1 category1 1 category1 A 8785 NULL NULL BTREE
table_fx 1 category_2 1 category A 36467 NULL NULL BTREE
table_fx 1 paga 1 paga A 2 NULL NULL BTREE
table_fx 1 name 1 name A 1932758 NULL NULL BTREE
table_fx 1 name_2 1 name NULL 1 NULL NULL FULLTEXT
table_fx 1 id_shop_2 1 id_shop NULL 1 NULL NULL FULLTEXT
table_fx 1 category 1 categoria NULL 1 NULL NULL FULLTEXT
table_fx 1 search_TT_2 1 search_TT NULL 1 NULL NULL FULLTEXT
##################### SHOW TABLE STATUS LIKE 'table_fx'
Name Engine Version Row_format Rows Avg_row_length Data_length Max_data_length Index_length Data_free Auto_increment Create_time Update_time Check_time Collation Checksum Create_options Comment
table_fx MyISAM 10 Dynamic 1932758 807 1561112276 281474976710655 532156416 0 4927807 2012-08-10 17:31:18 2012-08-10 20:45:30 2012-08-10 17:34:32 latin1_swedish_ci NULL
##################### EXPLAIN SELECT 'table_fx'
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE NULL NULL NULL NULL NULL NULL NULL No tables used
##################### SSH#top
28179 mysql 20 0 908m 253m 3388 S 394.7 1.6 34:01.87 mysqld
4238 root 0 -20 0 0 0 S 0.3 0.0 34:43.84 loop0
31620 root 20 0 12932 1140 736 R 0.3 0.0 0:00.01 top
31625 prezzion 20 0 107m 9308 5664 S 0.3 0.1 0:00.01 php
##################### Main >> System Health >> Process Manager
Pid Owner Priority CPU % Memory % Command
28179 (Trace) (Kill) mysql 0 223 1.7 /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/fdx1.sense.com.pid --skip-external-locking
7 (Trace) (Kill) root - 82.4 0.0 [migration/1]
##################### ./mysqltuner.pl
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.95-community-log
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB +Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 702)
[!!] Total fragmented tables: 14
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 11m 4s (56K q [84.756 qps], 745 conn, TX: 50M, RX: 11M)
[--] Reads / Writes: 89% / 11%
[--] Total buffers: 874.0M global + 27.2M per thread (200 max threads)
[OK] Maximum possible memory usage: 6.2G (39% of installed RAM)
[OK] Slow queries: 0% (146/56K)
[OK] Highest usage of available connections: 11% (22/200)
[OK] Key buffer size / total MyISAM indexes: 550.0M/549.0M
[OK] Key buffer hit rate: 99.7% (29M cached / 84K reads)
[OK] Query cache efficiency: 82.1% (42K cached / 51K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 4% (89 temp sorts / 1K sorts)
[!!] Joins performed without indexes: 409
[OK] Temporary tables created on disk: 12% (296 on disk / 2K total)
[OK] Thread cache hit rate: 97% (22 created / 745 connections)
[OK] Table cache hit rate: 99% (750 open / 756 opened)
[OK] Open file limit used: 3% (1K/40K)
[OK] Table locks acquired immediately: 98% (22K immediate / 22K locks)
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Adjust your join queries to always utilize indexes
Variables to adjust:
join_buffer_size (> 16.0M, or always use indexes with joins)
##################### Same var/log/mysql_slow_query.log
# Query_time: 6 Lock_time: 0 Rows_sent: 1 Rows_examined: 1932758
SELECT COUNT(DISTINCT(name)) as resultcount, category FROM `table_fx` WHERE search_name LIKE '%flussante%' and search_name LIKE '%gel%' and search_name LIKE '%ersa%';
___________________________
SELECT DISTINCT(category) from table_fx WHERE CHAR_LENGTH(category) > 3 and search_name LIKE '%Asus%' and search_name LIKE '%EPC1015PD%' and search_name LIKE '%W009S%' GROUP BY category ORDER BY category ASC limit 120;
_______________________
# Query_time: 10 Lock_time: 4 Rows_sent: 15 Rows_examined: 297777
SELECT DISTINCT(name),id AS IID, price ,image_url,shop, brand , paga, cat, cat1, description FROM table_fx where shop="as1" or shop="as2" or shop=as3" or shop="as4" or shop="as5" or shop="as6" or shop="as7" or shop="as8" GROUP BY name ORDER BY paga DESC limit 15;
_______________________
# Time: 120806 19:52:22
# Query_time: 7 Lock_time: 0 Rows_sent: 1 Rows_examined: 1932758
SELECT COUNT(DISTINCT(name)) as resultcount, category FROM `table_fx` WHERE search_name LIKE '%Kaspersky%' and search_name LIKE '%Lab%';
_____________________________
# Query_time: 8 Lock_time: 2 Rows_sent: 15 Rows_examined: 884
SELECT * , MIN( price ) AS minPrice, MAX( price ) AS maxPrice, COUNT( id ) AS numshop FROM `table_fx` WHERE search_name LIKE '%CARD%' and search_name LIKE '%READER%' GROUP BY name ORDER BY paga DESC LIMIT 0,15;
_____________________________________________________
Thanks, i'm writing from italy, there are a lot of information on this topic