Query for results page takes minutes to execute and cause website crash

[Testbusters]

Hi,

i'm experiencing serious performance issue on my website. Website runs on a 10 Core VPS and 10 GB of ram. Some months ago we had a 6 core with 6 gb of ram and was fine, now it can't take the website up. Reason is we have installed this plugin some months ago and in a few months we had thousand and thousand of submitting in quizzes. Now table wp_mlw_results has 38115 rows in our DB. We think it's not efficient the way the plugin it works when you have thousand of submitting. We tried using a fulltext index in this db table, but it didn't solve the problem. Our host siteground said that this is causing server not responding.

MySQL logs shows wp_mlw_results queries that are really heavy like this:

`Executed 21h 26m 54s ago for 60.022594 sec on Database --> Unknown (NOT FOUND / DELETED) Date: 2017-10-22 12:09:54 Query_time: 60.022594 Rows_examined: 38113: Rows_sent 0 Lock_time: 0.000170

Schema: dbname Last_errno: 0 Killed: 0 SELECT * FROM nwtbdbmlw_results WHERE deleted='0' AND (user=45113 OR email='email@email.com') ORDER BY result_id DESC LIMIT 0, 10;`

I have many of this queries per second, in a lot of situations this lend to database not responding and website crashing.

[fpcorso]

Hey @Testbusters

Wow, 38k+ rows in the results table! I think your site is the busiest QSM has been used on so far which is why these issues had not been reported previously.

Can you provide a few more specs about QSM's usage? # of quizzes/surveys on site? # of questions? You can quickly copy this data from the "Support Information" section of the "Help" page within the "Quizzes/Surveys" menu.

[fpcorso]

Also, @Testbusters which version of the plugin are you currently using on this site? Do you have any addons installed?

[Testbusters]

Hi @fpcorso, thank you for your reply!

Can you provide a few more specs about QSM's usage? # of quizzes/surveys on site? # of questions?

Also, @Testbusters which version of the plugin are you currently using on this site? Do you have any addons installed?

I think you can read all this info below :)

The problem with the database table is that you save in a single field (quiz_results) of the table "wp_mlw_results" too much informations:

• Answers from the user

• Questions

• Correct and Wrong Answers

And this is just for one user in a single field, if you have thousand of submitting, this became a disaster. In my dev site, i have 3000 rows in this table and it's still slow, because of that field.

Note that 38000 rows in "mlw_results" are taking 1.5GB of my DB. My total DB is 2.6 GB that's over 60% of total.

Site Information

Site URL: https://www.testbusters.it Home URL: https://www.testbusters.it Multisite: No WordPress Information

Version: 4.8.2 Language: it_IT Active Theme: canvas-child 1.0 Debug Mode: Disabled Memory Limit: 1024M Plugins Information

Must Use

Active

Adminimize: 1.11.2 Anti-Spam by CleanTalk: 5.79 Background Processing Test for Import / Export: 1.0.0 Cloudflare: 3.3.1 Code Snippets: 2.9.4 Contact Form 7: 4.9 Contact Form 7 - reCAPTCHA: 1.2.0 Contact Form CFDB7: 1.1.5 Custom Facebook Feed Pro - Reviews: 1.0.3 Custom Facebook Feed Pro Personal: 3.1 Custom Tabs: 0.1 DHL Express/DHL Paket WooCommerce Shipping with Print Label: 3.3.5 Envato Market: 1.0.0-RC2 Event Tickets: 4.5.7 Event Tickets Plus: 4.5.6 fattura24: 1.4.0 Follow-Up Emails: 4.5.2 GG Backend Search: 1.1.4 Goo.gl: 1.4.3 Google Analytics for WordPress by MonsterInsights: 6.2.4 Google Webfonts For Woo Framework: 1.6.4 Groups: 2.3.0 Groups Import Export: 1.2.4 Groups WooCommerce: 1.11.3 Heartbeat Control: 1.2.2 Itthinx Updates: 1.2.0 iubenda Cookie Solution: 1.14.3 Lazy Facebook Comments: 2.0.1 Lazy Load by WP Rocket: 1.4.3 Loco Translate: 2.0.16 Nav Menu Roles: 1.8.6 No Adverts For Master Suite: 1.0 PixelYourSite PRO: 6.3.4 QMN Advanced Leaderboard: 1.0 QMN Extra Text Variables: 1.0.0 QMN Gradebook: 0.1.0 QSM - Export & Import: 0.1.0 QSM - Export Results: 1.4.1 QSM - Logic: 1.0.1 QSM - Reporting And Analysis: 1.3.1 QSM - User Dashboard: 1.1.1 Query Monitor: 2.15.0 Quiz And Survey Master: 5.1.2 SB WooCommerce Email Verification: 1.3 Scalability Pro: 4.33 SeedProd Coming Soon Page Pro: 5.7.11 Simple Calendar: 3.1.10 testbusters: 0.9.4 The Events Calendar: 4.6.2 The Events Calendar: Remove QR Codes: 0.1.1 The Events Calendar PRO: 4.4.18 Toggle Mine: 0.1 W3 Total Cache: 0.9.5.4 WooCommerce: 3.2.1 WooCommerce Checkout Field Editor: 1.5.7 WooCommerce Conditional Shipping and Payments: 1.2.9 WooCommerce Customer/Order CSV Export: 4.3.7 WooCommerce Min/Max Quantities: 2.3.18 WooCommerce Order Status Manager: 1.7.3 WooCommerce Product Bundles: 5.5.2 WooCommerce Sequential Order Numbers Pro: 1.11.3 Wordfence Security: 6.3.20 WP-Mail-SMTP: 0.10.1 WP Disable: 1.5.13 WPFront User Role Editor: 2.13.1 Yoast SEO: 5.7.1 Inactive

Advanced Database Cleaner: 2.0.0 Captcha: 4.3.6 Disable Emails: 1.3.0 MathJax-LaTeX: 1.3.6 My Private Site: 2.14.1 Really Simple CAPTCHA: 2.0.1 Social Media Feather: 1.8.4 Subscribe & Connect: 1.1.4 UpdraftPlus - Backup/Restore: 2.13.12.22 Use-your-Drive: 1.7.7 WooCommerce - Store Exporter Deluxe: 2.2.2 WooCommerce Sequential Order Numbers: 1.8.2 WooSidebars: 1.4.3 WooSidebars - Sidebar Manager to WooSidebars Converter: 1.1.3 WP User Avatar: 2.0.8 Server Information

PHP : 5.6.31 MySQL : 5.6.36 Webserver : Apache/2.4.25 (Unix) mod_hive/6.6 OpenSSL/1.0.1e-fips mod_fastcgi/2.4.6 QSM Information

Initial Version : 4.6.4 Current Version : 5.1.2 Total Quizzes : 24 Total Active Quizzes : 16 Total Questions : 1112 Total Active Questions : 960 Total Results : 38115 Total Active Results : 37986 QSM Recent Logs

Log created at 2017-10-27 19:17:14 : Error 0003 - from UPDATE nwtbdbmlw_quizzes SET quiz_name = 'Simulazione 24 Agosto (illimitata)' WHERE quiz_id = 23 Log created at 2017-09-07 13:18:21 : Error 0008 - from Log created at 2017-09-04 18:25:30 : Error 0021 - from UPDATE nwtbdbmlw_results SET deleted = 1 WHERE result_id = 33926 Log created at 2017-08-21 16:26:24 : Error 0004 - from SHOW FULL COLUMNS FROM nwtbdbmlw_questions Log created at 2017-08-21 16:24:49 : Error 0004 - from SHOW FULL COLUMNS FROM nwtbdbmlw_questions

[Testbusters]

@fpcorso Hi, this issue is still present. It's forcing us to delete results after few quizzes, otherwise, the database became huge and slow in mlw_results. Problem in this table is field "quiz_results" which has too much text in it. You need to change the way this plugin uses the database to get it working on big numbers. Hope you will solve it! Thanks

[fpcorso]

@Testbusters How many questions do you have in your quiz? Our main site (https://quizandsurveymaster.com/) now has almost 30,000 rows in the results table and we have seen no performance issue and are on a much smaller VPS than you are running.

[fpcorso]

@Testbusters Is the main query that is giving the issue still the query in the original issue? I did some research previously and that query is from the User Dashboard addon. Where do you have that shortcode located? Is that on a page somewhere on the website or on the same page as the quiz?

[Testbusters]

Hi @fpcorso :) we are creating one quiz every 2 months more or less. Every quiz has got 60 questions. Every question has got 6 answers and "info". The quiz is taken by approximately 2k/3k people, but they raise getting close to September (last year we had between 8k and 12k entries for the last three quizzes). and we would like to keep as much history as possible (those data are precious for us, we don't want to delete results every year)

To recap:

• around 6 quizzes per year
• 60 questions per quiz
• 6 answers+info per question
• around 40k entries every year

We have the shortcode on a dedicated cached page. The problem is also the huge dimension that we reach in the database. With 35k entries the size of the table raise to ~1GB (our db is now 2,5GB so this would be around 40% increase per year) and it's absurd to increase the size of our database like this. Your plugin is using one field to store all the questions and answers for every entry. Wouldn't it be more convenient to divide it in different tables, having just references to the questions and answers instead of duplicating the entire questions and answers? I ask you because that field for us can have between 20k/30k chars, so it's becoming quite huge.

I understand it's a big change, and I'm not expecting for it to be solved and optimized fast, but I think a quiz plugin should be scalable and should handle even large amount of data (since it's usually very important to reach as many people as possible).

Thank you very much! Really appreciate this plugin and your work behind it :)

[fpcorso]

Hey @Testbusters !

I agree that 1GB is way too much. The original decision to have the results include question, answer, correct answer, contact field labels, etc... comes from the fact that this plugin is used in many different settings.

Admins regularly delete questions, change answers, change text, etc.. Many of our users are in industries/situations where they want (sometimes legally required to do so) to have the exact text and answers shown to the user when the user was completing form. This is why I currently have the plugin set up as it is.

That said, this is far from an optimized solution. Something I have been considering is keeping "revisions" of questions and other user text and then only having references to those questions in the results field similarly to what you were describing.

However, this would be a massive change as we would then have sites that have both results stored in the old way and results stored in the new way which would make analysis (our own addons or their own custom solution) much more complex.

I completely agree that something needs to change to allow better scalability but am unsure what the "right" solution is. I will continue pondering this over and doing some tests to see if I can identify the right path forward.

As always, I appreciate your insights and feedback.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.