mysql queries taking up over 100% CPU size
I have a website based on socialengine framework but it happens that one of the mode i am using takes up the CPU ever since the database size increased, i have tried all i could to fix this but can't just get a way around it, here is what i have below
Server Status attached
Process Manager attached
My.cnf......
[mysqld]
innodb_buffer_pool_size=234217728
max_allowed_packet=368435456
query_cache_size=99M
tmp_table_size=260M
max_heap_table_size=260M
thread_cache_size=4
max_connections=1000
wait_timeout=30800
interactive_timeout=30800
table_open_cache=800
open_files_limit=20000
innodb_file_per_table=1
local-infile=0
join_buffer_size=128K
table_cache=99
long_query_time=1
log-slow-queries=/var/log/mysql/log-slow-queries.log
slow query logs>>>>
i need help pls, is it something with my.cnf? or the queries that are taking up huge % of CPU Regards, Ayodeji.
usr/sbin/mysqld, Version: 5.5.40-cll (MySQL Community Server (GPL)). started with:
Tcp port: 0 Unix socket: (null)
Time Id Command Argument
/usr/sbin/mysqld, Version: 5.5.40-cll (MySQL Community Server (GPL)). started with:
Tcp port: 0 Unix socket: (null)
Time Id Command Argument
# Time: 150107 23:24:44
# User@Host: freezy_social[freezy_social] @ localhost []
# Query_time: 4.533085 Lock_time: 0.000119 Rows_sent: 5 Rows_examined: 33706
use freezy_social;
SET timestamp=1420669484;
SELECT DISTINCT `engine4_mp3music_album_songs`.* FROM `engine4_mp3music_album_songs`
LEFT JOIN `engine4_mp3music_albums` ON engine4_mp3music_albums.album_id = engine4_mp3music_album_songs.album_id WHERE (engine4_mp3music_albums.search = '1') ORDER BY `download_count` DESC LIMIT 5;
# User@Host: freezy_social[freezy_social] @ localhost []
# Query_time: 4.755712 Lock_time: 0.000026 Rows_sent: 5 Rows_examined: 33706
SET timestamp=1420669484;
SELECT DISTINCT `engine4_mp3music_album_songs`.* FROM `engine4_mp3music_album_songs`
LEFT JOIN `engine4_mp3music_albums` ON engine4_mp3music_albums.album_id = engine4_mp3music_album_songs.album_id WHERE (engine4_mp3music_albums.search = '1') ORDER BY `download_count` DESC LIMIT 5;
# User@Host: freezy_social[freezy_social] @ localhost []
# Query_time: 4.732454 Lock_time: 0.000024 Rows_sent: 5 Rows_examined: 33706
SET timestamp=1420669484;
SELECT DISTINCT `engine4_mp3music_album_songs`.* FROM `engine4_mp3music_album_songs`
LEFT JOIN `engine4_mp3music_albums` ON engine4_mp3music_albums.album_id = engine4_mp3music_album_songs.album_id WHERE (engine4_mp3music_albums.search = '1') ORDER BY `download_count` DESC LIMIT 5;
# User@Host: freezy_social[freezy_social] @ localhost []
# Query_time: 4.846341 Lock_time: 0.000020 Rows_sent: 5 Rows_examined: 33706
SET timestamp=1420669484;
SELECT DISTINCT `engine4_mp3music_album_songs`.* FROM `engine4_mp3music_album_songs`
LEFT JOIN `engine4_mp3music_albums` ON engine4_mp3music_albums.album_id = engine4_mp3music_album_songs.album_id WHERE (engine4_mp3music_albums.search = '1') ORDER BY `download_count` DESC LIMIT 5;
# User@Host: freezy_social[freezy_social] @ localhost []
# Query_time: 5.112259 Lock_time: 0.000021 Rows_sent: 5 Rows_examined: 33706
SET timestamp=1420669484;
SELECT DISTINCT `engine4_mp3music_album_songs`.* FROM `engine4_mp3music_album_songs`
LEFT JOIN `engine4_mp3music_albums` ON engine4_mp3music_albums.album_id = engine4_mp3music_album_songs.album_id WHERE (engine4_mp3music_albums.search = '1') ORDER BY `download_count` DESC LIMIT 5;
# User@Host: freezy_social[freezy_social] @ localhost []
# Query_time: 5.295649 Lock_time: 0.000026 Rows_sent: 5 Rows_examined: 33706
SET timestamp=1420669484;
SELECT DISTINCT `engine4_mp3music_album_songs`.* FROM `engine4_mp3music_album_songs`
LEFT JOIN `engine4_mp3music_albums` ON engine4_mp3music_albums.album_id = engine4_mp3music_album_songs.album_id WHERE (engine4_mp3music_albums.search = '1') ORDER BY `download_count` DESC LIMIT 5;
# Time: 150107 23:24:45
# User@Host: freezy_social[freezy_social] @ localhost []
# Query_time: 5.257231 Lock_time: 0.000032 Rows_sent: 5 Rows_examined: 33706
SET timestamp=1420669485;
SELECT DISTINCT `engine4_mp3music_album_songs`.* FROM `engine4_mp3music_album_songs`
LEFT JOIN `engine4_mp3music_albums` ON engine4_mp3music_albums.album_id = engine4_mp3music_album_songs.album_id WHERE (engine4_mp3music_albums.search = '1') ORDER BY `download_count` DESC LIMIT 5;
# User@Host: freezy_social[freezy_social] @ localhost []
# Query_time: 5.171313 Lock_time: 0.000023 Rows_sent: 5 Rows_examined: 33706
SET timestamp=1420669485;
SELECT DISTINCT `engine4_mp3music_album_songs`.* FROM `engine4_mp3music_album_songs`
LEFT JOIN `engine4_mp3music_albums` ON engine4_mp3music_albums.album_id = engine4_mp3music_album_songs.album_id WHERE (engine4_mp3music_albums.search = '1') ORDER BY `download_count` DESC LIMIT 5;
# User@Host: freezy_social[freezy_social] @ localhost []
# Query_time: 5.166304 Lock_time: 0.000028 Rows_sent: 5 Rows_examined: 33706
SET timestamp=1420669485;
SELECT DISTINCT `engine4_mp3music_album_songs`.* FROM `engine4_mp3music_album_songs`
LEFT JOIN `engine4_mp3music_albums` ON engine4_mp3music_albums.album_id = engine4_mp3music_album_songs.album_id WHERE (engine4_mp3music_albums.search = '1') ORDER BY `download_count` DESC LIMIT 5;
# User@Host: freezy_social[freezy_social] @ localhost []
# Query_time: 5.432443 Lock_time: 0.000023 Rows_sent: 5 Rows_examined: 33706
SET timestamp=1420669485;
SELECT DISTINCT `engine4_mp3music_album_songs`.* FROM `engine4_mp3music_album_songs`
LEFT JOIN `engine4_mp3music_albums` ON engine4_mp3music_albums.album_id = engine4_mp3music_album_songs.album_id WHERE (engine4_mp3music_albums.search = '1') ORDER BY `download_count` DESC LIMIT 5;
# User@Host: freezy_social[freezy_social] @ localhost []
# Query_time: 5.169090 Lock_time: 0.000022 Rows_sent: 5 Rows_examined: 33706
SET timestamp=1420669485;
SELECT DISTINCT `engine4_mp3music_album_songs`.* FROM `engine4_mp3music_album_songs`
LEFT JOIN `engine4_mp3music_albums` ON engine4_mp3music_albums.album_id = engine4_mp3music_album_songs.album_id WHERE (engine4_mp3music_albums.search = '1') ORDER BY `download_count` DESC LIMIT 5;
# User@Host: freezy_social[freezy_social] @ localhost []
# Query_time: 5.286295 Lock_time: 0.000023 Rows_sent: 5 Rows_examined: 33706
SET timestamp=1420669485;
SELECT DISTINCT `engine4_mp3music_album_songs`.* FROM `engine4_mp3music_album_songs`
LEFT JOIN `engine4_mp3music_albums` ON engine4_mp3music_albums.album_id = engine4_mp3music_album_songs.album_id WHERE (engine4_mp3music_albums.search = '1') ORDER BY `download_count` DESC LIMIT 5;
# User@Host: freezy_social[freezy_social] @ localhost []i need help pls, is it something with my.cnf? or the queries that are taking up huge % of CPU Regards, Ayodeji.
-
Re: mysql quaries taking up over 100% CPU size I will recommend you to use mysql tunner script to optimize your mysql settings. Also, if you want to increase the performance of website then you can go MariaDB. 0 -
Re: mysql quaries taking up over 100% CPU size Hello :) You may receive more user-feedback if you post the output of a MySQL tuner. The following thread should help you get started: mysqlmymonlite.sh server stats gathering tool for cPanel Server Thank you. 0 -
Re: mysql quaries taking up over 100% CPU size here is mysqltuner output /$ ./mysqltuner.pl >> MySQLTuner 1.4.0 - Major Hayden >> Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]MySQLTuner-perl by major >> Run with '--help' for additional options and output filtering Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. [[0;32mOK[0m] Currently running supported MySQL version 5.5.40-cll Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. [[0;32mOK[0m] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [[0;34m--[0m] Status: [0;32m+ARCHIVE [0m[0;32m+BLACKHOLE [0m[0;32m+CSV [0m[0;31m-FEDERATED [0m[0;32m+InnoDB [0m[0;32m+MRG_MYISAM [0m Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. [[0;34m--[0m] Data in MyISAM tables: 264M (Tables: 88) [[0;34m--[0m] Data in InnoDB tables: 112M (Tables: 483) [[0;34m--[0m] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [[0;31m!![0m] Total fragmented tables: 340 Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. -------- Security Recommendations ------------------------------------------- [[0;32mOK[0m] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [[0;34m--[0m] Up for: 7m 14s (34K q [78.601 qps], 781 conn, TX: 70M, RX: 4M) [[0;34m--[0m] Reads / Writes: 84% / 16% [[0;34m--[0m] Total buffers: 1.3G global + 2.2M per thread (1000 max threads) [[0;32mOK[0m] Maximum possible memory usage: 3.5G (7% of installed RAM) [[0;32mOK[0m] Slow queries: 0% (0/34K) [[0;32mOK[0m] Highest usage of available connections: 0% (8/1000) [[0;32mOK[0m] Key buffer size / total MyISAM indexes: 8.0M/4.5M [[0;32mOK[0m] Key buffer hit rate: 99.9% (152K cached / 174 reads) [[0;32mOK[0m] Query cache efficiency: 41.3% (10K cached / 25K selects) [[0;32mOK[0m] Query cache prunes per day: 0 [[0;32mOK[0m] Sorts requiring temporary tables: 0% (0 temp sorts / 123 sorts) [[0;31m!![0m] Temporary tables created on disk: 40% (25 on disk / 61 total) [[0;32mOK[0m] Thread cache hit rate: 98% (8 created / 781 connections) [[0;32mOK[0m] Table cache hit rate: 95% (140 open / 147 opened) [[0;32mOK[0m] Open file limit used: 0% (48/20K) [[0;32mOK[0m] Table locks acquired immediately: 99% (17K immediate / 17K locks) [[0;32mOK[0m] InnoDB buffer pool / data size: 128.0M/112.6M [[0;32mOK[0m] InnoDB log waits: 0 -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate Temporary table size is already large - reduce result set size Reduce your SELECT DISTINCT queries without LIMIT clauses /$ ./mysqltuner.pl >> MySQLTuner 1.4.0 - Major Hayden >> Bug reports, feature requests, and downloads at [url=http://mysqltuner.com/]MySQLTuner-perl by major >> Run with '--help' for additional options and output filtering Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. [[0;32mOK[0m] Currently running supported MySQL version 5.5.40-cll [[0;32mOK[0m] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. [[0;34m--[0m] Status: [0;32m+ARCHIVE [0m[0;32m+BLACKHOLE [0m[0;32m+CSV [0m[0;31m-FEDERATED [0m[0;32m+InnoDB [0m[0;32m+MRG_MYISAM [0m [[0;34m--[0m] Data in MyISAM tables: 264M (Tables: 88) [[0;34m--[0m] Data in InnoDB tables: 112M (Tables: 483) [[0;34m--[0m] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [[0;31m!![0m] Total fragmented tables: 340 Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. Warning: Using unique option prefix pass instead of password is deprecated and will be removed in a future release. Please use the full name instead. -------- Security Recommendations ------------------------------------------- [[0;32mOK[0m] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [[0;34m--[0m] Up for: 7m 47s (36K q [77.773 qps], 889 conn, TX: 73M, RX: 4M) [[0;34m--[0m] Reads / Writes: 83% / 17% [[0;34m--[0m] Total buffers: 1.3G global + 2.2M per thread (1000 max threads) [[0;32mOK[0m] Maximum possible memory usage: 3.5G (7% of installed RAM) [[0;32mOK[0m] Slow queries: 0% (0/36K) [[0;32mOK[0m] Highest usage of available connections: 0% (8/1000) [[0;32mOK[0m] Key buffer size / total MyISAM indexes: 8.0M/4.5M [[0;32mOK[0m] Key buffer hit rate: 99.9% (152K cached / 215 reads) [[0;32mOK[0m] Query cache efficiency: 43.5% (11K cached / 26K selects) [[0;32mOK[0m] Query cache prunes per day: 0 [[0;32mOK[0m] Sorts requiring temporary tables: 0% (0 temp sorts / 135 sorts) [[0;31m!![0m] Temporary tables created on disk: 31% (31 on disk / 97 total) [[0;32mOK[0m] Thread cache hit rate: 99% (8 created / 889 connections) [[0;32mOK[0m] Table cache hit rate: 98% (634 open / 641 opened) [[0;32mOK[0m] Open file limit used: 1% (228/20K) [[0;32mOK[0m] Table locks acquired immediately: 99% (18K immediate / 18K locks) [[0;32mOK[0m] InnoDB buffer pool / data size: 128.0M/112.6M [[0;32mOK[0m] InnoDB log waits: 0 -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate Temporary table size is already large - reduce result set size Reduce your SELECT DISTINCT queries without LIMIT clauses0 -
Re: mysql quaries taking up over 100% CPU size Also, keep in mind that you should let MySQL run for at least 24 hours before running the tuner to get accurate results. Thank you. 0 -
Re: mysql quaries taking up over 100% CPU size ]Also, keep in mind that you should let MySQL run for at least 24 hours before running the tuner to get accurate results. Thank you.
here is mysqltuner reccommendation now-------- Performance Metrics ------------------------------------------------- [[0;34m--[0m] Up for: 6h 35m 17s (20M q [852.189 qps], 16K conn, TX: 89B, RX: 3B) [[0;34m--[0m] Reads / Writes: 98% / 2% [[0;34m--[0m] Total buffers: 1.6G global + 12.6M per thread (1000 max threads) [[0;32mOK[0m] Maximum possible memory usage: 13.9G (29% of installed RAM) [[0;32mOK[0m] Slow queries: 0% (152/20M) [[0;32mOK[0m] Highest usage of available connections: 4% (44/1000) [[0;32mOK[0m] Key buffer size / total MyISAM indexes: 1000.0M/4.5M [[0;32mOK[0m] Key buffer hit rate: 100.0% (39M cached / 566 reads) [[0;32mOK[0m] Query cache efficiency: 80.1% (16M cached / 20M selects) [[0;32mOK[0m] Query cache prunes per day: 0 [[0;32mOK[0m] Sorts requiring temporary tables: 0% (147 temp sorts / 3M sorts) [[0;31m!![0m] Joins performed without indexes: 128 [[0;31m!![0m] Temporary tables created on disk: 67% (4K on disk / 6K total) [[0;32mOK[0m] Thread cache hit rate: 91% (1K created / 16K connections) [[0;32mOK[0m] Table cache hit rate: 99% (1K open / 1K opened) [[0;32mOK[0m] Open file limit used: 2% (324/11K) [[0;32mOK[0m] Table locks acquired immediately: 99% (4M immediate / 4M locks) [[0;32mOK[0m] InnoDB buffer pool / data size: 128.0M/120.9M [[0;32mOK[0m] InnoDB log waits: 0 -------- 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 Temporary table size is already large - reduce result set size Reduce your SELECT DISTINCT queries without LIMIT clauses Variables to adjust: join_buffer_size (> 4.0M, or always use indexes with joins)0
Please sign in to leave a comment.
Comments
5 comments