MySQL error during large queries. Timeout related?

QueueMetrics > MySQL storage and Qloaderd/Uniloader

<< < (2/3) > >>

WRP:
I tried your two recommendations but they unfortunately didn't work. I:

1. recreated the indexes for the tables being queried
2. added 'enableQueryTimeout=true' to the JDBC_URL setting in web.xml

I enabled slow query logging with MySQL and found this to be (at least one of) the slow queries:

--- Quote ---SELECT `time_id` , `call_id` , `queue` , `agent` , `verb` , `data1` , `data2` , `data3` , `data4` , `data5` , `unique_row_count` FROM queue_log WHERE `partition` ='P001' AND (`time_id` >= '1325433600' AND `time_id`<= '1357672768' ) AND `queue` IN ( '', 'NONE' , 'q-301' , '3000' , '3012' , '3011' , '3010' , 'none' , '3003' , '3200' , '3009' , 'q-300' , '3008' ) ORDER BY `time_id` ASC , `unique_row_count` ASC;
--- End quote ---

If I run this command from the MySQL CLI client, it completes after about 25-30 seconds. When I run the custom report, it dies consistently after about 37 seconds.

In case it is of any use, here is our JDBC_URL value:

--- Quote ---jdbc:mysql://localhost/queuemetrics?sessionVariables=sql_mode=''&autoReconnect=true&zeroDateTimeBehavior=convertToNull&jdbcCompliantTruncation=false&user=queuemetrics&password=[removed]&socketTimeout=0&enableQueryTimeouts=false
--- End quote ---

What else can we try to remedy this issue? Thanks.

QueueMetrics:
How much memory does QM have? how many result rows is this query?

WRP:
The result returns just short of 1.2 million results. We have 3072 MB of memory allocated to QM (-Xms3072M -Xmx3072M).

I increased both of the memory settings to 5120M and restarted queuemetrics but saw the same error message when generating the custom report.

QueueMetrics:
I was thinking that this would lessen garbage collections and thence make reading quicker.
There must be a timeout somewhere?

WRP:
We have not (that I'm aware of) added any timeout logic. And again, the query works from the MySQL CLI client. Would it help if we sent you our config files directly?

Navigation

[0] Message Index

[#] Next page

[*] Previous page

Go to full version