Why is “Rows added in this batch” taking longer and longer?

[thomas56Participant]

Hi

Plugin version: 2.16.29.24

I have a very huge wp_postmeta table (about 2.4 million rows). I noticed that when doing the backup, every run for the next 100 pages to crawl takes longer and longer:

Table wp_postmeta: Rows added in this batch (start page: 700; next: 800): 100000 in 28.93 seconds (Okt 09 22:31:10) took 36s
Table wp_postmeta: Rows added in this batch (start page: 800; next: 900): 100000 in 36.37 seconds (Okt 09 22:31:46) took 43s
Table wp_postmeta: Rows added in this batch (start page: 900; next: 1000): 100000 in 43.32 seconds (Okt 09 22:32:30) took 51s
Table wp_postmeta: Rows added in this batch (start page: 1000; next: 1100): 100000 in 51.01 seconds (Okt 09 22:33:21) took 55s
Table wp_postmeta: Rows added in this batch (start page: 1100; next: 1200): 100000 in 55.60 seconds (Okt 09 22:34:16) took 66s
Table wp_postmeta: Rows added in this batch (start page: 1300; next: 1400): 100000 in 66.61 seconds (Okt 09 22:36:27) took 71s
…

Why is it like this? I mean normally it should take the same time to fetch the next 100 pages, or?

Additionally, i see 2 cores with 100% cpu usage for mysqld. Why only two cores, i have 8 in my server?

Best,
Thomas

[thomas56Participant]

This reply has been marked as moderator-only.

[udadminKeymaster]

Because that’s the behaviour of MySQL in processing the SQL LIMIT statement; the more pages you get into a query, the more work it has to do to identify the correct set of results.

UpdraftPlus only runs inside one PHP process at a time. PHP is not multi-threaded.

[thomas56Participant]

Ok i see but why is it not like it fetches based by the primary key (id) per table, so 1-100, 101-200, 201-300…?
Please explain how Updraft works in this way.

[udadminKeymaster]

There is nothing in SQL that forces primary key values to be either numeric or monotonically ascending from zero. Primary key values can be the names of cities in Russia if the creator of the table likes it that way.

However, having said that, of course, core WP tables have known and defined properties, which we could take advantage of to maximise performance. So, I’m going to create a task in our system to look into that – thank you for the idea!

[thomas56Participant]

Thanks a lot, i am just trying to debug why my backups fail since 2 weeks (max_execution_time is 120s, i was told to increase but nothing has changed on my server thats why im curious).

Another question, take a look at this:

Table wp_postmeta: Rows added in this batch (start page: 700; next: 800): 100000 in 28.93 seconds (Okt 09 22:31:10) 36s
Table wp_postmeta: Rows added in this batch (start page: 800; next: 900): 100000 in 36.37 seconds (Okt 09 22:31:46) 43s
Table wp_postmeta: Rows added in this batch (start page: 900; next: 1000): 100000 in 43.32 seconds (Okt 09 22:32:30) 51s
Table wp_postmeta: Rows added in this batch (start page: 1000; next: 1100): 100000 in 51.01 seconds (Okt 09 22:33:21) 55s
Table wp_postmeta: Rows added in this batch (start page: 1100; next: 1200): 100000 in 55.60 seconds (Okt 09 22:34:16) 66s
Table wp_postmeta: Rows added in this batch (start page: 1300; next: 1400): 100000 in 66.61 seconds (Okt 09 22:36:27) 82s
Table wp_postmeta: Rows added in this batch (start page: 1600; next: 1700): 100000 in 82.59 seconds took 82s
Table wp_postmeta: Rows added in this batch (start page: 1700; next: 1800): 100000 in 81.68 seconds 81s
Table wp_postmeta: Rows added in this batch (start page: 1900; next: 2000): 100000 in 86.01 seconds 86s
Table wp_postmeta: Rows added in this batch (start page: 2000; next: 2100): 100000 in 90.12 seconds 90s
Table wp_postmeta: Rows added in this batch (start page: 2000; next: 2100): 100000 in 90.12 seconds 90s
Table wp_postmeta: Rows added in this batch (start page: 2100; next: 2200): 100000 in 94.27 seconds 94s
Table wp_postmeta: Rows added in this batch (start page: 2200; next: 2300): 100000 in 98.70 seconds 98s
Table wp_postmeta: Rows added in this batch (start page: 2300; next: finished): 52606 in 56.05 seconds
Table wp_postmeta: finishing file (backup_2020-10-09-2229_…._8ecfc82d7c11-db-table-wp_postmeta.table.gz – 418.1 KB)

Table wp_posts: Rows added in this batch (start page: 0; next: 100): 100000 in 51.16 seconds

Why is wp_posts taking 51s for the first 100 pages? I mean its a new table.

[thomas56Participant]

Now its saying “next resumption 2”
and no mysqld usage anymore

On resumption 3 it started working again but only for a short time

My MySQL slow-query-log have two entries

# Time: 201009 22:51:39
# User@Host: aih_wp_test_db[aih_wp_test_db] @ localhost []
# Thread_id: 2730905 Schema: aih_wp_test QC_hit: No
# Query_time: 2.046817 Lock_time: 0.000051 Rows_sent: 1000 Rows_examined: 123000
# Rows_affected: 0
use aih_wp_test;
SET timestamp=1602276699;
SELECT * FROM wp_posts LIMIT 122000, 1000;

# Time: 201009 23:01:21
# User@Host: aih_wp_test_db[aih_wp_test_db] @ localhost []
# Thread_id: 2732396 Schema: aih_wp_test QC_hit: No
# Query_time: 2.103538 Lock_time: 0.000194 Rows_sent: 500 Rows_examined: 123000
# Rows_affected: 0
SET timestamp=1602277281;
SELECT * FROM wp_posts LIMIT 122500, 500;

And no progress in Updraft. Strangely, the two mysqld processes with 100% cpu have stopped after approx 20 seconds.

[udadminKeymaster]

I don’t know (it’s all happening inside the MySQL daemon – UD is just running SELECT queries to request the data), but at a guess, it’s probably just because the posts table has more columns and more data in each row that postmeta does.

[thomas56Participant]

And the last line in the backup log is

1861.519 (2) Table wp_posts: Total expected rows (approximate): 128968

[udadminKeymaster]

You might want to look at the configuration of your MySQL server generally; I just tried SELECT * FROM wp_posts LIMIT 122500, 500; on a site I have access to, and it only used 0.495s of wall-clock time. i.e. 400% of the performance of the same query on yours.

[thomas56Participant]

Ok i got it.

Once i increased the “max_allowed_packet” size from 16 to 32MB, the backup finished (it failed always before).

Anyway thanks for your explanation and for creating the task into your system on how to maximize performance (see my analysis above).

[udadminKeymaster]

This reply has been marked as private.

[thomas56Participant]

This reply has been marked as private.

[udadminKeymaster]

Hi Thomas,

Please can you post a link to the whole logfile?

Thank you,
David

[thomas56Participant]

This reply has been marked as private.

[Author]

Posts