Transition Technology: Ticket #369: Drupal-level performance enhancements

hours, totalhours, description changed

jim — Sun, 04 Dec 2011 23:38:49 GMT

hours changed from 0.0 to 0.5
totalhours changed from 0.0 to 0.5
description modified (diff)

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

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.
downloaded slow queries log from /var/log/mysql/mysql-slow.log to analyse soon
in settings.php changed session.cookie_lifetime (session lifetime) to 3 days from 23, made session.gc_maxlifetime (session garbage collection) daily

cc, hours, totalhours changed

jim — Mon, 05 Dec 2011 14:14:06 GMT

cc ed added
hours changed from 0.0 to 0.25
totalhours changed from 0.5 to 0.75

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.

hours, totalhours changed

jim — Mon, 05 Dec 2011 14:21:12 GMT

hours changed from 0.0 to 0.25
totalhours changed from 0.75 to 1.0

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

hours, totalhours, description changed

jim — Mon, 05 Dec 2011 17:48:27 GMT

hours changed from 0.0 to 0.75
totalhours changed from 1.0 to 1.75
description modified (diff)

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.

attachment set

jim — Mon, 05 Dec 2011 17:49:03 GMT

attachment set to mysqltuner.txt

Output from MySQL Tuner in DB Tuner module

hours, totalhours changed

chris — Mon, 05 Dec 2011 21:15:58 GMT

hours changed from 0.0 to 0.5
totalhours changed from 1.75 to 2.25

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

hours, totalhours, description changed

jim — Mon, 05 Dec 2011 22:01:53 GMT

hours changed from 0.0 to 0.05
totalhours changed from 2.25 to 2.3
description modified (diff)

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.

hours, totalhours changed

chris — Tue, 06 Dec 2011 11:08:21 GMT

hours changed from 0.0 to 0.23
totalhours changed from 2.3 to 2.53

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 — Tue, 06 Dec 2011 12:11:49 GMT

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 — Tue, 06 Dec 2011 12:29:22 GMT

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.

type, description changed

jim — Wed, 14 Dec 2011 13:09:12 GMT

type changed from defect to maintenance
description modified (diff)

(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

hours, totalhours, description changed

jim — Thu, 09 Feb 2012 19:07:43 GMT

hours changed from 0.0 to 0.15
totalhours changed from 2.53 to 2.68
description modified (diff)

Updating and cleaning up for clarity...

DONE & REMOVED:

~~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.~~
~~Lower session timeout to 1 day from whatever it is now -- PHP settings or settings.php tweak, quick-ish~~
~~Enable more caching for panels/views -- this job is simple but long-ish... Many are done, some left to do.~~
~~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.

status, description changed; resolution set

jim — Mon, 04 Feb 2013 19:21:54 GMT

status changed from new to closed
resolution set to fixed
description modified (diff)

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.