This week I have been doing a great deal of testing with SSD to determine the feasibility of using SSD in a MySQL production system. I will be working on a complete write up, but I thought I would tantalize you with some findings.

The system running the tests is built from NewEgg.com with the Drives supplied by Easy Computing Company. I am using a Q6600 Quad Core (2.4Ghz) with 6 GB of Ram, and 2 72GB 10K Rpm Raptor drives. These tests are done with 1 32GB mtron device. I tested with the onboard SATA controller and a generic super micro 8 port controller. I will be testing Easy’s Managed Flash Technology soon. The results shown here are not using Easy Computing’s MFT technology.

Random Read Test:

sysbench v0.4.8:  multi-threaded system evaluation benchmark

128 files, …

Hi!

You can now find the specifications, flow charts and implementation details for all (except one) parts of Maria at:

http://forge.mysql.com/worklog/search.php?k=Maria&t=tds

This makes Maria the best documented project in the history of MySQL AB. Yes, I know that this doesn't say much, but I think we have done a really decent job in this case.

The public worklog doesn't yet display the dependences between tasks, like our internal worklog does, which makes it a bit hard to navigate between tasks and to understand in which order tasks will be done. I hope this will be fixed soon.

So, you may ask, what is the one task that we have not published.? This is a new very cool way to index data to make some searches extremely fast. The reason for not releasing it is just that …

Well I am trying to use dbt2 to test some ssd drives.  This is my first time trying to use the tool, and I am not impressed.  Their seems to be a general lack of documentation out there, on top of the scripts being very buggy. I am trying 0.37 & 0.40.
The first thing is the scripts are somewhat of mess.  Things like setting user/socket  settings are not intuitive and in .37 required me to hack the run_workload script.  The run_workload script calls scripts that do not exist anymore.  Which is really annoying.

The lack of documentation hurts because all the references I have seen show a 20 warehouse run with x number of terminals/threads.  At the levels I have seen I get crap results like 500TPM, but when I look at the system my system is not even breaking a sweet.  I upped the # of warehouses to 50, and the threads to like 700 and I end up getting near 18K TPM.  And this puts my CPU @ 45% …

I’ve started working on a session on the practice of online ombuds ? especially as it applies to Free Software and Open Source communities. The session is based on my work in the Free Software, PHP, Mozilla, MySQL and Open Source communities - in particular, recent work (which I need to get back to) on the Open Source Initiative mailing lists.

So far, the session has been pitched to the following events: CommunityOne 2008 (waiting), FOSDEM 2008 (accepted), JavaOne 2008 (declined), Open Web Vancouver 2008 (waiting) and OSCON 2008 (waiting).

I’ll start posting alpha versions of slides and so on as they are ready. …

A

The initial release of the FederatedX Pluggable Storage Engine for MySQL is now
available. I developed the Federated Storage Engine when I worked at MySQL,
and really saw a lot of potential with it. However, there were many other
projects that I had to give attention to, and many features and bug fixes that
users wanted didn't come into fruition because of the busy schedule. I left
MySQL a year ago to pursue an opportunity with Grazr what has kept me equally
busy. However, I have made a resolution this year to give some projects that I
have wanted to improve the attention that they need. I still see a lot of
potential with the "federated" concept.

Federated as it is isn't what many people expect it to be. IBM for instance,
has a federation as a very integral part of DB2. The Federated Storage Engine
is a proof-of-concept storage engine -- not to say it doesn't …

At Flickr adding capacity is easy. Since we are able to scale at a function of user growth, independent of hardware classes. This makes adding new hardware easy. I'm able to adjust the server weight on the FLY to give more users to a certain class of hardware over another.


Changing schema is easy as well and now I do it much safer. In the pass I would turn off a master for each partitioned dataset all at the same time: then stay up for 20+ hours and execute an alter across the entire server farm, that was off line.

Now I do it in stages and do it throughout the week. Its a little slower but I get to sleep. Additionally with the new method I am able to do more all at the same time-like rebuild the entire dataset for a partition.

Here are my steps for doing a change that takes more then 10 hours:

remove the servers from the site config
push my.cnf.maintenance to the servers …

One performance gotcha with MEMORY tables you might know about comes from the fact it is the only MySQL storage engine which defaults to HASH index type by default, instead of BTREE which makes indexes unusable for prefix matches or range lookups. This is however not performance gotcha I'm going to write about. There is one more thing you should be aware which again comes from the fact MEMORY tables use HASH indexes by default.

I've created rather similar test table:

PLAIN TEXT SQL:

1. CREATE TABLE `test` (
2.   `id` int(11) NOT NULL AUTO_INCREMENT,
3.   `c` tinyint(4) DEFAULT NULL,
4.   PRIMARY KEY  (`id`),
5.   KEY `c` (`c`)
6. ) ENGINE=MEMORY

and populated it with 1.000.000 rows ALL of them having same value for c column.

Now I'm performing random deletes by primary key (DELETE FROM test WHERE …