Thread Starter
rooify
(@rooify)
Further to the above: is this a bwp query?
SELECT p.* FROM wp_posts p WHERE p.post_status = ‘publish’ AND p.post_password =’ ‘;
db is around 40G with:
296,372,879 rows in wp_postmeta
11,822,908 in wp_posts
23,729,634 in wp_term_relationships
Hello,
That query could be a BWP query, but I’m not certain because it is too generic.
Do you happen to enable the Google Image Sitemap extension? What is your current “Split limit”?
Judging from wp_postmeta’s size, I suspect that it hanged when trying to fetch data from that table. You can check your mysql log for slow queries, but if there’s none it could be that PHP timed out when processing the data fetched from mysql instead.
Thread Starter
rooify
(@rooify)
Hi Khang,
No extensions are added (no images, not news). The current split is at 1000 but we have had it as low as 100/100 & 10 queries until we forced a memory error on sitemapindex.xml.
php memorry is now 266MB so we don’t get a mem error on the sitemapindex but the ‘part’ sitemaps still timeout.
This is what i am getting in the plugin logs:
‘Mar 03, 2016 01:06:02 AM —post_job_listing_part22629.xml does not have any item. There are no public ABN. Enable/disable sitemaps via BWP Sitemaps >> XML Sitemaps. (debugging was on)
Mar 03, 2016 01:02:25 AM —post_job_listing_part22629.xml will be served using module file post.php. (debugging was on)
Mar 02, 2016 11:57:35 PM —post_job_listing_part22813.xml does not have any item. There are no public ABN. Enable/disable sitemaps via BWP Sitemaps >> XML Sitemaps. (debugging was on)
Mar 02, 2016 11:54:19 PM —post_job_listing_part22813.xml will be served using module file post.php. (debugging was on)
Mar 02, 2016 11:53:43 PM —post_job_listing_part12447.xml does not have any item. There are no public ABN. Enable/disable sitemaps via BWP Sitemaps >> XML Sitemaps. (debugging was on)’
Would there be any queries you would advise that we investigate to see if we can break them down? I appreciate any direction/
You can debug the post module file (bwp-google-xml-sitemaps/src/modules/post.php) to determine exactly what is causing the timeout. It is heavily commented so hopefully it won’t be difficult for you to find the way around that file.
Since the post module uses SQL cycling, the actual query can only be found in get_results function in bwp-google-xml-sitemaps/src/class-bwp-gxs-module.php file, which is the base sitemap module.
Thread Starter
rooify
(@rooify)
Thanks Khang, ill check it out.
Big task for any process to go through a table of that size, i guess ill have to try and split it up somehow.
Glad you got it working. Would you mind sharing your solution?
Thread Starter
rooify
(@rooify)
quick and dirty, using BWP for posts, pages but wrote a script for the listings based on this:
https://github.com/o/sitemap-php
Submitted two index sitemaps to GWT for now.
