Scott, these QA_DEBUG_PERFORMANCE data can help?

Question

The server only has my site Q2A

CPU usage

Last 1 minute 121.97 Last 5 minutes 122.31 Last 15 minutes 94.13

 

QA_DEBUG_PERFORMANCE 

http://pastebin.com/ztquvY3f

 

Thanks

Answer 1

First thing I notice:

4367.29 ms - 95073 rows - 2 columns

 Your qa_options table has over 95,000 rows! Q2A core has around 300 options, most plugins only add a handful of options.

So you must have a plugin that's added thousands of options somehow. You should check through your table and see where they have come from.

Most likely it's a bug with a plugin that keeps adding options with different names.

Or maybe it's from a bunch of plugins you have installed at one time but then removed. Over time, adding and removing plugins leaves options in the table that are no longer used.

 

Edit: if you're not sure which options are filling up the table, try this query in phpMyAdmin:

SELECT SUBSTRING(title, 1, LOCATE('_', title)) AS sub, count(*) AS num
FROM qa_options GROUP BY sub ORDER BY num DESC

That will show you which prefixes for options are most common, e.g.
permit_   41
show_     32
points_   23

Comments

Thanks, Scott!

There are many lines like these:

q2am_98693_advert_image_url
q2am_98693_enable_adverts
q2am_98749_advert_image_url
q2am_98749_enable_adverts
q2am_98769_advert_image_url
q2am_98769_enable_adverts
q2am_98780_advert_image_url
q2am_98780_enable_adverts
q2am_98802_advert_image_url
q2am_98802_enable_adverts
q2am_98808_advert_image_url
q2am_98808_enable_adverts
q2am_98810_advert_image_url
q2am_98769_enable_adverts
q2am_98780_advert_image_url
q2am_98780_enable_adverts
q2am_98802_advert_image_url
q2am_98802_enable_adverts
q2am_98808_advert_image_url
q2am_98808_enable_adverts
q2am_98810_advert_image_url

the plugin Q2AM Simple Advert has been disabled on my site for several months.
It is causing the problem? Do you have any idea how I can solve this?

Installed on my server I have the theme SnowFlat developed by Q2A Market and plugin Q2AM Next Previous Question

---

Looks like a bug with that plugin here: https://github.com/q2amarket/q2am-simple-adverts/blob/master/qa-adverts-layer.php#L104

It's automatically generating options for every question on the site. If you are not using that plugin any more then you can delete all those options with numbers. You can use a query like this to remove them all quickly

DELETE FROM `qa_options` WHERE title RLIKE '^q2am_[0-9]'

Back up the options table beforehand in case something goes wrong!

---

Do you think this may be causing the errors on my site?

I found lines similar to these:


q2am_senna_enable_adverts
q2am_serachcse_advert_image_url
q2am_serachcse_enable_adverts
q2am_server-status_advert_image_url
q2am_server-status_enable_adverts
q2am_servers_advert_image_url
q2am_servers_enable_adverts
q2am_services_advert_image_url
q2am_services_enable_adverts
q2am_session.php_advert_image_url
q2am_session.php_enable_adverts
q2am_setpasswd.cgi_advert_image_url
q2am_setpasswd.cgi_enable_adverts
q2am_SetSecurity.shm_advert_image_url
q2am_SetSecurity.shm_enable_adverts
q2am_setup.php_advert_image_url

---

From the sound of it you have tons of these options in your DB. Those ones are not even Q2A, I think someone visited yoursite.com/setup.php, Q2A looked for that page and the plugin added an option for that.

So the best thing would be to delete all those similar options:

DELETE FROM `qa_options` WHERE title LIKE '%_enable_adverts';
DELETE FROM `qa_options` WHERE title LIKE '%_advert_image_url';

---

This what I have:
FROM `qa_options`
Showing rows 0 - 29 (36922 total, Query took 0.0002 sec)

What does this mean????

---

Jonatan,
Yes it is the one:

q2am_12748_enable_adverts

I have tons of above statement ends with different ending...

What should we do Scott?
Thanks

---

I have open a question topic:
http://www.question2answer.org/qa/50714/simple-adverts-adding-thousands-thousands-qa_options-table

---

truthonlytruth,
Your site is also unavailable or too slow?

---

Jonatan, I have cleaned my option table and edited files and right now testing and it works... i think Scott nailed it...

Hopefully plugin owner will fix that too.... :)
Thanks for catching this my god I hade 36 thousands extra line in that table...

Mind blowing... :)

---

Scott is a great developer! I am very grateful to him for helping me again.

truthonlytruth, my website presents this error message at times:

http://www.question2answer.org/qa/50077/

This has happened to your site?

---

A big NO...
That sounds like your mysql server is going away...
Did you apply Scott's performance improvement patch on that question?

---

Yes. The performance improved greatly after the improvements. But even this problem happens sometimes.
Tomorrow I'll delete the errors caused by the plugin.
I hope this is causing the problem.

---

Delete the lines help improve enough. Thank you Scott.
But there is some other problem that causes unavailability of my site at times.