Re: At Performance Limit for 2 GB RAM?
Jay-
Thanks for the additional input. I am uncertain about my initial results regarding the ordering of (stn_id and metadata_id). All of the tests I have completed within the last day and a half indicate my response to KimSeong was incorrect and that stn_id did perform better. Marginally for the Innodb backend and dramatically for the MyISAM backend. A difficulty I am having with the results is how poor the MyISAM with (metadata_id, stn_id,...) performs.
Here are the results from a subset of data (20 Million Rows):
Engine Index Avg. Time
MyISAM (stn_id, metadata_id) 0.25s
MyISAM (metadata_id, stn_id) 20.3s
InnoDB (stn_id, metadata_id) 0.3s
InnoDB (metadta_id, stn_id) 0.5s
I can accept that the other indexing arrangement is superior, but I am troubled by my lack of understanding as to why the other results are so far off. They seem to be stuck just above the 20s barrier and the InnoDB database doesn't seem to have the same affliction.
I guess I need to explain a bit of what I am doing. The original reason for creating y_metadata_temp table was to break a more complex query into pieces for trouble shooting. The first part of the query selects a group of metadata rows based on a number of factors, date, time offset, issue time, etc. When I broke the query apart I saw that the time required to retrieve these items was trivial and that the problems seemed to be with the y_statsinfo table.
For the sake of argument I have been indexing the temporary table on my benchmarking runs, but don't see a terribly significant change. Also I have been using a count query to benchmark the performance as well. They show the query time to be about 5 secs and in reality the summarized data is what is needed in the end.
And in terms of the Dell white paper here is a brief run down:
Tables
------------------------------
Customers (200M rows)
Orders (120M rows)
Orderlines (600M rows)
Products (1M rows)
Inventory (1M rows)
The Tests
------------------------------
85 Threads
80% Of Users Are Returning
20% Of Users Are New
1-6 Product searches
1-9 Items Purchased
0.25 Delay Between Thread Requests
Well I think that answers all of the questions posed and here is what I am still trying to figure out:
1. Explain why MyISAM performs good with one index and very poorly with the other.
2. A stronger feeling as to if my search for a query from 250-500M rows in under 5 secs is reasonable. My results right now are returning that queries take between 1 and 5 secs with the numbers lower as the cache becomes more saturated. The problem is that I am testing on an unloaded development machine. I am wondering how this is going to play with other queries. Running 20 threads with the queries from the 200M row table resulted in response times of 30 seconds which is well into the unacceptable range.
Anything else I can try would be appreciated.
Thanks,
Nick