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] >> >
_______________________________________________ discuss mailing list -- [email protected] To unsubscribe send an email to [email protected]
