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

Hello,


The second part of the query is unfortunately necessary to get the details for any subquestions in case you want to save URL params in subquestions

I don't really understand though, why it would be slow for you as it just compares two indexed IDs. Either you have a huge database and/or you have a very bad SQL server machine.

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

The best would be if we can get your database and test for ourselves. I was thinking of maybe writing an anonymization console script that will drop all important personal data from a LimeSurvey database (like responses, participants and users), so users can send something to us easily for debugging purposes.

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).
 

wrote:

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?

 

he he …
 

Surveys

On IIS with MSSQL elsewhere.
Big server, but clearly : a linux server is better.
 

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

 


Thanks again to all,

Ginet