On Tue, 2005-07-19 at 16:07 -0400, Landon Jones wrote:
> I have a mysql database with 150 tables, but with most of them only
> having a few thousand rows and only a couple in the hundreds of
> thousands of rows. My problem is that when I get two many rows the
> performance falls off considerable. I added several indexes and that
> helped fix the problem. But then the number of rows in one particular
> table increased to a little over 800,00 and the performance took a huge
> hit. I cleaned out the table down to 90,000 rows and all was good. I
> believe the issues was that the total size of the database would no long
> fix into memory.
If performance falls a significantly with data growth you're describing
this often means there are some problems with query design, schema
design or indexing. If you would provide query among EXPLAIN plat
for it I guess you will be able to get more help.
When database does not fit in memory any more you have to optimize
better. Especially for light load when database fits in memory almost
anything works - scanning 1.000.000 rows would typically take fractions
of seconds in MySQL.
> How can I determine how much total memory mysql requires for my
> database? How can I tell what is the max memory it can use?
If you want full database to fit in memory you can take a look at
database footprint on the disk. Typically you need less than that, how
much less however depends on your workload.
Peter Zaitsev, Senior Performance Engineer
MySQL AB, www.mysql.com