Drupal-level performance enhancements

[jim]

Per TTech discussion, some easy/quick things we can do to improve the site's performance... In rough order of ease technically and politically:

2. Trawl SQL logs for slow queries, find out what's causing them and eradicate the cause -- tough job but potentially huge returns.
3. Add flood control/DoS blocking and other temporary blocks at the server level if an IP address or crawler bot is taking the piss. A job for Chris.
4. Do config/performance enhancement ideas from DB Tuner (was in mysqltuner.log, JK to run again around xmas)
5. Disable Piwik on LIVE and use Google Analytics instead OR move Piwik to another server. Chris is opposed to this but server resources are at a premium unfortunately -- See #371
6. Massively shrink site CSS -- Small performance increase for 'normal' users, BIG gains for older computers and mobile devices - As Laura points out, better than lots of images, but it still needs to re-use CSS rules/selectors where possible and throw out legacy code.
7. Force HTTP only for non-logged in users (explained above) -- BIG boost, quick settings change, though might need Chris to alter htaccess/varnish setup.
8. Enable Throttle module to auto-disable modules during heavy loads -- Throttle gets a bad press and can cause issues.
9. Go on a module cull -- This is hard and slow, and will result in a loss of functionality. But some modules could be dropped if there's a will.
10. Switch to NGINX -- Chris thinks this is a lot of work, see #357

[jim]

Added ticket, edited for clarity and order... Plus:

1. As of 23:20 on 4 Dec 2011 the site uses Syslog instead of Database log -- should save many extra requests to MySQL. Will wait a week then uninstall that module to significantly shrink the database.
2. downloaded slow queries log from /var/log/mysql/mysql-slow.log to analyse soon
3. in settings.php changed session.cookie_lifetime (session lifetime) to 3 days from 23, made session.gc_maxlifetime (session garbage collection) daily

[jim]

1) Have now emptied Batch and Watchdog tables, so 1) is completely done.
2) A quick glance through the slow queries log shows plenty of lines of enquiry:

• Shows Piwik as having many slow queries (some over 30s) when processing a day's entries.
• Tagadelic module (tag cloud generator) is slow, but this is cached for 6 hours so only occasional.
• Site search are the cause of many more... As per my emails regarding the database size, we can shrink the search indexes by not indexing all content types...
• Sharing engine gets a few entries too, but only for caching... Have log in and truncate the cache tables, plus do 1) too.

[jim]

5) Have now added more caching to popular/ubiquitous pages, blocks and views.

[jim]

• I've added (outside Git since it's since been removed) the DB Tuner module.
• I've told it to add any missing recommended indexes.
• It's got LOTS of hints for MySQL setup/config here: ​https://www.transitionnetwork.org/admin/settings/dbtuner/mysqltuner -- its output is attached as mysqltuner.log
• Also did the above on workspaces and sharing engine, removing DB Tuner after I added the indexes.
• ensured sharing engine and workspaces have same session duration, plus improved caching in SE.

See attached mysqltuner.log for handy mysql config tips!

(also updated ticket details, reordering striking and adding where needed.

[jim]

Output from MySQL Tuner in DB Tuner module

[chris]

OK, these are the suggstions from mysqltuner.txt and what I have changed:

41 Increase query_cache_size -- there are too many low memory prunes.

Changed:

#query_cache_limit       = 16M
#query_cache_size        = 16M
query_cache_limit       = 32M
query_cache_size        = 32M

81 tmp_table_size-max_heap_table_size: 0
82 tmp_table_size: 16.0 Mb
83 max_heap_table_size: 16.0 Mb
85 Too many temporary tables are being written to disk. Increase max_heap_table_size and tmp_table_size.

Added:

tmp_table_size                        = 32M
max_heap_table_size                   = 32M

93 Too many intermediate temporary tables are being created; consider increasing sort_buffer_size (sorting), read_rnd_buffer_size (random read buffer, ie, post-sort), read_buffer_size (sequential scan).

Changed:

#sort_buffer_size        = 1M
sort_buffer_size        = 6M
#read_buffer_size        = 1M
read_buffer_size        = 6M
#read_rnd_buffer_size    = 32M
read_rnd_buffer_size    = 64M

102 MyISAM key buffer (index cache) % used is low. You may need to decrease the size of key_buffer_size, re-examine your tables to see if indexes have been removed, or examine queries and expectations about what indexes are being used.

Changed:

#key_buffer              = 128M
key_buffer              = 32M

119 The rate of opening tables is high, increase table_open_cache to avoid this.

Added:

table_open_cache        = 4096

151 InnoDB log file size is not an appropriate size, in relation to the InnoDB buffer pool. Consider changing either\ninnodb_log_file_size or innodb_buffer_pool_size

Added:

innodb_log_file_size                  = 8MB
innodb_buffer_pool_size               = 32M

The database server has been restarted and I'll keep an eye on the memory usage, MySQL was using around 240M of RAM, ​https://kiwi.transitionnetwork.org/munin/webarch.net/quince.webarch.net-multimemory.html

[jim]

Excellent work Chris. This might need revisiting as you say for memory constraints, but hopefully the above will help loads...

Updating description. Noting item 9) is now at #371, too.

[chris]

I have added a line to the wiki:NewLiveServer#logcheck config to suppress the emailing of the drupal entries in syslog out -- it was sending a 250+ line email of the new drupal entries from /var/log/syslog to me every hour.

[jim]

Is it possible to get the Drupal reports made daily and put in a directory rather than emailed? If it was errors and above (not notices and warnings) that'd be really handy.

But as you say there's always grep if not...

[chris]

The log is available on quince at /var/log/syslog and it's rotated once a day and the old files are at syslog.1 syslog.2.gz syslog.3.gz syslog.4.gz syslog.5.gz syslog.6.gz syslog.7.gz.

Yesterday's syslog.1 is 8706 lines and 1.8M of uncompressed text.

The be honest I don't think anyone of us will ever have the time to read these logs, but having them there to grep for things if a problem arises is handy.

[jim]

(EDIT: keeps mangling my numbered lists!) Moving to maintenance...

2) underway
3) needs Chris to tweak the firewall settings, or replace if it can't handle basic flood control
7) Should be revisited in a few weeks -- I'll add DB Tuner and test again at some point.
8) in discussion at #371 and #292
9) Laura will do over time
10) This is a test we can do IF performance issues arise again
11, 12 and 13 to be avoided unless absolutely necessary

[jim]

Updating and cleaning up for clarity...

DONE & REMOVED:

1. Disable Drupal DB Logging, switching to SysLog? instead. -- Small but consistent boost, especially when under heavy load... We'd need a dev-friendly way of viewing the logs though. Very quick change.
2. Lower session timeout to 1 day from whatever it is now -- PHP settings or settings.php tweak, quick-ish
3. Enable more caching for panels/views -- this job is simple but long-ish... Many are done, some left to do.
4. Add indexes to tables where needed.~

Marking 7, 8 (see #371), 9 (for Laura), 13 (see #357) as done or underway elsewhere. 11 is a bad idea.

[jim]

I reckon this is done and any last tweaks are best re-assessed after the server move #478... But the new server kills off (3) and (7), above.