[Resolved] slow query broke my site

[Author]

Posts

[Jeffrey]

scraped internal resource around 20,000 posts, I also use type, form, view, and access plugin. but I don't know why the host CPU is no space usually in around 10 minutes when I start the server. I read some article and added few lines code like

innodb_buffer_pool_size=100M
max_allowed_packet=1024M
net_read_timeout=3600
net_write_timeout=3600
max_heap_table_size = 512M
tmp_table_size = 512M

but didn't get any improvement, I found a query is very very slow, even killed it, it also will appear till the site is broken. I did try to remove the scrape plugin and keep the posts only, the issue is not gone well. so I doubt it should be the query issue. I duplicated the site and uploaded, please help me fix it if possible, I have been working on this a couple days, but... ?

[Jeffrey]

I uploaded my site with Duplicator, in order to not broken the host, I deactivated the post type of Homestay and Country, so when you reproduce the site at local, you may need to activate that two post types. and I found if I deactivated those post types, no memory leak, no fully CPU, no slow queries, no big tmp files. please check out at local, because I have to deactivate that site, otherwise the host will be broken.

In order to appear this slow queries, you may need to wait for around 10 to 20 minutes after activating these two post types. Thanks

with the same language, please assign this ticket to Luo Yang, if possible. Thank you so much!

that slow query looks like

Copying to tmp table on disk

SELECT p.ID FROM wp_posts AS p , wp_postmeta as postmeta WHERE p.post_date < '2018-06-03 01:40:19'

[Luo Yang]

Hello,

Thanks for the details, I am downloading the files, will feedback if there is anything found

[Luo Yang]

The SQL query you mentioned above is wrong, it select the data from table "wp_postmeta", but does not add "WHERE" condition on it, it should be something like this:

SELECT p.ID FROM wp_posts AS p , wp_postmeta as postmeta WHERE p.post_date < '2018-06-03 01:40:19' AND postmeta.post_id=p.ID

I have tried above SQL query in my localhost, it works fine, see screenshot SQL query: sql.JPG.

Toolset plugins won't produce such kind of SQL query as you mentioned above:
https://toolset.com/forums/topic/slow-query-broke-my-site/#post-915083

since you are using some other plugins, I suggest you check these first:
1) Deactivate other plugins (except Types plugin) and switch to wordpress default theme 2017
2) Restart your mysql server, kill all slow queries and test again
3) If the problem is fixed, activate other plugins/theme one by one, try to locate the problem plugin/theme

[Jeffrey]

Hi Luo,

Thank you for the reply, Have you waited for 10 or 20 inutes after the site restart? I have tried to deactivate every plugin, theme and post type, and custom taxonomy, I did even delete the scrape plugin, slow queries are still here except I deactivate
the Homestay custom post type, before you test, please activate the custom post type, Homestay, when you are down, you will see that query around 10 to 20 minutes. even killed them, other queries would re-appear in 20 minutes...

🙁

485
Copying to tmp table on disk SELECT p.ID FROM wp_posts AS p  , wp_postmeta as postmeta WHERE p.post_date < '2017-12-14 06:18:29'

Kill
160
wordpress_8
localhost
wordpress_e
Query
117
Copying to tmp table on disk
SELECT p.ID FROM wp_posts AS p  , wp_postmeta as postmeta WHERE p.post_date < '2017-12-14 06:18:29'

[Jeffrey]

You may need to activate custom Homestay post type before you test, when you did, you will see the issue in 20 minutes. Also, you will see a big temporary file in the path of etc/tmp, which size is more than 20G probably if you don't kill that process

[Luo Yang]

你可能误会了我的意思，我已经导入了你的数据库备份，使用PHPmyadmin运行你上面提到的SQL语句：

SELECT p.ID FROM wp_posts AS p , wp_postmeta as postmeta WHERE p.post_date < '2018-06-03 01:40:19'

也同样出现你说的问题MYSQL数据库软件死机
然后我使用你的数据库搭建了一个wordpress网站，只Toolsets插件，并没有出现同样问题，也就是说Toolset插件并没有制造这样的明显错误，可能是你网站上的兼容问题或者服务器问题，你是不是有安装什么备份软件？请你检查一下你的服务器，如果有条件尝试导出你的数据库到一个全新的word press网站上测试。

[Jeffrey]

Yes, you are right, Updraftplus plugin migrating my site performing an incomplete task, I just uploaded all of files and database to the domain without that plugin, all issues disappeared. it took me around two weeks 🙁 太杯具