[ZBX-8465] MySQL & InnoDB: Zabbix server executes queries that sit in "Creating sort index" state for several hours Created: 2014 Jul 10  Updated: 2017 May 30  Resolved: 2015 Sep 20

Status: Closed
Project: ZABBIX BUGS AND ISSUES
Component/s: Server (S)
Affects Version/s: 2.2.4
Fix Version/s: None

Type: Incident report Priority: Critical
Reporter: Synthead Assignee: Unassigned
Resolution: Duplicate Votes: 0
Labels: None
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified
Environment:

Ubuntu Linux (precise) x86_64, Percona Server 5.6, Zabbix server 2.2.4.

Unique MySQL options:
innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=4G
innodb_buffer_pool_size=16G

Issue Links:
Duplicate
duplicates ZBX-8042 On server startup, the value cache be... Closed

 Description   

Like title.

my.cnf: http://pastie.org/9375234
processlist of Percona Server 5.6: http://pastie.org/pastes/9375240/text
zabbix_server.conf: http://pastie.org/9375266

It should also be noted that when Zabbix server is running, Percona Server experiences an extremely high disk load, which makes the rest of the server extremely slow with default options (except the larger cache as detailed in the environment). Here's iotop when the queries are sitting at "Creating sort index": http://pastie.org/9375243

 Comments   
Comment by Andrejs Čirkovs (Inactive) [ 2014 Jul 11 ]

What's the size of your zabbix database? Can you list the problematic queries (like "show full processlist")?

Comment by Synthead [ 2014 Jul 11 ]

The Zabbix DB is 75 GB large.

Oh, and I should have mentioned this earlier: this problem is only encountered when Zabbix server starts. After the queries finish, the problem seems to go away. As of right now, the production server is running without this issue, but it can be reproduced by restarting Zabbix server. It's Friday, so at the end of the day, I'll restart it and post the result from "show full processlist".

Comment by Andrejs Čirkovs (Inactive) [ 2014 Jul 14 ]

OK, please post the exact query so we can check which dbpatch is causing this issue, thank you.

Comment by Synthead [ 2014 Jul 14 ]

My apologies, I forgot to get those queries Friday! This is "show full processlist\G": http://codepad.org/A5aKFr1h

Comment by Oleksii Zagorskyi [ 2014 Jul 17 ]

Hi load on db described in ZBX-8042.
Current issue looks like a duplicate, linked just in case.

Comment by Oleksii Zagorskyi [ 2015 Sep 20 ]

Closed as duplicate.

Generated at Wed Jul 16 09:58:03 EEST 2025 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.