Help requested optimizing MySQL
Hello
Mysql cpu load to high 98%, Query very slow, 0% installed RAM, browsing very slow + blank page
Server
DualXeonE5-2620 - 24 CPU, 64 GB RAM
./tuning-primer.sh
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
wait_timeout=3
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
bind-address=127.0.0.1
symbolic-links=0
port=3306
skip-external-locking
#skip-innodb
max_connections = 2000
interactive_timeout=3
key_buffer = 5G
myisam_sort_buffer_size = 512M
join_buffer_size = 6M
read_buffer_size = 8M
sort_buffer_size = 6M
table_cache = 1024
read_rnd_buffer_size=6144K
thread_cache_size = 300k
thread_stack=128K
thread_concurrency=12
wait_timeout = 20
connect_timeout = 10
max_allowed_packet = 32M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 32M
query_cache_type = 1
#skip-locking
#skip-bdb
max_binlog_size=100M
default-storage-engine=MyISAM
tmp_table_size=2500M
max_heap_table_size=2500M
table_definition_cache = 500
#slow_query_log=1
#slow_query_log_file=mysql-slow.log
#long_query_time=5
log-queries-not-using-indexes
low_priority_updates=1
concurrent_insert=2
#skip-federated
#log-bin=mysql-bin
server-id = 1
[mysqld_safe]
#log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit = 35000
socket=/var/run/mysqld/mysqld.sock
nice=0
[mysqldump]
#quick
#max_allowed_packet = 16M
#quote-names
[myisamchk]
key_buffer = 512M
sort_buffer = 512M
read_buffer = 128M
write_buffer = 128M
[isamchk]
key_buffer = 512M
sort_buffer = 512M
read_buffer = 128M
write_buffer = 128M
[mysql.server]
#basedir=/var/lib/mysql
[mysqlhotcopy]
interactive-timeout
safe-show-database
set-variable=local-infile=0
./tuning-primer.sh
[root@ne-t194-320cl home]# ./tuning-primer.sh
-- MYSQL PERFORMANCE TUNING PRIMER --
- By: Matthew Montgomery -
MySQL Version 5.1.73 x86_64
Uptime = 0 days 0 hrs 26 min 4 sec
Avg. qps = 16
Total Questions = 25741
Threads Connected = 258
Warning: Server has not been running for at least 48hrs.
It may not be safe to use these recommendations
To find out more information on how each of these
runtime variables effects performance visit:
[url=http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html]MySQL :: MySQL 5.1 Reference Manual :: 5.1.4 Server System Variables
Visit -
Hello :) Please ensure you let MySQL run for at least 24 hours before running the tuner to ensure the most accurate results. Thank you. 0 -
run mysqltuner.pl and post results 0 -
more information about the database Tables 30 Rows 13,591,548 Data 1.3 GiB Indexes 265.2 MiB Total 1.5 GiB Overhead 96 B [COLOR="silver">- - - Updated - - - [root@ne-t194-320cl home]# ./tuning-primer.sh -- MYSQL PERFORMANCE TUNING PRIMER -- - By: Matthew Montgomery - MySQL Version 5.1.73 x86_64 Uptime = 0 days 21 hrs 18 min 18 sec Avg. qps = 17 Total Questions = 1343273 Threads Connected = 253 Warning: Server has not been running for at least 48hrs. It may not be safe to use these recommendations To find out more information on how each of these runtime variables effects performance visit: http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html Visit http://www.mysql.com/products/enterprise/advisors.html for info about MySQL's Enterprise Monitoring and Advisory Service SLOW QUERIES The slow query log is NOT enabled. Current long_query_time = 10.000000 sec. You have 28844 out of 1343294 that take longer than 10.000000 sec. to complete Your long_query_time seems to be fine BINARY UPDATE LOG The binary update log is NOT enabled. You will not be able to do point in time recovery See http://dev.mysql.com/doc/refman/5.1/en/point-in-time-recovery.html WORKER THREADS Current thread_cache_size = 16384 Current threads_cached = 5 Current threads_per_sec = 0 Historic threads_per_sec = 0 Your thread_cache_size is fine MAX CONNECTIONS Current max_connections = 2000 Current threads_connected = 253 Historic max_used_connections = 258 The number of used connections is 12% of the configured maximum. Your max_connections variable seems to be fine. INNODB STATUS Current InnoDB index space = 0 bytes Current InnoDB data space = 0 bytes Current InnoDB buffer pool free = 96 % Current innodb_buffer_pool_size = 8 M Depending on how much space your innodb indexes take up it may be safe to increase this value to up to 2 / 3 of total system memory MEMORY USAGE Max Memory Ever Allocated : 11.62 G Configured Max Per-thread Buffers : 51.02 G Configured Max Global Buffers : 5.04 G Configured Max Memory Limit : 56.06 G Physical Memory : 62.88 G Max memory limit seem to be within acceptable norms KEY BUFFER Current MyISAM index space = 919 M Current key_buffer_size = 5.00 G Key cache miss rate is 1 : 172564 Key buffer free ratio = 80 % Your key_buffer_size seems to be too high. Perhaps you can use these resources elsewhere QUERY CACHE Query cache is enabled Current query_cache_size = 32 M Current query_cache_used = 61 K Current query_cache_limit = 1 M Current Query cache Memory fill ratio = .18 % Current query_cache_min_res_unit = 4 K Your query_cache_size seems to be too high. Perhaps you can use these resources elsewhere MySQL won't cache query results that are larger than query_cache_limit in size SORT OPERATIONS Current sort_buffer_size = 6 M Current read_rnd_buffer_size = 6 M No sort operations have been performed Sort buffer seems to be fine JOINS Current join_buffer_size = 6.00 M You have had 0 queries where a join could not use an index properly Your joins seem to be using indexes properly join_buffer_size >= 4 M This is not advised OPEN FILES LIMIT Current open_files_limit = 35000 files The open_files_limit should typically be set to at least 2x-3x that of table_cache if you have heavy MyISAM usage. Your open_files_limit value seems to be fine TABLE CACHE Current table_open_cache = 1024 tables Current table_definition_cache = 500 tables You have a total of 651 tables You have 907 open tables. The table_cache value seems to be fine You should probably increase your table_definition_cache value. TEMP TABLES Current max_heap_table_size = 2.44 G Current tmp_table_size = 2.44 G Of 1941 temp tables, 6% were created on disk Created disk tmp tables ratio seems fine TABLE SCANS Current read_buffer_size = 8 M Current table scan ratio = 514 : 1 read_buffer_size seems to be fine TABLE LOCKING Current Lock Wait ratio = 1 : 0 You may benefit from selective use of InnoDB.
[root@ne-t194-320cl home]# netstat -an | grep :80 | wc -l 160540 -
mysqltuner.pl not tuning-primer anyway adjust: thread_cache_size = 200 max_connections = 500 sort_buffer_size = 256K read_rnd_buffer_size = 256K read_buffer_size=128K join_buffer_size=2M table_definition_cache = 1000 max_heap_table_size = 200M tmp_table_size = 200M remove those: thread_cache_size = 300k thread_stack=128K thread_concurrency=12 max_connect_errors = 10 low_priority_updates=1 "Mysql cpu load to high 98%," to make it simple, if you got 24 cores, you can have 24x100% = 2400% you should also review your slow queries, and optimize them, add some indexes if necessary etc. 0 -
$sql3 = mysql_query("SELECT * FROM config LIMIT 1"); $row3 = mysql_fetch_assoc($sql3); $log=$row3['con_log">; $sql2 = mysql_query("SELECT sl_start,sl_id FROM ss_list2 WHERE sl_ss='0' ORDER BY sl_id LIMIT 40"); while($row2 = mysql_fetch_assoc($sql2)){ echo "crk|".$row2['sl_start">."|".$log."-"; $sl_id = $row2['sl_id">; mysql_query("UPDATE scan_list2 SET sl_ss='1' WHERE sl_id='$sl_id'");
Is there any better solution for this problem to make the query faster, that one take more than 5 mint.0 -
it would be better to copy more content here Make sure there are idexes on sl_id in scan_list2 table and 1 index containing 2 columns (sl_ss, sl_id) you can show us the result of those queries (you can run them from phpmyadmin): explain extended SELECT sl_start,sl_id FROM ss_list2 WHERE sl_ss='0' ORDER BY sl_id LIMIT 40 explain extended SELECT sl_ss FROM scan_list2 WHERE sl_id='1' to check if there are indexes on it used well 0
Please sign in to leave a comment.
Comments
6 comments