MySQL improvement on OpenBSD 3.0

performance boost with thread library update

A pretty high-traffic site I run depends 100% on MySQL on OpenBSD webservers. I noticed under heavy loads that MySQL would start using 95% of CPU and all but freeze the system.

MySQL wasn't the problem. The threads were. Marco Hyman gave me all the info, below, in an email. I'm just posting it on a webpage for my fellow OpenBSD peeps, and letting you know it works for me. It's running on our live server now, and MySQL CPU usage is down from 60% (before) to 7% (now).

So really all we're doing is updating the threads to the newest (post-3.0-release) development. Then rebuilding the MySQL port, using those threads instead of the pth ones it's defaulted to use.

Note: I'm just a user. Not a programmer. I don't really know what I'm talking about. (I have no idea what threads are!) Just gathering and re-presenting the knowledge I've gathered about this problem and its solution.

Huge thanks to marc@openbsd.org and brad@openbsd.org!

ASSUMPTIONS (important!)

• You're using Open BSD 3.0 on an i386 processor. (This won't work in 2.9 or earlier. And might not be necessary in 3.1! And so far this only improves i386 processors like Pentium, Athlon, Duron, 486, etc.)
• Your box is connected to the internet.
• You're logged in as root user. (Through "su" or whatever.)
• You don't have MySQL installed on this box already.
• If you do have MySQL installed on this box already, this will still work, you'll just have to pkg_delete your existing MySQL BEFORE we start, I think.)

Step-by-Step INSTRUCTIONS

update thread lib (libc_r) to -current

cd /usr/src/lib/libc_r
cvs -d anoncvs@anoncvs1.ca.openbsd.org:/cvs up -PAd

compile and install the thread library

cd /usr/src/lib/libc_r
make cleandir
make obj
make depend
make
make install

fix the mysql ports makefile

cd /usr/ports/databases/mysql
vi Makefile

Comment-out the CONFIGURE_ENV lines and the LIB_DEPENDS lines in the Makefile. (For newbies: "comment-out" means to put a # as the first character of that line, so that line is ignored.)

old:

CONFIGURE_ENV= CPPFLAGS="-I${LOCALBASE}/include/pth" \
               LDFLAGS="-L${LOCALBASE}/lib/pth"

new:

#CONFIGURE_ENV= CPPFLAGS="-I${LOCALBASE}/include/pth" \
#               LDFLAGS="-L${LOCALBASE}/lib/pth"

old:

.if !defined(PACKAGING)
LIB_DEPENDS=   pthread.14:pth-1.4.*:devel/pth
.elif ${SUBPACKAGE} == "-server"
LIB_DEPENDS=   pthread.14:pth-1.4.*:devel/pth
RUN_DEPENDS=    :mysql-client-3.23.*:databases/mysql
.endif

new:

.if !defined(PACKAGING)
#LIB_DEPENDS=   pthread.14:pth-1.4.*:devel/pth
.elif ${SUBPACKAGE} == "-server"
#LIB_DEPENDS=   pthread.14:pth-1.4.*:devel/pth
RUN_DEPENDS=    :mysql-client-3.23.*:databases/mysql
.endif

make the MySQL port

make
cd w-mysql-3.23.42/mysql-3.23.42/mysql-test
./mysql-test-run

no errors in test? install it for real

cd /usr/ports/databases/mysql
make install

add /usr/local/lib/mysql to shlib_dirs in /etc/rc.conf

vi /etc/rc.conf

Look for the line:

shlib_dirs=   # extra directories for ldconfig

Change it to:

shlib_dirs="/usr/local/lib/mysql"

Or, if you have more than one directory there, use the curly-braces like this:

shlib_dirs="/usr/local/lib/{pth,kde2,mysql,libmcrypt}"

install the MySQL server

cd /usr/ports/packages/i386/All/
pkg_add mysql-server-3.23.42.tgz

copy a MySQL config file to your system

cd /usr/ports/databases/mysql/w-mysql-3.23.42/mysql-3.23.42/support-files
cp my-small.cnf /etc/mf.cnf

create the basics, and start the server

mkdir /var/mysql
chown mysql:mysql /var/mysql
mysql_install_db
safe_mysqld &
mysql

If it works, that should work. If not, try the MySQL or OpenBSD mailing lists to debug. (AFTER searching for your error in Google or the OpenBSD mailing list archives.)

add to startup script

vi /etc/rc.local

add this to the end:

if [ -x /usr/local/bin/safe_mysqld ]; then
 /usr/local/bin/safe_mysqld > /dev/null & echo -n ' mysql'
fi

might as well add php, too

cd /usr/ports/www/php4/
export FLAVOR="mysql"
make install
/usr/local/sbin/php4-enable
cp /usr/local/share/doc/php4/php.ini-dist /var/www/conf/php.ini

Prepare Apache:

vi /var/www/conf/httpd.conf

add the line:

AddType application/x-httpd-php .php .php3 .htm .html .inc

Make your test file:

cd /var/www/htdocs
vi index.html

and type just:

<?php
phpinfo();
?>

Save it and type (as root):

apachectl restart

Type the IP address of your box in your web browser, and see if it worked!