[ale] LAMP performance

[Mike Harrison]

On Mon, 13 Sep 2010, mike barnes wrote:

> I have been asked to come up with a solution for a 6 sigma application that
> can have up to 1 million transaction/hour.

"6 Sigma" - That's vague, but I am assuming you doing something measuring 
a manufacturing process.

Transaction - reading the other e-mails, it sounds like what you call a 
transaction is simply a database select, update or insert.
A "transaction" is often a term used for a set of database transactions
that consists of an event. It might be 50 reads, 2 inserts and an update
to complete a transaction, there may even be external soap calls or other
events to make up a transaction.

300/second or 1 million per hour is trivial for MySQL on good hardware...
especially if they are small (narrow) tables.

Assuming you are using InnoDB and there are simultanous reads and inserts,
it's still trivial.

Hardware is cheap, go drop $25k on a loaded intel modular server or
some other monster with insane amounts of ram and forget that whole 
problem.

Your issue is going to be software and database design.

This weeks example:
Months ago I paid a college edumacated and very intelligent whiz-bang
programmer to write a module that imported a bunch of fat poorly laid 
out XML (almost redundant, there is clean XML). He used the latest
object oriented techniques and libraries and it worked well until it hit 
scale. It's been taking two+ hours to import 30k records. I couldn't 
figure out his code, too abstract for an old procedural dog like me..
he used cursors and stuff that probably works really well on small 
datasets. Rewrote it over the weekend in PHP (he was using Ruby and 
Python). On my laptop where I am playing with it right now, I can parse
a 25MB XML file, extract 6000 main records and 25k supporting data 
records... in under 10 seconds. Into MySQL.

Each of the 6k main records takes 3 MySQL selects, a little math,
and 1 insert..

The supporting records are straight small table inserts..
so that is just 25k inserts.

So, 18k reads, 6k big fat table inserts, 25k inserts..
Lets call that 50k (rounded) SQL operations....
Lets call it 20 seconds just to be safe..
Lets call that 2500 SQL operations per second.

On my Laptop w/4GB of ram, running X and FireFox and..
in and out of a database that is > 1gb and the main table
being inserted into has > 22 million rows so far. 
------------------------------------------------------------


> Since, I have not done a lot of work in the LAMP arena, I thought that I
> would see what issues I will be having using the following platform.


My point was, the hardware is a trivial problem. Easily solved
by spending some money on hardware. There is good hardware advice
on this list..

How you design and code the system is the challenge.
Even the tools (PHP,Perl,Python,Java(yech),C,VB..)
don't matter to performance as much as the programmers
ability to use them.

Cripes, some of the ale crowd could probably do it in
bash, grep, awk and sed.

(I watched a flintknapper make a knife and whittle a doll last summer...
out of some rocks and deadwood.... made my ashamed of my nice sharp 
pocketknife I can barely use..)