Optimization of web GUI / adding an index on prev_id_session DB field

[salaun-urennes1]

While preparing our ugrade to Sympa 6.2.36 we noticed bad performances on every web request. I figured out that adding an index on prev_id_session DB field saves 1.2sec on every web request.

Version

6.2.36

Installation method

source package

While running NYTProf profiler I detected too much time spent on WWW::Session :

I then found out the DBI requests where the culprits :

On our server the session_table has 464712 entries ; the SQL query to load a session takes 1.4sec.

I then added on index on prev_id_session DB field ; the same SQL query now takes 0.2sec

=> I suggest you add an index on prev_id_session DB field

[racke]

It sounds reasonable to me to add an index to a field used very often in a WHERE condition.

[salaun-urennes1]

I went further in my tests and it turns out that, even with this index added, web access are still between 2.5sec and 4.2sec (just to access a list homepage).

I tried to empty the session_table and waooooh : access to the same web page takes from 0.4sec to 0.7sec :-)

=> I will do some further investigations to find out why the purge_session_table_task (default value for session_table_ttl is 2d) is not doing its job properly...

[racke]

Yes please do check that task - this should be really working reliable 🙄

[salaun-urennes1]

That task did not run since 2017 dec 15th :-(

$ ls /usr/local/sympa/spool/task/*session*
/usr/local/sympa/spool/task/1513302372.ACTION.purge_session_table._global

I go on my investigations...

[salaun-urennes1]

I found out why the purge_session_table_task task was not run on our 6.2.16 instance : task_manager process would die with the following error: Undefined subroutine &Sympa::send_notfy_to_listmaster called at /usr/local/sympa-6.2.16/bin/task_manager.pl line 1925

That bug has been fixed meanwhile.

But it would still make sense to add an index on prev_id_session DB field...

[racke]

I agree with you on having an index on prev_id_session. Do you want to produce a PR for this change?

[salaun-urennes1]

I pulled a request: #460