On 06/07/2011 12:02 AM, Jake Vickers wrote:
On 05/29/2011 03:32 AM, Martin Waschbüsch IT-Dienstleistungen wrote:
Hi Jake,
Am 28.05.2011 um 17:19 schrieb Jake Vickers:
So I'm still curious as to what you think the benefits are.
Segregating your data into multiple tables was always the way I
learned to obtain performance. The nature of the requests from QMT
are probably not those that would require any performance tuning -
the only one I've ever run into is the default number of connections.
So I'm willing to table that point if there are other mitigating
factors.
So what are the advantages/benefits you are thinking will be gained
from a single table?
I agree with Eric that it probably will not make much of a difference
with regards to performance.
However, a lot of applications that might want to have access to the
email user database are not all that flexible and do not support
having different domains in different tables.
E.g. using courier-auth with mysql or SOGo with mysql-backend, etc.
There are other examples.
For my own use, I have written an accounting backend that also cycles
through domains and users, etc. to produce detail-information to be
attached to invoices and that, too, is just way easier when having all
in one table.
Granted, that may be simplistic design on the parts of those programs,
but hey, it works and it is way easier to recompile vpopmail to
achieve compatibility than patch those programs.
Also, whenever there is any database schema change in order for
vpopmail to be upgraded, it is much easier to upgrade that one table
than to find all the tables that hold domains and path those.
Last but not least, mysql (and other databases) have
length-limitations for domain names. I always wondered, but never
tried, what would happen if I added a domain with a name longer than
'max-table-name-length' to the database?
I think the current limit for mysql is 32 chars and domain names can
be longer.
I have never tried a FQDN longer than 32 chars - RFC 1035 implies (if
I'm reading it correctly) of 255 chars:
2.3.4. Size limits
Various objects and parameters in the DNS have size limits. They are
listed below. Some could be easily changed, others are more
fundamental.
labels 63 octets or less
names 255 octets or less
TTL positive values of a signed 32 bit number.
UDP messages 512 octets or less
3. DOMAIN NAME SPACE AND RR DEFINITIONS
3.1. Name space definitions
Domain names in messages are expressed in terms of a sequence of labels.
Each label is represented as a one octet length field followed by that
number of octets. Since every domain name ends with the null label of
the root, a domain name is terminated by a length byte of zero. The
high order two bits of every length octet must be zero, and the
remaining six bits of the length field limit the label to 63 octets or
less.
To simplify implementations, the total length of a domain name (i.e.,
label octets and label length octets) is restricted to 255 octets or
less.
Although labels can contain any 8 bit values in octets that make up a
label, it is strongly recommended that labels follow the preferred
syntax described elsewhere in this memo, which is compatible with
existing host naming conventions. Name servers and resolvers must
compare labels in a case-insensitive manner (i.e., A=a), assuming ASCII
with zero parity. Non-alphabetic codes must match exactly.
Should be easy enough to change in 2.x.
Thanks for the app examples for the vpopmail DB schema. So the change
seems to hinge around opinion and interop with other applications. I
went back over the "helper apps" and
can't find anywhere in the code where a change may make something
currently supplied stop working, so I'll roll the change into the 2.x
test and we'll see what shakes loose.
---------------------------------------------------------------------------------
Thanks Jake.
This database conversion may be a little tricky to automate entirely.
I'm thinking there'd need to be a list of some sort identifying each
database table that needs to be converted/consolidated. Might be able to
write a query of database tables (metadata) to determine them all.
Do we have any MySQL experts here that can assist with such automation?
--
-Eric 'shubes'
---------------------------------------------------------------------------------
Qmailtoaster is sponsored by Vickers Consulting Group
(www.vickersconsulting.com)
Vickers Consulting Group offers Qmailtoaster support and installations.
If you need professional help with your setup, contact them today!
---------------------------------------------------------------------------------
Please visit qmailtoaster.com for the latest news, updates, and packages.
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
