« previous next »
Pages: [1]

Author Topic: MySQL performance issues  (Read 7742 times)

WRP

  • Jr. Member
  • **
  • Posts: 57
  • Karma: 1
    • View Profile
MySQL performance issues
« on: May 26, 2011, 00:51:41 »
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: [Select]
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)
Logged

WRP

  • Jr. Member
  • **
  • Posts: 57
  • Karma: 1
    • View Profile
Re: MySQL performance issues
« Reply #1 on: May 27, 2011, 22:50:11 »
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: [Select]
[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
Logged

QueueMetrics

  • Loway
  • Hero Member
  • *
  • Posts: 2999
  • Karma: 39
    • View Profile
    • QueueMetrics
Re: MySQL performance issues
« Reply #2 on: May 30, 2011, 10:34:13 »
It is weird that it has started at some point.

Try and run such a query:

Code: [Select]
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

and post the results.
Sometimes MySQL requires you to rebuild indexes manually (it happened to a couple of our clients in the past)
Logged

WRP

  • Jr. Member
  • **
  • Posts: 57
  • Karma: 1
    • View Profile
Re: MySQL performance issues
« Reply #3 on: May 30, 2011, 19:59:02 »
Thanks for the reply. Here is the output:

Code: [Select]
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 |
+----+-------------+-----------+-------+----------------------+--------------+---------+------+-------+-------------+
Logged

QueueMetrics

  • Loway
  • Hero Member
  • *
  • Posts: 2999
  • Karma: 39
    • View Profile
    • QueueMetrics
Re: MySQL performance issues
« Reply #4 on: May 31, 2011, 17:16:03 »
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?

Logged

WRP

  • Jr. Member
  • **
  • Posts: 57
  • Karma: 1
    • View Profile
Re: MySQL performance issues
« Reply #5 on: May 31, 2011, 19:13:42 »
We increased the amount of CPU and memory resources available recently, but this was after we began having performance issues. From every measurement I can find, it appears as though only the CPU is being stressed.

The queries normally return within 100 ms, but as of late, they have been taking up to 50 seconds (with 10-15 of these similar queries sitting in the processlist all at the same time).

What do you suggest we do to further troubleshoot this issue? Are there system tests that we can run or MySQL optimizations worth considering? I'm normally able to get on the system when the CPU pegging is occurring, so running tests or taking measurements while the issue is occurring is not out of the question.
Logged

QueueMetrics

  • Loway
  • Hero Member
  • *
  • Posts: 2999
  • Karma: 39
    • View Profile
    • QueueMetrics
Re: MySQL performance issues
« Reply #6 on: June 03, 2011, 10:26:42 »
Try optimizing  the queue_log table, with cluster index sorting based on the time_id field. This will take a while and has to be done when the system is down.
Logged

WRP

  • Jr. Member
  • **
  • Posts: 57
  • Karma: 1
    • View Profile
Re: MySQL performance issues
« Reply #7 on: June 06, 2011, 05:40:42 »
Can you suggest the SQL syntax to achieve this?
Logged

QueueMetrics

  • Loway
  • Hero Member
  • *
  • Posts: 2999
  • Karma: 39
    • View Profile
    • QueueMetrics
Re: MySQL performance issues
« Reply #8 on: June 06, 2011, 11:34:37 »
Try

Code: [Select]
ALTER TABLE `queue_log` ORDER BY `time_id`
Logged

tsolaaa

  • Newbie
  • *
  • Posts: 19
  • Karma: 0
    • View Profile
    • Email
Re: MySQL performance issues
« Reply #9 on: July 19, 2011, 00:06:45 »
So this is the last thing we tried and it was working since then.

However, as of this morning, we are hitting the problem again.

Running the MYSQL tuner script.

Code: [Select]
[tr][td]-------- Performance Metrics -------------------------------------------------
[--] Up for: 3d 14h 11m 52s (1M q [3.673 qps], 100K conn, TX: 53B, RX: 183M)
[--] Reads / Writes: 58% / 42%
[--] Total buffers: 330.0M global + 34.6M per thread (130 max threads)
[OK] Maximum possible memory usage: 4.7G (60% of installed RAM)
[!!] Slow queries: 11% (134K/1M)
[!!] Highest connection usage: 97%  (127/130)
[OK] Key buffer size / total MyISAM indexes: 64.0M/50.9M
[OK] Key buffer hit rate: 100.0% (379M cached / 25K reads)
[OK] Query cache efficiency: 51.8% (128K cached / 247K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 234 sorts)
[OK] Temporary tables created on disk: 1% (3K on disk / 209K total)
[OK] Thread cache hit rate: 99% (215 created / 100K connections)
[!!] Table cache hit rate: 0% (181 open / 160K opened)
[OK] Open file limit used: 10% (233/2K)
[OK] Table locks acquired immediately: 100% (278K immediate / 278K locks)[/sub]

We are still getting a ton of slow queries that make the CPU max out.

I have checked with anyone logged into the machine, and no is is running any insane reports, etc.


Here is an example of the "show processlist;"

Code: [Select]
mysql> show processlist;
+--------+--------------+---------------------+--------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| Id     | User         | Host                | db           | Command | Time | State        | Info                                                                                                 |
+--------+--------------+---------------------+--------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
| 101270 | queuemetrics | localhost:40641     | queuemetrics | Query   |  132 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue |
| 101271 | queuemetrics | localhost:40642     | queuemetrics | Query   |  129 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue |
| 101272 | queuemetrics | localhost:40643     | queuemetrics | Query   |  127 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue |
| 101274 | queuemetrics | localhost:40644     | queuemetrics | Query   |  126 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue |
| 101275 | queuemetrics | localhost:40645     | queuemetrics | Query   |  126 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue |
| 101276 | queuemetrics | localhost:40646     | queuemetrics | Query   |  124 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue |
| 101277 | queuemetrics | localhost:40647     | queuemetrics | Query   |  116 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue |
| 101278 | queuemetrics | localhost:40648     | queuemetrics | Query   |  116 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue |
| 101279 | queuemetrics | localhost:40649     | queuemetrics | Query   |  114 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue |
| 101280 | queuemetrics | localhost:40650     | queuemetrics | Query   |  103 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue |
| 101281 | queuemetrics | localhost:40651     | queuemetrics | Query   |  102 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue |
| 101282 | queuemetrics | localhost:40652     | queuemetrics | Query   |   94 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue |
| 101284 | queuemetrics | localhost:40653     | queuemetrics | Query   |   83 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue |
| 101285 | queuemetrics | localhost:40654     | queuemetrics | Query   |   82 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue |
| 101286 | queuemetrics | localhost:40655     | queuemetrics | Query   |   80 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue |
| 101287 | queuemetrics | localhost:40656     | queuemetrics | Query   |   82 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue |
| 101289 | queuemetrics | localhost:40657     | queuemetrics | Query   |   80 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue |
| 101290 | queuemetrics | localhost:40658     | queuemetrics | Query   |   80 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue |
| 101291 | queuemetrics | localhost:40659     | queuemetrics | Query   |   77 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue |
| 101292 | queuemetrics | localhost:40660     | queuemetrics | Query   |   69 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue |
| 101293 | queuemetrics | localhost:40661     | queuemetrics | Query   |   69 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue |
| 101294 | queuemetrics | localhost:40662     | queuemetrics | Query   |   68 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue |
| 101295 | queuemetrics | localhost:40663     | queuemetrics | Query   |   61 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue |
| 101297 | queuemetrics | 64.207.128.55:41921 | queuemetrics | Sleep   |    5 |              | NULL                                                                                                 |
| 101298 | queuemetrics | localhost:53878     | queuemetrics | Query   |   47 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue |
| 101299 | queuemetrics | localhost:53879     | queuemetrics | Query   |   44 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue |
| 101301 | queuemetrics | localhost:53880     | queuemetrics | Query   |   40 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue |
| 101302 | queuemetrics | localhost:53881     | queuemetrics | Query   |   39 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue |
| 101303 | queuemetrics | localhost:53882     | queuemetrics | Query   |   39 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue |
| 101304 | queuemetrics | localhost:53883     | queuemetrics | Query   |   36 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue |
| 101305 | queuemetrics | localhost:53884     | queuemetrics | Query   |   28 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue |
| 101306 | queuemetrics | localhost:53885     | queuemetrics | Query   |   25 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue |
| 101307 | queuemetrics | localhost:53886     | queuemetrics | Query   |   25 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue |
| 101308 | queuemetrics | localhost:53887     | queuemetrics | Query   |   19 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue |
| 101309 | queuemetrics | localhost:53888     | queuemetrics | Query   |   16 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue |
| 101310 | queuemetrics | localhost:53889     | queuemetrics | Query   |   11 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue |
| 101311 | queuemetrics | localhost:53890     | queuemetrics | Query   |    6 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue |
| 101312 | queuemetrics | localhost:53891     | queuemetrics | Query   |    6 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue |
| 101313 | queuemetrics | localhost:53892     | queuemetrics | Query   |    5 | Sending data | SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue |
| 101314 | root         | localhost           | queuemetrics | Query   |    0 | NULL         | show processlist                                                                                     |
+--------+--------------+---------------------+--------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+
40 rows in set (0.00 sec)



Also, here is some of the slow query logs

Code: [Select]


[root@qm log]# tail -f mysqld.slow.log
# Time: 110718 14:29:07
# User@Host: queuemetrics[queuemetrics] @ localhost [127.0.0.1]
# Query_time: 146  Lock_time: 0  Rows_sent: 30578  Rows_examined: 2136443
SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue_log  WHERE  partition ='P001' AND        (time_id >= '1310972400' AND time_id<='1577869260')     AND queue IN ( '', 'NONE'  , 'q-301'  , '3011'  , '3010'  , '3005'  , '3006'  , 'none'  , '3003'  , '3004'  , '3009'  , '3008' )   ORDER BY time_id ASC , unique_row_count ASC;
# User@Host: queuemetrics[queuemetrics] @ localhost [127.0.0.1]
# Query_time: 0  Lock_time: 0  Rows_sent: 0  Rows_examined: 22
INSERT INTO temp_union_556553 SELECT id_coda , nome_coda ,  LCASE(composizione_coda) AS composizione_coda,  LCASE(agenti_membri) AS agenti_membri ,  LCASE(agenti_spilloff_1) AS agenti_spilloff_1 ,  LCASE(agenti_spilloff_2) AS agenti_spilloff_2 , wrapuptime , announcement_duration , visibility_key , q_direction , sl_calls_y , sl_calls_r , sl_ag_calls_y , sl_ag_calls_r , sl_ag_wait_y , sl_ag_wait_r , sl_ag_pause_y , sl_ag_pause_r , sl_waittime_y , sl_waittime_r , sl_talktime_y , sl_talktime_r  FROM code_possibili WHERE   composizione_coda IN ( '3003', '3004', '3005', '3006', '3008', '3009', '3010', '3011', 'q-301',  'dummy-close-list' );
# User@Host: queuemetrics[queuemetrics] @ localhost [127.0.0.1]
# Query_time: 0  Lock_time: 0  Rows_sent: 10  Rows_examined: 10
SELECT * FROM temp_union_556553;
# Time: 110718 14:29:11
# User@Host: queuemetrics[queuemetrics] @ localhost [127.0.0.1]
# Query_time: 148  Lock_time: 0  Rows_sent: 1125  Rows_examined: 2136443
SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue_log  WHERE  partition ='P001' AND        (time_id >= '1310972400' AND time_id<='1577869260')     AND queue IN ( '', 'NONE'  , '3000'  , 'none'  , 'q-300' )   ORDER BY time_id ASC , unique_row_count ASC;
# Time: 110718 14:29:13
# User@Host: queuemetrics[queuemetrics] @ localhost [127.0.0.1]
# Query_time: 0  Lock_time: 0  Rows_sent: 3  Rows_examined: 3
SELECT * FROM temp_union_567841;
# Time: 110718 14:29:14
# User@Host: queuemetrics[queuemetrics] @ localhost [127.0.0.1]
# Query_time: 141  Lock_time: 0  Rows_sent: 30585  Rows_examined: 2136454
SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue_log  WHERE  partition ='P001' AND        (time_id >= '1310972400' AND time_id<='1577869260')     AND queue IN ( '', 'NONE'  , 'q-301'  , '3011'  , '3010'  , '3005'  , '3006'  , 'none'  , '3003'  , '3004'  , '3009'  , '3008' )   ORDER BY time_id ASC , unique_row_count ASC;
# User@Host: queuemetrics[queuemetrics] @ localhost [127.0.0.1]
# Query_time: 0  Lock_time: 0  Rows_sent: 0  Rows_examined: 22
INSERT INTO temp_union_687716 SELECT id_coda , nome_coda ,  LCASE(composizione_coda) AS composizione_coda,  LCASE(agenti_membri) AS agenti_membri ,  LCASE(agenti_spilloff_1) AS agenti_spilloff_1 ,  LCASE(agenti_spilloff_2) AS agenti_spilloff_2 , wrapuptime , announcement_duration , visibility_key , q_direction , sl_calls_y , sl_calls_r , sl_ag_calls_y , sl_ag_calls_r , sl_ag_wait_y , sl_ag_wait_r , sl_ag_pause_y , sl_ag_pause_r , sl_waittime_y , sl_waittime_r , sl_talktime_y , sl_talktime_r  FROM code_possibili WHERE   composizione_coda IN ( '3003', '3004', '3005', '3006', '3008', '3009', '3010', '3011', 'q-301',  'dummy-close-list' );
# User@Host: queuemetrics[queuemetrics] @ localhost [127.0.0.1]
# Query_time: 0  Lock_time: 0  Rows_sent: 10  Rows_examined: 10
SELECT * FROM temp_union_687716;
# Time: 110718 14:29:20
# User@Host: queuemetrics[queuemetrics] @ localhost [127.0.0.1]
# Query_time: 142  Lock_time: 0  Rows_sent: 1131  Rows_examined: 2136459
SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue_log  WHERE  partition ='P001' AND        (time_id >= '1310972400' AND time_id<='1577869260')     AND queue IN ( '', 'NONE'  , '3000'  , 'none'  , 'q-300' )   ORDER BY time_id ASC , unique_row_count ASC;
# Time: 110718 14:29:24
# User@Host: queuemetrics[queuemetrics] @ localhost [127.0.0.1]
# Query_time: 143  Lock_time: 0  Rows_sent: 1135  Rows_examined: 2136473
SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue_log  WHERE  partition ='P001' AND        (time_id >= '1310972400' AND time_id<='1577869260')     AND queue IN ( '', 'NONE'  , '3000'  , 'none'  , 'q-300' )   ORDER BY time_id ASC , unique_row_count ASC;
# Time: 110718 14:29:33
# User@Host: queuemetrics[queuemetrics] @ localhost [127.0.0.1]
# Query_time: 152  Lock_time: 0  Rows_sent: 30598  Rows_examined: 2136473
SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue_log  WHERE  partition ='P001' AND        (time_id >= '1310972400' AND time_id<='1577869260')     AND queue IN ( '', 'NONE'  , 'q-301'  , '3011'  , '3010'  , '3005'  , '3006'  , 'none'  , '3003'  , '3004'  , '3009'  , '3008' )   ORDER BY time_id ASC , unique_row_count ASC;
# User@Host: queuemetrics[queuemetrics] @ localhost [127.0.0.1]
# Query_time: 0  Lock_time: 0  Rows_sent: 0  Rows_examined: 22
INSERT INTO temp_union_266575 SELECT id_coda , nome_coda ,  LCASE(composizione_coda) AS composizione_coda,  LCASE(agenti_membri) AS agenti_membri ,  LCASE(agenti_spilloff_1) AS agenti_spilloff_1 ,  LCASE(agenti_spilloff_2) AS agenti_spilloff_2 , wrapuptime , announcement_duration , visibility_key , q_direction , sl_calls_y , sl_calls_r , sl_ag_calls_y , sl_ag_calls_r , sl_ag_wait_y , sl_ag_wait_r , sl_ag_pause_y , sl_ag_pause_r , sl_waittime_y , sl_waittime_r , sl_talktime_y , sl_talktime_r  FROM code_possibili WHERE   composizione_coda IN ( '3003', '3004', '3005', '3006', '3008', '3009', '3010', '3011', 'q-301',  'dummy-close-list' );
# User@Host: queuemetrics[queuemetrics] @ localhost [127.0.0.1]
# Query_time: 0  Lock_time: 0  Rows_sent: 10  Rows_examined: 10
SELECT * FROM temp_union_266575;
# Time: 110718 14:29:34
# User@Host: queuemetrics[queuemetrics] @ localhost [127.0.0.1]
# Query_time: 0  Lock_time: 0  Rows_sent: 3  Rows_examined: 3
SELECT * FROM temp_union_155538;
# User@Host: queuemetrics[queuemetrics] @ localhost [127.0.0.1]
# Query_time: 154  Lock_time: 0  Rows_sent: 30598  Rows_examined: 2136471
SELECT time_id , call_id , queue , agent , verb , data1 , data2 , data3 , data4 , data5   FROM queue_log  WHERE  partition ='P001' AND        (time_id >= '1310972400' AND time_id<='1577869260')     AND queue IN ( '', 'NONE'  , 'q-301'  , '3011'  , '3010'  , '3005'  , '3006'  , 'none'  , '3003'  , '3004'  , '3009'  , '3008' )   ORDER BY time_id ASC , unique_row_count ASC;
# Time: 110718 14:29:35
# User@Host: queuemetrics[queuemetrics] @ localhost [127.0.0.1]
# Query_time: 0  Lock_time: 0  Rows_sent: 0  Rows_examined: 22
INSERT INTO temp_union_813568 SELECT id_coda , nome_coda ,  LCASE(composizione_coda) AS composizione_coda,  LCASE(agenti_membri) AS agenti_membri ,  LCASE(agenti_spilloff_1) AS agenti_spilloff_1 ,  LCASE(agenti_spilloff_2) AS agenti_spilloff_2 , wrapuptime , announcement_duration , visibility_key , q_direction , sl_calls_y , sl_calls_r , sl_ag_calls_y , sl_ag_calls_r , sl_ag_wait_y , sl_ag_wait_r , sl_ag_pause_y , sl_ag_pause_r , sl_waittime_y , sl_waittime_r , sl_talktime_y , sl_talktime_r  FROM code_possibili WHERE   composizione_coda IN ( '3003', '3004', '3005', '3006', '3008', '3009', '3010', '3011', 'q-301',  'dummy-close-list' );
# User@Host: queuemetrics[queuemetrics] @ localhost [127.0.0.1]
# Query_time: 0  Lock_time: 0  Rows_sent: 10  Rows_examined: 10
SELECT * FROM temp_union_813568;
# Time: 110718 14:29:30


« Last Edit: July 19, 2011, 00:29:20 by tsolaaa »
Logged

tsolaaa

  • Newbie
  • *
  • Posts: 19
  • Karma: 0
    • View Profile
    • Email
Re: MySQL performance issues
« Reply #10 on: July 26, 2011, 20:36:47 »
UPDATE:

We ran

Code: [Select]
ALTER TABLE `queue_log` ORDER BY `time_id`
Again, and it seemed to fix the problem. We currently set up a cron job to have this run twice a month.

Will update to see if this completely revolves the issue.
Logged

QueueMetrics

  • Loway
  • Hero Member
  • *
  • Posts: 2999
  • Karma: 39
    • View Profile
    • QueueMetrics
Re: MySQL performance issues
« Reply #11 on: August 09, 2011, 10:26:47 »
It is weird that this happens - it is not supposed to since data is insert-only. Or do you have processes that edit/update that table?
Logged
Pages: [1]
« previous next »