Re: [vchkpw] Upgrading from 5.2.1 to 5.4.0 with MySQL
On Monday 07 June 2004 3:08 pm, Rick Widmer wrote: > Jeremy Kitchen wrote: > > On Monday 07 June 2004 02:22 pm, Jeremy Kitchen wrote: > >>>As an ex-database admin, I wrote the many-domains=no option as > >>>an optimization to the database schema to save disk space. > >>> > >>>Consider hosting one domain with 1 million email accounts. > >>>The pw_domain(64 char) field is redundant (only one domain). So > >>>we would be wasting 64MB of space in the database. So instead > >>>the table is named after the domain. > >> > >>I was about to ask you this but you ran off before I could.. so I'll just > >>post for discussion purposes. > >> > >>Isn't this what the varchar field is for? > > Yes it is, but the current database layout uses char fields. Still if > you have one domain with 1 million accounts, you have at least 4MB of > duplicated domain names, if the domain name is a.au, better than the > 64MB the current char field would use, but not as good as 0 bytes if the > table is named for the domain and you don't store the domain name at > all. It is interesting in theory, but there probably aren't many sites > like this. > > It does bring up an interesting question... would it be a good idea to > change from char to varchar for all the fields? Standard database design says to *never* use varchars. And if you do use them, you better have a darn good reason. It's easy to spot a newbie doing database design, all fields are varchars. Varchars make it very difficult for the SQL engine to optimize disk to memory paging. Which in practice means no optimization for tables with varchars. When each row is a fixed length, the engine can calculate how many rows will fit in a memory block. Then it can do one disk read per memory block (this is the optimization). With varchar fields the engine needs to read in a section of disk into a temporary memory buffer. Then parse the varchar lengths to calculate row lengths, one row at a time. The end result is more disk reads. And disk I/O is the bottleneck in databases. So it all boils down to significant performance improvements of fixed lenght rows over variable lenght rows. > It should give a > substantial reduction in database size, and I don't think it will be > that much slower. > Someone _should_ be able to do an alter table and > find out if anything breaks... > > > Rick > > p.s. It does bring up a real question - does anyone actually use > separate tables for each domain anymore? It would be nice if we could > remove the option some day. (It causes a number of four state ifdef > structures that are kind of messy.) I know what you mean. I kinda wish I never wrote the two versions. But removing it would break backward compatibility. Ken
Re: [vchkpw] Upgrading from 5.2.1 to 5.4.0 with MySQL
On Monday 07 June 2004 03:08 pm, Rick Widmer wrote: > Jeremy Kitchen wrote: > > On Monday 07 June 2004 02:22 pm, Jeremy Kitchen wrote: > >>>As an ex-database admin, I wrote the many-domains=no option as > >>>an optimization to the database schema to save disk space. > >>> > >>>Consider hosting one domain with 1 million email accounts. > >>>The pw_domain(64 char) field is redundant (only one domain). So > >>>we would be wasting 64MB of space in the database. So instead > >>>the table is named after the domain. > >> > >>I was about to ask you this but you ran off before I could.. so I'll just > >>post for discussion purposes. > >> > >>Isn't this what the varchar field is for? > > Yes it is, but the current database layout uses char fields. Still if > you have one domain with 1 million accounts, you have at least 4MB of > duplicated domain names, if the domain name is a.au, better than the > 64MB the current char field would use, but not as good as 0 bytes if the > table is named for the domain and you don't store the domain name at > all. It is interesting in theory, but there probably aren't many sites > like this. ahh good point. My thought is though, if you have a million users, 4 megs of extra 'wasted' space (even 64 megs) is trivial. I think people with a million users are more concerned about speed than fretting over a few megs of lost space :) > It does bring up an interesting question... would it be a good idea to > change from char to varchar for all the fields? It should give a > substantial reduction in database size, and I don't think it will be > that much slower. Someone _should_ be able to do an alter table and > find out if anything breaks... yea, I don't think that would break anything. Unfortunately I don't have a machine to try that on (other than a few production servers but I'm sure they'd shoot me if I broke them *grins*) > p.s. It does bring up a real question - does anyone actually use > separate tables for each domain anymore? It would be nice if we could > remove the option some day. (It causes a number of four state ifdef > structures that are kind of messy.) I know when I set up mysql backends I leave it at the default, I would assume that most people also do (unless they really know what they're doing, in which case they probably need the single table anyways for performance reasons) -Jeremy -- Jeremy Kitchen ++ Systems Administrator ++ Inter7 Internet Technologies, Inc. [EMAIL PROTECTED] ++ www.inter7.com ++ 866.528.3530 ++ 847.492.0470 int'l kitchen @ #qmail #gentoo on EFnet ++ scriptkitchen.com/qmail
Re: [vchkpw] Upgrading from 5.2.1 to 5.4.0 with MySQL
Jeremy Kitchen wrote: On Monday 07 June 2004 02:22 pm, Jeremy Kitchen wrote: As an ex-database admin, I wrote the many-domains=no option as an optimization to the database schema to save disk space. Consider hosting one domain with 1 million email accounts. The pw_domain(64 char) field is redundant (only one domain). So we would be wasting 64MB of space in the database. So instead the table is named after the domain. I was about to ask you this but you ran off before I could.. so I'll just post for discussion purposes. Isn't this what the varchar field is for? Yes it is, but the current database layout uses char fields. Still if you have one domain with 1 million accounts, you have at least 4MB of duplicated domain names, if the domain name is a.au, better than the 64MB the current char field would use, but not as good as 0 bytes if the table is named for the domain and you don't store the domain name at all. It is interesting in theory, but there probably aren't many sites like this. It does bring up an interesting question... would it be a good idea to change from char to varchar for all the fields? It should give a substantial reduction in database size, and I don't think it will be that much slower. Someone _should_ be able to do an alter table and find out if anything breaks... Rick p.s. It does bring up a real question - does anyone actually use separate tables for each domain anymore? It would be nice if we could remove the option some day. (It causes a number of four state ifdef structures that are kind of messy.)
Re: [vchkpw] Upgrading from 5.2.1 to 5.4.0 with MySQL
On Monday 07 June 2004 02:22 pm, Jeremy Kitchen wrote: > > As an ex-database admin, I wrote the many-domains=no option as > > an optimization to the database schema to save disk space. > > > > Consider hosting one domain with 1 million email accounts. > > The pw_domain(64 char) field is redundant (only one domain). So > > we would be wasting 64MB of space in the database. So instead > > the table is named after the domain. > > I was about to ask you this but you ran off before I could.. so I'll just > post for discussion purposes. > > Isn't this what the varchar field is for? or rather, field TYPE. -Jeremy -- Jeremy Kitchen ++ Systems Administrator ++ Inter7 Internet Technologies, Inc. [EMAIL PROTECTED] ++ www.inter7.com ++ 866.528.3530 ++ 847.492.0470 int'l kitchen @ #qmail #gentoo on EFnet ++ scriptkitchen.com/qmail
Re: [vchkpw] Upgrading from 5.2.1 to 5.4.0 with MySQL
On Monday 07 June 2004 02:20 pm, Ken Jones wrote: > On Monday 07 June 2004 2:01 pm, Jeremy Kitchen wrote: > > On Monday 07 June 2004 01:39 pm, Martin Leduc wrote: > > > Can you tel me quickly what is the difference between > > > enabled-many-domain=y and =n? > > > > as far as I know it only affects sql (mysql only?) backends, as with > > --enable-many-domains=y (default) it uses one table with all of the > > information, and with it =n it uses one table per domain. > > > > Someone who has actually dug through the code (Tom?, Michael?, Ken?) > > might be able to give you a more definite example, but from my experience > > that's the main difference. > > > > I think --enable-large-site (or similar) is what enables/disables the > > directory hashing, but don't quote me on that. > > As an ex-database admin, I wrote the many-domains=no option as > an optimization to the database schema to save disk space. > > Consider hosting one domain with 1 million email accounts. > The pw_domain(64 char) field is redundant (only one domain). So > we would be wasting 64MB of space in the database. So instead > the table is named after the domain. I was about to ask you this but you ran off before I could.. so I'll just post for discussion purposes. Isn't this what the varchar field is for? -Jeremy -- Jeremy Kitchen ++ Systems Administrator ++ Inter7 Internet Technologies, Inc. [EMAIL PROTECTED] ++ www.inter7.com ++ 866.528.3530 ++ 847.492.0470 int'l kitchen @ #qmail #gentoo on EFnet ++ scriptkitchen.com/qmail
Re: [vchkpw] Upgrading from 5.2.1 to 5.4.0 with MySQL
On Monday 07 June 2004 2:01 pm, Jeremy Kitchen wrote: > On Monday 07 June 2004 01:39 pm, Martin Leduc wrote: > > Can you tel me quickly what is the difference between > > enabled-many-domain=y and =n? > > as far as I know it only affects sql (mysql only?) backends, as with > --enable-many-domains=y (default) it uses one table with all of the > information, and with it =n it uses one table per domain. > > Someone who has actually dug through the code (Tom?, Michael?, Ken?) might > be able to give you a more definite example, but from my experience that's > the main difference. > > I think --enable-large-site (or similar) is what enables/disables the > directory hashing, but don't quote me on that. As an ex-database admin, I wrote the many-domains=no option as an optimization to the database schema to save disk space. Consider hosting one domain with 1 million email accounts. The pw_domain(64 char) field is redundant (only one domain). So we would be wasting 64MB of space in the database. So instead the table is named after the domain. On the other hand, consider hosting 700 domains. Mysql would have to access 700 tables. Mysql uses file descriptors for each table. In production, we saw mysql opening and closing file descriptors as it tried to share it's available file descriptors across sql queries, which seriously impacted performance. On this type of system, it we got better performance by putting all the data into one table. The end result after performance testing was the single table setup was the best for all servers, except for the case where there are 1 (or a few) domains with many-many email accounts. Hence we made "many domains" the default option. Ken Jones
Re: [vchkpw] Upgrading from 5.2.1 to 5.4.0 with MySQL
On Monday 07 June 2004 01:39 pm, Martin Leduc wrote: > Can you tel me quickly what is the difference between enabled-many-domain=y > and =n? as far as I know it only affects sql (mysql only?) backends, as with --enable-many-domains=y (default) it uses one table with all of the information, and with it =n it uses one table per domain. Someone who has actually dug through the code (Tom?, Michael?, Ken?) might be able to give you a more definite example, but from my experience that's the main difference. I think --enable-large-site (or similar) is what enables/disables the directory hashing, but don't quote me on that. -Jeremy -- Jeremy Kitchen ++ Systems Administrator ++ Inter7 Internet Technologies, Inc. [EMAIL PROTECTED] ++ www.inter7.com ++ 866.528.3530 ++ 847.492.0470 int'l kitchen @ #qmail #gentoo on EFnet ++ scriptkitchen.com/qmail
Re: [vchkpw] Upgrading from 5.2.1 to 5.4.0 with MySQL
Can you tel me quickly what is the difference between enabled-many-domain=y and =n? it's only the one "table per domain" when "no" is enabled ? Best Regards Martin From: Jeremy Kitchen <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: [vchkpw] Upgrading from 5.2.1 to 5.4.0 with MySQL Date: Mon, 7 Jun 2004 11:40:00 -0500 On Monday 07 June 2004 11:33 am, Martin Leduc wrote: > Thank you, thats working :) great. > Now I need to make a pre-production test. ok. > I would like to copy my vpopmail/domains dir and my Database to my DEVEL > server. ok. > Did I need other files? Like in the Qmail config? the users/ and control/ directories for qmail. -Jeremy -- Jeremy Kitchen ++ Systems Administrator ++ Inter7 Internet Technologies, Inc. [EMAIL PROTECTED] ++ www.inter7.com ++ 866.528.3530 ++ 847.492.0470 int'l kitchen @ #qmail #gentoo on EFnet ++ scriptkitchen.com/qmail _ MSN Search, le moteur de recherche qui pense comme vous ! http://fr.ca.search.msn.com/
Re: [vchkpw] Upgrading from 5.2.1 to 5.4.0 with MySQL
On Monday 07 June 2004 11:33 am, Martin Leduc wrote: > Thank you, thats working :) great. > Now I need to make a pre-production test. ok. > I would like to copy my vpopmail/domains dir and my Database to my DEVEL > server. ok. > Did I need other files? Like in the Qmail config? the users/ and control/ directories for qmail. -Jeremy -- Jeremy Kitchen ++ Systems Administrator ++ Inter7 Internet Technologies, Inc. [EMAIL PROTECTED] ++ www.inter7.com ++ 866.528.3530 ++ 847.492.0470 int'l kitchen @ #qmail #gentoo on EFnet ++ scriptkitchen.com/qmail
Re: [vchkpw] Upgrading from 5.2.1 to 5.4.0 with MySQL
Thank you, thats working :) Now I need to make a pre-production test. I would like to copy my vpopmail/domains dir and my Database to my DEVEL server. Did I need other files? Like in the Qmail config? Martin From: Jeremy Kitchen <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: [vchkpw] Upgrading from 5.2.1 to 5.4.0 with MySQL Date: Mon, 7 Jun 2004 11:00:59 -0500 On Monday 07 June 2004 10:13 am, Martin Leduc wrote: > Hi, > > I tried to upgrade the Vpopmail software but something is not work > correctly. We used the MySQL AUTH Module. > > If I read from the UPGRADE documentation, I read: > > --- Upgrading to 5.4 from 5.3.x or 5.2.x is straight forward. > > We dont have enabled many-domain, so we have a problem. right. > By default, the option enabled-many-domain is now enabled. as it should be. [snip confusing problem with simple workaround-ish solution] I would *highly* recommend going into your old source directory (hopefully you kept it around) and use 'vconvert' to pull your existing data from mysql into vpasswd files. Then, using the new binaries you have, use vconvert again to put the existing data back into the database in the structure it needs (I would highly recommend --enable-many-domains) That's the easiest way out of this mess for you, and shouldn't take but a minute or two. You don't even have to replace any binaries or anything, just run it where it stands. -Jeremy -- Jeremy Kitchen ++ Systems Administrator ++ Inter7 Internet Technologies, Inc. [EMAIL PROTECTED] ++ www.inter7.com ++ 866.528.3530 ++ 847.492.0470 int'l kitchen @ #qmail #gentoo on EFnet ++ scriptkitchen.com/qmail _ MSN Search, le moteur de recherche qui pense comme vous ! http://fr.ca.search.msn.com/
Re: [vchkpw] Upgrading from 5.2.1 to 5.4.0 with MySQL
On Monday 07 June 2004 10:13 am, Martin Leduc wrote: > Hi, > > I tried to upgrade the Vpopmail software but something is not work > correctly. We used the MySQL AUTH Module. > > If I read from the UPGRADE documentation, I read: > > --- Upgrading to 5.4 from 5.3.x or 5.2.x is straight forward. > > We dont have enabled many-domain, so we have a problem. right. > By default, the option enabled-many-domain is now enabled. as it should be. [snip confusing problem with simple workaround-ish solution] I would *highly* recommend going into your old source directory (hopefully you kept it around) and use 'vconvert' to pull your existing data from mysql into vpasswd files. Then, using the new binaries you have, use vconvert again to put the existing data back into the database in the structure it needs (I would highly recommend --enable-many-domains) That's the easiest way out of this mess for you, and shouldn't take but a minute or two. You don't even have to replace any binaries or anything, just run it where it stands. -Jeremy -- Jeremy Kitchen ++ Systems Administrator ++ Inter7 Internet Technologies, Inc. [EMAIL PROTECTED] ++ www.inter7.com ++ 866.528.3530 ++ 847.492.0470 int'l kitchen @ #qmail #gentoo on EFnet ++ scriptkitchen.com/qmail
[vchkpw] Upgrading from 5.2.1 to 5.4.0 with MySQL
Hi, I tried to upgrade the Vpopmail software but something is not work correctly. We used the MySQL AUTH Module. If I read from the UPGRADE documentation, I read: --- Upgrading to 5.4 from 5.3.x or 5.2.x is straight forward. We dont have enabled many-domain, so we have a problem. By default, the option enabled-many-domain is now enabled. It's ok when I specified --enabled-many-domain = n. I can do a vdominfo query. But when I try vuserinfo, this is the error: [EMAIL PROTECTED]:/home/vpopmail/bin# ~vpopmail/bin/vuserinfo [EMAIL PROTECTED] vmysql: sql error[3]: Table 'vpopmail.test.com' doesn't exist no such user [EMAIL PROTECTED] This is my ./configure options: --enable-clear-passwd=y \ --enable-valias=y \ --enable-mysql-logging=y \ --enable-sqwebmail-pass=y \ --enable-domains-dir=domains \ --enable-auth-module=mysql \ --enable-incdir=/usr/local/mysql/include/ \ --enable-libdir=/usr/local/mysql/lib/ \ --enable-many-domains=n \ --disable-users-big-dir I tried with and without --disable-users-big-dir, same result. We used mysql 4.0.20, new upgraded ;). Thanks for Help. Martin _ MSN Search, le moteur de recherche qui pense comme vous ! http://fr.ca.search.msn.com/
