Oh trust me, I know that. :-) My point was that it's much less expensive to
have tcpserver open up a persistent connnection to the MySQL server and keep
it open rather than launching another (albeit small) program that connects
to and queries the database. I'm only using MySQL for my SMTP relay (custom
hacks) and I'm doing an average of 19 queries per second to the database
over the last 60 days. Queries are only generated by SMTP sessions, updates
are triggered by successful POP or IMAP authentications.
Doing it my way, every incoming SMTP request only does a query. With your
method you have to open a TCP connection, connect to the database,
authenticate, query, and finally drop the connection for every SMTP
session. I have seven of these mail toasters, the largest one handling an
average of 1.7 million SMTP connections per day spread across 4 machines.
That's the one I care about most and will refer to throughout the rest of
this email. MySQL has no problems keeping up. It's running on a Quad Xeon
550 with 2GB of RAM and it only uses 5-10% of one CPU.
When we have HA available for MySQL I'll move all the authentication from
.cdb files into MySQL and the load on my MySQL server will rocket upward.
It's to everyone's advantage to make every piece of the system as
lightweight as possible. When that happens I'll probably also hack most of
the functionality of vchkpw into tcpserver as well. Then all the POP3
connections (which represent the majority of mail retrieval) will trigger
SQL updates via a persistent connection as well. At that time I'll probably
try to twist Ken's arm into including the modified tcpserver as a compile
time option to vpopmail.
Why is persistent connections so important? Your system has a finite
(normally tuneable) number of TCP connections that it can have open at a
given time. Why waste a 100 of those connections to MySQL when you can have
one persisent connnection? Why waste the CPU overhead necessary to open up
and tear down an extra 100 TCP connections per second? Need more reasons?
Read the very first tip given on MySQL's site about optimizing performance.
It reads:
"Use persistent connections to the database to avoid the connection
overhead. If you can't use persistent connections and you are doing a lot of
new connections to the database, you may want to change the value of the
thread_cache_size variable. See section 13.2.3 Tuning Server Parameters."
It's also much easier to troubleshoot problems when you can look at your SQL
servers processlist and only have 8 connections in there (two for each
server (tcpserver SMTP & tcpserver POP3)). It's cleaner, faster, and uses
less resources.
Another thought on this: What happens when you are using multiple SQL
servers (for HA) and the primary is down? With having a small program that
gets called by tcpserver, it's going to try to connect to the first SQL
server and will have to timeout before it tries the second. That's painful,
and every POP and SMTP session will then get a "timeout" length pause before
their connection succeeds. When tcpserver opens the connection, if the
persistent connection to the primary server drops, it'll try to reopen it.
If it failes to reopen, I could add some logic to get it to connect to a
secondary server. Then only the connections that arrive during the time
tcpserver's connection is retrying have a pause, once it's failed over to
the backup MySQL server(s) all connection resume as normal.
This is also the way the RADIUS protocol handles failover. The client sends
all requests and logging to the first configured server until that server
stops answering. It then fails over to the next server and stays there until
that server dies (at which time it'll try the first again). If we want
connections to return to the first MySQL server we can have tcpserver
periodically attempt to reconnect to the primary SQL server.
Matt
> -----Original Message-----
> From: Sean C Truman [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, May 30, 2001 12:52 PM
> To: Matt Simerson; Ken Jones; Dave 'Duke of URL' Weiner
> Cc: [EMAIL PROTECTED]
> Subject: Re: mysql high availability
>
>
> Matt,
>
> I can provide you with load specifications. MySQL can
> handle the work
> load. I have one customer site running 5 Node Cluster, This does over
> 500,000 messages a day. Attached is a extended-status from the mysql
> database server, a show of load average on the machine and
> also shows the
> table structure and record count.
>
> Machine Specs.
> Pent III 700Mhz
> 512MB Ram.
>
>
> With this cluster we are checking the database for relay and
> or deny flag
> every single connection. Point being that this setup is not
> even stressing
> the database and MySQL is a champ!.
>
>
> Sean
>
> ----- Original Message -----
> From: "Matt Simerson" <[EMAIL PROTECTED]>
> To: "'Sean C Truman'" <[EMAIL PROTECTED]>; "Ken Jones"
> <[EMAIL PROTECTED]>; "Dave 'Duke of URL' Weiner" <[EMAIL PROTECTED]>
> Cc: <[EMAIL PROTECTED]>
> Sent: Wednesday, May 30, 2001 1:33 PM
> Subject: RE: mysql high availability
>
>
> > I've been down this road and that's a terrible way to do
> it. Why? Because
> > for every SMTP connection you have to initiate a connection to the
> database.
> > That's expensive. What you really want to do is have the
> tcpserver program
> > make a persistent connection to the database and query it
> for every SMTP
> > session. I couldn't think of a better way to do it so I hacked up
> tcpserver
> > and added MySQL support to it. It reads a control file
> > (/var/qmail/control/sql) in the following format:
> >
> > server sql.domain.com
> > port 3306
> > database vpopmail
> > table relay
> > user username
> > pass secret
> > time 1800
> >
> > When/if the persistent connection drops it automatically
> retries. If it
> > fails then it allows the SMTP session anyway but does not
> set RELAYCLIENT.
> > It retries every "time" seconds.
> >
> > The patch is available on my web site:
> > http://matt.simerson.net/computing/mail.shtml and is found
> under the link
> > entitled "tcpserver MySQL patch"
> >
> > Matt
> >
> > > -----Original Message-----
> > > From: Sean C Truman [mailto:[EMAIL PROTECTED]]
> > > Sent: Wednesday, May 30, 2001 6:50 AM
> > > To: Ken Jones; Dave 'Duke of URL' Weiner
> > > Cc: [EMAIL PROTECTED]
> > > Subject: Re: mysql high availability
> > >
> > > Ken,
> > >
> > > A good way of getting around the NFS locking problem
> is to insert
> > another
> > > program inbetween tcpserver and qmail-smtpd that checks a
> database for a
> > > relay flag (Which can be set when you call
> open_smtp_relay()).and that
> > > program sets the RELAYCIENT.
> > >
> > > Sean
> > >
> > > ----- Original Message -----
> > > From: "Ken Jones" <[EMAIL PROTECTED]>
> > > To: "Dave 'Duke of URL' Weiner" <[EMAIL PROTECTED]>
> > > Cc: <[EMAIL PROTECTED]>
> > > Sent: Wednesday, May 30, 2001 7:28 AM
> > > Subject: Re: mysql high availability
> > >
> > >
> > > > Hi Dave,
> > > >
> > > > Dave 'Duke of URL' Weiner writes:
> > > >
> > > > >> This is probably worth talking about now, since we
> are working
> > > > >> on the mysql module for the next several weeks. Using Matt's
> > > > >> read/update patch as a starting point the mysql functions
> > > > >> are broken up into read-only queries and update queries
> > > > >> with separate definitions in vmysql.h for two mysql login
> > > > >> connections. One for updates and one for read only.
> > > > >>
> > > > >> I don't have any clean ideas yet on how to handle the
> > > > >> three cases most people are running into:
> > > > >> 1) one mysql server for everything
> > > > >> 2) replicated mysql system, with one read only server (slave)
> > > > >> and one update server (master)
> > > > >> 3) a primary server and a backup server
> > > > >>
> > > > >> Anyone have any ideas?
> > > > >
> > > > > Why yes, Ken, I do :)
> > > > >
> > > > > First off, a very good start. As you and I have
> > > discussed before, I was
> > > > > atempting a primary server with two slave servers
> > > replicating off the
> > > > > master. Using Ben's patch, everything seemed to work
> > > quite well, using
> > > the
> > > > > hostname of the master as the "Write" server, and
> localhost as the
> > > read-only
> > > > > server. In testing, when the master write server was
> > > shut down, users
> > > could
> > > > > not authenticate via pop, as the login routines were
> > > attempting to write
> > > the
> > > > > ip address and other info for the pop-before-smtp files.
> > > > >
> > > > > My only suggestion would be not to store the
> > > pop-before-smtp data in the
> > > > > MySQL database. That way, you end up with, IMO, a fault
> > > tolerant system
> > > for
> > > > > MySQL authentication.
> > > >
> > > > Yes, I was looking at that.
> > > > The first thing that I ran into is trying to minimize database
> > > > transactions during a pop access. The authentication part is
> > > > easy, just read the user info from mysql.
> > > >
> > > > There are two optional transactions that do updates during
> > > > an authentication
> > > > 1) the roaming users --enable-roaming-users=y
> > > > 2) authentication logging --enable-auth-log=y
> > > >
> > > > It seems pretty wasteful to have the authentication
> > > > mechanism open two database connections, one for
> > > > the read only auth and one for the two possible updates.
> > > > But I really can not see a way around it.
> > > >
> > > > For the roaming users stuff to work, the IP addresses
> > > > have to be centralized. Otherwise in a clustered setup
> > > > once they pop, they may or may not be able to use smtp
> > > > relaying. We have tried using a file based system with
> > > > a NFS mount, but it runs into synchronization problems
> > > > when multiple machines are updating the file at the same
> > > > time, and locking doesn't help because NFS locks will
> > > > fail.
> > > >
> > > > One thing we've been looking at is having "fall back"
> > > > servers for both the read and update mysql connections.
> > > > Meaning: if a connection to one fails, then it attempts
> > > > a connection to the fallback machine.
> > > >
> > > > The other thing is the authentication needs to be compiled
> > > > into the binaries. One other option is a patch from
> > > > Ondrej that gets the mysql auth data from environment
> > > > variables.
> > > >
> > > > Ken Jones
> > > >
> > >
> > >
> >
> >
>
