How to reduce database query’s td-composer on pages

Hi there at tagdiv,

I am troubleshooting my website https://regioonline.nl, a newssite with 64 subpages and 50.000+ posts. The databaseservice mariadb gets killed every day a few times when working in the backend. At first I was using a VPS with 16 CPU core and 60GB RAM, and now a shared hosting with 24 CPU core and 16GB RAM. In both cases the database issue is persistent, only on the (unmanaged) VPS the mariadb.service was not automaticly restarted (had to restart the server). I had someone managing that VPS for me, but he got another job (so I moved to shared hosting). Now I am more up to speed about use a Linux VPS (Debian 12) with Plesk and SSH, but still figuring things out before moving the site back again.

The site has a big database (3.2GB) with the main table being postmeta (2.7GB), serving all images from Google Drive through a plugin (WP Media Folder+addon).

With the use of Code Profiler and Query Monitor I am troubleshooting for main issues. All my (sub)pages generate about 459 Query’s per page of witch 416 are from td-composer (see https://prnt.sc/lwOgOlsnsEf2

The page I measured this on was https://www.regioonline.nl//regio-tilburg/, but all (sub)pages (see https://regioonline.nl/regiopagina/) are with the same result. The homepage is even a bit larger

On the mentioned page of Tilburg I disabled all Ajax pagination and block caching.

Using any caching solution (be it with plugins like WP Rocket or WP Supercache, or using serverside caching options like Varnish, Redis Object Cache or OPcache) will make the issue of the database server getting killed a lot worse. Ofcourse tried every combination possible, but only not caching anything, reduces the issue to a few times a day, instead off every 20 minutes or so.

Is this a question of too many blocks on a page (disabled block caching for Flex Blocks aswell) so td-composer generate that many query’s? What is the best option to reduce this to an acceptable level, not killing the databaseservice every time?

Do I need to tune down the number of topics/blocks on every page? Do I need to reduce the number of pages?

Good to now that every menuitems has it’s own seperat link. I also tried the deactivate all (sub)pages except the homepage and made links to to automatic generated category pages of every item, but even did not solve the issue.

Hope you can help me a few steps further, or can give any advise on how to proceed. If I tune down on settings, blocks and pages, is this info removed from the database (mainly postmeta) also? If not, how can I solve that?

Sorry for the bloat of information, but I am hoping to make the issue clear. I know this is not only a theme issue, so I have already contacted several pluginbuilders also. This helped already in reducing databasequery’s.

Okay Calin. Thanks for your insights. I will try your recommendations. It will take a while I think, but I am curious if things will improve.

Now I only need to figure out how to make a staging site. Never done that before, but I will work it out.

I also learned there are double query’s made (sometimes 1200) so that can not be good. The main plugins (beside your theme) contributing to this are WPMF (+cloud addon+gallery addon), Rank Math and FS-Poster. I will get back to you if I have some results Calain. Thanks for pointing me in a direction to investigate further.

Hi Calin,

Thanks for that insight. I have create a clone of my website, since the livesite is updated every day a staging site with ability to push to live is the right choiche.

I created a subdomain as domain and put cloned the livesite to that domain. Works great.

Now I am troubleshooting the datbase, mainly concentrating on postmeta since that is a 2.6GB table. Using an SQL query I listed the top 200 of heaviest rows (in datasize) from postmeta.

The largest by far are 2 rows called tdc_history with combined 259766.0000 of bytes. Do I read this as only 0,25MB or are it realy 2597660000 bytes (not point used) wich would be 2597.66MB.

But since it is the largest row, could this row be deleted? I also see a lot of rows starting with _oembed_ scoring high as well. Although I have no clue if they are theme related, could these rows be deleted as well?

Awaiting you answer eagerly this time!

Thanks Calin for advising me on this issue. I think it would be a start in beginning to solve the issue. Ofcourse I have also set out support tickets at all (important) plugin builders, hoping to find some more answers. I personally think it is a combination of things, wich makes all the more difficult to solve, but I move forward with more knowledge then I had before your answer 😉

Anyhow, thanks again for the fast support and clear answers Calin. Much appreciated. If you don’t mind could you keep this ticket open for now, while I investigate further on my staging site. Also trying your earlier suggestions.

Hi Calin,

After backup I have removed two rows of tdc_history on my staging site. It reduced the database a little bit, but the site keeps on working just fine. So it seems that there was no issue with this.

I also encoutered the following row: tdc_dirty_content

Is this pointing to revisions, temporary data etc? In other words, can this row be deleted?

In the meantime I also figured out that it is the plugin WP Media Folder that creates the largest amount of db rows in postmeta, wich have everything to do with the set image sizes in the themepanel of Newspaper and WP Core.

In have a question regarding those imagesize settings.

The WPMF Google Drive addon or the theme creates a db row _wp_attachment_metadata, wich holds references to the image file on Google Drive, but does so many different times in many different sizes (se below. I only mention the image size not the whole code. Every size is mentioned twice):

————

https://drive.google.com/thumbnail?id=19EI*************ZWn&sz=w300

https://drive.google.com/thumbnail?id=19EI*************ZWn&sz=w1024

https://drive.google.com/thumbnail?id=19EI*************ZWn&sz=w150

https://drive.google.com/thumbnail?id=19EI*************ZWn&sz=w768

https://drive.google.com/thumbnail?id=19EI*************ZWn&sz=w1536

https://drive.google.com/thumbnail?id=19EI*************ZWn&sz=w2048

https://drive.google.com/thumbnail?id=19EI*************ZWn&sz=w150

https://drive.google.com/thumbnail?id=19EI*************ZWn&sz=w218

https://drive.google.com/thumbnail?id=19EI*************ZWn&sz=w300

https://drive.google.com/thumbnail?id=19EI*************ZWn&sz=w324

https://drive.google.com/thumbnail?id=19EI*************ZWn&sz=w485

https://drive.google.com/thumbnail?id=19EI*************ZWn&sz=w696

https://drive.google.com/thumbnail?id=19EI*************ZWn&sz=w1068

https://drive.google.com/thumbnail?id=19EI*************ZWn&sz=w1920

https://drive.google.com/thumbnail?id=19EI*************ZWn&sz=w1600

https://drive.google.com/thumbnail?id=19EI*************ZWn&sz=w2200

https://drive.google.com/thumbnail?id=19EI*************ZWn&sz=w2800

https://drive.google.com/thumbnail?id=19EI*************ZWn&sz=w3400

https://drive.google.com/thumbnail?id=19EI*************ZWn&sz=w4000

——————-

This looks generated by WPMF, but it are the themepanel block settings => Thumbs on Modules/Blocks and I quess the WordPress thumbnail, meium, large and full settings.

At the moment I have all the thumb sizes turned on (except the retina), but I use 3 blocks in my pages, Beginning with one Big Grid Flex Block 6, several Big Grid Flex Block 1 and several Flex Block 1. With Flex Block 1 I use different views and image sizes to show.

However if I fill out ‘full’ or ‘1920×0’ the images fit in every Block in any size. It is good to know that before I upload an image through the WPMF Google Drive folder in WP Media Library, I first resize every image to max 1400w x 600h in jpg format and compressed to on everage 120Kb an image. At the moment there are over 35.000 images on Google Drive (about 3.5GB total in size).

Question (at last): if I can set every block in my pages to the full or 1920 setting, could I then disable all other thumbnail size settings? There is only one size image on Google Drive, but is delivered (as seens in the db row) in different size.

That is accumulating a lot of unnessary data it seems making my postmeta table very large and prone to disrupption of service.

If it is possible as I asked, I will have to contact WPMF for a way to get rid of the bloat of information no longer needed.

Hope you can make some sense om my message, because even writing it down was complex for myself.

Hi Calin,

Thanks for your tips regarding the recommanded thumbs settings. Your suggestion to regenerate would works if those thumbs where on the sae server in our uploads folder. That is however not the case.

Simply put: we have no thumbs. We only have one size (full or 1920) on Google Drive. Inside the _wp_attachment_metadata rows in our postmeta each thumbnailsize for our theme is described and shown if needed, but it does not fysicly exsist anywhere. It is just the one Google Drive image served in different sizes described in that table. There are about 13 sizes and each size is described twice in one row. There is one row for each image and there about 35.000 images. You can do the math for that is over 910,000 seperate rules in postmeta,

Example of _wp_attachment_metadata (first few lines of one row only):

a:7:{s:5:”width”;i:2560;s:6:”height”;i:1394;s:4:”file”;s:40:”Isolatie-Foto-gemeente-Altena-scaled.jpg”;s:8:”filesize”;i:317478;s:5:”sizes”;a:19:{s:6:”medium”;a:6:{s:4:”file”;s:79:”https://drive.google.com/thumbnail?id=1425AFrPRIGp9vQy9TsJIg8W9A1X2G1vo&sz=w300″;s:5:”width”;i:300;s:6:”height”;i:163;s:9:”mime-type”;s:10:”image/jpeg”;s:8:”filesize”;i:10721;s:3:”url”;s:79:”https://drive.google.com/thumbnail?id=1425AFrPRIGp9vQy9TsJIg8W9A1X2G1vo&sz=w300″;}s:5:”large”;a:6:{s:4:”file”;s:80:”https://drive.google.com/thumbnail?id=1425AFrPRIGp9vQy9TsJIg8W9A1X2G1vo&sz=w1024″;s:5:”width”;i:1024;s:6:”height”;i:558;s:9:”mime-type”;s:10:”image/jpeg”;s:8:”filesize”;i:71200;s:3:”url”;s:80:”https://drive.google.com/thumbnail?id=1425AFrPRIGp9vQy9TsJIg8W9A1X2G1vo&sz=w1024″;}s:9:”thumbnail”;a:6:{s:4:”file”;s:79:”https://drive.google.com/thumbnail?id=1425AFrPRIGp9vQy9TsJIg8W9A1X2G1vo&sz=w150″;s:5:”width”;i:150;s:6:”height”;i:82;s:9:”mime-type”;s:10:”image/jpeg”;s:8:”filesize”;i:3789;s:3:”url”;s:79:”https://drive.google.com/thumbnail?id=1425AFrPRIGp9vQy9TsJIg8W9A1X2G1vo&sz=w150″;}s:12:”medium_large”;……… etc.

So, you see that the image sizes need to be cleared inside the database and can not be regenerated.

Hi Calin,

Thanks for the helpfull insight. But I believe I have solved the proble regarding the database. It has someting to do with the theme but more so with wp Heartbeat API.

In figuring out witch rows that are left are the largest in WP I ran an SQL query:

SELECT meta_key, COUNT(*) AS number_of_rows
FROM wp_postmeta
GROUP BY meta_key
ORDER BY number_of_rows DESC;

This gave met the following list:

post_views_count
64288
rank_math_internal_links_processed
50282
_edit_last
48835
_edit_lock
47508
rank_math_focus_keyword
47228
rank_math_seo_score
38447
tdc_dirty_content
37281
tdc_icon_fonts
37271
post_view_7days_last_day
32308
post_views_count_7_day_last_date
32308
post_views_count_7_day_total
32308
post_views_last_24_hours
32120
post_views_last_48_hours

As you can see there are a lot post_views wich we don’t use. Can this be disabeled through the themepanel also? For now I delete all the rows starting with post_view

But two other elements are just as important _edit_last; and _edit_lock; Edit lock should only be used by wp for a short while preventing for several authors to work on the same post. Those rows should delete itself, but as you can see, there are over 47.000 rows of it. It seems that a wrong or non presint Heartbeat API was responsible for that. So I also delete _edit_last and _edit_lock

Th datbase witch was 3.1GB large with a postmeta of 2.6GB is now a 1.09GB database with 898MB postmeta.

Installed WP Rockect again with preload. Installed and enabled Redis Object cache and am testing the staging sites performance It all seems to work fine after several hours and is ligtning fast again.

Thanks for yur every positive support and patience Calin. Hope I realy tackeld the issue once and for all this time.

Thanks Calin,

I implemented everything on our live site today. At first, after removing several known rows of data, it seemed the postmeta table (viewing on tab Structure) had an overhead of 1.6GB. Something told me that could be the issue, after finding out that this is realy on reserved space in the database I optimised the table as adviced and 20 minutes later my postmeta table went from 2.6GB to 283,54MB. And everything is still working… (fingers crossed). I took me a few month, but finaly have seem to solve it ;-). Thanks you so much for your patience and assitance Calin. Again very much appreciated! You may also close this ticket!