Oh sh1t.... guessing I'm crap with sql
Oh sh1t.... guessing I'm crap with sql
Author
Discussion

TheExcession

Original Poster:

11,669 posts

281 months

Tuesday 5th June 2007
quotequote all
This is a Linux Top from my 'pants' development server - a 900MHz P-dunnonwhat, 2MB RAM....

We is REALLY hoping that our spanking new 3GHz servers are going to perform a bit better than this...
so...

if(brand_new_server.performance() <= pants_server.performance())
{
system.out.println("Ex is out of a job " ) ;
}else if (brand_new_server.performance() ~= pants_server.performance() )
{
system.out.println("Ex needs a DBA " ) ;
}else{
die() ;
}

For info: my box (3 GHz Pentium 4) running 4 auto-refreshing web pages is inserting records into the db (db is on separate box) at the rate of one every 50ms... my Pc is hardly breathing....


TheExcession

Original Poster:

11,669 posts

281 months

Tuesday 5th June 2007
quotequote all
Basically this is all about inserts into the DB, I'm trying to push about 4 inserts every 50 milli-seconds.

The inserts require a lot of lookups into other tables, is moving some of this code into stored procedures gonna help (We are running MySQL 5.xx) at the moment all te hardwork is being done in PHP.

Thus every insert requirs several lookups into very heavily indexed but static tables, finally there is one look up to try and find a matching event that is already in the events table.

I've played about with indexes on the event table, but it's a compromise between the insert and the possibillity of a lookup later.

Are Stored Procedures much cop in MySQL?

cheers
Ex

cottonfoo

6,053 posts

241 months

Tuesday 5th June 2007
quotequote all
Your load average is rather high, perhaps look for issues elsewhere. Is it IO bound at all?

Don

28,378 posts

315 months

Tuesday 5th June 2007
quotequote all
TheExcession said:
Basically this is all about inserts into the DB, I'm trying to push about 4 inserts every 50 milli-seconds.

The inserts require a lot of lookups into other tables, is moving some of this code into stored procedures gonna help (We are running MySQL 5.xx) at the moment all te hardwork is being done in PHP.

Thus every insert requirs several lookups into very heavily indexed but static tables, finally there is one look up to try and find a matching event that is already in the events table.

I've played about with indexes on the event table, but it's a compromise between the insert and the possibillity of a lookup later.

Are Stored Procedures much cop in MySQL?

cheers
Ex
Can't help you with MySQL specifics. However within the Microsoft world I can tell you that comparison languages ASP (php equiv) and TSQL (stored proc) should have similar properties.

TSQL is vastly, vastly more performant than ASP. Errrm. Order of magnitude at least. The reason is mainly because the TSQL executes in the SQL Server process and you don't have all the setup, inter-process communication, nonsense and boocks overhead that calling out of one language, building a datastream, sending it to a listener, having it parse it, then do it, then return a datastream all across processes entails. The TSQL just executes natively "inside" the database in the same way as a SQL statement would.

So if you could formulate an interface wherein instead of having many PHP calls out to MySQL you could have one with the minimum of parameters and then do all the looking up, checking and whatnot happen in a stored proc before returning success or failure to the PHP you should get a good performance boost.

Of course: your mileage may vary etc etc. Hope that helps.

I once earned a living as a DB performance trouble-shooter. I was bloody good at it. Then machines got properly fast and no-one gave a shit anymore.

Edited by Don on Tuesday 5th June 17:57

JamieBeeston

9,294 posts

296 months

Tuesday 5th June 2007
quotequote all
Don said:
I once earned a living as a DB performance trouble-shooter. I was bloody good at it. Then machines got properly fast and no-one gave a shit anymore.
Ain't that the truth..

The amount of ongoing MySQL performance issues I have to deal with on various pieces of high End kit that still push the envelope are not fun ;(

J

Don

28,378 posts

315 months

Tuesday 5th June 2007
quotequote all
Hmmm. Just read your second post. The table you are inserting into is heavily indexed.

Lots of different indeces I take it?

Do you really need to real-time insert into that table? If you (temporarily) drop all the indeces does it get a LOT faster?

If so you may need to look at storing the records you are inserting somewhere else and periodically (and in a different process) copying them over.

However: in these days of blisteringly fast databases I suspect its the overhead of setting up transactions with the MySQL server. Since neither PC is busy this is more of a latency problem than a performance problem. Reduce the number of transactions and things should get better. Can you do that by introducing stored procs?

ThePassenger

6,962 posts

266 months

Tuesday 5th June 2007
quotequote all
Ex, I'm not a DBA, but I've got ask... the box you took the top from, has it melted yet?

TheExcession

Original Poster:

11,669 posts

281 months

Tuesday 5th June 2007
quotequote all
cottonfoo said:
Your load average is rather high, perhaps look for issues elsewhere. Is it IO bound at all?
No shit! biglaugh it's all cpu, 4 external processes pumping data into the DB...

read on and I'll clarify the situation wink

TheExcession

Original Poster:

11,669 posts

281 months

Tuesday 5th June 2007
quotequote all
ThePassenger said:
Ex, I'm not a DBA, but I've got ask... the box you took the top from, has it melted yet?
hehe

I dunno if it's melted, the box is in the shed, I can't stand the fan noises.... (see my previuos post months ago about running CAT5 along side the elctric fence.)

So far I have four copies of Firefox inserting data rows at a minimum of 50 milliseconds per row, or basically as soon as the browser gets a response from the insert it (Java Script) sleeps for 50ms before starting the next insert.

Must admit it's settled down a bit now, 1 min load avereage is 17.60 headache

Now if I turn on 20 HTTP clients requesting info from the DB...... :snigger:

TheExcession

Original Poster:

11,669 posts

281 months

Tuesday 5th June 2007
quotequote all
Don said:
Hmmm. Just read your second post. The table you are inserting into is heavily indexed.

Lots of different indeces I take it?
Yes and no....

The table has about 65 columns, only the fist five are indexed now, indexing 30 of the remaining 60 columns didn't have too much of an impact.



Don said:
Do you really need to real-time insert into that table? If you (temporarily) drop all the indeces does it get a LOT faster?
I dropped the indexes on the additional 30 cols without much effect!

Don said:
If so you may need to look at storing the records you are inserting somewhere else and periodically (and in a different process) copying them over.
We are considering this architecture... but this kind of change will only really be done after we have some stats of how the users query the DB.

Don said:
However: in these days of blisteringly fast databases I suspect its the overhead of setting up transactions with the MySQL server. Since neither PC is busy this is more of a latency problem than a performance problem. Reduce the number of transactions and things should get better. Can you do that by introducing stored procs?
I agree, I'm running this on an OLD Dell box, 900MHz CPU with 2GB RAM running Centos4, MySQL 5.xx

[root@NAD /]# free
total used free shared buffers cached
Mem: 2075952 696804 1379148 0 47084 359780
-/+ buffers/cache: 289940 1786012
Swap: 2031608 0 2031608
[root@NAD /]#


We have three lovely jubbely Dual 3GHz Processor HP Boxes with 4GB RAM and much faster disks for the final platform. That will surely help.

Plus I'm pushing the inserts to the max...

See post below.

thanks for your input Don!
best
Ex

TheExcession

Original Poster:

11,669 posts

281 months

Tuesday 5th June 2007
quotequote all
ok,

bit of background.....


Central DB accepts HTTP requests for an insert into the event table based upon information seen on the satellite network.

This involves PHP processing the event info and deciding which fields in the input data go into which coulmns of the event table.

I'm driving this with a full 60 fields of information, so each event insertion requires 60 lookups to determine which coulmn in the event table a piece of information should go into. Thes lookup are on static data tables and these tables are indexed on every field - they'll propably change once or twice a year)

As a guestimate, in the real world I doubt we'll do more than 20 of these lookups.

Once all the correct columns are decided, then an SQL statement is created (as a text string in PHP) and this is then inserted into the event table.
Next a lookup into a rules table is made based upon paramters in the initial insert. If a rule fires, then there is a lookup into the event table to try and find a corresponding event and this info is written to a second table - (basicaly it just matches the event IDs of the incoming and matched event)

I'm going to move some of this to stored procedures as soon as I get time.

But always it's good to gather some metrics as you go down the development path so you can see if things are really getting better or worse as you bolt on new ideas.





ThePassenger

6,962 posts

266 months

Tuesday 5th June 2007
quotequote all
TheExcession said:
ThePassenger said:
Ex, I'm not a DBA, but I've got ask... the box you took the top from, has it melted yet?
hehe

I dunno if it's melted, the box is in the shed
~~Passenger is seen running to the shed, pack of burgers in hand yelling about a Bar-B-Q~~ biggrin

If the weathers anything like ours here on the south coast, that machines cooking, I'd get it to do a verify on the data it's written hehe

rebelstar

1,146 posts

275 months

Tuesday 5th June 2007
quotequote all
If it's any help, I don't think it's I/O bound. Your 'top' only shows 0.3% wait.

You are running with >90% user time though, so the good news is you may be able to do something about this. Your idea of moving things to stored procedures is a good one. Also consider using prepared statements to do the insert, and committing them as a single batch if possible. Admittedly I don't know how to do this in PHP (I'm a Java guy).


Don

28,378 posts

315 months

Tuesday 5th June 2007
quotequote all
OK.

For what you are doing TSQL etc may be a way to do it as you can do all your lookups in-process inside the database.

BUT

What I'd do is abandon the PHP logic and get closer to the metal. On IIS you can write ISAPI DLLs that run in-process in C++ dunno what's to do in Apache but something I'd guess. Your lookups? Hard code them into standing data right there in the C++. Then the only thing you need to send to the DB is the finished INSERT statement. Looks like doing that with plenty of indeces isn't the problem so its got to be deriving the INSERT statement from the standing data.

Now you have your prototype code it in something truly fast and your performance blues should go away.

Have the DLL(whatever it is in Linux) talk to MySQL via the lowest level API you can find.

If I were you I'd start with the stored procs. If it isn't quick enough investigate something closer to the metal. In my (Windows/Microsoft) world I can use ISAPI DLLs and I can even call out from stored procs to machine code if required.

TheExcession

Original Poster:

11,669 posts

281 months

Tuesday 5th June 2007
quotequote all
rebelstar said:
If it's any help, I don't think it's I/O bound. Your 'top' only shows 0.3% wait.

You are running with >90% user time though, so the good news is you may be able to do something about this. Your idea of moving things to stored procedures is a good one.
biggrin

rebelstar said:
Also consider using prepared statements to do the insert, and committing them as a single batch if possible.
This is a none starter, unfortunately any incoming event info needs to be parsed such that I know which columns to insert the data into. I cna however preprepare some of the later SQL as stored procs which we intend to try.

rebelstar said:
Admittedly I don't know how to do this in PHP (I'm a Java guy).
I'm a Java guy too - I feckin hate HTML, PHP and SQL... still where there's muck there's brass...

thanks for the post!
best
Ex

TheExcession

Original Poster:

11,669 posts

281 months

Tuesday 5th June 2007
quotequote all
Don said:
OK.

For what you are doing TSQL etc may be a way to do it as you can do all your lookups in-process inside the database.

BUT

What I'd do is abandon the PHP logic and get closer to the metal. On IIS you can write ISAPI DLLs that run in-process in C++ dunno what's to do in Apache but something I'd guess. Your lookups? Hard code them into standing data right there in the C++. Then the only thing you need to send to the DB is the finished INSERT statement. Looks like doing that with plenty of indeces isn't the problem so its got to be deriving the INSERT statement from the standing data.

Now you have your prototype code it in something truly fast and your performance blues should go away.

Have the DLL(whatever it is in Linux) talk to MySQL via the lowest level API you can find.

If I were you I'd start with the stored procs. If it isn't quick enough investigate something closer to the metal. In my (Windows/Microsoft) world I can use ISAPI DLLs and I can even call out from stored procs to machine code if required.
Don,

Thanks for the post, but the misses has the chess board out, so I need to swap system disks for a while and go drink more wine!

I'll be back (tomorrow)
best
Ex

dilbert

7,741 posts

262 months

Tuesday 5th June 2007
quotequote all
I read this thread, and refrained from posting because I know nothing about high level database implementations, but thought along the lines of what Don said. That I understood.

Like I say, I don't know much about all this database stuff, but I have some nice nippy hashes/lookups that I wrote in C++. I don't know exactly how fast they are, but I think that would depend largely on who's heap you use....... The heap I wrote isn't too shabby either.

If you want some performance/timing stats, let me know what you're trying to do in more detail, and I can generate them. You can probably get this sort of stuff off the shelf, but that's not the way I think.

If you're interested, we can work out how many beers it's gonna cost.

hehe

deckster

9,631 posts

286 months

Tuesday 5th June 2007
quotequote all
Don said:
OK.
What I'd do is abandon the PHP logic and get closer to the metal. On IIS you can write ISAPI DLLs that run in-process in C++ dunno what's to do in Apache but something I'd guess. Your lookups? Hard code them into standing data right there in the C++. Then the only thing you need to send to the DB is the finished INSERT statement. Looks like doing that with plenty of indeces isn't the problem so its got to be deriving the INSERT statement from the standing data.
Agree 100%. The data isn't the problem, it's all the database roundtrips. Cache the static data in-process and you should be fine.

page3

5,204 posts

282 months

Tuesday 5th June 2007
quotequote all
TheExcession said:
I'm a Java guy too - I feckin hate HTML, PHP and SQL... still where there's muck there's brass...Ex
Pah! PHP's a wonderful elegant language.

teacher Interesting thread though.

...mutters as he wanders back to his 90,000+ lines of PHP code...

TheExcession

Original Poster:

11,669 posts

281 months

Tuesday 5th June 2007
quotequote all
page3 said:
TheExcession said:
I'm a Java guy too - I feckin hate HTML, PHP and SQL... still where there's muck there's brass...Ex
Pah! PHP's a wonderful elegant language.

teacher Interesting thread though.

...mutters as he wanders back to his 90,000+ lines of PHP code...
Actually, I'm being a bit hard on PHP, I don't mind it, I like the associative arrays and a few other bits and bobs, what gets me is embedding HTML and SQL into it all, plus I thought I'd left procedural languages behind years ago.

There was a post here a while back with a link comparing PHP to PERL. It made me wonder if I'd jumped aboard the wrong ship when we chose PHP, but my logs and monitoring indicate that it's not Apache and PHP that are causing the problems, it's all in the DB. (Maybe the PHP-MySQL interface, I'm not certain yet)

Frankly, I'm crap at web design, I'd love us to have the money to get someone else in to do it. I just haven't shovelled enough muck yet!

Well, glad you think it's an interesting thread, I guess it does make a bit of a change from 'My internet connection doesn't work' or 'How do I get my WiFi going!'

I'll be back at all of you soon for some tuning tips once I know what it is that I need to be tuning!

best
Ex