The mailing list for listmasters using Sympa

[Alvar Freude <address@concealed>]

Hi all.

Am 04.05.2020 um 11:33 schrieb Olivier Salaün <address@concealed>:

Patch #1: prevent database calls from get_current_admins()

Response time gain: -62%

We noticed that there were 50.846 calls to Sympa::Database::do_prepared_query(), most of them from get_current_admins(). 

Beside this, if someone want to dig deeper into analysis of database performance I suggest to add full logging on the database server and view the log files (often a first look with tail -f shows a lot of noticeable queries).

I made a short test with a list admin page (sympa/admin/listname) of a small list and found some interesting queries, e.g.:

   SELECT count(*) 

     FROM subscriber_table 

    WHERE list_subscriber = $1 AND robot_subscriber = $2 AND user_subscriber = $3 

This query is called 6 times, every time with the exact same parameters. Each call is short, but when database server is on another host, this may cause a lot of latency in summary.

Another query is:

   SELECT date_epoch_admin AS "date", user_admin […] […]

     FROM admin_table 

    WHERE list_admin = $1 AND robot_admin = $2 

 ORDER BY user_admin

This is called 10 times with the same parameters in one page request.

Even when the queries are not exactly the same, it is often possible to combine some queries into one, which may speed up everything a lot.

You may use a tool like pgBadger to analyze PostgreSQL full log, https://pgbadger.darold.net and https://github.com/darold/pgbadger.

I use the following logging configuration:

log_destination = 'stderr'
logging_collector = on
log_directory = '../pg_log'
log_filename = 'postgresql-%Y-%m-%d.log'

log_min_duration_statement = 0
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d '

log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0
log_autovacuum_min_duration = 0

lc_messages='C'

When analyzing the logs with pgbadger, you may look in "Time consuming queries" and "Most frequent queries". Please have in mind, that pgBadger does not show network latency, which may add a lot of time when the database does not on the localhost and there are a lot of short running queries.

Ciao

  Alvar

-- 

Alvar C.H. Freude | https://alvar.a-blast.org | address@concealed

https://blog.alvar-freude.de/ 

https://www.wen-waehlen.de/

Attachment: signature.asc
Description: Message signed with OpenPGP