Impresive MySQL stats August 3rd, 2008

So I’m doing something that relies on MySQL a lot. Last night I had to test a rebuild process that we would use if there was a failure in our systems. Worst case secnairo would be we would have lost about a weeks data and need to reinsert the whole lot.

So I wrote a quick and nasty PHP script with multiple nested foreach() loops, yeah I know what your thinking, but as I said, it was a quick and nasty script. Basically all this script done was parse a directory full off XML files and based upon the content, perform multiple SELECT, INSERT or UPDATE based on the data per element in the given XML file.

So, on my little iMac (24″, 4GB RAM, 1TB Drive) the following are the stats from the completition of the script.

Total Processing Time: 4975.59566307 seconds.
Total DB Inserts: 1,961,000
Total Selects: 12,035,743
Total Updates: 10,465,071

This all translates to;

Inserts Per Second: 394.12
Selects Per Second: 2,418.95
Updates Per Second: 2,103.28

Keep in mind, I was watching a movie at the same time and that there has been no optimisation carried out on the server at all (Although the DB is fairly well normalised). Table format was MyISAM.

Now imagine what MySQL could do on an optimised Linux server with custom optimisations to make it faster. I am quite impresed given the workstation I was working on and the complexity of the queries we are doing. Pat on head for MySQL. Well done.