Re: [vchkpw] Re: Mysql table
Tom Collins wrote: I think we'll get better domain alias support if you pull column > `domain` out of table `Domains` and add it to the table `domain_alias`. I agree. This makes it easy to make sure domain names are unique. > I'm not sure we need to have a "master" and "alias" -- the names can > all be equal with this setup. I think we still need it. The 'real' domain is the one that has a file system behind it, and we need to know which one it is. I suggest a pointer field real_domain containing the domain_name_id of the real domain. [Isn't there something in mailing lists that ties the list to the real domain, that makes renaming domains hard?] We can save a little bit of space by storing the path to the domain directory in the domain table, and only the path from there down in the users table. The dir field in the users table should be renamed to user_dir. I think domain_alias should be renamed to domain_names. Is there a better name for domains now? You might even want to have the limits fields go into a separate > table, with one entry in that table declared "default". That way, > domains with default entries can all point to that single row in the > limits table. How about we remove the NOT NULL constraint from each of the limit values, and if they are null take the value from the 'domain' _system.default. This way if you leave any value NULL, the default is filled in from the database in a single query: SELECT b.domain as domain, c.domain as real_domain, if( a.ip_address IS NULL, e.ip_address, a.ip_address ) as ip_address, if( a.max... IS NULL, e.max..., a.max... ) as max..., ... FROM domain_names a LEFT JOIN domains b USING( domain_id ) LEFT JOIN domain_names c ON c.domain_id = b.real_domain LEFT JOIN domain_names d ON d.domain = '_system.default' LEFT JOIN domains e ON e.domain_id = d.domain_id WHERE a.domain = '$TargetDomain'; Rick Macdougall wrote: I'd add a key on timestamp. [in the relay table] OK. I also added a sequence field to the valias table. Here is a pseudo-diff showing the changes: CREATE TABLE Domains( domain_idbigint not null auto_increment, real_domain bigint, - domain char(195), // TC + domain_dir char(100), // RW ip_address char(18)NOT NULL DEFAULT '0.0.0.0', maxpopaccounts INT(10) NOT NULL DEFAULT -1 maxaliases INT(10) NOT NULL DEFAULT -1, maxforwards INT(10) NOT NULL DEFAULT -1, maxautorespondersINT(10) NOT NULL DEFAULT -1, maxmailinglists INT(10) NOT NULL DEFAULT -1, diskquotaINT(12) NOT NULL DEFAULT 0, maxmsgcount INT(12) NOT NULL DEFAULT 0, defaultquota INT(12) NOT NULL DEFAULT 0, defaultmaxmsgcount INT(12) NOT NULL DEFAULT 0, disable_pop TINYINT(1) NOT NULL DEFAULT 0, disable_imap TINYINT(1) NOT NULL DEFAULT 0, disable_dialup TINYINT(1) NOT NULL DEFAULT 0, disable_passwordchanging TINYINT(1) NOT NULL DEFAULT 0, disable_webmail TINYINT(1) NOT NULL DEFAULT 0, disable_relayTINYINT(1) NOT NULL DEFAULT 0, disable_smtp TINYINT(1) NOT NULL DEFAULT 0, disable_spamassassin TINYINT(1) NOT NULL DEFAULT 0, delete_spam TINYINT(1) NOT NULL DEFAULT 0, perm_account TINYINT(2) NOT NULL DEFAULT 0, perm_alias TINYINT(2) NOT NULL DEFAULT 0, perm_forward TINYINT(2) NOT NULL DEFAULT 0, perm_autoresponder TINYINT(2) NOT NULL DEFAULT 0, perm_maillistTINYINT(4) NOT NULL DEFAULT 0, perm_quota TINYINT(2) NOT NULL DEFAULT 0, perm_defaultquotaTINYINT(2) NOT NULL DEFAULT 0, cur_usersint, level_curint, level_maxint, level_start0 int, level_start1 int, level_start2 int, level_end0 int, level_end1 int, level_end2 int, level_mod0 int, level_mod1 int, level_mod2 int, level_index0 int, level_index1 int, level_index2 int, the_dir char(160), primary key( domain_id ), key( ip_address ), ); - CREATE TABLE domain_alias( // RW + CREATE TABLE domain_names( // RW -domain_alias_id bigint not null auto_increment, // RW +domain_name_id bigint not null auto_increment, // RW domain_idbigint not null, - aliaschar(195), // TC + domain char(195), // TC - primary key( domain_alias_id ), // RW + primary key( domain_name_id ),// RW foreign key( domain_id ) REFERENCES domains( dom
Re: [vchkpw] Re: Mysql table
Rick Widmer wrote: I wasn't quite ready for this, but it has been on my list for a long time... since it has come up, let me throw up this database structure for comment. I have reduced the number of tables (in a full installation) and made it relational.- CREATE TABLE relay( ip_addr char(18) not null, timestampdatetime, primary key( ip_addr ) ), No coffee yet so this may well be out in left field, but on large systems when a clear open smtp is run I don't think you'll want to be searching through 20K records in the table to see who to expire. I'd add a key on timestamp. Rick
Re: [vchkpw] Re: Mysql table
On Sep 21, 2007, at 4:32 PM, Rick Widmer wrote: Comments? I think we'll get better domain alias support if you pull column `domain` out of table `Domains` and add it to the table `domain_alias`. "Domain name" to "domain on the system" is a many to one relationship, so the name should be in a separate table. I'm not sure we need to have a "master" and "alias" -- the names can all be equal with this setup. I mention it because it reduces lookups to a single query (or at least a simpler query). Instead of needing to check for the domain name in one of two tables, you just check one. Apologies if this SQL has any MySQL-flavored syntax... SELECT `user`.`password`, `user`.`flags` FROM `domain_name`, `domain`, `users` WHERE `domain_name`.`domain_id` = `domain`.`domain_id` AND `user`.`domain_id` = `domain`.`domain_id` AND `domain_name`.`name` = '%s' AND `user`.`name` = '%s' You might even want to have the limits fields go into a separate table, with one entry in that table declared "default". That way, domains with default entries can all point to that single row in the limits table. -- Tom Collins - [EMAIL PROTECTED] Vpopmail - virtual domains for qmail: http://vpopmail.sf.net/ QmailAdmin - web interface for Vpopmail: http://qmailadmin.sf.net/
Re: [vchkpw] Re: Mysql table
I wasn't quite ready for this, but it has been on my list for a long time... since it has come up, let me throw up this database structure for comment. I have reduced the number of tables (in a full installation) and made it relational.- CREATE TABLE Domains( domain_idbigint not null auto_increment, domain char(195), ip_address char(18)NOT NULL DEFAULT '0.0.0.0', maxpopaccounts INT(10) NOT NULL DEFAULT -1, maxaliases INT(10) NOT NULL DEFAULT -1, maxforwards INT(10) NOT NULL DEFAULT -1, maxautorespondersINT(10) NOT NULL DEFAULT -1, maxmailinglists INT(10) NOT NULL DEFAULT -1, diskquotaINT(12) NOT NULL DEFAULT 0, maxmsgcount INT(12) NOT NULL DEFAULT 0, defaultquota INT(12) NOT NULL DEFAULT 0, defaultmaxmsgcount INT(12) NOT NULL DEFAULT 0, disable_pop TINYINT(1) NOT NULL DEFAULT 0, disable_imap TINYINT(1) NOT NULL DEFAULT 0, disable_dialup TINYINT(1) NOT NULL DEFAULT 0, disable_passwordchanging TINYINT(1) NOT NULL DEFAULT 0, disable_webmail TINYINT(1) NOT NULL DEFAULT 0, disable_relayTINYINT(1) NOT NULL DEFAULT 0, disable_smtp TINYINT(1) NOT NULL DEFAULT 0, disable_spamassassin TINYINT(1) NOT NULL DEFAULT 0, delete_spam TINYINT(1) NOT NULL DEFAULT 0, perm_account TINYINT(2) NOT NULL DEFAULT 0, perm_alias TINYINT(2) NOT NULL DEFAULT 0, perm_forward TINYINT(2) NOT NULL DEFAULT 0, perm_autoresponder TINYINT(2) NOT NULL DEFAULT 0, perm_maillistTINYINT(4) NOT NULL DEFAULT 0, perm_quota TINYINT(2) NOT NULL DEFAULT 0, perm_defaultquotaTINYINT(2) NOT NULL DEFAULT 0, cur_usersint, level_curint, level_maxint, level_start0 int, level_start1 int, level_start2 int, level_end0 int, level_end1 int, level_end2 int, level_mod0 int, level_mod1 int, level_mod2 int, level_index0 int, level_index1 int, level_index2 int, the_dir char(160), primary key( domain_id ), unique key( domain ) key( ip_address ), ); CREATE TABLE domain_alias( domain_alias_id bigint not null auto_increment, domain_idbigint not null, aliaschar(195), primary key( domain_alias_id ), foreign key( domain_id ) REFERENCES domains( domain_id ), unique key( alias ) ); CREATE TABLE relay( ip_addr char(18) not null, timestampdatetime, primary key( ip_addr ) ), CREATE TABLE users( user_id bigint not null auto_increment, domain_idbigint not null, name passwd char(40), uid int, flagsint, comment char(48), dir char(160), shellchar(20), passwd char(16), remote_ipchar(18), rate_limit int, active char(1), created datetime, timestampbigint default 0 NOT NULL, primary key( valias_id ), foreign key( domain_id ) REFERENCES domains( domain_id ), unique key ( name, domain_id ) " ); CREATE TABLE valias( valias_idbigint not null auto_increment, user_id bigint not null, name char(?), aliaschar(195), primary key( valias_id ), foreign key( user_id ) REFERENCES users( user_id ), unique key( name ) ); I use bigint keys because the last time I looked (admittedly it was a long time ago) if you had 2 bigints at the beginning of a table, and the value in the second one was small, those two fields were bigger than the data overwritten when a record is deleted from the table. This increases the amount of data you can recover until the record gets overwritten. (I have had to recover deleted records the by searching through the database files before...) If this happens, MANY_DOMAINS, CLEAR_PASS and IP_ALIAS_DOMAINS will all go away. If we need to enable/disable clear pass it will be an option in the file where we set the database open info. Also, I want to change query construction from creating defines to building them into strings. I would also like to remove all data base structure changes from the code, and provide a sql script to create the database. The vopomail database user should be able to operate with no more than INSERT, DELETE, UPDATE, and SELECT rights to the database.
[vchkpw] Re: Mysql table
Rick Macdougall wrote: > Robin Bowes wrote: >> What sort of size of database are folk using in the real world? How many >> users? >> >> What's the most common no. of users? >> >> R. >> > > I'm guessing 200 - 2000 but that's based on the servers I manage for > clients (around 20 or so). > > The biggest installation I manage is around 18k users and I could export > that with munged domain names / passwords if you wanted something that big. It's hardly worth bothering with for ~2000 users. The difference in memory/disk use would be minimal. An 18k user table would be interesting. R.
Re: [vchkpw] Re: Mysql table
Robin Bowes wrote: Rick Macdougall wrote: Robin Bowes wrote: Tom Collins wrote: I understand what you're proposing, but I would suggest that it would add complexity for little gain. Of course, that would need benchmarking to establish which is the faster method. If someone can give me a large dataset, I'm happy to crunch some numbers. R. How large ? 5k, 15k, 100k ? What sort of size of database are folk using in the real world? How many users? What's the most common no. of users? R. I'm guessing 200 - 2000 but that's based on the servers I manage for clients (around 20 or so). The biggest installation I manage is around 18k users and I could export that with munged domain names / passwords if you wanted something that big. Rick
[vchkpw] Re: Mysql table
Rick Macdougall wrote: > Robin Bowes wrote: >> Tom Collins wrote: >> >> I understand what you're proposing, but I would suggest that it would >> add complexity for little gain. Of course, that would need benchmarking >> to establish which is the faster method. >> >> If someone can give me a large dataset, I'm happy to crunch some numbers. >> >> R. >> > > How large ? 5k, 15k, 100k ? What sort of size of database are folk using in the real world? How many users? What's the most common no. of users? R.
Re: [vchkpw] Re: Mysql table
Robin Bowes wrote: Tom Collins wrote: I understand what you're proposing, but I would suggest that it would add complexity for little gain. Of course, that would need benchmarking to establish which is the faster method. If someone can give me a large dataset, I'm happy to crunch some numbers. R. How large ? 5k, 15k, 100k ? Rick
[vchkpw] Re: Mysql table
Tom Collins wrote: > On Sep 20, 2007, at 12:56 PM, Rick Widmer wrote: >> If I remember right, speed was the reason for separate tables, but >> testing showed it was not faster. I think the single table works >> better because all your mail users are accessing the same table, and >> its indexes so they stay loaded all the time. If you use separate >> tables it is always thrashing the cache as different files need to be >> accessed. > > If we were really looking for speed, we could move to a "ng" (next > generation) table format that was more relational. Provide tools to > migrate from the old to the new for those people who only access the > data through vpopmail's APIs. Continue to support the old method for > people who have home-grown apps that access the data. > > The domains should be in a table of their own, and the users table > should index the domains table. Having an index in the users table on > an int (and the resulting size savings) would be measurable. > > The biggest change would be updating the selects and inserts in the > code. Not a huge change -- just a join between the tables. > > Something to consider, and perhaps discuss further (on this list or > vpopmail-devel). I understand what you're proposing, but I would suggest that it would add complexity for little gain. Of course, that would need benchmarking to establish which is the faster method. If someone can give me a large dataset, I'm happy to crunch some numbers. R.
