Mysql: benchmarking many writes

April 28, 2009 § Leave a comment

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’)

March 2, 2008 § Leave a comment

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:

How to Kill MySQL Performance

February 3, 2008 § Leave a comment

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

Fotolog Backend

February 2, 2008 § Leave a comment

Flickr Backend

January 30, 2008 § Leave a comment

Early Flickr:

Flickr Architecture:

Digg Backend

January 30, 2008 § Leave a comment

LiveJournal Backend

January 27, 2008 § Leave a comment

August 2005: 

April 2004:

Where Am I?

You are currently browsing the mysql category at RAPD.

Follow

Get every new post delivered to your Inbox.