MySQL Forums
Forum List  »  Optimizer & Parser

Re: CPU usage 99% - and query very slow - help
Posted by: robert eeeeee
Date: August 06, 2012 01:04PM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: CPU usage 99% - and query very slow - help
2695
August 06, 2012 01:04PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.