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
> > >
> >
> >
>
>
[root@flash6 /root]# mysqladmin extended-status -p
Enter password:
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 5024048 |
| Aborted_connects | 12137 |
| Bytes_received | 2158958454 |
| Bytes_sent | 3307626474 |
| Connections | 55636039 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 0 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 415880 |
| Handler_read_first | 131954 |
| Handler_read_key | 107834504 |
| Handler_read_next | 58413268 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 12282118 |
| Handler_read_rnd_next | 95863567 |
| Handler_update | 28579456 |
| Handler_write | 13261246 |
| Key_blocks_used | 64972 |
| Key_read_requests | 796591854 |
| Key_reads | 61277 |
| Key_write_requests | 5607980 |
| Key_writes | 3416128 |
| Max_used_connections | 344 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 256 |
| Open_files | 272 |
| Open_streams | 0 |
| Opened_tables | 1050 |
| Questions | 177178873 |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 6978 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 9 |
| Sort_merge_passes | 0 |
| Sort_range | 60299 |
| Sort_rows | 370463 |
| Sort_scan | 0 |
| Table_locks_immediate | 121001602 |
| Table_locks_waited | 244368 |
| Threads_cached | 7 |
| Threads_created | 39186 |
| Threads_connected | 51 |
| Threads_running | 1 |
| Uptime | 2044425 |
+--------------------------+------------+
[root@flash6 /root]# uptime
12:44pm up 90 days, 21:27, 1 user, load average: 0.24, 0.12, 0.02
[root@flash6 /root]#
[root@flash6 /root]# mysql vpopmail -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 55642835 to server version: 3.23.33-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer
mysql> select count(*) from relay;
+----------+
| count(*) |
+----------+
| 809575 |
+----------+
1 row in set (0.00 sec)
mysql> desc relay;
+-------------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------+----------+------+-----+---------+-------+
| ip | char(18) | | PRI | | |
| deny | int(1) | YES | | NULL | |
| relayclient | int(1) | YES | | NULL | |
| timestamp | char(12) | YES | | NULL | |
+-------------+----------+------+-----+---------+-------+
4 rows in set (0.00 sec)
mysql>
