/ Register

Join the community today
Register Now

MariaDB Anyone using MariaDB 10.4.8 on live and busy site?

Discussion in 'Nginx, PHP-FPM & MariaDB MySQL' started by rdan, Sep 18, 2019.

Previous Thread Next Thread
Loading...
Page 2 of 4
  1. Oct 29, 2019 #21
    Xon

    Xon Active Member

    173
    61
    28
    Nov 16, 2015
    Ratings:
    +229
    Local Time:
    8:54 PM
    1.15.x
    MariaDB 10.3.x
    I recently had a client who was using XenForo 1.5 and Post Rating. With MariaDB 10.4.x it was causing what should have been 20% CPU usage turn into 500% CPU usage (the site would then become unresponsive).

    The issue was a join had been converted into a hash join without using an index; except this was the per-post cache table. Full table scan of something with as-many rows as the posts table. Oops. Force index via patching the Post Rating add-on fixed it.

    Then I identified standard XF1 join was causing similar behaviour and had to reach for the join_cache_level=0 hammer because patching them one by one just isn't feasible.

    This isn't surprising; Wordpress basically is a very small collection of tables with almost no joins when doing SQL queries. XenForo has a vastly more complex database schema.
     
  2. Oct 29, 2019 #22
    Xon

    Xon Active Member

    173
    61
    28
    Nov 16, 2015
    Ratings:
    +229
    Local Time:
    8:54 PM
    1.15.x
    MariaDB 10.3.x
  3. Oct 29, 2019 #23
    eva2000

    eva2000 Administrator Staff Member

    50,877
    11,786
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,240
    Local Time:
    10:54 PM
    Nginx 1.25.x
    MariaDB 10.x
    Ouch that's a huge difference ! MariaDB Jira bug tracker is insightful - there's even performance regressions from in MariaDB 10.3.18 from 10.3.17 being confirmed. Sometimes I feel like I should version lock minor MariaDB version updates to prevent them updating minor versions on yum updates until there's some time past to see if there's regressions LOL
     
  4. Oct 29, 2019 #24
    eva2000

    eva2000 Administrator Staff Member

    50,877
    11,786
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,240
    Local Time:
    10:54 PM
    Nginx 1.25.x
    MariaDB 10.x
  5. Nov 5, 2019 #25
    Xon

    Xon Active Member

    173
    61
    28
    Nov 16, 2015
    Ratings:
    +229
    Local Time:
    8:54 PM
    1.15.x
    MariaDB 10.3.x
    One of my XF1 add-ons is reliably crashing an Mariadb 10.4.8 instance o_O

    The query causing explosions is this;
    PHP:
    UPDATE IGNORE xf_conversation_message
    JOIN useress_unc_queue_content batch on batch    .batch_user_id = ? and batch.batch_content_id = xf_conversation_message.message_id
    SET user_id     = ?, username = ?
    WHERE user_id = ? and batch.batch_rank between '0' and NULL
    This was essentially a no-op in earlier version since" $value < NULL" always returns false (and the table useress_unc_queue_content is empty as well), but the NULL is reliably causing a hard crash. Casting to int before calling the SQL query and it work.

    Except running the broken query after patching & doing the updates does work! ( that is; there are no rows with a matching user_id, so it should be short circuiting the between check)

    What the heck.
     
    Last edited: Nov 5, 2019
  6. Nov 5, 2019 #26
    eva2000

    eva2000 Administrator Staff Member

    50,877
    11,786
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,240
    Local Time:
    10:54 PM
    Nginx 1.25.x
    MariaDB 10.x
    @Xon so this is on a XF customer's MariaDB 10.4.8 instance not yours as you already downgraded right ?
     
  7. Nov 5, 2019 #27
    Xon

    Xon Active Member

    173
    61
    28
    Nov 16, 2015
    Ratings:
    +229
    Local Time:
    8:54 PM
    1.15.x
    MariaDB 10.3.x
    Yes, it was the same client with XF1.5 & Post Rating causing massive performance issues with MariaDB 10.4.8. Except now with hard crashes for the database with a dumb (but valid) SQL query :(

    I might see if I can distill that into a small reproducible test case and submit it; but not sure when I'll have the time.
     
  8. Nov 5, 2019 #28
    eva2000

    eva2000 Administrator Staff Member

    50,877
    11,786
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,240
    Local Time:
    10:54 PM
    Nginx 1.25.x
    MariaDB 10.x
    Is this still with MariaDB 10.4 and default join_cache_level = 2 or at 0 ? I vaguely recall reading about optimizer_use_condition_selectivity default changes from value of 1 in MariaDB 10.3 to value of 4 also caused crashes in MariaDB 10.4 on jira bug tracker.
     
  9. Nov 5, 2019 #29
    Xon

    Xon Active Member

    173
    61
    28
    Nov 16, 2015
    Ratings:
    +229
    Local Time:
    8:54 PM
    1.15.x
    MariaDB 10.3.x
    MariaDB 10.4 & with join_cache_level=0

    :edit: wow, optimizer_use_condition_selectivity=4 also opens up a huge can of worms with optimizer regressions.
     
    Last edited: Nov 5, 2019
  10. Nov 17, 2019 #30
    eva2000

    eva2000 Administrator Staff Member

    50,877
    11,786
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,240
    Local Time:
    10:54 PM
    Nginx 1.25.x
    MariaDB 10.x
    ah missed your edited post - yeah so you confirmed setting from 4 to 1 helped too ?
     
  11. Nov 18, 2019 #31
    negative

    negative Active Member

    415
    50
    28
    Apr 11, 2015
    Ratings:
    +98
    Local Time:
    2:54 PM
    1.9.10
    10.1.11
    I upgraded my mariadb 10.4.10 today (with yum updates including kernel, and restarted after upgrades) and forum index page load increased dramatically.

    Now, it looks like about 14 seconds takes the query time on forum index page.
    That slow-down effect feels only on forum index

    I've tried to disable all add-ons, caching etc. But same. Even worse.

    When i enable the debug, i see that (most query time is only that query)
    Ekran Resmi 2019-11-18 02.25.38.png

    And when i look the mysql slow query logs in server, i see same queries in hundreds.

    So should i turn back to 10.3 (i don't know how it is safe without problem) or should i modify some settings like which talking above ( optimizer_use_condition_selectivity=4, join_cache_level)

    P.S: That load problem doesn't effect to guests, only for logged users.

    @Xon @eva2000
     
    Last edited: Nov 18, 2019
  12. Nov 18, 2019 #32
    eva2000

    eva2000 Administrator Staff Member

    50,877
    11,786
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,240
    Local Time:
    10:54 PM
    Nginx 1.25.x
    MariaDB 10.x
    Try the variables workaround if that doesn't work downgrade manually. Not sure why you'd upgrade to MariaDB 10.4 if you read this thread first ??? Or you didn't know of MariaDB 10.4 regressions before hand ?
     
  13. Nov 18, 2019 #33
    negative

    negative Active Member

    415
    50
    28
    Apr 11, 2015
    Ratings:
    +98
    Local Time:
    2:54 PM
    1.9.10
    10.1.11
    @eva2000 , You are right but yesterday my mariadb version was 10.4.8 and website runs without any problem, fast like on 10.3. (even maybe faster) But as i told you, yum update came with 10.4.10 version so we have that problem for now.

    So do you have any instructions how can downgrade mariadb version to 10.3 now on centminmod ? Thanks
     
    Last edited: Nov 18, 2019
  14. Nov 19, 2019 #34
    eva2000

    eva2000 Administrator Staff Member

    50,877
    11,786
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,240
    Local Time:
    10:54 PM
    Nginx 1.25.x
    MariaDB 10.x
    MariaDB doesn't officially support major version downgrades Downgrading between Major Versions of MariaDB and I wouldn't be providing free support and specific manual instructions for such either. However, other members are free to help out if they want.

    Not knowing how to safely downgrade manually can be risky as you could loose MySQL data if not done correctly. So only do it if you know what you're doing or hire someone to do it properly and hope that person you hire does it properly. You can also test the downgrade process on a test VPS the MariaDB 10.3 to 10.4 upgrade then 10.3 downgrade process with a copy of your mysql data until you get it right yourself.

    Or just try the workaround mysql variables until 10.4.x has a fix.

    Or just create a new Centmin Mod VPS server with MariaDB 10.3 and migrate/backup and restore your data from MariaDB 10.4 server to the new server. Keep the old MariaDB 10.4 server until you're satisfied that MariaDB 10.3 server data migration is 100% working.
     
  15. Nov 19, 2019 #35
    negative

    negative Active Member

    415
    50
    28
    Apr 11, 2015
    Ratings:
    +98
    Local Time:
    2:54 PM
    1.9.10
    10.1.11
    That is sadly, there are no any example instructions in here for do it.

    As i see in another websites, someone advice that backup database, uninstall mariadb 10.4 with yum-remove command and install back the mariadb 10.3 and import the data back. So it will works but we will lost the mariadb integration with centminmod completely :/

    So if i install new centminmod server with mariadb 10.3 and useing the Centmin Menu #21 what does effects?
     
  16. Nov 19, 2019 #36
    eva2000

    eva2000 Administrator Staff Member

    50,877
    11,786
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,240
    Local Time:
    10:54 PM
    Nginx 1.25.x
    MariaDB 10.x
    no you won't loose centmin mod integration doing this - ensure you backup /etc/my.cnf before hand too as that has centmin mod per MariaDB version group settings i.e. [mariadb-10.3] so you can have settings specific to each MariaDB major version in same /etc/my.cnf.

    centmin.sh menu option 21 still updates centmin mod as usual. Centmin Mod only installs/configures MariaDB server, once it's installed it's independent
     
  17. Nov 19, 2019 #37
    negative

    negative Active Member

    415
    50
    28
    Apr 11, 2015
    Ratings:
    +98
    Local Time:
    2:54 PM
    1.9.10
    10.1.11
    So i mean that, my way is right for you too? I think you can help in this mean anyway. :)
    1. backup my.cnf
    2. export full mysql backup
    3. yum-remove mariadb 10.4
    4. yum-install mariadb 10.3
    5. restore my.cnf
    6. import mysql backup
     
  18. Nov 19, 2019 #38
    eva2000

    eva2000 Administrator Staff Member

    50,877
    11,786
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +18,240
    Local Time:
    10:54 PM
    Nginx 1.25.x
    MariaDB 10.x
    between step 3 and 4 would generally need to make a copy of /var/lib/mysql data in say /var/lib/mysql-backup and then delete all mysql data in /var/lib/mysql data directory - hence risky as if you didn't backup your data properly = data loss

    if you are not sure test on a test VPS with a copy of your migrated data or do not do the downgrade and just wait for 10.4 yum update fix and apply workaround variables.

    I can't emphasize on the potential risk of permanent data loss if you mess up and I wouldn't be providing any free support for fixing such mess ups.
     
  19. Nov 20, 2019 #39
    negative

    negative Active Member

    415
    50
    28
    Apr 11, 2015
    Ratings:
    +98
    Local Time:
    2:54 PM
    1.9.10
    10.1.11
    Thank you for your reply @eva2000

    And what about downgrade to 10.4.8 (older version) from 10.4.10
    It is not major version so can i switch between them ?

    Because i have no problem on 10.4.8. These problems occurring on 10.4.9 and later.
    MariaDB 10.4.9 Release Notes

    I see something like that for do it here;
    MariaDB - MariaDB 10.4.9, 10.3.19 and 10.2.28, 10.1.42 and 5.5.66 Now Available


    And i check the logs what changed after last yum update, i confirm that is problem occuring 100% after 10.4.9 and above. (i was working fine on 10.4.8) so that is here;
    Code (Text):
    [21:52][root@server ~]# yum history info
Loaded plugins: fastestmirror, priorities, versionlock
Transaction ID : 157
Begin time     : Mon Nov 18 00:31:16 2019
Begin rpmdb    : 851:2ef1b75dbce1dd419edec0337471d87c33c1b026
End time       :            00:35:08 2019 (232 seconds)
End rpmdb      : 851:78d23d333b930d4d97f7cc34889ffbc69906823e
User           : root <root>
Return-Code    : Success
Command Line   : update --disableplugin=priorities --setopt=deltarpm=0 --enablerepo=remi
Transaction performed with:
    Installed     rpm-4.11.3-40.el7.x86_64                      @base
    Installed     yum-3.4.3-163.el7.centos.noarch               @base
    Installed     yum-metadata-parser-1.1.4-10.el7.x86_64       @anaconda
    Installed     yum-plugin-fastestmirror-1.1.31-52.el7.noarch @base
    Installed     yum-plugin-versionlock-1.1.31-52.el7.noarch   @base
Packages Altered:
    Updated MariaDB-client-10.4.8-1.el7.centos.x86_64      @mariadb
    Update                 10.4.10-1.el7.centos.x86_64     @mariadb
    Updated MariaDB-common-10.4.8-1.el7.centos.x86_64      @mariadb
    Update                 10.4.10-1.el7.centos.x86_64     @mariadb
    Updated MariaDB-compat-10.4.8-1.el7.centos.x86_64      @mariadb
    Update                 10.4.10-1.el7.centos.x86_64     @mariadb
    Updated MariaDB-devel-10.4.8-1.el7.centos.x86_64       @mariadb
    Update                10.4.10-1.el7.centos.x86_64      @mariadb
    Updated MariaDB-server-10.4.8-1.el7.centos.x86_64      @mariadb
    Update                 10.4.10-1.el7.centos.x86_64     @mariadb
    Updated MariaDB-shared-10.4.8-1.el7.centos.x86_64      @mariadb
    Update                 10.4.10-1.el7.centos.x86_64     @mariadb
    Updated galera-4-26.4.2-1.rhel7.el7.centos.x86_64      @mariadb
    Update           26.4.3-1.rhel7.el7.centos.x86_64      @mariadb
     
    Last edited: Nov 20, 2019
  20. Nov 20, 2019 #40
    negative

    negative Active Member

    415
    50
    28
    Apr 11, 2015
    Ratings:
    +98
    Local Time:
    2:54 PM
    1.9.10
    10.1.11
    And Finally, i word hard and test it on duplicated centminmod + website. After confirmed the issue fixed, i applied it on real server and everything works perfect now even on mariadb 10.4.8.

    In my researches, problem occuring after mariadb 10.4.9 and above.

    Now, i switched back to 10.4.8 version in realtime, problem is fixed. Also, that downgrade was minor downgrading so i didn't dump and import database. I just edit the yum repo and yum-downgrade command :)
     
    Last edited: Nov 20, 2019
Previous Thread Next Thread
Loading...
Page 2 of 4

.