High SQL load

Yes, it is a little bit to early to be using quiz 4 on a production site.

I don't know if quiz 3 scale better, or have better performance. I have little or no experience with Quiz 3. I do know that in Quiz 3 multichoice is a regular drupal module, and has been tested for a while. In quiz 4 multichoice has been rewritten to use the quiz OO framework(quiz_question). I strongly suspect that the OO framework causes some extra database load. In addition the Quiz 4 module haven't been performance tested yet.

If you try quiz 3 or profile quiz 4 I hope you'll find the time to post your findings here.

To be clear: We haven't done anything to map or improve performance in quiz 4 yet. We will work on performance in the beta releases. I don't think we'll have the time to make it perform super fast, but we will make it perform good enough to be "usable" when we reach RC.

Rogier,

If it is SQL related, then it should be easy to debug:

* Install an enable the Devel module
* Turn on the SQL Query Logging

It will show the time for each query, marking particularly slow ones in red.

You may also want to look at using Page Cache (with the Cache Exclude module to exclude quizzes) to optimize other parts of the site to operate quickly. Relieving load elsewhere would help Quiz perform better. And an opcode cache on Apache would help, too.

I followed mbutcher's suggestion #3 on a testing site with quiz 4.x. Created a quiz with 7 questions. As i proceed along the quiz as registered user the SQL load is as follow

Page execution time was 352.95 ms. Executed 48 queries in 42.47 milliseconds.
Memory usage:
Memory used at: devel_init()=0.92 MB, devel_shutdown()=9.76 MB.

Page execution time was 401.11 ms. Executed 51 queries in 36.11 milliseconds.
Memory usage:
Memory used at: devel_init()=0.92 MB, devel_shutdown()=9.88 MB.

Page execution time was 308.68 ms. Executed 45 queries in 27.95 milliseconds.
Memory usage:
Memory used at: devel_init()=0.92 MB, devel_shutdown()=9.73 MB.

Page execution time was 385.98 ms. Executed 46 queries in 57.42 milliseconds.
Memory usage:
Memory used at: devel_init()=0.92 MB, devel_shutdown()=9.77 MB.

// This is for feedback page that appears at the end of the quiz
Page execution time was 13750.48 ms. Executed 127 queries in 13059.28 milliseconds.
Memory usage:
Memory used at: devel_init()=0.92 MB, devel_shutdown()=10.35 MB.

Server configuration
CentOS 4.7 (512 MB RAM and AMD Opteron Quad core processor)
PHP 5.2.11
MySQL 5.0.85
Drupal 6.14 (page compression, block cache, CSS and JS aggregator enabled)

This SQL load seems to be normal for me (except for feedback page).

We are starting to work on these types of issues now.

It will still take some time before we reach this one, but the performance issues will be addressed before we release our release candidate.

We have done some improvements now. Would you be able to run a test of the latest dev release locally and post any slow queries here?

The quiz result page is still running an extreme amount of queries, but we're not going to fix this in 4.0. We have improved the performance of the result page a lot already, but it can still be a lot faster in my opinion. It will require a major rewrite though, and will result in a lot of issues.

In an upcoming release we will try to fetch all the data we need to generate that page from only one query. The alternative is to cache reports for each question, but I don't think that would be a good idea.

On the result reports this has been improved
- Before performance optimization begun each question node was loaded multiple times to provide its result report. This caused an enormous load on both php and mysql.
- Now none of the question nodes are loaded. This reduces mysql and php load by around 66 % on my local host.

On question taking this has been improved
- Before performance optimization begun the question node was loaded multiple times. It was loaded again and again for each function called from quiz_question.
- Now the question nodes are stored in static cache, resulting in a big performance improvement.

On the question browser this has been improved:
- Replaced inner join with outer join to avoid constructing cartesian products of all the table rows before LIMIT cuts away rows that aren't needed.
- Used LIMIT in the pagers count queries so that the count query won't have to join and process all rows. It only count enough rows to know whether or not it should show links to the next five pages. The link to the last page has been removed because we're not counting that far anymore
= This results in a major reduction on sql load if you have a lot of questions and results stored in your db. The performance gain increases by the amount of questions/results your search finds. (There are no performance gain if you are visiting the last pages of your result.)

Now I've also added indexes where needed to optimize quiz taking performance.

I mark this one as fixed. If there still are problems please reopen this issue.