On Saturday 19 October 2002 10:53 pm, Jesse Guardiani wrote:
> Howdy,


> I recently signed up for the vpopmail mailing list, and I asked this
> question there, but I don't think I got a suitable answer in return.
> So, I wonder if the designer of vpopmail would be kind enough to educate
> regarding the default table structure that vpopmail uses?


> First off, I'm very hesitant to run vpopmail with a monolithic table
> structure. It just seems like this table would get amazingly huge with
> hundreds of domains and ten or twenty users in each domain. It seems like
> individual tables would be much cleaner and more managable. Not all of the
> tables would have to be open at once, as would be the case with a
> monolithic table.
> When I made this comment (which, BTW, may indeed be ignorant. I don't
> pretend to be an expert. I haven't even come close to stress testing
> vpopmail/mysql with that many users yet. This is all speculation on my
> part, and I realize that.) on the the vpopmail mailing list, a gentleman
> replied that he thought mysql has a 64 table limit for simultaneous open
> tables. But after reading the documentation for mysql's current stable
> version, *I* didn't get that impression at all! It seems to me that this is
> a function of the max_table_cache variable (and a few others), and a per
> process file descriptor limit issue.

Sure, you can tell mysql to use more open file descriptors.

> Now, I run FreeBSD, which doesn't have per process file descriptor limits.
> So this doesn't seem like much of a problem to me. Sure, it may be a little
> slower than one monolithic table.... but.... would it really? I mean, with
> the monolithic table, aren't you relying on the table's indexes to find
> your domain? Wouldn't you skip a search of the index if you just opened a
> table using the name of the domain you are querying? Not to mention the
> memory that a monolithic table structure would take up. Wouldn't proper
> table caching be a better idea? Isn't that why it was invented in the first
> place? The domains with frequent authentication get entered into the cache
> and access time is optimized, whereas the domains with infrequent
> authentication stay the heck out of memory until needed.
> Am I crazy? (I'm almost afraid to ask that one...)
> I'm really looking forward to your reply. I'd like to get this straightened
> out in my mind. :)

We did stress testing and found that having one table per domain
is more efficent if you have only a few domains (less than 3) with
large numbers of users in each domain (more than 500). 
The main performance increase comes from space savings. Not
having to store the domain name for each email account.

On the other hand, if you have many domains  with few users
in each domain we saw a performance increase by putting all
the users/domains in one table. This lets mysql optimize
access to the data by keeping as much as possible in RAM.

The recommendations come from performance measurements,
not from any file descriptor limitation of mysql.

Hope that helps
Ken Jones

Reply via email to