query performance profiling

[Sxooter]

You must be thinking of cursors.

You can do (in postgresql or oracle)

declare cursor bubba as select * from bigasstable;
fetch 10 from bubba;

and it won't grab the whole table.

It'll grab it in pieces as you ask for it.

But the resource returned for select * from bigasstable is a resource to the whole dataset, and you can't return a resource to a set that isn't there.

[sneakyimp]

my point is why make a copy of bigasstable if it's already there?

[sneakyimp]

And check this out:

SELECT * FROM demo_events LIMIT 254000,1
TIME EXPIRED:3.28928494453 seconds

surely the index would help in this case? that seems WAY too slow.

[Sxooter]

See, the database is here, on box A, and the php server is here on box B. you run select * from table from php. PHP now has to grab that data and move it across the wire from box A and materialize it on box B so you can then do the whole "mysql_fetch_row" boogie on it.

If you want to move a bit at a time from a big table, then you need cursors. But you can't use cursors in a subselect.

[sneakyimp]

Hm. MySQL and PHP are on the same box. Does that mean we still have to cart all 173 MB of data from one spot on disk to another? And what about my query cache? It's that slow EVERY TIME.

Also, what about that last example? It's only 1 record!

[Sxooter]

Yep, it gets materialized (and in php land) copied across each time. As for the limit 254000,1, yeah, an index might help. But what you're really telling the database to do is grab all 254000 rows and throw away all but the last one. While for select * from table limit might be an easy thing to optimize, more complex queries aren't as we've seen earlier in this thread.

[sneakyimp]

Sxooter, you are THA MAN. It's starting to make a little sense now I guess. I'll probably try to upgrade soon to mysql 5 or something. Maybe try some subquery approaches. That way, this big slow handover might not be necessary....or something. Maybe calculate some indexes on a regular basis to make these random queries faster--if possible.

Still, my table is indexed and that limit query (select from table limit 250000, 1) is still REALLY slow. Like 3 seconds slow. I'm guessing that is due to a slow disk? Not enough RAM? How can I speed that* up?

[Sxooter]

That limit 254000,1 will never be fast, and it will continue to get slower as you scale your application up. Faster discs, more ram, all that might help, but when you get to a large enough dataset you won't be able to use that anymore. It's a bad way to get what you want. If you have a lookup table for it you can index that, and have a no gap sequence of numbers there, it's much faster.

For instance, suppose big table has 254000 records, with various scattered id numbers, like 1,200,450,230,124,4000,1234 and so on (assuming you're using order by to get the order you like, that's the sequence we see of ids) You make a lookup table like this:

create table lookup (id serial primary key, bigtableid int);
insert into lookup (bigtableid) (select bigtableid from bigtable order by somethingorother);

Note the use of a postgresqlism there, the serial type. It's autoinc in mysql...

Now, you can do something like:

<?php
$count = 254000;  # you can get this by doing a select max(id) from lookup...
$i = rand(1,254000);
$query = "select * from bigtable where bigtableid=$i";
# more code here...
?>

[Roger_Ramjet]

I've been watching this thread with interest, but too busy to get involved in a worthwhile way. And Sxooter had everything covered that I might have thought of.

Point 1: why is edit_id edit_id varchar(20) NOT NULL default '',? Not that this is the cause of the problem.

Yes, simple table select should be fast and efficient - and yours is not. When it is not then I would look to server problems not design problems.

The first thing I would advise you to do is to recreate the table and indexes and to then copy your data across.
Now, I don't know how one defragements a mysql database or reindexes it - all my real db work is on Access and SQL Server - but efficient hard disk storage is always a must.
Getting your data in primary index order is always going to pay off so given the lack of inbuilt utilities in mysql one just does a select into new table ordered by primary key and then drops the old table, recreate all indexes and start from there.

Then see what the benchmark for SELECT * is. If it is still bad then look to your host server for the root of the problem.

Also look through the bug-fix documentation for later versions - this may be something that forced a new build version. If so then upgrade.

Let us know the outcome of a table rebuild etc.

[Sxooter]

Yes, simple table select should be fast and efficient - and yours is not. When it is not then I would look to server problems not design problems.

Ummmmm, sorry Roger, but I'm gonna have to disagree here. A simple table select of EVERY row can only be as fast as the hardware the query runs on, and generally should be avoided unless needed.

For instance, I've got a table with 8,208,570 rows, and each row is an average of about 2k. That's about 16 gigabytes of data. A select * from that_table takes about 66 seconds. Most of that time is spent reading from the hard drive. That's hundreds of megs a second from the hard drive.

Sure, for a table with 20 or 100 rows, a select * should be pretty much immediate. But one should avoid selecting large number of rows only to throw away 99.999 percent of them in the next step. It's an inherently inefficient design, and will never scale to any real number of entries in your database.

If you want a solution to this kind of problem to scale, you've pretty much got to abandon the select * from bigtable approach.

[sneakyimp]

Roger Ramjet wrote:

Point 1: why is edit_id edit_id varchar(20) NOT NULL default '',? Not that this is the cause of the problem.

The edit_id was a random id that allows a user to edit or activate the events they have entered. I wanted to be random so that user's couldn't skip the email validation step as spammers might start abusing the site. For what it's worth I tried removing this index to see if it would speed things up. It did a little.

I also tried the test you just described...creating a shiny new table from the potentially fragmented old one. I used phpMyAdmin's "copy table" functionality which in truth does just what you explained--selects into a new table with a different name and its own index file. I tried deleting and re-adding the indexes.

The only thing that really seemed to help was reducing the number of fields (and thereby the size) of the table in question. For instance, when i removed the 'info' field which is type TEXT, the query went from 10 seconds to about 5. I then removed ALL of the varchar fields and the speed of the query went to about 2.5 seconds. The speed of the SELECT * query seemed to be a roughly linear function of the size of the table.

Consider the sizes of the files. test_events is the same as demo_events but with all the text/varchar fields and their various indexes removed:

devserver:/var/lib/mysql/foo# ls -l *_events*
-rw-rw----  1 mysql mysql      9469 2007-01-25 14:29 demo_events.frm
-rw-rw----  1 mysql mysql 173176888 2007-01-25 14:29 demo_events.MYD
-rw-rw----  1 mysql mysql  10510336 2007-01-25 14:30 demo_events.MYI
-rw-rw----  1 mysql mysql      9242 2007-01-25 14:33 test_events.frm
-rw-rw----  1 mysql mysql  12446049 2007-01-25 14:33 test_events.MYD
-rw-rw----  1 mysql mysql   2082816 2007-01-25 14:33 test_events.MYI

I think Sxooter may be right (which is a real bummer actually). It means I have to be really sneaky.

And by the way, the development server is really slow as I've described before. P3/800 Mhz with an old school bus that connecting to the hard drives.

[Sxooter]

sneakyimp wrote:

I think Sxooter may be right (which is a real bummer actually). It means I have to be really sneaky.

Dude, it's not me that's right so much as the laws of physics...

Alright, how many possible eids do you have for a user? Is it possible to build a table that has just userids -> eids in it and is updated everytime your big tables are updated?

Then, you can just do something like:

select eid from lookuptable where userid=1234 order by random limit 1

and should get good performance. then use that eid to pick from the big table with.

[sneakyimp]

There are two concepts for users.

1) Users can come search for something to do - in which case ALL events should be considered.

2) Users can post events--like if they have a band that is playing. I don't need any random selection for this type of user.

Case 1 is the primary function of the site--letting users search for something to do. I can limit the time frame or geographic location or various other indexable values, but it would appear that we have ruled out values which are not indexable because they cause a table scan. This search step is what I want to pick the random eids for.

My biggest increase in speed before was gained from selecting the huge result set and performing my own random selection using PHP code. It would seem that I might be able to get around that if I am somehow able to maintain a lookup table with two fields: id and eid. If id is packed from 1 to N with no holes, I can just pick a few numbers between 1 and N and then use a query like

SELECT eid FROM table WHERE id IN (n1, n2, n3, n4, n5)

Does that sound right? What I'm wrestling with now is how to handle the selection of 5 events randomly from a search user's zip code. Obviously it is not practical to have one lookup table for each of my 43,000 zip codes is it?

Also, is there somewhere official where I can look to have it described to me how MySQL copies large result sets for PHP's use? I wasn't aware of this at all and feel like I was caught with my pants down. I'd like to know more about it if I am to correspond with those code review guys again.

[Sxooter]

I think you're headed in the right direction. You might be able to set up a lookup table for dates as well, and use a query on that to narrow the ids to look through.

[sneakyimp]

Well I'm still really worried about my main search query which isn't picking random events but rather searching this huge database for events matching certain criteria including:

1) date window
2) time of day
3) proximity to a particular zip code
4) cost
5) category/subcategory

I guess the rules of thumb I can bank on are
avoid table scans at all costs
use only indexed fields in joins or where clauses
* don't ask for giant record sets

HOWEVER my indexes are quite large in some cases. Is there some way to keep them active in memory so the disk doesn't slow things down? Might there be mysql parameters I can tweak for better performance? My server is basically using the defaults.

And what about some information on how MySQL and PHP share data? Can anyone recommend some reading there?

[Sxooter]

I'm guessing that you might get better performance with a star schema

http://en.wikipedia.org/wiki/Star_schema

[Roger_Ramjet]

How do you arrive at the 'proximity to a zip code'? Seems to me that getting that subset first will reduce the rest to a mnageable number of records.

Big indexes means high cardinality and the higher the cardinality the more benefit from using an index: each index entry will point to fewer records - generally of course. DB engines construct execution plans based first on the cardinality of the indexes involved for just that reason, so don't worry about that. The db engine does not read indexes sequentially either, unless they are very small, they use more sophisticated algorithms than that. They will also try to keep them memory resident, as does the operating system using lazy-write.

Dunno about any books but you should spend some time reading the mysql manual sections on how it uses indexes and memory etc.

[sneakyimp]

Sxooter wrote:

I'm guessing that you might get better performance with a star schema

http://en.wikipedia.org/wiki/Star_schema

I read that entry. I'm not sure I understand what it means unless
a) we are reducing the size of tables by breaking them into smaller and smaller bits.
b) that somehow different tables get stored on different machines to bring more processing power to bear on the situation.

I suspect point A is what the intent is. Sadly, a JOIN between two large tables (e and eta in my examples) is one of our biggest performance killers.

Sxooter, If you could spoon feed me by paraphrasing that article I'd sure appreciate it.

Roger, I hear what you are saying. But the big JOINs are troublesome. I calculate proximity to a zip code at the moment with an elaborate query that performs a great circle distance calculation from the latitude and longitude of a particular zip code. I'll probably reduce this to a much rougher but faster simple comparison of long and lat. Additionally, rather than trying to JOIN my event table to the zip table, I will simply de-normalize my DB yet again by storing the long and lat in the main event table.

[Roger_Ramjet]

sneakyimp wrote:

I calculate proximity to a zip code at the moment with an elaborate query that performs a great circle distance calculation from the latitude and longitude of a particular zip code.

When and where are you doing that? Is that data stored in a table or are you doing it on the fly?

[Sxooter]

From what you've posted recently, I'm not so sure a star schema would help. The primary reason to go with one is that you can easily view various dimensions of your data by including various tables for each one. For what you're doing, I'm not sure it would help.

OK, which is the MOST selective of your criteria, generally? Is it zip code, category, etc???

We need to focus on that first. If it's zip code, then we need an efficient and fast way to find all the entries in a particular zip code.

To do that, you can use some kind of simple lat/long based bounding box to do an initial search. I.e. every zip gets a rough max_lat,min_lat,max_long,min_long and you see if the event's lat/long get it inside a box or not with a very simple AND/OR in a where clause. (or use a db which can store geometric data types and use the overlap operator... ahem... 🙂 )

If it's something else, let me know. Basically, whichever thing will reduce your number of options the mostest the fastest, we want to optimize the firstest...