Strugling to find out if Rank Math kills mariadb.service or not

#1069329
  • Resolved Regio Online

    1 month, 2 weeks ago

    Rank Math free

    I’ve been struggling for months with a problem that takes down the mariadb.service database process, giving me the error message ‘Error establishing a database connection’.

    This problem started sometime late last year when I was running the website on an unmanaged VPS with 16 CPU cores, 60GB RAM, and 2TB storage (Strato). After the VPS administrator moved on to something else, I had to (temporarily) migrate to shared hosting (24 CPU cores and 16GB) with Vimexx.

    Theme:

    Newspaper by tagdiv

    Uses its own composer – td-composer and td-cloudlibrary plugins

    Main (heavier) plugins:

    Rank Math (SEO)

    FS-Poster (autopost to Facebook)

    WP Media Folder (wrapper for Media Library) + cloud add-on + gallery add-on

    Other:

    Askimet

    AllAccessible

    Cookie Notice & Consent

    Health Check & Troubleshooting

    Standard Featured Image

    WP Consent API

    WP Super Cache

    WPS Hide Login

    Query Monitor (for debugging database queries)

    WordPress version: 6.8.2
    PHP version: 8.4
    PHP Memory limit: 256M

    In wp-config:

    wp-memory-limit = 256M

    wp-max-memory-limit = 512M

    Database: InnoDB
    Size: 3.2GB, of which the postmeta table is 2.6GB

    The website is a news site and dynamic, with 20+ new posts daily. Images are served from Google Drive via the WP Media Folder cloud add-on.

    What goes wrong and when:
    When new posts are published in the backend, the database service goes down 2 to 3 times a day. At Vimexx, it restarts within 20 seconds, but on the VPS, it had to be restarted via SSH. There wasn’t yet a script for autorestart of the mariadb service.

    I was using WP Rocket as a caching plugin, but the problem became increasingly worse with it. I first had to disable the caching plugin via FTP, and only then would the database work again. Currently, I’m temporarily using WP Super Cache, as advised by the theme, but it also caused issues. Only after disabling it via FTP does the site work again, and I can re-enable the plugin.

    In the meantime, I’ve tried WP Rocket (paid version), WP Super Cache, WP Fastest Cache, and W3 Total Cache (separately, of course).

    Also, independently of the plugins, or in combination with them, I’ve tried server-side OPcache, Varnish cache, and Redis Object Cache (including the plugin). All of them lead to the database connection breaking.

    With Query Monitor, I’ve gained more insight into the number of DB queries. Due to some adjustments in plugins, the number of queries in the backend when creating a new post is 137. When opening a page in the backend, it’s 459.

    The load of Rank Math seems resonable, but there are a lot of additional rules in postmeta and wp_otions.

    I have written support tickets to all plugin developers (and now you) and the theme developer. The latter advised creating a staging site to test what else I can adjust.

    Additional context:

    The Newspaper theme is quite heavy.

    The plugins WP Media Folder, FS-Poster, and Rank Math are also heavier.

    With currently 50,000+ posts, I realize the database (especially postmeta) is enormous.

    The postmeta table is filled with rows from various plugins.

    Query Monitor also sometimes indicates duplicate queries (from 2 to even 1200).

    I realize that I am presenting a specific and perhaps exceptional problem here wich might have nothing to do with your plugin, but after 8 months, I hope to receive pointers, directions, and possibly even solutions if you have any. Also posted this question on the Dutch WP forum.

    All insights are welcome and appreciated, as are all (critical) questions and/or remarks.

Viewing 11 replies - 1 through 11 (of 11 total)
  • RankBot

    1 month, 2 weeks ago

    Hello,

    I’m sorry to hear about the issues you’re facing with the mariadb.service and the database connection errors. It sounds quite frustrating.

    From the information you’ve provided, it seems that the problem may not be directly caused by Rank Math itself, but rather by the overall load and configuration of your server, especially considering the size of your database and the number of new posts being published daily. Here are a few things to consider:

    1. Database Load: With a postmeta table size of 2.6GB and frequent updates (20+ new posts daily), your database may be under significant load, especially if there are multiple plugins interacting with it simultaneously.

    2. Memory Limits: Your PHP memory limit is set to 256M, which might be insufficient for your current setup, especially with multiple heavy plugins. You might want to consider increasing this limit further if possible.

    3. Caching Plugins: Since you mentioned issues with both WP Rocket and WP Super Cache, it could be beneficial to test your site without any caching plugins temporarily to see if that alleviates the problem. Caching can sometimes lead to conflicts or excessive load on the database.

    4. Query Monitor: Since you are using Query Monitor for debugging, keep an eye on slow queries or any specific plugin that might be causing excessive database calls when new posts are published.

    5. Hosting Environment: Shared hosting environments can sometimes struggle with high traffic or resource-intensive operations. If possible, consider moving back to a VPS or a more robust hosting solution that can handle your site’s demands better.

    If you continue to experience issues, it might be helpful to reach out to your hosting provider for assistance, as they can provide insights into server performance and any potential misconfigurations.

    If you have any further questions or need more specific guidance, please feel free to reply to this ticket, and a human agent from our support team will assist you further.

    Sources:
    WordPress Database Error Commands Out of Sync; You Can’t Run This Command Now » Rank Math

    Regio Online

    1 month, 2 weeks ago

    Rank Math free

    Hi,

    I understand you only can assist in Rank Math related issues. That makes it also difficult, because I can not say this is a Rank Math issue, but only that Rank Math ‘might’ play a role in combination of things.

    Good to know we tried the theme with all advised optimal settings and WP Super Cache. The last one we had to uninstall again. It seems when ever some form of caching takes place the database gets overloaded and shuts down. The part where Rank Math might play a role is in the additional settings placed in wp-options and wp-postmeta tables. I understand if there are SEO settings, but as with other plugins we use, sometimes rows are not cleaned up if no longer needed. Can you tell me if Rank Math has any of those temporary settings that stay in the database? And if so, how to clean them if possible?

    Without the caching plugin the database is reasonably stable (once or twice a day a shut down), but somewhere at certain moment it seems multiple plugins and the theme all make a query tot the db and that shuts down the db. As stetd before, I don’t think Rank Math is the real problem, but maybe al small part of it. Your code is well written and maintained. But maybe you have some insights that can help to better use Rank Math or change some settings.

    Thanks in advance for your time!

    Regio Online

    1 month, 2 weeks ago

    Rank Math free

    I have additional info that might be important.

    I found our activated child theme has some rank math filters, of wich one is poiting to the postmeta table. See all filters (hooks) below:

    add_filter(‘rank_math/seo_analysis/postmeta_table_limit’, function ( $limit ) {
    return 5000000;
    }
    );

    add_filter( “rank_math/opengraph/facebook/image”, function( $attachment_url ) {
    $attachment_id = get_post_meta(get_queried_object_id(), ‘_thumbnail_id’, true);
    $gdrive_image = get_post_meta($attachment_id, ‘wpmf_drive_link’, true);
    if($gdrive_image) {
    $attachment_url = $gdrive_image;
    }
    return $attachment_url;
    });

    add_filter(‘rank_math/researches/tests’, function ($tests, $type) {
    unset($tests[‘contentHasTOC’]);
    return $tests;
    }, 10, 2 );

    add_filter(‘rank_math/researches/tests’, function ($tests, $type) {
    unset($tests[‘hasContentAI’]);
    return $tests;
    }, 10, 2);

    Also, after every Rank Math update we have to comment out below data in includes/opengraph/class-image.php on lines 241-252

    // Validate image only when it is not added using the opengraph filter.
    // if ( $validate_image ) {
    // $attachment_url = explode( ‘?’, $attachment[‘url’] );
    // if ( ! empty( $attachment_url ) ) {
    // $attachment[‘url’] = $attachment_url[0];
    // }

    // If the URL ends in .svg, we need to return.
    // if ( ! $this->is_valid_image_url( $attachment[‘url’] ) ) {
    // return;
    // }
    // }

    If we leave this enabled no images are shared with our Facebookpages when our posts are autoposted by FS-Poster.

    Regio Online

    1 month, 1 week ago

    Rank Math free

    I have commented out the filter in functions.php Newspaper Child Theme, to see if this could be a cause of the issues mentioned.

    add_filter(‘rank_math/seo_analysis/postmeta_table_limit’, function ( $limit ) {
    return 5000000;
    }
    );

    Jeremy

    1 month ago

    Hello,

    Thank you for contacting Rank Math support, and sorry for any inconvenience that might have been caused due to that.

    As you mentioned, it’s difficult to say with certainty whether Rank Math plays a direct role in the database issues you’re encountering, but based on what you’ve shared, we can confirm a few things.

    Rank Math does store metadata in the wp_postmeta and wp_options tables for various SEO features, but we strive to keep this as optimized as possible. That said, it’s possible for older or unused data to accumulate over time, especially on large and dynamic sites like yours. So since you already have a staging site in place, we recommend performing a clean test by completely removing Rank Math and its settings from the database. You can follow this guide: https://rankmath.com/kb/uninstall/
    If the database crashes continue even after doing this, it would indicate that Rank Math isn’t the root cause.

    The custom filters you’ve applied especially the one increasing the postmeta table limit — might contribute to the query load. Testing without these filters will also help determine if they’re playing a role in the instability. Let us know how that goes.

    With all that said, given that the issue results in the MariaDB service shutting down, your hosting provider is in the best position to assist further. They can review the server logs and database crash reports in detail, which might reveal exact causes such as query timeouts, resource limits, or lockups.

    Don’t hesitate to get in touch with us if you have any other questions.

    Regio Online

    1 month ago

    Rank Math free

    Thanks Jeremy,

    Great suggestion for the staging site. But we are closing in on another plugin that might accumulate way more data in the postmeta table then Rank Math: WP Media Folder with Google Drive addon. Also have a ticket standing out with them.

    You did mention however that old an unused data could accumilate from Rank Math. Is this some that can be cleaned from within your plugin our with SQL queries in PHPMyAdmin for instance?

    I agree that Rank Math has an effect, but that this in our opinion not the root cause. We think that the many image size configurations both in WP core and our themesettings have something to do with that.

    Non the less it never hurst to investigate all options ofcourse.

    Hope you have an answer for my Rank Math related questions! Thanks in advance for your time taken Jeremy. It is much appreciated!

    Jeremy

    1 month ago

    Hello,

    Our plugin doesn’t automatically clean up unused entries, although you can clear Analytics data from here:
    Rank Math → General Settings → Analytics → Clear Analytics Data

    For other entries like old postmeta or options, there’s no built-in cleanup tool, but a developer could review and clean those via phpMyAdmin if needed.

    Let us know if you’d like help identifying Rank Math-related entries.

    Regio Online

    1 month ago

    Rank Math free

    Hi Jeremy,

    I solved the issue after month of searching trying and asking half the world for support 😉 I did not need toclean out Rank Math data, but did clear some rows in postmeta. At first my postmeta was still 2.6GB, but looking closer in the table on tab Stucture, I saw that this table had an overhead of 1.6GB. I optimized the table with the button, and later tried and SQL Query because I didn’t see results immediatly.

    Patience is not my best virtue, but after 20 minutes or so, the Postmeta table went from 2.6GB to 249,9MB and my site is still working. Amazing… (and fingers crossed).

    So thanks again for all your hard work and efforts in trying to provide me with solutions. It is realy very much appreciated Steven!

    Jeremy

    4 weeks ago

    Hello,

    Glad to hear that you were able to pinpoint and resolve the issue.

    Yes, optimizing the postmeta table can make a huge difference, and it’s great to know your site is running smoothly again. Hoping it stays that way.

    Thanks for the update, and don’t hesitate to reach out if you ever need anything else.

    Regio Online

    4 weeks ago

    Rank Math free

    It stil is doing great with Rank Math and even WP Rocket caching. Also enabled OPcache on server. The only thing not working with this setup of my site is Redis Object Cache. I am more then hapy with all the result and now know where to look in the future.

    And sorry for calling you Steven Jeremy, all those support goeroes are messing with my mind, haha.

    Princess

    3 weeks, 6 days ago

    Hello,

    Glad that helped.

    Please feel free to reach out to us again in case you need any other assistance.

    We are here to help.

    Thank you.

Viewing 11 replies - 1 through 11 (of 11 total)

You must be logged in to reply to this ticket.