The mailing list for listmasters using Sympa

[Cretu Adrian <address@concealed>]

Thanks for the hints Steve, can you tell me how many lists you have on your server? My server hosts around 2200

On Thu, Sep 12, 2013 at 1:18 AM, Steve Shipway <address@concealed> wrote:

Check your table indexes!  Make sure conf_table has a unique primary index by robot_conf and label_conf.  Also, check your MySQL has enough memory.

 

In our case, I added a couple more new indexes (sorry, but I can’t remember which ones offhand) where they were needed to improve performance; I also tuned our MySQL to give a lot more memory to in-memory index caching, and this massively improved performance.  Since the MySQL instance is dedicated to Sympa I could safely do this…  I also added a couple of indexes on the list_table and installed the riseup patch as we have tens of thousands of lists on our server and the web frontend ran very slowly without this.

 

For comparison, this is how we have tuned MySQL – note that YMMV!  This is a 12GB host with 4 CPU and a LOT of lists.

 

query_cache_type=1

query_cache_limit=1M

query_cache_size=48M

# max_connections=500

# Reduced to 200 as memory will not be enough for 500 connections.

# 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

# Default set to 100.

max_connections=200

interactive_timeout=100

# Reduced wait_timeout to prevent idle clients holding connections.

wait_timeout=30

connect_timeout=10

#table_cache=256

#table_cache=1024

# Checked opened tables and adjusted accordingly after running for a while.

table_cache=768

#tmp_table_size=32M by default

tmp_table_size=64M

# max_heap_table_size must be the same as tmp_table_size

max_heap_table_size=64M

thread_cache=128

# Maybe reduce it to 32 to prevent memory hogging. Also, see notes below.

#thread_cache=32

key_buffer=258M

# Commented out the buffer sizes and keeping the default.

# sort_buffer_size=2M by default.

sort_buffer_size=2M

# read_buffer_size=128K by default.

read_buffer_size=2M

join_buffer_size=2M

# 1Mb of read_rnd_buffer_size for 1GB RAM -- see notes below.

# read_rnd_buffer_size=256K by default.

read_rnd_buffer_size=1M

# myisam_sort_buffer_size used for ALTER, OPTIMIZE, REPAIR TABLE commands.

# myisam_sort_buffer_size=8M by default.

myisam_sort_buffer_size=64M

# thread_concurrency = 2 * (no. of CPU)

thread_concurrency=8

# log slow queries is a must. Many queries that take more than 2 seconds.

# If so, then your tables need enhancement.

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

long_query_time=2

# Required to avoid problems with BIG lists: ESSENTIAL for Sympa

max_allowed_packet = 50000000

 

 

Steve

 

---

Steve Shipway

ITS Unix Services Design Lead

University of Auckland, New Zealand

Floor 1, 58 Symonds Street, Auckland

Phone: +64 (0)9 3737599 ext 86487

DDI: +64 (0)9 923 6487

Mobile: +64 (0)21 753 189

Email: address@concealed

P Please consider the environment before printing this e-mail : 打印本邮件，将减少一棵树存活的机会

 

From: address@concealed [mailto:address@concealed] On Behalf Of Cretu Adrian
Sent: Thursday, 12 September 2013 3:29 a.m.
To: address@concealed
Subject: [sympa-users] slowness problem after upgrade from 5.2.1

 

 

  Hi guys

  I had to upgrade an old 5.2.1 sympa to the last debian stable one that is 6.1.11.

  The steps were:

  - dump the old postgress database, create a new mysql database and import the data

  - update the configuration files of the new sympa

  - move the archives to the new server

  - run the upgrade command

 After all this I could login on web on the new sympa and do all sorts of things but everything was slow, even sympa --help or sympa -v is slow.  After enabling mysql logs and a strace on the pid it seems that each time I use the sympa cli or api it does a massive number of mysql selects.

ex:
1496 Query      SELECT value_conf AS value FROM conf_table WHERE (robot_conf ='lists.mydoamin.com' AND label_conf ='color_2')                
1496 Query      SELECT value_conf AS value FROM conf_table WHERE (robot_conf ='lists.mydomain.com' AND label_conf ='color_2')                
1496 Query      SELECT value_conf AS value FROM conf_table WHERE (robot_conf ='lists.mydomain.com' AND label_conf ='color_15') 

root@sympa2:~# time sympa -v
Sympa version is 6.1.11

real    0m26.970s
user    0m7.980s
sys    0m1.980s
root@sympa2:~#

Any hints are welcome , thanks.