sorting - High Mysql Load - CPU 360% -
i having high mysql cpu usage , on server intel(r) xeon(r) cpu e5520 @ 2.27ghz 16 core of processors , 16 g.b ram running on centos 5.4
the load average server 500 - 900 , that's high numbers
here information out
mysql configuration file /etc/my.cnf
[mysqld] local-infile=0 safe-show-database skip-locking skip-innodb skip-networking old_passwords back_log = 50 skip-innodb max_connections = 40000 key_buffer_size = 640m myisam_sort_buffer_size = 256m myisam_max_sort_file_size = 2048m join_buffer_size = 3m read_buffer_size = 3m sort_buffer_size = 3m table_cache = 8000 thread_cache_size = 1024 wait_timeout = 300 interactive_timeout = 600 connect_timeout = 10 tmp_table_size = 1024m max_heap_table_size = 1024m max_allowed_packet = 128m net_buffer_length = 16384 max_connect_errors = 100000 long_query_time=0 thread_concurrency = 32 concurrent_insert = 2 table_lock_wait_timeout = 30 read_rnd_buffer_size = 4m bulk_insert_buffer_size = 16m query_cache_limit = 4m query_cache_size = 128m query_cache_type = 1 query_prealloc_size = 262144 query_alloc_block_size = 65536 range_alloc_block_size = 4096 transaction_alloc_block_size = 8192 transaction_prealloc_size = 4096 default-storage-engine = myisam max_write_lock_count = 8 tmpdir=/mysqltmp set-variable=long_query_time=1 log-slow-queries=/var/log/mysql/log-slow-queries.log [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid nice = -10 open_files_limit = 8192 [mysqldump] quick max_allowed_packet = 128m [myisamchk] key_buffer_size = 640m sort_buffer_size = 64m read_buffer_size = 16m write_buffer_size = 16m [mysqlhotcopy] interactive-timeout ===============note delete queries because cannot post here more 30000 character=====================================
show full processlist; 393786 site_dtl localhost site_dtl query 1 locked update dle_post set news_read = news_read +1 id = '3657' 402437 root localhost none sleep 496 --- --- 409374 root localhost none sleep 180 --- --- 411150 tgofmovi_home localhost tgofmovi_home sleep 40 --- --- 411230 tgofmovi_home localhost tgofmovi_home sleep 36 --- --- 411401 site_dtl localhost site_dtl query 1 locked select * dle_post approve = '1' , category in ( 80 ) order date desc limit 0 , 10 411410 site_dtl localhost site_dtl query 0 locked select * dle_post approve = '1' , category in ( 80 ) order date desc limit 0 , 10 411430 site_dtl localhost site_dtl query 0 locked select * dle_post approve='1' , category in(1,2,7,6,8) , id not in (0) , date >= ' 411433 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(80) order date desc limit 0,10 411439 site_dtl localhost site_dtl query 0 locked select * dle_post approve='1' , category in(80) order date desc limit 0,10 411443 site_dtl localhost site_dtl query 0 locked select * dle_post approve='1' , category in(80) order date desc limit 0,10 411444 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(91) , id not in (0) , date >= '2010-11 411490 site_dtl localhost site_dtl query 2 sorting result select * dle_post approve='1' , category in(80) order date desc limit 0,10 411491 site_dtl localhost site_dtl query 2 sorting result select * dle_post approve='1' , category in(80) order date desc limit 0,10 411498 site_dtl localhost site_dtl query 0 locked select * dle_post approve='1' , category in(80) order date desc limit 0,10 411509 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(91) , id not in (0) , date >= '2010-11 411511 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(80) order date desc limit 0,10 411512 site_dtl localhost site_dtl query 0 locked select * dle_post approve='1' , category in(80) order date desc limit 0,10 411513 site_dtl localhost site_dtl query 0 locked select * dle_post approve='1' , category in(80) order date desc limit 0,10 411514 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(80) order date desc limit 0,10 411515 site_dtl localhost site_dtl query 0 locked select * dle_post approve='1' , category in(80) order date desc limit 0,10 411526 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(80) order date desc limit 0,10 411527 site_dtl localhost site_dtl query 0 locked select * dle_post 411541 site_dtl localhost site_dtl query 2 sorting result select * dle_post approve='1' , category in(80) order date desc limit 0,10 411543 site_dtl localhost site_dtl query 0 locked select * dle_post approve='1' , category in(1,2,7,6,8) , id not in (0) , date >= ' 411544 site_dtl localhost site_dtl query 0 locked select * dle_post approve='1' , category in(1,2,7,6,8) , id not in (0) , date >= ' 411545 site_dtl localhost site_dtl query 2 sorting result select * dle_post approve='1' , category in(80) order date desc limit 0,10 411547 site_dtl localhost site_dtl query 0 locked select * dle_post approve='1' , category in(1,2,7,6,8) , id not in (0) , date >= ' 411548 site_dtl localhost site_dtl query 2 sorting result select * dle_post approve='1' , category in(80) order date desc limit 0,10 411549 site_dtl localhost site_dtl query 2 sorting result select * dle_post approve='1' , category in(80) order date desc limit 0,10 411565 site_dtl localhost site_dtl query 2 sorting result select * dle_post approve='1' , category in(80) order date desc limit 0,10 approve='1' , category in(91) , id not in (0) , date >= '2010-11 411618 site_dtl localhost site_dtl query 0 locked select * dle_post approve='1' , category in(91) , id not in (0) , date >= '2010-11 411619 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(80) order date desc limit 0,10 411620 site_dtl localhost site_dtl query 0 locked select * dle_post approve='1' , category in(91) , id not in (0) , date >= '2010-11 411621 site_dtl localhost site_dtl query 0 locked select * dle_post approve='1' , category in(91) , id not in (0) , date >= '2010-11 411622 site_dtl localhost site_dtl query 0 locked select * dle_post approve='1' , category in(91) , id not in (0) , date >= '2010-11 411623 site_dtl localhost site_dtl query 0 locked select * dle_post approve='1' , category in(91) , id not in (0) , date >= '2010-11 411624 site_dtl localhost site_dtl query 0 locked select * dle_post approve='1' , category in(91) , id not in (0) , date >= '2010-11 411625 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(91) , id not in (0) , date >= '2010-11 411796 site_dtl localhost site_dtl query 1 copying tmp table select id, title, date, alt_name, category, flag dle_post approve='1' order rand() lim 411797 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(37,43,54,55,56,60,61,62,63,64,69,70,71,46,5 411798 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(37,43,54,55,56,60,61,62,63,64,69,70,71,46,5 411800 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(65) , id not in (0) , date >= '2010-11 411801 site_dtl localhost site_dtl query 1 copying tmp table select id, title, date, alt_name, category, flag dle_post approve='1' order rand() lim 411802 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(65) , id not in (0) , date >= '2010-11 411803 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(44,54,55,56,60,61,62,63,64,69,70,71) , id 411804 site_dtl localhost site_dtl query 1 locked select id, title, date, alt_name, category, flag dle_post approve='1' order date desc 411807 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(65) , id not in (0) , date >= '2010-11 411808 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(37) , id not in (0) , date >= '2010-11 411809 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(65) , id not in (0) , date >= '2010-11 411811 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(65) , id not in (0) , date >= '2010-11 411812 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(65) , id not in (0) , date >= '2010-11 411813 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(65) , id not in (0) , date >= '2010-11 411814 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(44,54,55,56,60,61,62,63,64,69,70,71) , id 411818 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(44,54,55,56,60,61,62,63,64,69,70,71) , id 411819 site_dtl localhost site_dtl query 1 locked select id, title, date, alt_name, category, flag dle_post approve='1' order date desc 411820 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(65) , id not in (0) , date >= '2010-11 411821 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(65) , id not in (0) , date >= '2010-11 411822 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(65) , id not in (0) , date >= '2010-11 411823 site_dtl localhost site_dtl query 1 copying tmp table select id, title, date, alt_name, category, flag dle_post approve='1' order rand() lim 411825 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(44,54,55,56,60,61,62,63,64,69,70,71) , id 411826 site_dtl localhost site_dtl query 1 copying tmp table select id, title, date, alt_name, category, flag dle_post approve='1' order rand() lim 411827 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(65) , id not in (0) , date >= '2010-11 411828 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(65) , id not in (0) , date >= '2010-11 411829 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(65) , id not in (0) , date >= '2010-11 411830 site_dtl localhost site_dtl query 1 copying tmp table select id, title, date, alt_name, category, flag dle_post approve='1' order rand() lim 411831 site_dtl localhost site_dtl query 1 locked select id, title, date, alt_name, category, flag dle_post approve='1' order date desc 411833 site_dtl localhost site_dtl query 1 locked select id, title, date, alt_name, category, flag dle_post approve='1' order date desc 411834 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(48) , id not in (0) , date >= '2010-11 411836 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(44,54,55,56,60,61,62,63,64,69,70,71) , id 411837 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(44,54,55,56,60,61,62,63,64,69,70,71) , id 411838 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(44,54,55,56,60,61,62,63,64,69,70,71) , id 411841 site_dtl localhost site_dtl query 1 copying tmp table select id, title, date, alt_name, category, flag dle_post approve='1' order rand() lim 411842 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(44,54,55,56,60,61,62,63,64,69,70,71) , id 411843 site_dtl localhost site_dtl query 1 copying tmp table select id, title, date, alt_name, category, flag dle_post approve='1' order rand() lim 411845 site_dtl localhost site_dtl query 1 locked select id, title, date, alt_name, category, flag dle_post approve='1' order date desc 411855 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(37) , id not in (0) , date >= '2010-11 411856 site_dtl localhost site_dtl query 1 locked select id, title, date, alt_name, category, flag dle_post approve='1' , date >= '2010- 411857 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(37) , id not in (0) , date >= '2010-11 411858 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(37) , id not in (0) , date >= '2010-11 411859 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(37) , id not in (0) , date >= '2010-11 411860 site_dtl localhost site_dtl query 1 locked select id, title, date, alt_name, category, flag dle_post approve='1' order date desc 411861 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(37) , id not in (0) , date >= '2010-11 411862 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(37) , id not in (0) , date >= '2010-11 411863 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(37) , id not in (0) , date >= '2010-11 411864 site_dtl localhost site_dtl query 1 locked select id, title, date, alt_name, category, flag dle_post approve='1' order date desc 411865 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(37) , id not in (0) , date >= '2010-11 411866 site_dtl localhost site_dtl query 1 locked select id, title, date, alt_name, category, flag dle_post approve='1' order date desc 411867 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(37) , id not in (0) , date >= '2010-11 411868 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(37) , id not in (0) , date >= '2010-11 411869 site_dtl localhost site_dtl query 2 sorting result select id, title, date, alt_name, category, flag dle_post approve='1' order date desc 411870 site_dtl localhost site_dtl query 1 sending data select count(*) count dle_post category regexp '[[:<:]](81)[[:>:]]' , approve , da 411871 site_dtl localhost site_dtl query 2 sorting result select id, title, date, alt_name, category, flag dle_post approve='1' order date desc 411872 site_dtl localhost site_dtl query 2 sorting result select id, title, date, alt_name, category, flag dle_post approve='1' order date desc 411873 site_dtl localhost site_dtl query 1 copying tmp table select id, title, date, alt_name, category, flag dle_post approve='1' order rand() lim 411874 site_dtl localhost site_dtl query 1 copying tmp table select id, title, date, alt_name, category, flag dle_post approve='1' order rand() lim 411875 site_dtl localhost site_dtl query 1 copying tmp table select id, title, date, alt_name, category, flag dle_post approve='1' order rand() lim 411876 site_dtl localhost site_dtl query 1 copying tmp table select id, title, date, alt_name, category, flag dle_post approve='1' order rand() lim 411877 site_dtl localhost site_dtl query 1 copying tmp table select id, title, date, alt_name, category, flag dle_post approve='1' order rand() lim 411878 site_dtl localhost site_dtl query 1 copying tmp table select id, title, date, alt_name, category, flag dle_post approve='1' order rand() lim 411879 site_dtl localhost site_dtl query 1 sending data select count(*) count dle_post category regexp '[[:<:]](44|80|81|54|55|60|61|62|63|64| 411890 site_dtl localhost site_dtl query 1 sending data select count(*) count dle_post category regexp '[[:<:]](44|80|81|54|55|60|61|62|63|64| 411891 site_dtl localhost site_dtl query 1 locked select id, title, date, alt_name, category, flag dle_post approve='1' , date >= '2010- 411892 site_dtl localhost site_dtl query 1 copying tmp table select id, title, date, alt_name, category, flag dle_post approve='1' order rand() lim 411893 site_dtl localhost site_dtl query 1 copying tmp table select id, title, date, alt_name, category, flag dle_post approve='1' order rand() lim 411894 site_dtl localhost site_dtl query 1 sending data select count(*) count dle_post category regexp '[[:<:]](44|80|81|54|55|60|61|62|63|64| 411895 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(44,54,55,56,60,61,62,63,64,69,70,71) , id 411896 site_dtl localhost site_dtl query 1 sending data select count(*) count dle_post category regexp '[[:<:]](36|37|43|44|80|81|54|55|60|61| 411897 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(44,54,55,56,60,61,62,63,64,69,70,71) , id 411898 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(44,54,55,56,60,61,62,63,64,69,70,71) , id 411899 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(44,54,55,56,60,61,62,63,64,69,70,71) , id 411900 site_dtl localhost site_dtl query 1 locked select * dle_post approve='1' , category in(44,54,55,56,60,61,62,63,64,69,70,71) , id 411901 site_dtl localhost site_dtl query 2 sorting result select id, title, date, alt_name, category, flag dle_post approve='1' order date desc 411902 site_dtl localhost site_dtl query 1 locked select id, title, date, alt_name, category, flag dle_post approve='1' order date desc 411903 site_dtl localhost site_dtl query 1 locked select id, title, date, alt_name, category, flag dle_post approve='1' order date desc 411904 site_dtl localhost site_dtl query 1 locked select id, title, date, alt_name, category, flag dle_post approve='1' order date desc 411905 site_dtl localhost site_dtl query 1 locked select id, title, date, alt_name, category, flag dle_post approve='1' order date desc 411907 site_dtl localhost site_dtl query 1 locked select id, title, date, alt_name, category, flag dle_post approve='1' order date desc 411908 site_dtl localhost site_dtl query 2 sorting result select id, autor, date, short_story, substring(full_story, 1, 15) full_story, xfields, title, cat 411909 site_dtl localhost site_dtl query 1 locked select id, title, date, alt_name, category, flag dle_post approve='1' order date desc 411910 site_dtl localhost site_dtl query 1 locked select id, title, date, alt_name, category, flag dle_post approve='1' order date desc 411911 site_dtl localhost site_dtl query 1 locked select id, title, date, alt_name, category, flag dle_post approve='1' order date desc 411912 site_dtl localhost site_dtl query 1 locked select id, title, date, alt_name, category, flag dle_post approve='1' order date desc 411919 site_dtl localhost site_dtl query 2 sorting result select id, title, date, alt_name, category, flag dle_post approve='1' order date desc 411921 site_dtl localhost site_dtl query 2 sorting result select id, title, date, alt_name, category, flag dle_post approve='1' order date desc 411922 site_dtl localhost site_dtl query 2 sorting result select id, title, date, alt_name, category, flag dle_post approve='1' order date desc 411950 root localhost mysql query 0 --- show processlist ====================================================
show create table dle_post\g *************************** 1. row *************************** table: dle_post create table: create table `dle_post` ( `id` int(11) not null auto_increment, `autor` varchar(40) not null default '', `date` datetime not null default '0000-00-00 00:00:00', `short_story` text not null, `full_story` text not null, `xfields` text not null, `title` varchar(255) not null default '', `descr` varchar(200) not null default '', `keywords` text not null, `category` varchar(200) not null default '0', `alt_name` varchar(200) not null default '', `comm_num` smallint(5) unsigned not null default '0', `allow_comm` tinyint(1) not null default '1', `allow_main` tinyint(1) unsigned not null default '1', `allow_rate` tinyint(1) unsigned not null default '1', `approve` tinyint(1) not null default '0', `fixed` tinyint(1) not null default '0', `rating` smallint(5) not null default '0', `allow_br` tinyint(1) not null default '1', `vote_num` smallint(5) unsigned not null default '0', `news_read` mediumint(8) not null default '0', `votes` tinyint(1) not null default '0', `access` varchar(150) not null default '', `symbol` char(3) not null default '', `flag` tinyint(1) not null default '0', `editdate` varchar(15) not null default '', `editor` varchar(40) not null default '', `reason` varchar(255) not null default '', `view_edit` tinyint(1) not null default '0', `tags` varchar(255) not null default '', `metatitle` varchar(255) not null default '', primary key (`id`), key `autor` (`autor`), key `alt_name` (`alt_name`), key `category` (`category`), key `approve` (`approve`), key `allow_main` (`allow_main`), key `date` (`date`), key `symbol` (`symbol`), key `comm_num` (`comm_num`), key `tags` (`tags`), fulltext key `short_story` (`short_story`,`full_story`,`xfields`,`title`) ) engine=myisam auto_increment=5265 default charset=utf8 1 row in set (0.03 sec) ====================================================
show table status 'dle_post'\g *************************** 1. row *************************** name: dle_post engine: myisam version: 10 row_format: dynamic rows: 4971 avg_row_length: 6685 data_length: 33232848 max_data_length: 281474976710655 index_length: 7998464 data_free: 0 auto_increment: 5266 create_time: 2010-04-01 15:21:19 update_time: 2010-11-14 19:24:19 check_time: 2010-06-20 01:32:40 collation: utf8_general_ci checksum: null create_options: comment: 1 row in set (0.00 sec) ====================================================
show table status 'dle_post'\g *************************** 1. row *************************** id: 1 select_type: simple table: null type: null possible_keys: null key: null key_len: null ref: null rows: null extra: no tables used 1 row in set (0.00 sec) ====================================================
show variables '%buffer%'; +-------------------------------+-----------+ | variable_name | value | +-------------------------------+-----------+ | bulk_insert_buffer_size | 16777216 | | innodb_buffer_pool_awe_mem_mb | 0 | | innodb_buffer_pool_size | 8388608 | | innodb_log_buffer_size | 1048576 | | join_buffer_size | 3145728 | | key_buffer_size | 671088640 | | myisam_sort_buffer_size | 268435456 | | net_buffer_length | 16384 | | preload_buffer_size | 32768 | | read_buffer_size | 3145728 | | read_rnd_buffer_size | 4194304 | | sort_buffer_size | 3145728 | +-------------------------------+-----------+ 12 rows in set (0.00 sec) ====================================================
i hope information required fix issue
note : commands has been executed when server load 180 - 350
thanks
do have slow query log? post explain statements too.
your order rand() slow big tables, there better methods, this example. generate random number between 0 , max(primary_key), select * table primary_key > @that_random_number limit 1.
so instead of select * dle_post order rand() limit 1;
do this:
calculate maximum value of primary key (auto_increment) , store in variable.
select max(primary_id_column_name) dle_posts limit 1;
generate random number (best in application avoid database roundtrip time, although can combine in step 1 replacing max
floor(rand() * max);. example in php:$chosen = rand(0,$max);
now first row primary key equal or bigger value:
select * dle_posts primary_id_column_name > $chosen limit 1
does index on (approve, category, date) help?
your slow query log shows no big problems. if can modify sourcecode, can rewrite following query:
select count(*) count dle_users from_unixtime(reg_date) > now() - interval 1 hour; to query don't use now, have application fill timestamp. query cache can cache query.
Comments
Post a Comment