PIFR 2 client for MySQL on Ubuntu 9.10

We encourage users to post events happening in the community to the community events group on https://www.drupal.org.
You are viewing a wiki page. You are welcome to join the group and then edit it. Be bold!

in-progress: installs mysql 5.1

starting from http://qa.drupal.org/node/44, http://groups.drupal.org/node/39942

ec2-request-spot-instances -t c1.medium -p 0.094 -k XXX -g pifr2 -v ami-bb709dd2

ssh -i XXX.pem ubuntu@ec2-99-201-0-242.compute-1.amazonaws.com

note - for use on AWS to re-bundle you must add multiverse to /etc/apt/sources.list and sudo apt-get install ec2-api-tools

sudo apt-get update
sudo apt-get upgrade

sudo apt-get --no-install-recommends install apache2 php5-common libapache2-mod-php5 php5-cli php5-gd php5-curl patch htop cvs rsync cron wget openssh-blacklist-extra

sudo apt-get --no-install-recommends install mysql-server mysql-client php5-mysql

set a root password for mysql (for example: WXYZ123) , then save it in ~/.my.cnf like:

[mysql]
password=WXYX123

[mysqldump]
password=WXYZ123

[mysqladmin]
password=WXYZ123

This allows the ubuntu user to invoke mysql -uroot without needing to type password.

Add innodb conf as /etc/mysql/conf.d/drupal.cnf

[mysqld]

innodb_log_group_home_dir = /var/lib/mysql
innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_files_in_group = 2
innodb_log_buffer_size = 2M
# This should match your number of CPUs
innodb_thread_concurrency = 2
# If you have lots of RAM, you can increase the innodb_buffer_pool_size
innodb_buffer_pool_size = 512M
innodb_additional_mem_pool_size = 10M
innodb_file_per_table = 1
innodb_file_io_threads = 4
innodb_flush_log_at_trx_commit=0
innodb_max_dirty_pages_pct = 70
innodb_support_xa = 0
innodb_checksums = 0
innodb_doublewrite = 0

Note: Not a lot of testing has been done with variations to these additional configuration options. This one is simply known to work.

sudo /etc/init.d/mysql restart

add drupal.ini to /etc/php5/conf.d with the following (sudo nano /etc/php5/conf.d/drupal.ini) :

short_open_tag = Off
expose_php = Off
memory_limit = 256M
display_errors = On
log_errors = Off

sudo a2enmod rewrite
sudo perl -pi -e 's/(\s+AllowOverride)\s+None$/\1 All/g' /etc/apache2/sites-available/default

sudo /etc/init.d/apache2 restart

mysqladmin -u root create drupal
mysqladmin -u root create drupal_checkout
mysql -uroot -e"GRANT ALL ON drupal.* TO 'username'@'localhost' IDENTIFIED BY 'password';"
mysql -uroot -e"GRANT ALL ON drupal_checkout.* TO 'username'@'localhost' IDENTIFIED BY 'password';"

cd /var
sudo chown ubuntu www
cvs -z6 -d :pserver:anonymous:anonymous@cvs.drupal.org:/cvs/drupal checkout -r DRUPAL-6-15 -d www drupal
cd www
sudo rm index.html

chmod 755 sites/default
cp sites/default/default.settings.php sites/default/settings.php

nano sites/default/settings.php

add:

$db_url = array();
$db_url['default'] = 'mysqli://username:password@localhost/drupal';
$db_url['pifr_checkout'] = 'mysqli://username:password@localhost/drupal_checkout';

mkdir sites/default/files
chmod 777 sites/default/files
touch sites/default/files/review.log
chmod 777 sites/default/files/review.log
sudo chown www-data:www-data sites/default/files/review.log
ln -s ./sites/default/files/checkout

mkdir sites/all/modules
cd sites/all/modules
cvs -z6 -d:pserver:anonymous:anonymous@cvs.drupal.org:/cvs/drupal-contrib checkout -r DRUPAL-6--1 -d cvs_deploy contributions/modules/cvs_deploy

cvs -z6 -d:pserver:anonymous:anonymous@cvs.drupal.org:/cvs/drupal-contrib checkout -r DRUPAL-6--2-2 -d project_issue_file_review contributions/modules/project_issue_file_review

Install Drupal, enable CVS Dploy, PIFR, PIFR Client, PIFR Assertion modules

crontab -e

    */2 * * * * /usr/bin/wget -O - -q -t 1 http://localhost/cron.php

Using a RAM disk

Backup and then patch /etc/init.d/mysql with the attached patch to run the MySQL databases and checkout directory from RAM in tmpfs. Make sure the result init.d mysql script is still chmod 755. As of November 13, 2009, it takes about 71MB of space in /var/lib/mysql to run a basic test of Drupal 7 core, so keep that in mind if you change the tmpfs allocations provided in the setup patches.

Then execute the following so you can keep your Drupal configuration as long as you do clean starts and stops of MySQL.

sudo mkdir /var/lib/.mysql
sudo chown -R mysql:mysql /var/lib/.mysql
mkdir /var/www/sites/default/.files
touch /var/www/sites/default/.files/remove_me_later.txt
sudo chown -R www-data:www-data /var/www/sites/default/.files
sudo /etc/init.d/mysql stop
cd /var/lib/.mysql
cd /var/lib/mysql
rm -r *
cd /var/lib
/etc/init.d/mysql start
rm /var/www/sites/default/.files/remove_me_later.txt

Once you are in a final configured state, you might also want to create a /var/lib/.mysql.bak directory for storing a working copy of your database files. There are cases where mysql could fail to start, then it could copy nothing from the empty /var/lib/mysql directory to the stateful /var/lib/.mysql copy.

Managing the boxes

We should start using something like BCFG2 to manage PIFR clients. I'm using BCFG2 for some large client projects, and Chapter Three is using it for Pantheon. Encoding configuration into a system like that is always better than a set of instructions on a wiki. --David Strauss

We've been using puppet - seems more common and generally works fine. The goal here is mostly to bundle an AMI so these steps only need to be done once, but certainly having a configuraiton management tool would be better in the long run.

I was asked for my comments on optimizing this mysql configuration, but I'm not 100% sure what to say. There's not a lot of testing around fast table standup and teardown for InnoDB. One suggestion would be at the code level: ensure that TRUNCATE runs before DROP TABLE in order to minimize server-wide locking time during teardown. Using innodb_file_per_table may actually hurt performance by requiring file deletion on disk (which is slow for ext3) rather than re-use of the space in the InnoDB main data files. Also, try to use the InnoDB plugin instead of the built-in -- and quite antiquated -- engine. We might be able to optimize the innodb_flush_method setting, but that's tricky to do without possibly causing other problems, not just data durability ones.

An option to add: innodb_support_xa=0
Startup options to add: --skip-innodb_checksums --skip-innodb_doublewrite --innodb_locks_unsafe_for_binlog

If we can get everything running on a memory-based disk (as I did once for PIFR in a way that didn't hold up very well), that would be the biggest win of all.

--David Strauss

AttachmentSize
mysql.init_d.patch_.txt1.91 KB