version 3 of the engine), whenever space on disk is reduced the
overall performance goes up assuming that the new format doesn't
require chewing up the processor too much.
The schema was the following:
CREATE TABLE accesslog (
id int(10) unsigned NOT NULL ,
method varchar(255) default NULL,
bytes_sent varchar(255) default NULL,
protocol varchar(255) default NULL,
uri varchar(255) default NULL,
args varchar(255) default NULL,
hostname varchar(255) default NULL,
client varchar(255) default NULL,
Referer varchar(255) default NULL,
slash_user varchar(255) default NULL,
e2_node varchar(255) default NULL,
time datetime NOT NULL default '0000-00-00 00:00:00'
The schema type was selected because its the type of Schema in 5.0
that Archive doesn't do as well at. Lots of Varchars, no blobs. In
5.1 the new encoding methods make both of them essentially the same
(and anything that is NULL is now doesn't even have a size record
The command for generating the data:
--user=root --create=create3.sql --query=access.sql --
engine=archive,myisam,blackhole --number-of-queries=10000 --csv=/
What I looked at was the average over 10 iterations. I did run for
300 connections but the results look a bit funky and I need to see
what was going on. The machine these ran on is a dual AMD Opeteron
with 2gigs of memory and a sata disk. The data was taken from a
snapshot of data out of one of my production databases.
Why did I look at blackhole? Blackhole is a NULL engine in MySQL. No
actual disk actions are done for it, so it gives me a baseline to see
what the overhead is for these types of calls. Everytime I see an
engine benchmark, I would like to see Blackhole because it gives me a
better meaning of what is the Parser+Optimize and what is the actual
What else did I learn from this? I really need to write up a perl
script that generates graphs from the output of mysqlslap. Having to
copy and paste this stuff into Excel took me more time then anything
For 5.1 I am using the head of the tree as of yesterday afternoon.
Since I was wanting to compare against 5.0 I did not use
autoincrements for the first field, Archive only has them as of 5.1.
What next? I am going to rerun with a lot more data :)
If I have the time to take a dump of the SQL that my stats package
uses to generate traffic reports I may run that as well. It would be
interesting to see how well aggregate queries turn out.
I've also got a Quad PPC5 that has Ubuntu server on it. I am curious
to see how that does under stress.