High Performance Drupal meetup in Mar Vista on August 10, 2011

We encourage users to post events happening in the community to the community events group on https://www.drupal.org.
christefano's picture
Start: 
2011-08-10 17:00 - 20:00 America/Los_Angeles
Organizers: 
Event type: 
User group meeting
Video: http://blip.tv/ladrupal/episode/5466668  

Caffeinated Drupal at Drupal Cafe Interested in Pressflow, APC, Varnish and memcache? Want to jump from shared or "grid" hosting to VPS or dedicated hosting but aren't sure what to do? Do you just want to get more out of what you currently have and take your Drupal site to the next level?

Meet with friends, have coffee and talk about Drupal performance on the Westside at the Venice Grind between 5-8pm on Thursday, August 10, 2011. Afterward, we might go for dinner somewhere nearby or a stiff drink across the street at the Good Hurt. Last time we went for noodle bowl at Mistuwa. You're invited!

To get last-minute updates, sign up by clicking the Signup button below or stay tuned to the comments on this page (or both!).

When: 5pm to 8pm on Wednesday, August 10, 2011
Where: Venice Grind at 12224 Venice Blvd, Los Angeles, CA 90066

How to prepare for this meetup

Just bring your laptop, a copy of Pro Drupal Development or whatever else you need. We're meeting at the back patio, so bring a jacket just in case it's cold.

There's plenty of metered parking on Venice Blvd. as well as some free parking in the back. Parking on the street is free after 6pm, so be sure to feed your meter if you come at 5pm.

If for some reason you need a lot of bandwidth, please note that the free wireless at the Venice Grind is limited to 1Mb per client, so bring your MiFi router, a phone you can tether with or your credit card to get a 5Mb connection for the day.

Topics

Google MapThis time we'll be focusing on High Performance Drupal. If you're interested in things like Pressflow, APC, Varnish, Pantheon, and memcache, or if you already know about multi-tier infrastructure and want to talk about things like HipHop, CloudFlare, Google Page Speed, Virtualmin, AWS, RightScale, Chef and DevStructure, this is the place for you.

Location

We're meeting in the back patio at the Venice Grind café on the corner of Venice Blvd. and S. Centinela Ave. Their phone number is 310-397-2227.

If it rains or there are too many people and we need more space, we will rendezvous at Coffee Connection, which is right around the corner. We met there once before for a DrupalCamp LA 2009 planning meeting. Stay tuned by clicking the Signup button below or check comments on this page (or both!) before the meetup.

Comments

One of the things I'm looking

christefano's picture

One of the things I'm looking forward to talking about today is Google's Page Speed trio:

  • A Page Speed browser extension that's similar to YSlow
  • An Apache mod_pagespeed module that rewrites the output of web pages to make them lighter
  • A CDN-like service that's also called "Google Page Speed" that uses Google's infrastructure to serve your websites

I like the browser extension and mod_pagespeed module but the CDN-like service is in private testing and the jury's still out. I installed the mod_pagespeed Apache module and saw pages on some sites load up to 40% faster. This was on sites that already have aggregated CSS and JS files and mod_deflate enabled on the server.

You recording/screencap'ing

Techivist's picture

You recording/screencap'ing it (video would be great but just audio would still be helpful, if possible)? Sure would be nice as those topics look super interesting! Will be staying in to work on the SGVLUG website tonight. :D

I've dabbled in Pantheon & REALLY like it. While in there, I was like "wtf is Jenkins? never heard of it..." but realized it was Hudson which had renamed itself.

Miguel Hernandez - www.migshouse.com
Founder & CEO - The OpenMindz Group
Writer- Linux Journal & TechZulu

Are you guys recording the

jmolivas's picture

Are you guys recording the sessions (audio or video) to shared with the rest of us who can not attend drive for 3 1/2 hours to a DrupalCamp it's fine, but kinda hard for a meetup

--
http://jmolivas.com/
@jmolivas

Yes

vmi's picture

We did record a 25 min. presentation on installing Virtualmin/Webadmin, drush, drupal. I'm sure a link will follow once it's up.

Also, it's really great to see people not just get answers to their questions but to have them answered with a presentation! As I've seen this happen more than once I just want to thank all the talented people who are generous with their knowledge and time that make the LA Drupal community so beneficial for others.

Any thoughts about smushit

jmolivas's picture

Any thoughts about smushit (yahoo), related to Drupal implementation module or patch in order to take advantage of smush.it service

--
http://jmolivas.com/
@jmolivas

That already

--


Dave Hansen-Lange
Director of Technical Strategy, Advomatic.com
Pronouns: he/him/his

mod_pagespeed is pretty darn good

BTMash's picture

I'd already set up most of my server to be with the same kind of settings that mod_pagespeed has but it certainly makes the whole thing a LOT easier (I tried it on a server that wasn't and saw nearly similar speed bumps). And to see the speed bump so easily is always pleasing :D

Wish I could make it out, but

BTMash's picture

Wish I could make it out, but I have a prior commitment that I was already booked off for. Hopefully we'll have one of these again in the future and I can talk about the kind of things I do and learn from the things you all do :)

MySQL tuning tools to use

bvirtual's picture

Three tools I got a bump in MySQL performance of a reduction in the time it takes for a query to return it's result set by 30% or so. I still have more config changes to make, expect to cut another 30% from the query response time.

Use BOTH of the below tuning tools, as they report different aspects.

http://www.mysqltuner.com - perl script download
- Makes recommendations
- Indicates problem areas
- Gives you an OK when the area is fine.

tuning-primer.sh - shell script
- Reports lots of MySQL status values important for performance evaluation
- Makes suggestions of what to change to enhance performance


Others I've tried that come with MySQL:

mysqladmin status extended-status
mysqladmin proc
mysqlcheck
myisamchk


Run 'OPTIMIZE TABLE table_name' for all tables to defragment them.

I found two shell scripts that do all your database tables for you, and list out percentage frag, or how bad the problem is. I'm pasting the two I found below. I forgot where I downloaded them from, rather I thought it faster to paste them than search web browser history.

#!/bin/sh

echo -n "MySQL username: " ; read username
echo -n "MySQL password: " ; stty -echo ; read password ; stty echo ; echo

mysql -u $username -p"$password" -NBe "SHOW DATABASES;" | grep -v 'lost+found' | while read database ; do
mysql -u $username -p"$password" -NBe "SHOW TABLE STATUS;" $database | while read name engine version rowformat rows avgrowlength datalength maxdatalength indexlength datafree autoincrement createtime updatetime checktime collation checksum createoptions comment ; do
if [ "$datafree" -gt 0 ] ; then
fragmentation=$(($datafree * 100 / $datalength))
echo "$database.$name is $fragmentation% fragmented."
mysql -u "$username" -p"$password" -NBe "OPTIMIZE TABLE $name;" "$database"
fi
done
done

#!/bin/bash
#
# 2008-01-24 Written by Sascha Curth (mysql@sascha-curth.de)
#
# 2008-02-05 Simon J Mudd <sjmudd@pobox.com>
#            Modification to allow defaults-file to be used. This avoids
#            passwords being visible on the command line.
#
# 2008-02-05 Sascha Curth (mysql@sascha-curth.de)
#            Added: socket option
#            Bugfix: "user" option was not used in script
#
########

while getopts 'D:d:u:p:o:s:' OPTION ; do
  case $OPTION in
    D)   defaults_file="--defaults-file=$OPTARG";;
    d)   DB=$OPTARG;;
    u)   user="-u $OPTARG";;
    p)   pwd="-p$OPTARG";;
    o)   output="$OPTARG";;
    s)   socket="--socket=$OPTARG";;
  esac
done

mysql="mysql $defaults_file $user $pwd $socket"
if [ -z "$DB" ]
then
        DB=$mysql -N -e &quot;show databases&quot;
fi
for db in $DB
do
        for table in $mysql -N $db -e &quot;show tables&quot;
        do
                ENGINE=$($mysql $db -e "show table status like '$table'\G" |  awk '/Engine/ {print $2}')
                if [ "$ENGINE" == "MyISAM" ]
                then
                        SIZE=$($mysql $db -e "show table status like '$table'\G" | awk '/Data_length/ {print $2}')
                        FREE=$($mysql $db -e "show table status like '$table'\G"  | awk '/Data_free/ {print $2}')
                        ROWS=$($mysql $db -e "show table status like '$table'\G" | awk '/Rows/ {print $2}')
                        if [ $FREE -gt 0 ] && [ $SIZE -gt 0 ]
                        then
                                FRAGMENTATION=$(printf '%6.2f%%' $(echo "scale=4; $FREE/($SIZE/100)"|bc))
                                if [ "$output" == "" ]
                                then
                                        echo "$FRAGMENTATION $db.$table"
                                elif [ "_$output" == "_pain" ]
                                then
                                        WEIGHT=$(printf '%.0f' $(echo "scale=4; $FRAGMENTATION * $ROWS"|bc))
                                        echo "$WEIGHT $db.$table"
                                fi
                        fi
                fi
        done
done

Peter

LA's Open Source User Group Advocate - Volunteer at DrupalCamp LA and SCALE

Drupal's MySQL "open files" limit needs increasing

bvirtual's picture

After 18 hours I finally got mysqltuner 1.2.0 to have all OKs and no recommendations.

tuning-primer.sh still has some issues it wants me to tweak, but I need to wait 24 hours or more for it's recommendations to change, like it did while I slept last night.

I see I will have to run MySQL's OPTIMIZE defrag table weekly.

I have so many Drupal websites, I've lost count. I had to bump "open files" limits in both MySQL and the Linux OS. Now, MySQL can cache all my tables' schema and rows.

I changed a lot more in my.cnf, over the last 18 hours... ugh. More on that later.

BTW, IMPORTANT: Read the relevant manual pages when editing values, to be sure you do not crash your server (easy to do). You MUST read and learn the issues when bumping up values!!!


Here are my Hints:

To get MySQL to restart faster (in unix) try

renice -n -15 restartcommand

so to not impact existing db fetches with 'client connection lost' error web pages to the end user.

Do not forget to a final 'renice -n -2 restartcommand' to avoid impacting the OS and other processes.

Having MySQL run at a slightly higher priority than Apache(Web Server) means queries are completed faster, and your Drupal pages will serve faster.


Added to /etc/security/limits.conf, so rebooting the OS will have the new "open files" limits for MySQL

mysql hard nofile ##### - see below
mysql soft nofile ##### - the above hard limit minus 1% to see if I get a warning email

RTFM with "man limits.conf" to know the syntax to use. Do not trust my stuff!!! You've been warned.


/etc/mysql/my.cnf added:

query_cache_type = 1 - turn on query_cache_size value
query_cache_size = 128M - default is zero, or off.
query_cache_limit = 96M - I needed a larger value for CGI I wrote for a catalog search.

open_files_limit = ##### - (changing default of 1024)
table_definition_cache = ##### * 0.35
table_open_cache = ##### * 0.2
table_cache = ##### * 0.35

where ##### is

100 mysql overhead + number of drupal sites * ( 60 drupal tables + 10 overhead + 10 just in case ) + number of future drupal sites * 80 + extra you need

where I may or may not have been conservative enough. Why? CCK and modules add more tables per site.


IMPORTANT WARNING:

KEEP IN MIND, I have 32 gig of RAM, with a soft limit of 4 gig, so I bumped my values way high. Meaning, I do not mind having MySQL reserve, yes reserve, 75% of the RAM (3 gig). However, my current my.cnf values report in mysqltuner as reserving only 6%. I'm happy.

If your machine does not have similar RAM, well, be aware using all your OS RAM may CRASH your OS. Make sure the values in my.cnf will NOT exceed available RAM, and leave some free for other processes.

Peter

LA's Open Source User Group Advocate - Volunteer at DrupalCamp LA and SCALE

bvirtual's picture

Oh, as you likely will not reboot the OS to get the new /etc/security/limits.conf edits, so you will need to issue this command:

ulimit -n #####

that dynamically increases the open file limits.

ulimit -a

will report many kernel limits.

RTFM with 'man ulimit' as this command increases the open files limit for ALL processes, not just MySQL. And it's value may not last through a reboot. Thus, upon rebooting, my.cnf values might cause the OS to crash. Meaning the computer is hosed, until booted in single user mode, and the limit issue fixed. Meaning edit /etc/security/limits.d

BTW, On other OSes YMMV regarding pathnames and commands. Windows has similar limits.

Good luck.

Pete

Peter

LA's Open Source User Group Advocate - Volunteer at DrupalCamp LA and SCALE

Tuning is definitely an ongoing thing

BTMash's picture

Like you said, db table sizes will change over time, your tuning will tend to change as well. I would recommend take a close read through http://mysqlperformanceblog.com. The guys that work there have been core mysql contributors in the past (and later created a fork from mysql called PerconaDB). Its what I've used to tune the db at my workplaces and their advice is excellent. Now that you are using mysqltuner, I would also recommend looking at mysqlreport. Its not as easy to read as mysqltuner but gives a LOT of stats about the db server. Based off the stats you then make the tuning changes. D.O uses it as part of their report services each night and I did for a long time as well.

Best of luck :)

http://mysqlperformanceblog.c

bvirtual's picture

http://mysqlperformanceblog.com/ has been good reading, if only I had a ton more time.

For those without shell command line access, or do not write php code, the Drupal project mysqlreport will work wonders, I think.

Cool, it comes with a lot of documentation for it's report, even installed locally at /sites/all/modules/mysqlreport/mysqlreportguide.html

I see it has a lot of Drupal specific stats as well. I'd have to install, enable, grant permission, on dozens of web sites. I wonder if drush can run mysqlreport from a central install folder, and write the report to a file (web page) for viewing, and will average across sets of domain names. ;-) I'll do this for the high traffic sites.

Many thanks to Ashok!

Pete

Peter

LA's Open Source User Group Advocate - Volunteer at DrupalCamp LA and SCALE

Sweet!

mikeytown2's picture

Thanks for the tip about mysql report, I've added that to the project page of dbtuner which has a port of mysqltuner included in it.

Well played ;)

BTMash's picture

Well played on my name, bvirtual. Very well played ;) I actually hadn't heard of the mysqlreport module (I had used mysqlreport strictly from the server to look at the stats. This is pretty darn awesome and will take a look at this some more :)

(the) Ashok in Delhi... haha

mike stewart's picture

(the) Ashok in Delhi... haha ... rofl ... funny link @bvirtual! greant play on Rain's typo... aw, @Rain... I'm afraid of what comes next :-P

--
mike stewart { twitter: @MediaDoneRight | IRC nick: mike stewart }

Comparing the 3 MySQL report tools

bvirtual's picture

Below is my initial suggestions to first time users of these packages to optimize MySQL. Please do not believe me, as I'm using them for the first as well. I was just fortunate to be hosted at www.VPSCoop.org, with 8 CPUs and 32G RAM and the install default values resulted in 'fast enough' queries, for low web page latencies.

I think beginners should use mysqltuner first, as it has simple recommendations grouped at the bottom.

Then, use tuning-primer.sh which has twice as many reported values, and will take four times as long to tweak my.cnf values even better. Still use mysqltuner while doing tweaking from tuning-primer.sh. The latter tweaking will take several days, if not weeks, as MySQL usage needs to "average" out over 48 hours or more, for the 'better' recommendations to come out of tuning-primer.sh.

Both reports do more than just transfer values from the MySQL commands that merely list values. Both reports combine values, creating ratios, comparing them, measuring them against expectations, to derive meaningful recommendations. Most "general" recommendations, but valuable to learn the first steps in optimizing MySQL my.cnf parameters.

I've yet to confirm my initial scanning of the mysqlreport long list of values. It appears one must following the link at the top of each section to read the supplied documentation for each grouping of related value, do a manual comparison, calculate the ratios yourself, before understanding what tweaks to make in my.cnf. Graduating to mysqlreport means having a superior understanding of all reported values and what they truly mean, thus one will make superior my.cnf tweaks, including when MySQL is being used in outside of normal conditions. I look forward to this learning curve.

The command line 'mysqlreport' is available from http://hackmysql.com/mysqlreport, which is a site that is no longer maintained, and will be likely going away within the year. The Drupal project by the same name appears to have the same output values, with extra Drupal specific section.

After finishing the MySQL my.cnf tweaking learning curve to better use the available hardware (RAM), I look forward to more hardware specific optimizations tools (using RAM wiser), like memcache, varnish, which may require installing OS specific software packages. Then, learning PHP language specific optimizations, like PHP op-code caching. Next, pick some low hanging fruit of Drupal specific tools, like module Boost. Finally, the ultimate pinnacle of achievement will be how all these optimizations synergize together, and how to tweak each to get the most from the 'neighboring' optimizing tool.

In a few months, I should be able to write up the relative merits of each level of these tools, which are mutually exclusive, and what ones combine well. Maybe someone has done this already and I just have not found it? I'll have to read more DO, than GDO.

The Rain is mainly in the plains. But plain is not the Rain.

Pete

Peter

LA's Open Source User Group Advocate - Volunteer at DrupalCamp LA and SCALE

Wish I could've made it...

craigmc's picture

save some smart for me!

Los Angeles, CA

Here's the video I recorded

christefano's picture

Here's the video I recorded at this meetup when I set up a new VPS with Virtualmin, Drush and Drupal:

   http://blip.tv/ladrupal/episode/5466668

I edited the original post, too, and put the link at the top. I'll be going through past meetup announcements and adding links to the videos recording during those meetups.