Oh sh1t.... guessing I'm crap with sql
Discussion
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....

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....

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
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
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.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
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 bo
ocks 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
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
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?
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 said:
Ex, I'm not a DBA, but I've got ask... the box you took the top from, has it melted yet?

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

Now if I turn on 20 HTTP clients requesting info from the DB...... :snigger:
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....Lots of different indeces I take it?
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
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.
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.
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?

I dunno if it's melted, the box is in the shed

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

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).
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).
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.
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.
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.
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.

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
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,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.
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
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.

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.

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.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.
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.
Interesting thread though....mutters as he wanders back to his 90,000+ lines of PHP code...
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
Gassing Station | Computers, Gadgets & Stuff | Top of Page | What's New | My Stuff




it's all cpu, 4 external processes pumping data into the DB...