The mailing list for listmasters using Sympa

["McLaren, Terrence M" <address@concealed>]

Steve R. – After working with Steve S. on MySQL tuning, I created the attached config with setting overviews and  references.

 

Hope it helps,

 

-Terry McLaren

  University of Illinois

 

From: address@concealed [mailto:address@concealed] On Behalf Of Steve Shipway
Sent: Wednesday, February 10, 2016 3:01 PM
To: Steve Rich <address@concealed>; address@concealed
Subject: RE: [sympa-users] List of lists, close list, soap api timeouts

 

My first guess would be a database issue, particularly if you have a large number of lists.

 

If you have a missing index, then this could cause the problem.   Try switching on query logging (eg, slow queries if using MySQL) so that you can find out which are the queries actually being sent and taking a long time.  Then you can get the DB to explain which indices it is using, to see if you have any  missing indices.

 

From your list of affected functions, I would guess the list subscribers table may be missing an index (or have some index issue) but that’s just a guess.

 

Another thing we did (we have about 40,000 lists in our server now) is tuning of the MySQL database to ensure more indices held in memory, which had a big speedup on the things like you state.  Also, some tweaking of the regexp search code on 6.1 is absolutely necessary as the basic code does not make full use of RLIKE and similar which are available with MySQL.    I think that 6.2 does incorporate most of this, though.

 

Steve

 

Example Options for mod_fcgid:

<IfModule mod_fcgid.c>

    IPCCommTimeout 900

    IPCCommTimeout 900

    BusyTimeout 900

# Allow lots of clients

    MaxProcessCount 20

# Get around error log issue

    MaxRequestLen 10000000

</IfModule>

 

Example Options for MySQL (12GB memory, 4CPU host)

query_cache_type=1

query_cache_limit=1M

query_cache_size=48M

# memory=key_buffer+(sort_buffer_size+read_buffer_size)*max_connections

# which is now: 64 + (1 + 1) * 200 = 464 MB

# max_connections = approx. MaxClients setting in httpd.conf file

max_connections=200

interactive_timeout=100

wait_timeout=180

connect_timeout=10

table_cache=768

tmp_table_size=64M

max_heap_table_size=64M

thread_cache=128

key_buffer=258M

read_buffer_size=2M

join_buffer_size=2M

read_rnd_buffer_size=1M

myisam_sort_buffer_size=64M

# thread_concurrency = 2 * (no. of CPU)

thread_concurrency=8

log_slow_queries=/var/log/mysqld.slow.log

long_query_time=2

# Required to avoid problems with BIG lists

max_allowed_packet = 50000000

 

Steve Shipway

T: +64 9 3737 599 ext 86487

E: address@concealed

(

 

Attachment: .my.cnf-MySQL config optimized for Sympa.pdf
Description: .my.cnf-MySQL config optimized for Sympa.pdf