In his keynote at DrupalCon Boston on March 5 Brian Aker discussed the latest approaches to six problems related to scaling up MySQL:
I had a chance to sit down with Brian after his keynote to ask him questions about a few of these approaches. Here is the interview:
Thanks to Vic Germani for producing this audio.
Resources:
Back now from DrupalCon, I'm parsing all that happened last week in Boston. For me it was a whirlwind, interrupted by a plethora of hassles, including a nasty head cold, keyboard and trackpad on my MBP crapping out, a crashed demo, and several hours separated from my Treo while it rode around in the back of a Boston cab. All that negative energy converging on me was more than offset by the positive vibe at the four day conference. The kindness of the cabbie who drove crosstown to return my phone helped too.
One of the highlights for sure was spending time with a new Sun colleague, Brian Aker from MySQL. We had breakfast at Henrietta's near Harvard Square before his keynote on Wednesday. I asked him about the merger with Sun, what's next for MySQL, and how he'd like to see our field organizations work together. He said the merger has been pretty well received and there was a general appreciation at MySQL for Sun's commitment to open source (something I hope will rub off on Brian's Slashdot amigo Chris Dibona, who conspicuously left Sun off of his Tuesday keynote list of companies that "get" open source). There is a tradition of collaboration between Sun and MySQL too, which Brian indicated ought to help smooth the integration. Lot's of his work is going into memcached these days, particularly in the libmemcached client. He cleared up a misconception for me regarding Innodb: since Innodb is GPL'd, the risk of Oracle smothering it is nil - the community is driving it, and it's not the dead end many had feared. What's next? Don't expect to see MySQL 5.1 until 2009; do expect a maturing and further specializing application of the MySQL engines MyISAM, Innodb, BDB, and Archive; and plan for an adoption ramp for DRBD. Brian had some great advice for Sun's field engineers: get familiar with MySQL technology by taking advantage of the many training resource available at MySQL.com. MySQL University is a great place to start, (be sure to catch Brian's talk on EC2 March 29). I also caught some good audio one-on-one with Brian after his keynote which I will post separately, along with his advice on scaling up your database.
RDF and Semantic Web were topics of much conversation and at least one BoF session. With the addition of RDF modules in Drupal 6, developers can mashup data from multiple sites in very interesting ways. If Web3.0 is massively distributed data mining, indexing, and mashing it all up, then Drupal is positioned to be the portal for this convergence, as Dries Buytaert resolutely declared in his Monday keynote
I gave a talk on running Drupal on Sun, with some help from Chris Cheetham from Project Caroline, at the end of the day on Wednesday (slides at right). As luck would have it, my demo froze up, but I did manage to show Drupal running in a Solaris Zone, and DTrace to count function calls from Drupal. Chris's demo of Drupal deployment to Project Caroline went much smoother.
Another highlight was awarding the grandprize Sun Fire T1000 server to the winners of the Showcase Site competition. PingVision won it for their work on Popular Science Magazine. Congratulations to Kevin Bridges and the rest of the crew at PingVision.
There was a lot of support for the next DrupalCon to be held in Hungary this fall. It will be hard to top the Boston event, but I know this community will do their best to have the best one yet.
Whilst installing Drupal 6.0 on Solaris Express Developer Edition (SXDE) 1/08 I ran into a few glitches with the brand new Webstack, which makes it's debut in this build of OpenSolaris. (SXDE is Sun's distro of OpenSolaris. It's the best way to get access to all the latest stuff in a relatively feature complete distro of OpenSolaris without having to build the whole O/S yourself.) With the advent of Webstack integration you don't need a separate download to get all the AMP stack integration and optimizations previously only available in Coolstack.
The executive summary of the solution to the Drupal 6.0 install glitches is:
include_path = ".:/usr/php5/5.2.4/include/php:/opt/drupal-6.0"
ln -sf /usr/mysql/5.0/lib/mysql/libmysqlclient.so.15 \
/usr/mysql/5.0/lib/mysql/libmysqlclient.so.12
Now, with these fixes in place, I have the advantages of the Service Management Framework (SMF) and DTrace, plus an AMP stack compiled with optimizations for Solaris and the processor architecture (AMD64, in my case).
If you're interested in a more detailed account of the glitches and fixes, read on...
The first glitch prevented the Drupal index.php page from rendering, and appeared in the apache error_log as:
PHP Warning: include_once() [<a href='function.include'>function.include</a>]: Failed opening
'includes/install.inc' for inclusion (include_path='/usr/php5/5.2.4/include/php'
which was remedied by adding '.' to the include path in include_path in /etc/php5/5.2.4/php.ini
include_path = ".:/usr/php5/5.2.4/include/php"
Then, proceeding to the database setup, MySQL gave an error:
"Client does not support authentication protocol requested"
This was easily resolved by the procedure posted on http://dev.mysql.com/doc/refman/5.0/en/old-client.html, but that's a compromise on MySQL password strength, so not ideal. This allowed me to proceed to the next glitch, which apache error_log explained as:
After much hair pulling, Sriram pointed me to the solution he worked out for the same problem with MediaWiki installation:"ld.so.1: httpd: fatal: relocation error: file
/usr/php5/5.2.4/modules/mysqli.so: symbol
mysql_set_local_infile_handler: referenced symbol not found"
ln -sf /usr/mysql/5.0/lib/mysql/libmysqlclient.so.15 \
/usr/mysql/5.0/lib/mysql/libmysqlclient.so.12
and another addition to include_path in /etc/php5/5.2.4/php.ini to add Drupal's base dir
include_path = ".:/usr/php5/5.2.4/include/php:/opt/drupal-6.0"
Turns out that spoofing the library name like this also solved the "Client does not support authentication protocol ..." problem too, so I'm back to full MySQL password strength.
After these three simple but obscure fixes it was all clean sailing. I now have Drupal running in six zones across two separate instances of SXDE 1/08 using the latest Webstack.
I'd never seen the reference outside of Sun before today, but there it was, in the conference program for DrupalCon Boston :
*AMP
Describing the Site Building track at the conference, the program lists track topics, including, "Drupal and *AMP, a systems level
view". The asterisk clearly denotes a substitution variable, which had previously been the constant "L", preceding the abbreviation for Apache-MySQL-PHP/Perl/Python. This is a clear tip of the hat to Solaris. The new acronym, presumably pronounced star amp, bestows equal opportunity status to Solaris and Linux within the Drupal community.
I expect there will be plenty of interest in Solaris and other Sun technologies at DrupalCon, which convenes March 3-6 at the Boston Convention & Expo Center - the news of Sun's intent to acquire MySQL did not go unnoticed, and high profile sites running on Solaris are turning up frequently. I guess that's why my Inbox has been overflowing with correspondence from the Drupal community.
Sun is a Gold sponsor of DrupalCon Boston. We'll be handing out Solaris Express DVD's and we're giving a Sun Fire T1000 server to the winner of the Drupal Showcase competition. I'll be a panelist in the Performance Tuning session, and I'll also be presenting some Solaris deployment techniques and best practices similar to what I presented at a DrupalCon last September in Barcelona. I'm hoping we'll also have a contingent from Project Caroline to let the community in on the next big thing in Web2.0 development platforms. As if all that from Sun wasn't enough, rumor has it, MySQL Director of Technology, Brian Aker is going to keynote the conference.
The Coolstack 1.1 AMP package installs the 32-bit version of MySQL by default. We want to let the horses out of the corral on this SunFire X2200 M2, so we also install the 64-bit version, which is provided as a separate package. Since we need the 32-bit version in order to compile php5, we keep it in its original /opt/coolstack/mysql_32bit location.
After running mysql_install_db and the other steps in /opt/coolstack/mysqlREADME we then to prep MySQL to be a first class citizen on Solaris 10.
Like the the CoolStack 1.1 Apache, CoolStack 1.1 MySQL is not integrated with SMF. Here are the resulting manifest and method files to get MySQL working cleanly as a service:
/var/svc/manifest/network/mysql.xml :
<?xml version='1.0'?>
<!DOCTYPE service_bundle SYSTEM '/usr/share/lib/xml/dtd/service_bundle.dtd.1'>
<!--
Manifest for MySQL
-->
<service_bundle type='manifest' name='CSKmysql:mysql'>
<service
name='network/mysql'
type='service'
version='1'>
<create_default_instance enabled='false' />
<single_instance />
<!--
Wait for network interfaces to be initialized.
-->
<dependency name='network'
grouping='require_all'
restart_on='error'
type='service'>
<service_fmri value='svc:/milestone/network:default'/>
</dependency>
<!--
Wait for all local filesystems to be mounted.
-->
<dependency name='filesystem-local'
grouping='require_all'
restart_on='none'
type='service'>
<service_fmri
value='svc:/system/filesystem/local:default'/>
</dependency>
<exec_method
type='method'
name='start'
exec='/lib/svc/method/CSKmysql start'
timeout_seconds='60'>
</exec_method>
<exec_method
type='method'
name='stop'
exec='/lib/svc/method/CSKmysql stop'
timeout_seconds='60'>
</exec_method>
<exec_method
type='method'
name='restart'
exec='/lib/svc/method/CSKmysql restart'
timeout_seconds='60'>
</exec_method>
</service>
</service_bundle>
/lib/svc/method/CSKmysql :
#!/usr/bin/sh
#
# Method file for MySQL
#
# This uses the MySQL packages from CoolStack 1.1
# CSKmysql
#
# Modify accordingly!
#
# NOTE: Make sure DB_DIR is owned BY the mysql user and group and chmod
# 700.
#
. /lib/svc/share/smf_include.sh
DB_DIR=/site-data0/data
PIDFILE=${DB_DIR}/`/usr/bin/uname -n`.pid
case "$1" in
start)
/opt/coolstack/mysql/bin/mysqld_safe --user=mysql --datadir=${DB_DIR} --pid-file=${PIDFILE} > /dev/null &
;;
stop)
if [ -f ${PIDFILE} ]; then
/usr/bin/pkill mysqld_safe >/dev/null 2>&1
/usr/bin/kill `cat ${PIDFILE}` > /dev/null 2>&1 && echo -n ' mysqld'
fi
;;
'restart')
stop
while pgrep mysqld > /dev/null
do
sleep 1
done
start
;;
*)
echo ""
echo "Usage: `basename $0` { start | stop | restart }"
echo ""
exit 64
;;
esac
Then import the service:
# svccfg import /var/svc/manifest/network/mysql.xml
Before starting MySQL we need to put a config file in /etc. The example small config provided with MySQL is just right for now.
# cp /opt/coolstack/mysql/share/mysql/my-small.cnf /etc/my.cnf
Then change the datadir setting in /etc/my.cnf to point to the 1.3TB zfs pool on the external StorageTek 3511:
39 datadir=/site-data0/data
Finally, set the data dir with proper ownership:>
# chown myqsql:mysql /site-data0/data
And make sure it starts:
# svcadm -v enable mysql
At this point we're ready to set up the content management system, Drupal.
Additional tips for MySQL on Solaris:
This blog copyright 2009 by downstream
