QueueMetrics > MySQL storage and Qloaderd/Uniloader
MySQL performance issues
WRP:
Just today, we began experiencing MySQL performance issues with our Queuemetrics install. MySQL will jump up to 150-200% CPU in top for 5-15 seconds or so every so often and cause the Queuemetrics web interface to lag greatly. This issue seems to have started roughly two weeks ago and has gradually become more prominent.
I was able to grab the processlist from MySQL during one of the slowdowns. I've included the output at the bottom of this message. As you can see, the queuemetrics queries are running slowly (up to 9 seconds of execution time). If I check the processlist when the CPU isn't high, there will generally only be a single queuemetrics query, and according to the tomcat logs, they will only take around 100ms to execute.
We did increase the amount of memory available to Java to 512MB. This may have helped marginally.
Has anyone seen anything like this before? How can we solve this performance problem?
--- Code: ---mysql> show full processlist;
+------+--------------+---------------------+--------------+---------+------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+--------------+---------------------+--------------+---------+------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 3517 | queuemetrics | 64.207.128.55:57419 | queuemetrics | Sleep | 6 | | NULL |
| 7353 | queuemetrics | localhost | NULL | Query | 0 | NULL | show full processlist |
| 7411 | queuemetrics | localhost:58330 | queuemetrics | Query | 9 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5 FROM queue_log WHERE partition ='P001' AND (time_id >= '1306306800' AND time_id<='1577869260') AND queue IN ( '', 'NONE' , 'q-301' , '3005' , '3006' , 'none' , '3003' , '3004' , '3009' , '3008' ) ORDER BY time_id ASC , unique_row_count ASC |
| 7412 | queuemetrics | localhost:58331 | queuemetrics | Query | 7 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5 FROM queue_log WHERE partition ='P001' AND (time_id >= '1306306800' AND time_id<='1577869260') AND queue IN ( '', 'NONE' , '3000' , 'none' , 'q-300' ) ORDER BY time_id ASC , unique_row_count ASC |
| 7413 | queuemetrics | localhost:58332 | queuemetrics | Query | 6 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5 FROM queue_log WHERE partition ='P001' AND (time_id >= '1306306800' AND time_id<='1577869260') AND queue IN ( '', 'NONE' , 'q-301' , '3005' , '3006' , 'none' , '3003' , '3004' , '3009' , '3008' ) ORDER BY time_id ASC , unique_row_count ASC |
| 7414 | queuemetrics | localhost:58333 | queuemetrics | Query | 4 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5 FROM queue_log WHERE partition ='P001' AND (time_id >= '1306306800' AND time_id<='1577869260') AND queue IN ( '', 'NONE' , 'q-301' , '3005' , '3006' , 'none' , '3003' , '3004' , '3009' , '3008' ) ORDER BY time_id ASC , unique_row_count ASC |
| 7415 | queuemetrics | localhost:58334 | queuemetrics | Query | 3 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5 FROM queue_log WHERE partition ='P001' AND (time_id >= '1306306800' AND time_id<='1577869260') AND queue IN ( '', 'NONE' , 'q-301' , '3005' , '3006' , 'none' , '3003' , '3004' , '3009' , '3008' ) ORDER BY time_id ASC , unique_row_count ASC |
| 7417 | queuemetrics | localhost:58336 | queuemetrics | Query | 1 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5 FROM queue_log WHERE partition ='P001' AND (time_id >= '1306306800' AND time_id<='1577869260') AND queue IN ( '', 'NONE' , 'q-301' , '3005' , '3006' , 'none' , '3003' , '3004' , '3008' ) ORDER BY time_id ASC , unique_row_count ASC |
| 7418 | queuemetrics | localhost:58337 | queuemetrics | Query | 0 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5 FROM queue_log WHERE partition ='P001' AND (time_id >= '1306306800' AND time_id<='1577869260') AND queue IN ( '', 'NONE' , '3000' , 'none' , 'q-300' ) ORDER BY time_id ASC , unique_row_count ASC |
+------+--------------+---------------------+--------------+---------+------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
9 rows in set (0.00 sec)
--- End code ---
WRP:
We are still having this issue. We've tried increasing the resources that MySQL is allowed, but it hasn't helped. Here is our current MySQL config:
--- Code: ---[mysqld]
query_cache_size = 128M
thread_cache = 8
table_cache = 768
query_cache_limit = 128M
tmp_table_size = 128M
max_heap_table_size = 128M
join_buffer_size = 16M
log_slow_queries = /var/log/mysqld.slow.log
long_query_time = 2
log-queries-not-using-indexes
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
# Disabling symbolic-links is recommended to prevent assorted security risks;
# to do so, uncomment this line:
# symbolic-links=0
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
--- End code ---
QueueMetrics:
It is weird that it has started at some point.
Try and run such a query:
--- Code: ---EXPLAIN
SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5 FROM queue_log WHERE partition ='P001' AND (time_id >= '1306306800' AND time_id<='1577869260') AND queue IN ( '', 'NONE' , '3000' , 'none' , 'q-300' ) ORDER BY time_id ASC , unique_row_count ASC
--- End code ---
and post the results.
Sometimes MySQL requires you to rebuild indexes manually (it happened to a couple of our clients in the past)
WRP:
Thanks for the reply. Here is the output:
--- Code: ---mysql> EXPLAIN
-> SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5 FROM queue_log WHERE partition ='P001' AND (time_id >= '1306306800' AND time_id<='1577869260') AND queue IN ( '', 'NONE' , '3000' , 'none' , 'q-300' ) ORDER BY time_id ASC , unique_row_count ASC;
+----+-------------+-----------+-------+----------------------+--------------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-----------+-------+----------------------+--------------+---------+------+-------+-------------+
| 1 | SIMPLE | queue_log | range | idx_sel,partizione_b | partizione_b | 26 | NULL | 74032 | Using where |
+----+-------------+-----------+-------+----------------------+--------------+---------+------+-------+-------------+
--- End code ---
QueueMetrics:
It seems to be working just fine, just returning about 74K rows, so it might take a while to run. Did you change anything recently? (new server, new network card, more traffic in the CC?)
Also, is your system stalled by not having enough CPU or enough I/O?
Navigation
[0] Message Index
[#] Next page
Go to full version