Problem with *very* slow replication

From: Christopher E. Brown <cbrown(at)woods.net>
Date: Tue Oct 23 2007 - 11:36:51 EDT

I have a pair of FreeBSD 6.2 systems running 5.1.19. Both systems modern dual core with 4G ram.

They currently support a single table, a session database for our webmail platform. Table format is

+----------------+--------------+------+-----+---------------------+-------+
| Field          | Type         | Null | Key | Default             | Extra |
+----------------+--------------+------+-----+---------------------+-------+
| session_key    | varchar(64)  | NO   | PRI |                     |       |
| username       | varchar(128) | NO   | MUL |                     |       |
| LastUpdate     | timestamp    | NO   | MUL | CURRENT_TIMESTAMP   |       |
| session_expiry | datetime     | NO   | MUL | 0000-00-00 00:00:00 |       |
| session_data   | longtext     | YES  |     | NULL                |       |
+----------------+--------------+------+-----+---------------------+-------+

There are 0 - 2500 records in the table at any time, both servers (even during peak times) are never under 95% idle. (No cpu shortage, no i/o blocking).

Reads to writes are 3 to 1, up to 50 Qs/sec.

Interconnect is a 9000byte MTU GigE over a very lightly loaded switch.

The session_data blob varies from 8k to 2M with 95% of records being < 40k

All reads/writes happen on the primary, unless it goes offline. Then the webmails systems switch the the secondary and stay ther until prodded.

Now, the fun part. The replication never goes faster than 10 Qs a second. *BUT*, the slave always reports 0 seconds behind the master, and the SQL thread is always caught up with the relay file.

The master constantly shows

Command: Binlog Dump

   State: Writing to net

while the slave shows

Slave_IO_State: Waiting for master to send event

So, the slave falls further and further behind.

I am currently watching both systems at 99% idle with the master at 35 Qs/sec and the slave only getting 7 per sec.

It is almost like the binlog dump on the master is parsing based on a very slow timer or somthing.

Ughhh

General performance (other than replication) is fine, and an earlier testbed using the exact same table structure and MySQL configs on similar hardware but running Linux 2.6.x had no trouble keeping up with a test load of 300 - 500 Qs/sec (though the test load was actually 4:1 read to write w/ session_data size of 48k).

MySQL was build from the ports tree

>Release: mysql-5.1.19-beta (FreeBSD port: mysql-client-5.1.19)

>C compiler: cc (GCC) 3.4.6 [FreeBSD] 20060305
>C++ compiler: c++ (GCC) 3.4.6 [FreeBSD] 20060305
>Environment:

         <machine, os, target, libraries (multiple lines)> System: FreeBSD puffin.acsalaska.net 6.2-RELEASE-p4 FreeBSD 6.2-RELEASE-p4 #0: Thu Apr 26 17:55:55 UTC 2007
root@i386-builder.daemonology.net:/usr/obj/usr/src/sys/SMP i386

Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Using built-in specs.
Configured with: FreeBSD/i386 system compiler Thread model: posix
gcc version 3.4.6 [FreeBSD] 20060305
Compilation info: CC='cc' CFLAGS='-O2 -fno-strict-aliasing -pipe ' CXX='c++' CXXFLAGS='-O2 -fno-strict-aliasing -pipe -O2 -fno-strict-aliasing -pipe -felide-constructors -fno-rtti -fno-exceptions' LDFLAGS='' ASFLAGS='' LIBC:
-r--r--r-- 1 root wheel 922668 Jan 11 2007 /lib/libc.so.6 -r--r--r-- 1 root wheel 1935968 Jan 11 2007 /usr/lib/libc.a lrwxrwxrwx 1 root wheel 14 Apr 4 2007 /usr/lib/libc.so -> /lib/libc.so.6
Configure command: ./configure '--localstatedir=/var/db/mysql'

'--without-debug' '--without-readline' '--without-libedit' 
'--with-libwrap' '--with-mysqlfs' '--with-low-memory' 
'--with-comment=FreeBSD port: mysql-client-5.1.19' 
'--enable-thread-safe-client' '--

with-plugins=max-no-ndb' '--enable-assembler'

'--with-named-thread-libs=-pthread' '--without-server' 
'--prefix=/usr/local' '--build=i386-portbld-freebsd6.2' 'CC=cc' 
'CFLAGS=-O2 -fno-strict-aliasing -pipe  ' 'CXXFLAGS=-O2 

-fno-strict-aliasing -pipe -O2 -fno-str
ict-aliasing -pipe -felide-constructors -fno-rtti -fno-exceptions' 'CXX=c++' 'build_alias=i386-portbld-freebsd6.2'

-- 
MySQL Replication Mailing List
For list archives: 
http://lists.mysql.com/replication
To unsubscribe:    
http://lists.mysql.com/replication?unsub=lists@pantek.com

Received on Tue Oct 23 11:37:03 2007