Because I had a bit of time I ran the script against two fresh MariaDB
containers on my workstation and here are the numbers, which look more
realistic
5.5.36
mysql::select_version::q/s....................... 43227
mysql::select_all::q/s........................... 21382
mysql::seq_insert::q/s............................. 165
mysql::update::q/s................................. 157
mysql::update_with_index::q/s...................... 161
mysql::transaction_insert::t/s..................... 157
mysql::delete::q/s................................. 159
10.11.6
mysql::select_version::q/s....................... 65544
mysql::select_all::q/s............................ 5134
mysql::seq_insert::q/s............................. 116
mysql::update::q/s................................. 161
mysql::update_with_index::q/s.................... 18419
mysql::transaction_insert::t/s..................... 155
mysql::delete::q/s................................. 122
As you can see some interesting differences:
- SELECT * being way slower on 10.11 on this benchmark
- UPDATE with index much faster on 10.11
the rest looks quite similar (benchmarking select version is without
much interest...)
Le lun. 7 oct. 2024 à 23:49, Guillaume Lefranc
<[email protected]> a écrit :
I'm going to take my best guess here and assume that the 5.5
benchmark results are false results, which I am honestly surprised
that nobody noticed before.
4M inserts or updates per second is completely unrealistic in real
life scenarios.
I gave a quick look to that benchmark script; it's single threaded
and does no error checking at all. There is just no way such a
script could reach 4M inserts per second, so I'm concluding you're
just benchmarking errors here and nothing gets written to the DB.
If you want to corroborate my theory just look at SHOW GLOBAL
STATUS LIKE 'Com_Insert' and SHOW GLOBAL STATUS LIKE 'Com_Update'
counters on this DB. It should tell you how many inserts/updates
have been recorded.
I don't want to criticize the PHP script author's work, because
his script might be perfectly fine for most situations, but if you
want to do accurate DB benchmarking you should give a try to Sysbench.
GL
Le lun. 7 oct. 2024 à 17:38, Gordan Bobic via discuss
<[email protected]> a écrit :
The only way there can be that big a difference is if there is
a flushing discrepancy in cofiguration.
Have you verified your settings are actually the same,
particularly the ones containing "flush" and "sync" substrings.
I seem to recall there was also a flush bug fixed since 5.5
that makes things slower since then but made 5.5 not entirely
crash-safe.
On Mon, 7 Oct 2024, 18:33 cyusedfzfb via discuss,
<[email protected]> wrote:
Ok, tested just now with Server version: 10.4.34-MariaDB
MariaDB Server:
Server version: 10.4.34-MariaDB MariaDB Server
mysql::ping,0.0001
mysql::select_version,0.1368
mysql::select_all,0.7346
mysql::select_cursor,0.9662
mysql::seq_insert,4.4442
mysql::bulk_insert,0.6946
mysql::update,2.5573
mysql::update_with_index,0.3629
mysql::transaction_insert,4.7147
mysql::indexes,0.2814
mysql::delete,4.3666
mysql::select_version::q/s,14616
mysql::select_all::q/s,2723
mysql::seq_insert::q/s,450
mysql::update::q/s,782
mysql::update_with_index::q/s,9248
mysql::transaction_insert::t/s,424
mysql::delete::q/s,458
Total time,29.3222 s
Peak memory usage,23.82 MiB
So to update the "key observations":
- **UPDATE performance:**
- 5.5.68: *3,895,386* q/s
- 10.11.9: *1,418* q/s
- 10.4.34: *782* q/s
- **UPDATE with index:**
- 5.5.68: *4,105,436* q/s
- 10.11.9: *8,963* q/s
- 10.4.34: *9,248* q/s
- **DELETE performance:**
- 5.5.68: *4,436,065* q/s
- 10.11.9: *899* q/s
- 10.4.34: *458* q/s
Hmm. 10.4.34 behaves much like 10.11.6, and very unlike
the speedy
gonzalez 5.5.68
This is all very strange.
On 10/7/24 16:41, Reinis Rozitis via discuss wrote:
>> How can it be that MariaDB 5.5.68 is performing several
orders of magnitude faster than 10.11.x for these specific
operations?
> Hello,
> if you have means, could you test 10.4.34 which while
EOL is the last version based on 5.5/5.6 MySQL and issues
like these appeared
>
> https://jira.mariadb.org/browse/MDEV-30501#comment-272740
> https://jira.mariadb.org/browse/MDEV-29988
> https://jira.mariadb.org/browse/MDEV-26055
>
> Which while marked Closed, point to different
issues/problems where situation is still unclear ..
>
> I haven't tried the 10.11.x nor 11.x but none of the
short-term release branches after 10.4 have performed even
close (though not that dramatically) at least for our
workload, so currently stuck on 10.4.x
>
>
> p.s. sorry for not answering your questions and possibly
increasing the load :)
>
> wbr
> rr
>
>
>
> _______________________________________________
> discuss mailing list -- [email protected]
> To unsubscribe send an email to
[email protected]
_______________________________________________
discuss mailing list -- [email protected]
To unsubscribe send an email to
[email protected]
_______________________________________________
discuss mailing list -- [email protected]
To unsubscribe send an email to [email protected]
Le lun. 7 oct. 2024 à 17:38, Gordan Bobic via discuss
<[email protected]> a écrit :
The only way there can be that big a difference is if there is
a flushing discrepancy in cofiguration.
Have you verified your settings are actually the same,
particularly the ones containing "flush" and "sync" substrings.
I seem to recall there was also a flush bug fixed since 5.5
that makes things slower since then but made 5.5 not entirely
crash-safe.
On Mon, 7 Oct 2024, 18:33 cyusedfzfb via discuss,
<[email protected]> wrote:
Ok, tested just now with Server version: 10.4.34-MariaDB
MariaDB Server:
Server version: 10.4.34-MariaDB MariaDB Server
mysql::ping,0.0001
mysql::select_version,0.1368
mysql::select_all,0.7346
mysql::select_cursor,0.9662
mysql::seq_insert,4.4442
mysql::bulk_insert,0.6946
mysql::update,2.5573
mysql::update_with_index,0.3629
mysql::transaction_insert,4.7147
mysql::indexes,0.2814
mysql::delete,4.3666
mysql::select_version::q/s,14616
mysql::select_all::q/s,2723
mysql::seq_insert::q/s,450
mysql::update::q/s,782
mysql::update_with_index::q/s,9248
mysql::transaction_insert::t/s,424
mysql::delete::q/s,458
Total time,29.3222 s
Peak memory usage,23.82 MiB
So to update the "key observations":
- **UPDATE performance:**
- 5.5.68: *3,895,386* q/s
- 10.11.9: *1,418* q/s
- 10.4.34: *782* q/s
- **UPDATE with index:**
- 5.5.68: *4,105,436* q/s
- 10.11.9: *8,963* q/s
- 10.4.34: *9,248* q/s
- **DELETE performance:**
- 5.5.68: *4,436,065* q/s
- 10.11.9: *899* q/s
- 10.4.34: *458* q/s
Hmm. 10.4.34 behaves much like 10.11.6, and very unlike
the speedy
gonzalez 5.5.68
This is all very strange.
On 10/7/24 16:41, Reinis Rozitis via discuss wrote:
>> How can it be that MariaDB 5.5.68 is performing several
orders of magnitude faster than 10.11.x for these specific
operations?
> Hello,
> if you have means, could you test 10.4.34 which while
EOL is the last version based on 5.5/5.6 MySQL and issues
like these appeared
>
> https://jira.mariadb.org/browse/MDEV-30501#comment-272740
> https://jira.mariadb.org/browse/MDEV-29988
> https://jira.mariadb.org/browse/MDEV-26055
>
> Which while marked Closed, point to different
issues/problems where situation is still unclear ..
>
> I haven't tried the 10.11.x nor 11.x but none of the
short-term release branches after 10.4 have performed even
close (though not that dramatically) at least for our
workload, so currently stuck on 10.4.x
>
>
> p.s. sorry for not answering your questions and possibly
increasing the load :)
>
> wbr
> rr
>
>
>
> _______________________________________________
> discuss mailing list -- [email protected]
> To unsubscribe send an email to
[email protected]
_______________________________________________
discuss mailing list -- [email protected]
To unsubscribe send an email to
[email protected]
_______________________________________________
discuss mailing list -- [email protected]
To unsubscribe send an email to [email protected]
