Re: At Performance Limit for 2 GB RAM?
Jay-
Took a long weekend, so now I am getting back things.
> Cool. Thanks for clearing that up. It wasn't all
> that clear to me from the schema...
No problem.
> so I don't think that either query should
> return in more than a second...
That was what I was feeling.
> On a side note, if proper indexing is in place,
> queries will not take a linear growth pattern in
> processing time; that is, if you double the
> records, it won't double the query time...*if*
> indexes are current and on the appropriate
> columns.
> I think you mean it is constant, not linear,
> correct? You mean that the query seems to take
> the same amount of time, regardless of the number
> of records.
What my tests have shown is that until you reach a threshold where the index is larger than the key_buffer_size the performance is independent of the total number of rows in the table, but linearly dependent upon the number of rows being retrieved. (This is where I came up with the 800,000 rows per second this is the number of items in the results set.)
> Have you considered increasing the
> key_buffer_size? Perhaps what is occurring is
> that MyISAM is thrashing disk and memory resources
> because it cannot fit all the index blocks for the
> 20 million rows in the table, and therefore is
> releasing blocks from the key cache in an LRU
> strategy in order to make room for the blocks
> needed by the query. If this is happening, then
> this can seriously impact performance, as MySQL is
> working double-time to do the key searches.
I have some latitude to increase the key_buffer, but at this point I don't know that it will have anything more than an incremental improvement since the index of the 200 million records is 3.1 GB it is clear that there will be some thrashing regardless of how large I am able to set the key_buffer. Additionally I am currently working in an issolated environment. We are testing this design with the intent of moving it to a production server that already has significant database loads. If I can't get the performance under control with a dedicated server and cache of 700MB, I don 't see how it is going to be any better with at most 1.5GB of cache being polluted by other database loads.
Hope this info helps clarify things and thanks again for all of the feedback.
Nick