Slow Query Issue (Version 3.22.8+200309)

We observed that LS frequently performs such queries:

select '' as act,
up.*,
q.title,
sq.title as sqtitle,
q.question,
sq.question as sqquestion
from lime_survey_url_parameters up
left join lime_questions q on q.qid=up.targetqid
left join lime_questions sq on q.qid=up.targetsqid
where up.sid='123456';

Each such query takes about 1 second in our mySQL database.

The strange thing about this query is that there are two joins to the same table "lime_questions" for "lime_survey_url_parameters", and that the column "targetsqid" in this table always seems to contain the value NULL.
If this is true, the second join can be omitted, which speeds up the query considerably.

I would be happy to hear your opinions on this.

Roland

Thanks for the answer!

The SELECT-command needs a lot of time. Maybe it is simply because the table "lime_questions" in our case contains more than 185000 records.
The table field "lime_questions.qid" is indexed, but "lime_survey_url_parameters.targetqid" and "lime_survey_url_parameters.targetsqid" is not.
The use of a query cache in mySQl speeds up the said query considerably (after it has been stored in the cache).
I still believe that the query can be rewritten to make it faster.

Kind regards
Roland Baier

I am interested in this topic as well. Roland, have you seen any change in most recent versions of LimeSurvey since your last post?

I also manage a very large installation of LimeSurvey 3.x and we usually start to see performance degradation (especially in the administration interface) when passing the ~2500 surveys mark in the system, which has forced us to do periodic cleanings and implement policies about how long can a survey be in the system, which is not ideal at all. We currently have 3476 surveys and 215995 records in our lime_questions table. LimeSurvey is very popular and useful in our institution, and I am a firm advocate for it, but these scalability issues are worrisome as more and more users are adopting the software.

We have mysql cache enabled as well. However, I have noticed that, although the hit rate amongst cacheable queries is quite decent (~85%), the hit rate amongst all queries is not good (~40%). Some tuning tools even recommend to turn off mysql caching for this reason. How has been your experience in this regard?

@c_schmitz : I will be more than happy to provide details about our installation that could be useful to help in diagnosing or testing on "big" systems like ours.

Best,

Ginet

segui wrote:

We currently have 3476 surveys and 215995 records in our lime_questions table. LimeSurvey is very popular and useful in our institution, and I am a firm advocate for it, but these scalability issues are worrisome as more and more users are adopting the software.
 

You have one of the bigger LimeSurvey instances mentioned in this forum. What is the setup in terms of hardware and software?
Would be interesting to see how a copy of the database would perform with LS4 (not ready for production).
 

Thanks everybody! Yes, our users and surveys numbers have grown significantly since our adoption of LimeSurvey back in 2012.

@c_schmitz : The idea of providing an anonymized version of our database sounds great, but unfortunately we have surveys in our LimeSurvey system that are associated with ongoing projects and studies that can't be disclosed yet.

These are some of our specs in terms of hardware and software (can provide more details if needed):
- VM running RHEL Server 7.9.
- CPU: 8 cores.
- RAM: 16GB.
- Disk: 200GB (writing to SSDs on the backend storage).

- LS version: 3.25.16+210302, db version 365.
- DB Engine: mariadb 10.5.8 running on the same server.
- DB options: InnoDB, mariadb Transparent Database Encryption, 100MB query cache enabled.

The server is dedicated exclusively to this LimeSurvey installation (no other applications or databases are hosted here).

Would be interesting to see how a copy of the database would perform with LS4 (not ready for production).

@jelo: We have successfully upgraded to LS4 with a copy of our database and uploads, for testing purposes. Unfortunately, we can't use it in production until it's production-ready. I still noticed slowness on that version, but haven't done extensive testing/diagnosing yet.

Here is my surveys counter :-D

 


Thanks again to all,

Ginet