Mysql: benchmarking many writes

Purpose:

• Benchmarking Mysql writes to be compared with key-value databases writes.

Basic info:

• 2.53 G Core 2 Duo Mac Book Pro
• 4 GB RAM
• Ruby client

Code can be found: here

Results for 100,000 rows with 16 char length value:

Write 100000 rows with string-length: 16
Thread ID: 659670
Total: 9.095328

%self     total     self     wait    child    calls  name
66.58      6.06     6.06     0.00     0.00   100000  Mysql#query (ruby_runtime:0}
20.30      9.10     1.85     0.00     7.25        1  Integer#times (ruby_runtime:0}
13.11      1.19     1.19     0.00     0.00   100000  Object#insert_statement (/Users/didip/projects/ruby/mysql-profile/write_profile.rb:27}
0.00      9.10     0.00     0.00     9.10        1  Object#write_many_profile (/Users/didip/projects/ruby/mysql-profile/write_profile.rb:37}

Results for 1,000,000 rows with 16 char length value:

Write 1000000 rows with string-length: 16
Thread ID: 659670
Total: 88.175784

%self     total     self     wait    child    calls  name
66.33     58.49    58.49     0.00     0.00  1000000  Mysql#query (ruby_runtime:0}
20.48     88.18    18.06     0.00    70.12        1  Integer#times (ruby_runtime:0}
13.19     11.63    11.63     0.00     0.00  1000000  Object#insert_statement (/Users/didip/projects/ruby/mysql-profile/write_profile.rb:27}
0.00     88.18     0.00     0.00    88.18        1  Object#write_many_profile (/Users/didip/projects/ruby/mysql-profile/write_profile.rb:37}

SqlAlchemy: SQLError: (OperationalError) (2006, ‘MySQL server has gone away’)

What is this problem about:

This is actually MySQL error message. It means that there are no MySQL connections anymore.By MySQL default, idling database connections will be closed after 8 hours. Not having MySQL connections means that no SQL operations can be performed. SqlAlchemy is simple reporting the error.

When using SQLAlchemy, how to prevent such problems?

See sample code below:

engine = create_engine(db_path, pool_size = 100, pool_recycle=7200)

 Take a look at the pool_recycle variable. That will configure SqlAlchemy engine to reconnect every x number of seconds. This easy solution makes sure the existence of MySQL connection.

References:

• SqlAlchemy Trac – FAQ
• Mail Archive – MySQL server has gone away 
• GoogleGroup – Has Gone Away 

How to Kill MySQL Performance

This is 1 interesting slideshow about the peculiars of MySQL. Enjoy!

Fotolog Backend

Flickr Backend

Early Flickr:

Flickr Architecture:

Digg Backend

LiveJournal Backend

August 2005: 

April 2004:

PHP 5.2.4/5.2.5 on Leopard… (–with-mysql problem)

is pretty much neutered. It doesn’t have a lot of extensions that I need such as: curl, mysql (this is the big one), pdo, etc, etc…

Update: It seems like I did not went through a clean-wiped-out install when installing Leopard. Many of my old development tool directories stayed the same, but some are different. PHP happens to be one of the few that’s different.

That sucks… O well, all I have to do is rebuild PHP 5.2.4/5.2.5 from source (+ all the extensions that I want) right?

WRONG!!! Big Fat Wrong!

I cannot even do:

./configure -–with-mysql=/usr/local/mysql

After that I got this console message:

configure: error: -–with-mysql=/usr/local/mysql: invalid option; use –help to show usage

Huh? Why? WTF?

There are plenty of people complaining about the exact same problem, but no one seemed to have the answers.

Can anyone help me? Can anyone points me to the working PHP 5.2.4/5.2.5 source?

Please…

I got Leopard (OS X) installed!!!!!

and then MySQL is broken… Damn…

There are quite a number of people giving suggestions on how to deal with this problem.

It turns out to be just permission problem. Below is what I did:

Fri Nov 09–at[~]–do–>sudo chown -R {my_username} /usr/local/mysql/data

Done.

Now I can start and stop MySQL via Preference Pane as usual.

Hopefully this help some readers who want to upgrade to OS X Leopard.

Python: SQLAlchemy & MySQL installation problems…

Another dependency issue happened again to me while re-installing my python development environment.

What did I do to re-install Python?

1. Downloaded python 2.5.1
2. Downloaded easy_install
3. Installed every modules using easy_install (w/o version number, so that I get the most recent stable build)

What went wrong?

1. SQLAlchemy import was messed up. All the sudden I couldn’t import create_session…

What did I do?

>>> import sys
>>> sys.path
['', '/Library/Frameworks/Python.framework/Versions/2.5/lib/

python2.5/site-packages/SQLAlchemy-0.4...egg', ]

Nothing is weird, but this Google Group discussion mentioned that an unrelated project had similar dependency issue to mine. Their solution is to downgrade SQLAlchemy to 0.3.10 (version 0.3.11 is also OK)

Using SQLAlchemy:

easy_install -U “SQLAlchemy==0.3.11″ 

Voila, import problem solved.

2. Cannot run MySQL_python because of missing dot so file, now that’s messed up big time. This is the snippet of the error:

ImportError: dlopen(~/.python-eggs/MySQL_python-1.2.2-py2.5-macosx-10.5-i386.egg-tmp/_mysql.so, 2): Library not loaded: /usr/local/mysql/lib/mysql/libmysqlclient_r.15.dylib
Referenced from: ~/.python-eggs/MySQL_python-1.2.2-py2.5-macosx-10.5-i386.egg-tmp/_mysql.so
Reason: image not found

What did I do?

Googling this problem, reveal the cure for it:

sudo mkdir /usr/local/mysql/lib/mysql
sudo cp /usr/local/mysql/lib/libmysqlclient_r.15.dylib /usr/local/mysql/lib/mysql/libmysqlclient_r.15.dylib

Apparently the particular mysql lib file is located in the wrong folder.

Done, now the project runs smoothly the way it was before.

Lesson Learned:

Google Search is very Python friendly, use it extensively.