Note, additional improvements could be made by:

• Making this a parameter to the function for further control
• Testing and support for other DB Engines

Could you please describe what primary key you would like to add, what benefit is it expected to provide, and when does the server occasionally attempt to insert the same row twice?

We have modified our history* tables to have the following schema

CREATE TABLE `history` (
  `itemid` bigint(20) UNSIGNED NOT NULL,
  `clock` int(11) NOT NULL DEFAULT '0',
  `value` double(16,4) NOT NULL DEFAULT '0.0000',
  `ns` int(11) NOT NULL DEFAULT '0'
) ENGINE=TokuDB DEFAULT CHARSET=latin1
PARTITION BY RANGE (clock)
(
...
PARTITION p20160111 VALUES LESS THAN (1452470400)ENGINE=TokuDB
);
ALTER TABLE `history`
  ADD PRIMARY KEY (`itemid`,`clock`);

With this schema we are able to get roughly three times the performance we where getting. Disk read IOPS are significantly reduced, which is extremly important on the hardware we are using (networked SAN optimized for write once, read rarely workloads (with a SSD cache)). Partularly it now no longer takes ~2 minutes to render a complex graph for an item updating at 15seconds for 12/24hours.. The queries to render this graph now complete in 1-6 seconds (depending on SSD cache / age of data), or about 0.5 - 1s uncached per item in graph over a large span.

I am unsure when it happens, however I have observed (at rate of ~10/day on our current test server) errors like "Duplicate entry '42120-1452240997' for key 'PRIMARY' [insert into history_uint ..."

Given that the most frequent item is 1 per second this is obviously non-sensense, but regardless the correct behaviour would be to ignore the duplicate entry and insert the rest. The behaviour is however to abandon all values in the bulk due to the single or few duplicate values. either "INSERT IGNORE" or more correctly (since it updates the value with the later version) "INSERT ... ON DUPLICATE UPDATE").

Now this setup is likely not to be used by everyone, its our attempt to scale Zabbix up to ~1-2TB/60d historical data (size including trends for 2 yrs), while keeping the cost reasonable.

The "history" tables have "ns" field, which stands for "nanoseconds". So if two "clock"s are identical, the "ns" field is used to distinguish the timestamps. This is especially useful for log files. So ignoring all values with the same "clock" except one is incorrect.

In any case, partitioning is not officially supported by Zabbix, so you might wish to refer to https://www.zabbix.org/wiki/Getting_help for community help on that. It is proposed to close this request as "Won't fix".

Meanwhile, you might wish to vote on ZBXNEXT-806 or ZBXNEXT-714.

a) Partitioning is irrelevant to the feature suggestion, the ticket is regarding PRIMARY KEY support. But FYI despite the lack of official support, I dont know anyone without running Zabbix without partitioning.
b) log files are not responsible for the duplicate primary key messages, we do not need or use that functionality (honestly there are better solutions out there for that)
c) ns is largely irrelevant to monitors as the lowest update frequency is 1/s, regardless this patch would be recommended still if ns was included in the PK (its not in our schema, its not needed for our usecase)

This patch ensures graceful handling of duplicate key errors. Granted these will not happen with the current schema. Its within the conceivable realm of modifications those in need to scale will perform, and there is little to no cost to ensuring graceful handling.

This is not a request to change the current schema, any one running Zabbix at scale will have their own customizations. Regardless of weather you provide either TokuDB (or any other high scale database) schema or Partitioning schema those with the need will either develop in house, or hire someone to do it for them.

Offtopic; We regularly collaborate with the two other companies with large Zabbix installations, both run Partitioning, one runs TokuDB and the other a very significant hardware investment. I also have a few individuals who I have helped out along the way, normally the first thing done is to disable housekeeper and enable Partitioning.

The current schema already has an index for "itemid, clock" in all history tables. Could you please describe the benefits of turning that (officially, together with "ns") into a primary key?

Significantly reduced read IOPs leading to better performance on certain database engines. Leading to better read performance (write performance improvements unknown / not cared about in our case), probably improved as well (depending on clustering middle insert overheads).

Our benchmarks are with TokuDB, however I would expect the same (if not more) improvements with InnoDB which also clusters on its Primary Key in the engine. I am not aware of any improvements that would result in MyISAM or other DB platforms.

Clustered indexes store the row in with the index data saving a disk seek. Like a tree

[col1{m nodes}]->[col2{m nodes}]->[leaf node: row]

instead of

[col1{m nodes}]->[col2{m nodes}]->[leaf node: row* pointer]

Extremely simplified example (for the common query of format itemid=? AND clock < ? and clock > ?)

No PK: Lookup itemid: ? -> perform range query for clock between ? and ? -> foreach index result (pointer to row) -> seek to pointer to row (return)
With PK: Lookup itemid: ? -> perform range query for clock between ? and ? -> foreach index result, return

I hope that makes some sense, its easier to draw. Heres a stack overflow thats mostly correct - http://stackoverflow.com/questions/1251636/what-do-clustered-and-non-clustered-index-actually-mean

On a rotational disk (particularly a slow one) without a PK significantly more reads and seeks are performed. This is accurate for MySQL, I see no reason it wouldnt be for others.

Note: TokuDB specifically does support additional non-unique clustered indexes, we did however not see much improvement from using this feature (only a marginal improvement of ~5-10%). That engine specific however.

Dear Mathew,

where did you add this:
#ifdef HAVE_MYSQL
zbx_strcpy_alloc(&sql_command, &sql_command_alloc, &sql_command_offset, " ON DUPLICATE KEY UPDATE value=VALUE(value)");
#endif

file src/libs/zbxdbhigh/db.c but which line in the function?

I'm adapting this change but is this really everything you changed? How about the db overflow function? how did you ensure that the field value does exist? Why did you not update all fields and just value?

First of all, Mathew, thank you very much! Your post is very useful!

Before switching to PK(itemid, clock) at history and history_uint tables we were waiting about 12 seconds to draw graphs for 1-3d from history for 10 items. Now it's about 0.5 sec for 10 items for 1-3 days.
Another good feature: we do not collect several values for same itemid and clock (second) anymore. It is rather mistake to get them in history and history_uint tables. That was the reason why our DB has grown twice bigger than it is now.

Patching C code is necessary for such PK! Otherwise you will get stalled triggers for random items, that is very annoying and discouraging. The reason is desynchronization between Zabbix Server cache and DB data as a result of exceptions of PK violations and discarding large portions of values of random items and triggers from being inserted/updated to DB.

My first version of patch is here. It's rather simple and could be improved I believe, but it works for me:
https://github.com/vagabondan/zabbix-patches/tree/master/zabbix-3.2/ZBXNEXT-3089

Reference:
We have Zabbix v3.2.4 instance with performance of 10knvps, DB size (after implementing changes) is 800 GB for 7 days raw history and about 1 year of trends. DB type Percona MySQL, v.5.7.17, engine InnoDB (=XtraDB in case of Percona).
Web: nginx+apache+php-fpm.

Sorry for not getting back to you Stefan Priebe, The patch by Andrey adds it in the correct location.

We did not have any issues with the overflow function, it could just be our use case did not pass though those paths - or perhaps due to us using an older version. I don't see any problems with Andrey's patch myself - when we upgrade next I'll give it a shot (It's clear he put some effort into it +1).

We have had the odd (~3) stalled triggers over the years, but never any serious quantities. Always fixed with a restart. I didn't even think to associate it with this patch! It's very possible that it's the cause.

Other than that, we have been running this in production happily. Without this patch we would have needed to upgrade our servers again this year most likely, now it's still running at <10% CPU, DB size is almost 500GB at 7knvps. Graphs are near instant

I applied the patch from @Andrey but i'm getting stalled triggers every few days. Any idea why this happens?

Is there some pattern behind the triggers that stall?

Haven't found one. To me it seems to be a difference between Zabbix Cache and DB? Which get's solved by a restart as the cache is then rebuild from the DB?

I'm very curious why I haven't hit it. I wonder if something in the newer Zabbix's makes it worse.

Are you doing 1 - 2s polling or something that creates alot of conflicts?

No smallest check is 60s and those where i got it were 120s

I'm happy to start debugging - just not sure where to start.

Stefan, please look at ZBX-11768 and ZBX-11454. Might be related to your issue.

Even though i'm seeing the same message like described in https://support.zabbix.com/browse/ZBX-11768 i never had that problem before this commit. But may be i missed it just by accident. At least i'm seeing exactly these entries.

It is very strange @Stefan. Please check once again that you`ve applied all changes to C code and recompiled new binaries from modified sources.
By the way, did you implement primary keys only for history and history_uint tables or to all history tables?

Code is applied to local git repo. Source is also def. rebuild. I only changed history and history_uint table.

Do you observe new debug messages in log file from newly implemented code on debuglevel=5?
If you could send me modified files I would check the difference with my version.

With debuglevel 5 the log is spammed too much. I'll do the following:
1.) wait until it happens again
2.) if it happens on a regular basis i'll modify the code so that it logs even with debuglevel=0

Mhm today i had again something like this:
52298:20170420:110900.599 cannot find open problem events for triggerid:302027, lastchange:1492679340
52298:20170420:110902.337 cannot find open problem events for triggerid:108505, lastchange:1492679342
52298:20170420:110908.353 cannot find open problem events for triggerid:230724, lastchange:1492679348
52298:20170420:110909.351 cannot find open problem events for triggerid:275841, lastchange:1492679349
52298:20170420:110911.452 cannot find open problem events for triggerid:112434, lastchange:1492679351

Any idea how to test / debug this?

I'm currently testing Andrey's patch on an up to date version of Zabbix.

No TokuDB on this server, just InnoDB/TokuDB. Performance is a little worse due to this (was <10% now 20-40% under similar kvps out 200%). Still a big improvement compared to no PK (was at 200% and failing).

2 High Spec Xeon Cores, 4GB ram, All SSD storage.

No stalls or issues detected at this stage.

Stefan, I believe, we are talking about different problems.

In my case, stalled triggers were triggers in problem status while item values hadn't been satisfying triggers condition anymore, i.e. triggers did not came back to OK status although item values had really returned to level under trigger threshold.

In your case, messages states that events are missing for some triggers. It could be a housekeeper issue and resembles this situation:
https://support.zabbix.com/browse/ZBX-11426

No that should not be the case. The mentioned triggers and problems were just some minutes old. So they could not be removed by the house keeper.

zabbix produce duplicate entries in database when exiting.

syncer 2:

  1289:20170422:222720.615 __zbx_zbx_setproctitle() title:'history syncer #2 [synced 0 items in 0.000042 sec, syncing history]'
  1289:20170422:222720.615 In DCsync_history() history_num:83
  1289:20170422:222720.616 query [txnlev:1] [begin;]
  1289:20170422:222720.616 In DCmass_update_items()
....
  1289:20170422:222721.515 In DCmass_add_history()
  1289:20170422:222721.515 query [txnlev:1] [insert into history (itemid,clock,ns,value) values (134733,1492889240,1,0.000000),(134741,1492889240,2,0.000000),(134734,1492889240,4,0.000000),(134742,1492889240,5,0.000000),(134735,1492889240,7,36.000000),(134743,1492889240,8,0.020000),(134736,1492889240,10,67.500000),(134744,1492889240,11,0.050000),(140410,1492889240,13,0.940000),(140411,1492889240,14,0.080000),(134739,1492889240,16,0.000000),(134746,1492889240,17,0.000000),(134845,1492889240,19,1.310000),(134847,1492889240,20,0.000000),(134846,1492889240,22,1.270000),(134848,1492889240,23,0.010000),(136076,1492889240,25,0.000000),136077,1492889240,26,0.000000),(141318,1492889240,28,0.960000),(141319,1492889240,29,0.130000),(136389,1492889240,31,0.940000),(136390,1492889240,32,0.080000);
] 
  1289:20170422:222721.516 query [txnlev:1] [insert into history_uint (itemid,clock,ns,value) values (134728,1492889240,0,35),(134729,1492889240,3,35),(134730,1492889240,6,0),(134731,1492889240,9,0),(140409,1492889240,12,0),(134738,1492889240,15,35),(134843,1492889240,18,0),(134844,1492889240,21,0),(136075,1492889240,24,36),(141317,1492889240,27,0),(136388,1492889240,30,0),(124978,1492889240,276077174,2),(124981,1492889240,276077174,48),(124969,1492889240,277202600,1),(137361,1492889241,305425779,1),(98001,1492889241,305585308,2),(97941,1492889241,305683354,2),(98121,1492889241,305705949,0),(98601,1492889241,305804258,2),(98421,1492889241,305841939,4),(98541,1492889241,305988850,0),(98661,1492889241,306035939,2),(136317,1492889241,307932899,408),(136205,1492889241,307932899,0),(133776,1492889241,307932899,11179074),(136293,1492889241,307932899,24),(136289,1492889241,307932899,0),(136181,1492889241,307932899,0),(136265,1492889241,307932899,0),(127071,1492889241,340680532,1),(127004,1492889241,340680532,1),(127014,1492889241,340680532,528),(127092,1492889241,340680532,824),(126998,1492889241,340680532,360),(127000,1492889241,340680532,216),(126999,1492889241,340680532,504),(127011,1492889241,340680532,224),(127006,1492889241,340680532,1),(127010,1492889241,340680532,608),(127007,1492889241,340680532,1),(127001,1492889241,340680532,976),(127002,1492889241,340680532,192),(127005,1492889241,340680532,1),(127008,1492889241,340680532,1),(127012,1492889241,340680532,472),(127201,1492889241,340680532,0),(127013,1492889241,340680532,1232),(126949,1492889241,340680532,27592328);
] 
  1289:20170422:222721.859 query [txnlev:1] [insert into history_text (itemid,clock,ns,value) values (133771,1492889241,307932899,'1.30.B022'),(133770,1492889241,307932899,'A1');
] 
...
  1289:20170422:222721.915 End of zbx_process_triggers()
  1289:20170422:222721.915 End of DCmass_update_triggers()
  1289:20170422:222721.915 In DCmass_update_trends()
  1289:20170422:222721.915 End of DCmass_update_trends()
  1289:20170422:222721.915 In process_trigger_events() events_num:0
  1289:20170422:222721.915 End of process_trigger_events() processed:0
  1289:20170422:222721.915 query [txnlev:1] [commit;]
  1289:20170422:222722.499 Got signal [signal:15(SIGTERM),sender_pid:1193,sender_uid:106,reason:0]. Exiting ...

note itemid 133770 for example.

parent process:

  1193:20170422:222722.483 Got signal [signal:15(SIGTERM),sender_pid:25686,sender_uid:0,reason:0]. Exiting ...
  1193:20170422:222722.498 zbx_on_exit() called
  1193:20170422:222724.498 In DBconnect() flag:1
  1193:20170422:222724.499 End of DBconnect():0
  1193:20170422:222724.499 In free_database_cache()
  1193:20170422:222724.499 In DCsync_all()
  1193:20170422:222724.499 In DCsync_history() history_num:89
  1193:20170422:222724.499 syncing history data...
  1193:20170422:222724.499 query [txnlev:1] [begin;]
  1193:20170422:222724.499 In DCmass_update_items()
.....
  1193:20170422:222724.500 In DCmass_add_history()
  1193:20170422:222724.500 query [txnlev:1] [insert into history (itemid,clock,ns,value) values (117802,1492889237,437534335,100.000000),(117804,1492889237,437534335,0.000000),(131650,1492889237,437534335,0.000518),(131648,1492889237,437534335,0.000000),(134733,1492889240,1,0.000000),(134741,1492889240,2,0.000000),(134734,1492889240,4,0.000000),(134742,1492889240,5,0.000000),(134735,1492889240,7,36.000000),(134743,1492889240,8,0.020000),(134736,1492889240,10,67.500000),(134744,1492889240,11,0.050000),(140410,1492889240,13,0.940000),(140411,1492889240,14,0.080000),(134739,1492889240,16,0.000000),(134746,1492889240,17,0.000000),(134845,1492889240,19,1.310000),(134847,1492889240,20,0.000000),(134846,1492889240,22,1.270000),(134848,1492889240,23,0.010000),(136076,1492889240,25,0.000000),(136077,1492889240,26,0.000000),(141318,1492889240,28,0.960000),(141319,1492889240,29,0.130000),(136389,1492889240,31,0.940000),(136390,1492889240,32,0.080000),(23302,1492889242,488341822,0.000000);
]
  1193:20170422:222724.500 query [txnlev:1] [insert into history_uint (itemid,clock,ns,value) values (131647,1492889237,437534335,1),(117801,1492889237,437534335,0),(134728,1492889240,0,35),(134729,1492889240,3,35),(134730,1492889240,6,0),(134731,1492889240,9,0),(140409,1492889240,12,0),(134738,1492889240,15,35),(134843,1492889240,18,0),(134844,1492889240,21,0),(136075,1492889240,24,36),(141317,1492889240,27,0),(136388,1492889240,30,0),(124978,1492889240,276077174,2),(124969,1492889240,277202600,1),(137361,1492889241,305425779,1),(98001,1492889241,305585308,2),(97941,1492889241,305683354,2),(98121,1492889241,305705949,0),(98601,1492889241,305804258,2),(98421,1492889241,305841939,4),(98541,1492889241,305988850,0),(98661,1492889241,306035939,2),(136233,1492889241,307932899,816),(133776,1492889241,307932899,11179074),(127005,1492889241,340680532,1),(127093,1492889241,340680532,1384),(127007,1492889241,340680532,1),(127008,1492889241,340680532,1),(127006,1492889241,340680532,1),(127004,1492889241,340680532,1),(126949,1492889241,340680532,27592328),(127201,1492889241,340680532,0),(140122,1492889242,488500358,2864),(142282,1492889242,488545788,944);
]
  1193:20170422:222724.501 query [txnlev:1] [insert into history_text (itemid,clock,ns,value) values (133770,1492889241,307932899,'A1'),(133771,1492889241,307932899,'1.30.B022');
]
....
  1193:20170422:222733.456 End of DCflush_trends()
  1193:20170422:222733.456 query [txnlev:1] [commit;]
  1193:20170422:222733.555 syncing trend data done
  1193:20170422:222733.555 End of DCsync_trends()
  1193:20170422:222733.555 End of DCsync_all()
  1193:20170422:222733.555 In zbx_mem_destroy() descr:'history cache'
  1193:20170422:222733.555 End of zbx_mem_destroy()
  1193:20170422:222733.555 In zbx_mem_destroy() descr:'history index cache'
  1193:20170422:222733.555 End of zbx_mem_destroy()
  1193:20170422:222733.555 In zbx_mem_destroy() descr:'trend cache'
  1193:20170422:222733.555 End of zbx_mem_destroy()
  1193:20170422:222733.555 End of free_database_cache()
  1193:20170422:222733.555 In free_configuration_cache()
  1193:20170422:222733.555 In zbx_mem_destroy() descr:'configuration cache'
  1193:20170422:222733.555 End of zbx_mem_destroy()
  1193:20170422:222733.555 In zbx_strpool_destroy()
  1193:20170422:222733.555 In zbx_mem_destroy() descr:'string pool'
  1193:20170422:222733.555 End of zbx_mem_destroy()
  1193:20170422:222733.555 End of zbx_strpool_destroy()
  1193:20170422:222733.555 End of free_configuration_cache()
  1193:20170422:222733.555 In zbx_vc_destroy()
  1193:20170422:222733.555 In zbx_mem_destroy() descr:'value cache size'
  1193:20170422:222733.555 End of zbx_mem_destroy()
  1193:20170422:222733.555 End of zbx_vc_destroy()
  1193:20170422:222733.555 In zbx_free_ipmi_handler()
  1193:20170422:222733.555 End of zbx_free_ipmi_handler()
  1193:20170422:222733.555 In free_selfmon_collector() collector:0x7f471099a000
  1193:20170422:222733.555 End of free_selfmon_collector()
  1193:20170422:222733.555 In zbx_unload_modules()
  1193:20170422:222733.555 End of zbx_unload_modules()
  1193:20170422:222733.555 Zabbix Server stopped. Zabbix 3.3.0 (revision 65339).

push same items to database again.

After fighting very slow graphs, the above solution works excellent! Some details.

I started by applying the Andrey;s patch to zabbix 3.0.7 (which we're currentje using).

Next I renamed the existing tables to old tables, and created new tables:

MariaDB [zabbix]> rename table history to history_old;
MariaDB [zabbix]> CREATE TABLE `history` (
      >  `itemid` bigint(20) unsigned NOT NULL,
      >  `clock` int(11) NOT NULL DEFAULT '0',
      >  `value` double(16,4) NOT NULL DEFAULT '0.0000',
      >  `ns` int(11) NOT NULL DEFAULT '0',
      >  PRIMARY KEY `history_1` (`itemid`,`clock`, `ns`)
      > ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
MariaDB [zabbix]> rename table history_uint to history_uint_old;
MariaDB [zabbix]> 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',
      >   PRIMARY KEY `history_1` (`itemid`,`clock`, `ns`)
      > ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
MariaDB [zabbix]>

Note I included ns in the PRIMARY KEY, to reduce the duplicates count.
I restarted the zabbix-server to make sure it proceeds storing data in the new tables. In the graphs history (not the trends) had gone, but "new" history was added in the present.
Next I exported th old tables and imported them in the new tables:

[rolf.fokkens@zabbix-test ~]$ mysqldump --no-create-info -u zabbix -p zabbix history_old | xz -T 3 > history.sql.xz
Enter password: 
[rolf.fokkens@zabbix-test ~]$ xzcat -T 3 history.sql.xz | grep INSERT | sed 's/history_old/history/' | mysql -u zabbix -p zabbix
Enter password: 
[rolf.fokkens@zabbix-test ~]$ mysqldump --no-create-info -u zabbix -p zabbix history_uint_old | xz -T 3 > history_uint.sql.xz
Enter password: 
[rolf.fokkens@zabbix-test ~]$ xzcat -T 3 history_uint.sql.xz | grep INSERT | sed 's/history_uint_old/history_uint/' | mysql -u zabbix -p zabbix
Enter password: 

Finally I drop the old tables:

MariaDB [zabbix]> drop table history_old;
MariaDB [zabbix]> drop table history_uint_old;

Now the performance of graphs in the UI is excellent!

Patch was updated to 3.4 release version and still available by link https://github.com/zabbix/zabbix-patches.

We also obtained a significant speed up with PRIMARY KEY(itemid, clock, ns) on history and history_uint. Before the change a 3day graph on a single item took ~10 seconds to draw (8640 raw data points). Now with primary key it takes no more than a second.

As it was already noted, a clustered index puts values together on the same page so that values for a specific itemid are chronologically located on the same InnoDB (or whatever) page. So as it was shown in our case, a processing of 3day graph requires to read far more pages with the current scheme than with a primary key. I would even speculate that the current scheme requires to read 8640 individual pages vs. 22 pages if a primary key would have been used = 8640 / (16384( = page size) * .8 (= reserved for record growth) / 32 (= record size)).

Plus (MySQL specific), there is an indirection with InnodDB secondary indexes since they refer to primary/rowid column value (not to physical offset). So there are twice as more reads than with just a primary key only.

So the question is, why this change is not in the mainline?
Dear developers, do you care to answer?

Any hesitations about getting this patch into mainline? Running a Percona XtraDB cluster in production and seeing errors related to the lack of a primary key. Seems quite stable and sensible to add this change to increase performance.

We also may want to consider extending this idea to the history_text table as well. I could make the change on my installation but I am unsure of the implications of just changing the db structure without changing code.

The addition of a primary key seems to help achieve better performance for medium and large setups. I am having good luck with the history and history_uint so far, though it needs to be noted that the patch needs to be implemented with a combination of Rolf's steps above for anyone finding this thread and needing some clarity.

In Zabbix 3.4 MySQL schema, I've identified the following tables as the ones without primary keys:

| zabbix             | dbversion                                          | InnoDB             |
| zabbix             | history                                            | InnoDB             |
| zabbix             | history_str                                        | InnoDB             |
| zabbix             | history_uint                                       | InnoDB             |

This is my currently proposed workaround:

ALTER TABLE history_uint add pk_column INT AUTO_INCREMENT PRIMARY KEY;
ALTER TABLE history_str add pk_column INT AUTO_INCREMENT PRIMARY KEY;
ALTER TABLE history add pk_column INT AUTO_INCREMENT PRIMARY KEY;
ALTER TABLE dbversion add pk_column INT AUTO_INCREMENT PRIMARY KEY;

This technically protects us also when Zabbix tries to make duplicate entries. It doesn't know of these "pk_column" columns so it shouldn't try to include that in its queries...right?

david.ko, IMHO,
looks strange for me, i.e. primary keys show great benefits when index == primary key.
So in best situation, zabbix will use itemid and clock as primary key. Just adding new auto incremented key - useless.

Background:
I have been trying to use Percona_XTRA_DB cluster with my zabbix setup and this patch got me most of the way to quieting the logs with this error:
[Warning] WSREP: Percona-XtraDB-Cluster doesn't recommend use of DML command on a table <db.tablename>

In order for Percona-XtraDB cluster to work properly without being isolated to a single write master and/or potentially miss updates, all tables should be updated with primary keys.

To add to what David posted, here is the list of tables that appear to be most active and lack a primary key:
history
history_uint
history_str
history_text

I extended the patch to include these (removed duplicate parts... see my github for full patch):
int zbx_db_insert_execute(zbx_db_insert_t *self)
{
int ret = FAIL, i, j;
+ int isPK = 0;
+ char* history_tab="history";
+ char* history_uint_tab="history_uint";
+ char* history_text_tab="history_text";
+ char* history_str_tab="history_str";

<removed the unimportant text>
and then:

+#ifdef HAVE_MYSQL
+ if(strcmp(self->table->table,history_tab) == 0 || strcmp(self->table->table,history_uint_tab) == 0 || strcmp(self->table->table,history_text_tab) == 0 || strcmp(self->table->table,history_str_tab) == 0 )
+ isPK = 1;
+ zabbix_log(LOG_LEVEL_DEBUG, "zbx_db_insert_execute: in HAVE_MYSQL: history_tab=[%s], history_uint_tab=[%s], history_text_tab=[%s], history_str_tab=[%s], isPK=[%d]", history_tab,history_uint_tab,history_text_tab,history_str_tab,isPK);
+#endif

I also made the table changes to add PK support:

rename table history_text to history_text_old;
CREATE TABLE `history_text` (`itemid` bigint(20) unsigned NOT NULL,`clock` int(11) NOT NULL DEFAULT '0',`value` text NOT NULL,`ns` int(11) NOT NULL DEFAULT '0',PRIMARY KEY `history_1` (`itemid`,`clock`, `ns`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;

rename table history_str to history_str_old;
CREATE TABLE `history_str` (`itemid` bigint(20) unsigned NOT NULL,`clock` int(11) NOT NULL DEFAULT '0',`value` varchar(255) NOT NULL DEFAULT '',`ns` int(11) NOT NULL DEFAULT '0',PRIMARY KEY `history_str_1` (`itemid`,`clock`, `ns`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I've posted a clone of the zabbix patches with my edits here 3089 and zabbix 3.4 only: https://github.com/jgerry2002/zabbix-patches.git

If anyone has time or the ability to test this out and verify that my changes are even remotely sane it would be helpful. I may have missed something in this process as it was mostly a guess as to how all this works. So far things appear to be running in my own version of zabbix (3.4.6) without any issues, but my setup is fairly new and does not contain much history data yet.

If anyone has time or the ability to test this out and verify that my changes are even remotely sane it would be helpful.

I did exactly this in our code, except also for history_log table. I had planned to test this out today. I'll post with updates later today.

FYI, we're using the official Docker image supplied by Zabbix: https://hub.docker.com/r/zabbix/zabbix-server-mysql/

I got their Dockerfile and made the following changes to make a new "patched" image:

107,115c107
<     make -j"$(nproc)" -s dbschema 1>/dev/null
<
< # Patch ZBXNEXT-3089
< # https://github.com/zabbix/zabbix-patches/blob/master/zabbix-3.4/ZBXNEXT-3089/ZBXNEXT-3089.patch
< ADD patches/ZBXNEXT-3089/include/db.h /tmp/zabbix-${ZBX_VERSION}/include/
< ADD patches/ZBXNEXT-3089/src/libs/zbxdbhigh/db.c /tmp/zabbix-${ZBX_VERSION}/src/libs/zbxdbhigh/
< ADD patches/ZBXNEXT-3089/database/mysql/schema.sql /tmp/zabbix-${ZBX_VERSION}/database/mysql/schema.sql
<
< RUN cd /tmp/zabbix-${ZBX_VERSION} && \
---
>     make -j"$(nproc)" -s dbschema 1>/dev/null && \

Keep in mind that the released patch, does not have the logging check for all the history tables types, just history and history_uint. I've added the other types in a similar way.

Funny you should mention history_log. I just observed history_log warnings last night, so I will once again extend the patch to cover that as well. I'll post something when I update my version of the patch again tomorrow.

If possible, you should attempt to insert some history data to check with. I have a semi-live site with about a 600 devices in it but its ok if I have to make some changes at least for the short term.

Added a modification for history_log in the patch. It appear to compile ok.

Recreated the history_log table:

rename table history_log to history_log_old;
CREATE TABLE `history_log` (`itemid` bigint(20) unsigned NOT NULL,`clock` int(11) NOT NULL DEFAULT '0',`timestamp` int(11) NOT NULL DEFAULT '0',`source` varchar(64) NOT NULL DEFAULT '',`severity` int(11) NOT NULL DEFAULT '0',`value` text NOT NULL,`logeventid` int(11) NOT NULL DEFAULT '0',`ns` int(11) NOT NULL DEFAULT '0',PRIMARY KEY `history_log_1` (`itemid`,`clock`, `ns`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Posted an update to my github copy of all these patches.

Added a modification for history_log in the patch. It appear to compile ok.

Same here. I did the patch for all of the history* tables. Compilation showed no issues. I dumped roughly 10GB of data into each table, and spinned up Zabbix 3.4.6. Everything seems normal, and it is collecting data just fine.

Isn't the elastic search support in 3.4 far better than this? (https://www.zabbix.com/documentation/3.4/manual/appendix/install/elastic_search_setup)

Sure, but this is what it says in the doc you linked:

Elasticsearch support is experimental!

David, do your modifications differ from mine at all?

I am running the modifications in semi-production without any issues. I have very low load on my XtraDB cluster as well. I'm on marginal/older hardware, so every bit of extra performance I can obtain is worth my time.

Stefan, I had not noticed the elastic search support, but yes elastic search would be a much stronger and better performing solution going forward as it is fairly straightforward to create elasticsearch machines/clusters/containers.

Justin, I'm glad someone else is actively looking at this besides me.

We are doing a complete migration to "real" production tomorrow. So far, in test, everything seems okay, but I have not been able to obtain any performance data. Once I have something, I'll post an update.

david.ko
Also measure storage size - how much index data eats.

Also measure storage size - how much index data eats.

I would have wanted to know about this as well, but I don't think this is very trivial. As the issue Reporter said, Zabbix has a tendency of "occasionally attempting to insert the same row twice." I actually forgot about this when first attempting the migration, and then realized there's a bunch of duplicate rows in all the history* tables, which caused the load to fail due to the new primary keys. I had to replace all the "INSERT INTO history*" with "INSERT IGNORE INTO history". This means that I'll be throwing away all the duplicate rows (well, rows that now have the same primary keys that we defined above - itemid, clock, ns), meaning less amount of data. There definitely is a way to measure exactly how much I'm throwing away, but I don't think our team has much time to spend on this.

Justin, I realized I never answered this question:

David, do your modifications differ from mine at all?

So far so good in production. I applied this patch to Zabbix server version 3.4.5. I don't see anything in the logs that report warns or errors that could be related to this. I'll keep watching this thread to answer any questions others may have.

Hello all.
I must add my experince. Only tables that haven't primary keys are history tables. We have mysql DB in Gallera cluster setup and size of biggest history table count many rows (i can add details i don't remeber righr now) . Size of our zabbix DB is near 200GB. We encouring problem with DB synchonization when we delete some host, which cause clear history tables. At this moment galera synchronization of tables without primary key working with full table scan on synchronizing host. If I remove host, it cause delete milions of history rows and this is serious performance problem on host which needs to synchronize. We need to carefully remove monitored hosts, because of this issue. In tihs case i need to vote for add primary keys to these tables. I don't care about disk size which these keys consume, because it add speed up and solve best prsctice for using Galera cluster.

Hello guys,

does anybody know if this patch also works for Zabbix 4.0 LTS version?

I would need to replicate the MySQL 5.7 zabbix DB. Is it possible to revert the primary key patch after the replication for better patch management.

Thanks.

Best regards

Mike

With MySQL Group Replication it is a requirement to have Primary Keys on all tables.

https://dev.mysql.com/doc/refman/5.7/en/group-replication-requirements.html

Is Zabbix going to stay compatibe with MySQL?

Probably not, I gave up. We'll be deprecating our Zabbix soon and I'll stop watching this thread.

Please consider this as a critical/blocker.

MariaDB 10.4 with ~3Tb storage space used, LTS 5.0:

+----------------------------+--------+---------+------------+------------+----------------+--------------+-----------------+--------------+--------------+----------------+---------------------+---------------------+------------+-----------+----------+----------------+---------+------------------+-----------+
| Name                       | Engine | Version | Row_format | Rows       | Avg_row_length | Data_length  | Max_data_length | Index_length | Data_free    | Auto_increment | Create_time         | Update_time         | Check_time | Collation | Checksum | Create_options | Comment | Max_index_length | Temporary |
+----------------------------+--------+---------+------------+------------+----------------+--------------+-----------------+--------------+--------------+----------------+---------------------+---------------------+------------+-----------+----------+----------------+---------+------------------+-----------+
| history                    | InnoDB |      10 | Dynamic    | 8324945384 |             59 | 496652140544 |               0 | 386167226368 | 412009627648 |           NULL | 2021-08-25 15:36:36 | 2021-08-25 12:53:30 | NULL       | utf8_bin  |     NULL | partitioned    |         |                0 | N         |
| history_log                | InnoDB |      10 | Dynamic    |         61 |          16384 |       999424 |               0 |       999424 |            0 |           NULL | 2021-08-25 02:00:02 | NULL                | NULL       | utf8_bin  |     NULL | partitioned    |         |                0 | N         |
| history_str                | InnoDB |      10 | Dynamic    | 9283747054 |             67 | 628587003904 |               0 | 425439019008 |    244318208 |           NULL | 2021-08-25 02:00:02 | 2021-08-25 11:43:38 | NULL       | utf8_bin  |     NULL | partitioned    |         |                0 | N         |
| history_text               | InnoDB |      10 | Dynamic    |         61 |          16384 |       999424 |               0 |       999424 |            0 |           NULL | 2021-08-25 02:00:02 | NULL                | NULL       | utf8_bin  |     NULL | partitioned    |         |                0 | N         |
| history_uint               | InnoDB |      10 | Dynamic    | 8156686972 |             59 | 485237309440 |               0 | 368067182592 |    273678336 |           NULL | 2021-08-25 02:00:02 | 2021-08-25 11:43:38 | NULL       | utf8_bin  |     NULL | partitioned    |         |                0 | N         |

 
Two problems - size of the index and also slowdown of external data exporting, changing index to primary key reduces x10 time (from 10 to 1 minute) to export history data from the same table and the same data and the same hardware spec.

*Using index is not efficient approach in terms of a space usage and DB server performance, especially with NO support of ClickHouse/Cassandra and other scalable solutions. *

Edgar perhaps you can encourage this by submitting your LTS 5.0 version of the patch?

I'm still running this myself however on an outdated Zabbix version.

We are currently investigating the possibility of introducing primary keys for history tables in Zabbix 6.0. So far our tests demonstrate relatively minor (5-10% max) performance improvements likely caused by significant savings in disk space (around 30%). The tests were performed with MySQL and PostgreSQL on a large data set.

@alexei Does this include the performance of running housekeeper in cleaning up millions of datapoints?

splitice, performance of housekeeper is not a priority. The tests are mainly focused on the performance of insert and select operations.

@alexei, system performance is the sum of all it's parts. The housekeeper which runs regularly will stress the database server quite significantly. You could view this as lowering the performance of inserts for it's entire operation.

I'd like to stress that in our testing (originally in 2016) the performance gain was considerable. It took the server from not working at all (overloaded) to working snappily. I havent ever tested since without the history pk patch since. Currently running it on 5.4.3 with 4 knvps (occasionally peaks above 10k due to trappers). CPU usage is 70-80% of one core.

splitice, I am with you. What I'd like to say is that housekeeper performance (it will be better for sure) won't affect our decision, why test then.

When you say large data set... I believe we are all engineers here and can handle numbers in TeraBytes or millions of rows.

Please state real world numbers.

With a moderate data set of around 260M of records in history (23.5GB data+index, MySQL) we see around 40% disk space saving when using primary keys. PostgreSQL uses around 31GB of disk space with original schema and 23GB with primary keys for the same data set, so it is about 27% less disk space.

We definitely want to introduce primary keys for history tables in 6.0. Now double checking that it won't introduce any issues with partitioning and Timescale, also this will require certain changes on Zabbix Server side to make sure that we handle duplicate values nicely.

That are some interesting numbers there @alexei Thanks for the insight - this seems like a massive improvement in regards to disk space, especially in the bigger environments. In those bigger environments a 5-10% observed improvement of performance is significant imo. 

brian.baekel, true. That's why it is so tempting to have it implemented. Today we are finishing our final tests with Timescale, if everything goes well then it goes straight to dev team for inclusion into 6.0. Note that the database schema will not be adjusted during upgrade from earlier versions of Zabbix for obvious reasons.

Hello alexei, how does this feature interact with scalable storage in ZBXNEXT-714? Both are in roadmap for 6.0.

Shouldn't it be implemented there, when saving new data?

fpaternot, ZBXNEXT-714 is nothing to do with this ticket, it is about having well-defined history API to enable storage of time series data in any external engines using pluggable architecture.  I have to say that ZBXNEXT-714 will likely be postponed to 6.2.

All preparation work and tests are finished. We are introducing primary keys for all tables in Zabbix 6.0.

Related

I've been looking into the possibilities of using the MariaDB connect engine's parititioning capability instead of InnoDB's recently with the potential goal of either:

a) Spreading load and storage requirements out to multiple mysql servers (active + archival); or

b) Introducing tables shared to S3 (MariaDB S3 engine)

Still early days in evaluation (Currently waiting on a larger dataset of approx 40GB to import into the new schema).

As part of this work I've identified that it might worth exploring the pro/con's of "INSERT ... ON DUPLICATE KEY UPDATE" and "INSERT IGNORE" as well as the potential of inserting directly into partitions in a CONNECT based table. I'll be experimenting with these once a larger dataset is imported.

splitice, data storage must not require update operations if possible, that is why we will not use "INSERT ... ON DUPLICATE KEY UPDATE" for sure. It will make history API much simpler and lowers the bar for various storage engines.

alexei, your PRIMARY KEY changes for 6.0 will include nanoseconds in the primary key (itemid,clock,ns)?

I must admit all our testing in recent years has been with a primary key on "itemid,clock" (with the patch in this issue). That might account for some peoples here increased performance on top of your testing. I'd argue many people don't really care about nanoseconds and hence are happy to be restricted to one value per second accuracy.

I'm thinking our testing of CONNECT will involve patching the INSERT query builder to insert into "history_p$format"  as I think that will be required to get the INSERT (zabbix-server) side of connect functioning. 

My initial findings with CONNECT have been:

1. CONNECT uses table level read/write locking (may be after partition selection, to be investigated). INSERT'ing onto a CONNECT table may have insufficient performance (possibly advoid with underlying table INSERT)
2. CONNECT is not compatible with the query cache (non issue, it's not a performance requirement for Zabbix Server)
3. CONNECT does not support INSERT IGNORE or INSERT ... ON DUPLICATE KEY UPDATE
4. SELECT performance over CONNECT is in the same order of magnitude as a InnoDB partitioned table (small scale test, large scale test data loading currently)

I think it has real potential when paired with MariaDB's S3 engine (also there are other mediums exposed either via CONNECT or directly is engines) for archive data and may present an alternative to you guys supporting other storage mediums directly as the data is exposed directly as a read-only MariaDB/MySQL table with most of the same capabilities.

splitice, Primary key will be based on (itemid,clock,ns). I do not quite understand the idea with CONNECT (IMHO it will kill performance), it sounds like off-topic anyway.

alexei,

I think the root issue in this issue is the quest for high performance and scalability for larger users. Our extension of this to enable archival storage is interlinked (high speed NVMe storage is very expensive, we can afford either high speed storage or high capacity) is a bit off-topic but related at the design level.

The users participating in this issue are using primary keys and partitioning to acheive these goals currently. I think that same user group would be interested in my findings (which so far, are not killing performance at all) but hey if you don't feel this is a suitable place to share that - it's fine I won't. CONNECT's performance is not particularly bad (at-least if used primarily for frontend queries, with backend INSERTS being routed in the application layer).

Is this something Zabbix would ever adopt as a defacto schema? Probably not.

Is there valid findings (perhaps in the benifits of application layer partitioning) that could be of a positive influence to later design? Of that I have no doubt.

–

Data to back up my "not particularly bad" statement:

 MariaDB [zabbix-server]> SELECT * FROM `history_uint` WHERE itemid=23693 AND clock BETWEEN 1635209900 AND 1635210000 LIMIT 30;
+--------+------------+-------+-----------+
| itemid | clock | value | ns |
+--------+------------+-------+-----------+
| 23693 | 1635209901 | 1 | 521480589 |
| 23693 | 1635209911 | 1 | 171632831 |
| 23693 | 1635209921 | 1 | 740553104 |
| 23693 | 1635209931 | 1 | 442566066 |
| 23693 | 1635209941 | 1 | 678118281 |
| 23693 | 1635209951 | 1 | 904607350 |
| 23693 | 1635209961 | 1 | 849167510 |
| 23693 | 1635209971 | 1 | 116414855 |
| 23693 | 1635209981 | 1 | 589172639 |
| 23693 | 1635209991 | 1 | 713434572 |
+--------+------------+-------+-----------+
10 rows in set (0.000 sec)

MariaDB [zabbix-server]> SELECT * FROM `history_uint_connect` WHERE itemid=23693 AND clock BETWEEN 1635209900 AND 1635210000 LIMIT 30;
+--------+------------+-------+-----------+
| itemid | clock | value | ns |
+--------+------------+-------+-----------+
| 23693 | 1635209901 | 1 | 521480589 |
| 23693 | 1635209911 | 1 | 171632831 |
| 23693 | 1635209921 | 1 | 740553104 |
| 23693 | 1635209931 | 1 | 442566066 |
| 23693 | 1635209941 | 1 | 678118281 |
| 23693 | 1635209951 | 1 | 904607350 |
| 23693 | 1635209961 | 1 | 849167510 |
| 23693 | 1635209971 | 1 | 116414855 |
| 23693 | 1635209981 | 1 | 589172639 |
| 23693 | 1635209991 | 1 | 713434572 |
+--------+------------+-------+-----------+
10 rows in set (0.003 sec)

To replicate:

Disk and buffer pool pre-warmed. No query cache. Underlying SELECT routed to the backend CONNECT table completed in just above 0.001s so 0.002s is the overhead. Some overhead likely due to increased number of partitions with history_uint_connect.

history_uint and history_connect have the same scale of data (33GB for history_uint, 30GB for history_uint_connect ). history_uint using 24hr per partition and PK over "itemid,clock" and history_uint_connect using partition every 1hr.

INSERT performance is more seriously affected INSERT'ing from Zabbix-Server through CONNECT does not appear as viable at scale (appears roughly 2-3x slower and takes a table level write lock), application layer (zabbix-server) patch is next on my testing list to introduce application layer partitioning support to bypass CONNECT.

I am looking forward to implementing two things in Zabbix. One is history API, which will allow to connect any storage engines. It is already in the roadmap. Another thing is about splitting operational data (i.e. data used for trigger processing) from longer term history used for visualization and reporting. Also I do not think that sharding logic should be part of Zabbix software, it must be implemented on the storage side.

Primary keys for historical tables has been implemented under ZBXNEXT-6921. I am closing this ticket.