New to the community? Start here

Multisite table wp_sitemeta grows huge – currently 27.82 GB

I’ve noticed my multisite has been growing huge in size and this month I can’t optimise the wp_sitemeta table like I have done before to reduce it.

The table is 28 GB.

I am not sure if it’s related to the wild table growth, but one of the subsites I can’t see the media library because it just hangs.

Thanks in advance for any help.

Wed, 22 Jan 2020 8:30:17
  • mbitcon
    • Problem solver

    Did you have a look into the table? Have a look what keys seem to be there ver often. If the keys start with _transient, it is general safe to delete them. The name of the keys also might give a hint which plugins or themes are creating them, maybe you find the culprit that way…

  • Adam
    • Support Gorilla

    Hi Marc

    I hope you’re well today!

    I second mbitcon on that: take a look at that table and try to identify the data/kind of data that occurs the most. It’s very difficult to say anything more without actually seeing the table as it can be caused by tons of different reasons.

    Usually though, it’s either some underlying issue that prevents site from “clearing up” any “temporary” data that it might be saving there (e.g. from some plugin or plugins) or it’s some plugin “going wild” due to some conflict or bug resulting in not being able to “clean up after itself”. But these are just some common reasons.

    Still, to identify that the first thing to do is to identify some “pattern” and/or “type of data” that takes so much space.

    Are you able to browser that table via phpMyAdmin? If yes, did you try to review it and find such data there? Or do you need help with this?

    Best regards,
    Adam

  • Kylie
    • Site Builder, Child of Zeus

    Thanks mbitcon and Adam Czajczyk – I have looked in phpMyAdmin but I don’t know how to tell which record is getting huge. There are about 151 rows, but I can’t see which rows are big. What am I looking for?

    I have just ‘optimized’ so now it’s not huge any more but I do want to make sure the out of control growth doesn’t keep happening (I do the optimizing every few weeks but it’s not a sustainable task for me!).

  • Adam
    • Support Gorilla

    Hi Marc

    Thank you for response!

    If it’s only 151 rows then it should by no means be this big unless there’s some very big sets of data stored in some records (like e.g. full high-resolution images stored in db – which is uncommon and not recommended but possible).

    That 28GB – that was the size of _sitemeta table alone or entire database? What’s the site after optimization?

    I’ll be happy to take a closer look at that to see if I can find out what might be/have been causing this but for that I’d need a full direct access to site. If you want me to do this, would you please provide me with access credentials?

    Note: Don’t post your login details in this ticket.
    Instead, you can send me your details using our contact form https://wpmudev.com/contact/#i-have-a-different-question and the template below:

    Subject: “Attn: Adam Czajczyk”
    – Site login URL
    – WordPress admin username
    – WordPress admin password
    – FTP credentials (host/username/password) <- not necessary if cPanel provided - cPanel credentials (host/username/password) <- preferred; if no cPanel then FTP and phpMyAdmin login URL please - Folder path to site in question - Link back to this thread for reference - Any other relevant urls/info Best regards, Adam

  • Kylie
    • Site Builder, Child of Zeus

    Cheers @adamczajczyk. The 28 GB was just that one table (sitemeta). After optimization it was maybe 300 kB? Can’t remember but it was MUCH smaller. Note that I have seen this one table as large as 60 GB or more and CPUs were going mad – I had to upgrade my server at one point, before I could figure out what was going on. Painful. And still the site is very slow! Aaargh.

    I’ve sent you the creds you asked for – thanks so much in advance for your help.

  • Adam
    • Support Gorilla

    Hi mbitcon

    Actually not, the site is quite complex and customized multisite and seems to be around for quite some time already. The difference in size of the table before and after optimization is also enormous so I believe it wasn’t really anything specific but at this point it’ll be best to just keep an eye on the table.

    So I suggested making an export of the table “as is” currently and if it starts to grow rapidly again, making another export before running optimization so we could then compare those two and hopefully it will give some additional clues.

    Best regards,
    Adam

  • Layanan TIK
    • WPMU DEV Initiate

    Hello, same here
    I have multisite wordpress with 1200+ sites and lots of plugins
    I have tried doing “wp cli optimize” , but it seemed it doesnt solved the problem
    Then, after doing query “OPTIMIZE TABLE wp_sitemeta” in database
    I managed to reduce wp_sitemeta.ibd size from 19 GIGA to 10Mega