OCPL performance issue: Fulldump generation

[following5]

Generation of replication fulldump is extremely slow on my OCPL develsite. Geocaches are written fast, but the logs are written at a speed of ~ 10.000/minute. This means it takes about 4 hours for the 2,5 Mio. Logs! And nearly blocks the VM during that time, because of high CPU load.

This is likely the same problem as #542. OKAPI runs select statemens with in (log_uuids …) for 500 logs each. Database load is high during the log dump. Runs fast on the OCDE site.

This is harder to fix than the search method; and there are lots of those constructs in OKAPI code. Maybe there is some configuration switch for MariDB to fix it? There must be lots of applications out there with the same problem. Hard to imagine that they all must be rewritten for new MariaDB.

• OCDE develsite: mysql Ver 15.1 Distrib 5.5.44-MariaDB, for Linux (x86_64) using readline 5.1
• OCPL develsite: mysql Ver 14.14 Distrib 5.7.12, for Linux (i686) using EditLine wrapper

[following5]

Oops... that's MariDB at OCDE develsite, and MySQL at OCPL. So the problem may not be related to MariaDB at all, but just to the OCPL database server settings.

[following5]

On the OC.PL production site it runs fast, too - about 4 minutes for the last fulldump.

[following5]

On the OC.PL production site it runs fast, too - about 4 minutes for the last fulldump.

Oh dear, I misunderstood the Opencaching.PL cronreport. Fulldump was scheduled for today 13:30, and now it says:

Fulldump: okapi-dump.tar.bz2, 417584837 bytes, generated 2018-11-13T14:43:06+01:00

And then OKAPI complained about stalled crontab.

This means it took 1 hour and 13 minutes, which well matches the > 4 hours on the devel-VM. Compared to 2 minutes at Opencachig.DE, for roughly half the amount of data.

So it's a real problem. I already experimented with OCPL DB server settings, but did not find a solution.

[following5]

It's running very slow, but not noticably slowing down the Opencaching.PL site. Just the OKAPI cronjobs are stalled. This means that the map is not updated for 1+ hour.

(Opencaching.PL admins may receive another OKAPI cronjob warning at ~ 17:45 today.)

[kojoty]

@following5 just a question: so I understand that full dump of OCDE is much faster than full dump at OCPL?

[following5]

I we take into account that OCPL has ~ 3 times more cache_logs entries, then OCDE fulldump is still 10-15 times faster.

[following5]

test.opencaching.de seems to have a similar problem with ReplicateCommon:update_clog_table().