Increasing SPIP performance on dedicated server

If you often see "Warning: a technical problem (SQL server) prevents access to this part of the site" this means you have a matters with performance of your site. If you use virtual hosting you could contact support, but if you manage the dedicated server look for my expirience.

My experience is based on supporting and developing SPIP website with Magento e-commerce and Mailman email listes with over 30 000 subscribers.
Number of visitors daily exceed 50 000 and daily traffic is over 20GB.

The productivity of SPIP was terrible so I’ve done some additional steps to improve work of the webserver and SPIP.

1. Tuning Mysql. The default MySQL config file was totally unsuitable for production server, so I’d done some tweaks. Here my config.

[mysqld]
log_slow_queries=slow.log
long_query_time=50

thread_cache_size = 10

query_cache_size = 128M
query_cache_limit=2M
query_cache_min_res_unit = 2048

table_cache = 3000
tmp_table_size=1024M
max_tmp_tables = 256
max_heap_table_size=256M

set-variable = max_connections=300

key_buffer_size=64M

sort_buffer_size=2M

set-variable=local-infile=0

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql

Options long_query_time, log_slow_queries are set for debugging the slow queries and is useful when you searching for most "heavy" visitors actions.

Dont forget to reload mysqld after changing the config file.

2. The Spip search (recherche). The new config gave some performance improvement, but there were "peak load" every 2-5 minutes that make website almost unaccessible. Investigation of the slow.log shows that spaming bots submited their "viagra cialis" queries (up to 2k size) into the search form. There more than 50 000 articles in database, so one smap request turn off the website for 1-2 minutes.

I’m changed the search from SPIP to google search. You could protect the SPIP search form with captca or any other way you like.

3. Cutting the size of SPIP cache from 1Gb up to 300 Mb. This is my only point of view, but I think that managing a big cache give us no productivuty boost. That’s why I prefer a moderate cache size.

4. Placing the cache directories to RAM filesystem. As you know the fastest available media is RAM, not HDD. Its a good idea to store all your /local/ and /tmp/cache/ directories on RAM filesystems. But mention that all cached files and resized images would be recalculated every time you reboot the server.

5. And of course use ?var_mode=debug and ?var_profile=1 to analyze the most heavy queries in your templates.

If you know any other tweaks to increase the SPIP performance please contact me via email form.