We have a bunch of existing servers which run just fine, inserting our thousand rows/second, but the new batch just will not do it. As the day goes on and the table grows, it gets slower and slower, until we are millions of rows behind.
I've tried to match all the settings, but still no juice.
tokudb_commit_sync=OFF; tokudb_block_size=1048576; tokudb_analyze_in_background = OFF; tokudb_auto_analyze=0;
NOTE the current tables on the new servers were created with a 4MB block size, though I don't think that would cause this kind of performance degradation.
Old [working] servers (on worse but similar hardware)
Linux 2.6.32-504.30.3.el6.x86_64 CentOS release 6.6 (Final) tokudb_version: 5.6.36-82.0 mysql Ver 14.14 Distrib 5.6.36-82.0, for Linux (x86_64) using 6.0
New [fubar'd] servers:
Linux 3.10.0-862.3.2.el7.x86_64 CentOS Linux release 7.5.1804 (Core) tokudb_version: 5.7.21-21 mysql Ver 14.14 Distrib 5.7.21-21, for Linux (x86_64) using 6.2
I think I may be onto something with this, however, when I do a SHOW INDEX, the old servers have cardinality equal to the number of rows in the table for ALL indices, while the new servers have varying cardinality, sometimes small, dependent on the cardinality (as expected). (Both tables are identical, aside from the data)
So it seems the new servers are getting caught up in some sort of index optimization on each new write (LOAD DATA LOCAL btw), while the old servers are able to insert much faster because they don't do as much?
Please help me I've been all over google and in logs and code for 16 hours today and I have no more ideas.
The goal is to make the new servers have INSERT throughput like the Old servers.