Re: Performace of a database with a lot of tables

[Dan Buettner]

Olexandr, I fear that model may not scale well.  Certainly having data
spread across so many different tables reduces the effectiveness of memory
caching.

I recommend you alter the design to have 30 tables total, each with a column
for user id, rather than 30 tables per user.  This may improve performance
by allowing memory caching to be more effective.  If you index the user id
field performance should remain good even though the tables will be larger.

Dan


On 2/9/07, Olexandr Melnyk <omelnyk@stripped> wrote:
>
> Hello,
>
> I am working on hosting solution for a popular open source script, where
> users can sign up and get an own forum. We use InnoDB, and each user has
> an
> own set of tables (around 30); there are currently around 500 users
> registered. Despite the large count of tables, database is small, around
> 50
> MB.
>
> Server is dual Xeon with 4GB of RAM and MySQL uses all memory, available
> on
> the server, so there's even nothing left for Apache.
>
> As the user base is growing, what steps would you suggest me to do, in
> order
> to keep good database performance and make it more efficient on RAM usage?
> The former is more important.
>
> ------------------------
> Olexandr Melnyk,
> http://omelnyk.net/
>