Are you sure you have correct index for table history_uint? Please check.

Also execute:

explain select value from history_uint where itemid=16617 and clock>1353413817 and clock<=1353417417 order by clock desc limit 2

and post result here.

BTW The optimization is absolutely wrong.

The table has an index/key: KEY `history_uint_1` (`itemid`,`clock`)

Explain select returns:
==========

1 row in set (0.05 sec)
==========

For the record, our history_uint table is quite big and contains 25179305 entries.

The query cannot take more than 1-2 seconds to execute unless the table is locked or IO is very saturated. Try to execute it from command line to see what I mean. The query is absolutely perfect.

First run:
===
MariaDB [zabbix]> select value from history_uint where itemid=16617 and clock>1353413817 and clock<=1353417417 order by clock desc limit 2 ;
Empty set (7.45 sec)
===

Second run:
===
MariaDB [zabbix]> select value from history_uint where itemid=16617 and clock>1353413817 and clock<=1353417417 order by clock desc limit 2 ;
Empty set (2.80 sec)
===

Third run:
===
MariaDB [zabbix]> select value from history_uint where itemid=16617 and clock>1353413817 and clock<=1353417417 order by clock desc limit 2 ;
Empty set (3.01 sec)
===

Fourth run:
===
MariaDB [zabbix]> select value from history_uint where itemid=16617 and clock>1353413817 and clock<=1353417417 order by clock desc limit 2 ;
Empty set (2.76 sec)
===

The MySQL data files run from an SSD so it should be all nice & fast.
These issues were not there when we were using 1.8.x but only started happening after we re-installed 2.0.3 and started using that one. Maybe there is something changed in the table definition?

Table structure:
====
CREATE TABLE `history_uint` (
`itemid` bigint(20) unsigned NOT NULL,
`clock` int(11) NOT NULL DEFAULT '0',
`value` bigint(20) unsigned NOT NULL DEFAULT '0',
`ns` int(11) NOT NULL DEFAULT '0',
KEY `history_uint_1` (`itemid`,`clock`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
====

With the LogSlowQueries=3000 active in zabbix_server.conf we had 16248 slow query reports during a 15 hours period. That also makes mysqld use ~ 700% CPU as seen in atop history. The server uses Intel Core i7 @ 2.67GHz with 8GB RAM. Want to remind you that the mysql database is on SSD. The patched version of zabbix_server doesn't show any slow queries and works like a charm!

From unpatched zabbix_server log:
===
$ zgrep "slow query" /var/log/zabbix-server/zabbix_server.log.1.gz | wc -l
16248
===

Size of zabbix database:
===
$ du -sh /mnt/data/mysql/zabbix
9.6G /mnt/data/mysql/zabbix
===

===
Number of slow queries distributed by seconds it took to execute them:
Queries Seconds_taken
404 3 seconds (404 queries on the history_uint table took 3 seconds in the 15 hours period)
582 4 seconds
402 5 seconds
645 6 seconds
438 7 seconds
314 8 seconds
323 9 seconds
375 10 seconds
380 11 seconds
358 12 seconds
332 13 seconds
386 14 seconds
357 15 seconds
353 16 seconds
360 17 seconds
368 18 seconds
360 19 seconds
386 20 seconds
306 21 seconds
312 22 seconds
334 23 seconds
343 24 seconds
326 25 seconds
307 26 seconds
325 27 seconds
261 28 seconds
324 29 seconds
243 30 seconds
211 31 seconds
192 32 seconds
212 33 seconds
237 34 seconds
217 35 seconds
194 36 seconds
160 37 seconds
189 38 seconds
167 39 seconds
174 40 seconds
130 41 seconds
133 42 seconds
154 43 seconds
118 44 seconds
106 45 seconds
105 46 seconds
96 47 seconds
93 48 seconds
76 49 seconds
104 50 seconds
141 51 seconds
173 52 seconds
210 53 seconds
240 54 seconds
233 55 seconds
186 56 seconds
166 57 seconds
128 58 seconds
129 59 seconds
172 60 seconds
203 61 seconds
181 62 seconds
145 63 seconds
132 64 seconds
100 65 seconds
85 66 seconds
73 67 seconds
46 68 seconds
45 69 seconds
37 70 seconds
22 71 seconds
31 72 seconds
22 73 seconds
19 74 seconds
22 75 seconds
10 76 seconds
8 77 seconds
5 78 seconds
4 79 seconds
2 80 seconds
1 81 seconds
4 82 seconds
1 83 seconds
===

You have some problems with your database:

mysql> select value from history_uint where itemid=7755304 and clock>unix_timestamp(now() - INTERVAL 2 DAY) and clock<=unix_timestamp(now()) order by clock desc limit 2 ;
+-------+
| value |
+-------+
|     0 |
|     0 |
+-------+
2 rows in set (0.00 sec)

mysql> explain select value from history_uint where itemid=7755304 and clock>unix_timestamp(now() - INTERVAL 2 DAY) and clock<=unix_timestamp(now()) order by clock desc limit 2 ;
+----+-------------+--------------+-------+----------------+----------------+---------+------+------+-------------+
| id | select_type | table        | type  | possible_keys  | key            | key_len | ref  | rows | Extra       |
+----+-------------+--------------+-------+----------------+----------------+---------+------+------+-------------+
|  1 | SIMPLE      | history_uint | range | history_uint_1 | history_uint_1 | 12      | NULL |  126 | Using where |
+----+-------------+--------------+-------+----------------+----------------+---------+------+------+-------------+
1 row in set (0.01 sec)

mysql> select table_rows  from tables where table_name = 'history_uint';
+------------+
| table_rows |
+------------+
|  626679856 |
+------------+
1 row in set (0.02 sec)

Please check MySQL configuration. Your suggested query maybe not work on other supported by Zabbix databases.

Any news on this issue?

After upgrading the memory to 24GB and tweaking some mysql options we got it running smoothly. Thanks!

Thanks, I'm closing it then.