Sja Hangs When Replicating Huge Tables

[beblx]

When I want to replicate a database that contains a very large table (about 1,2 G:cool: sja stucks and ALL THAT I GET is:

[root@slave-mlgw bin]# ./sja datasync-alltables.xml -l/var/log/sync-myslq.log

SQLyog Job Agent Version 5.0

Copyright © Webyog Softworks Pvt. Ltd.. All Rights Reserved.

Sync started at Mon May 1 00:20:46 2006

Table SrcRows TgtRows Inserted Updated Deleted

========================= ====== ===== ===== ===== =====

`audit_log` 0 0 0 0 0

`blacklist` 11 11 0 0 0

`geoip_country` 77947 77947 0 0 0

`inq` 1 4684 1 0 4684

`maillog` 523492 523191 Terminated

There is NOTHING in the Log except:

Sync started at Mon May 1 00:20:46 2006

Where could I find any idea of what goes wrong ???

This runs on CentOS 4.2 with mysql-4.1.12-3.RHEL4.1

Could you help me to find this very strange behaviour ?

[peterlaursen]

I do not think it hangs. I think it is working! And it cannot works faster than it gets the data it needs from the servers!

Check CPU and Network traffic from Task Manager! And don't be surprised if traffic is only 5-10 KB/sec. That is quite common with the sort of operation on cheap ISP's with a shared database server.

If you work with big databases you should learn to use the and -options effectively.

Refer to:

http://www.webyog.com/articles/Using_SQLyo…L_Databases.pdf

.. here I describe an example where I reduced sync time from 11 hours to 5 minutes doing so!

A detail: SJA does not REPLICATE. It SYNCHRONIZES.

http://www.webyog.com/faq/11_60_en.html

[beblx]

peterlaursen wrote on May 1 2006, 01:54 AM:

I do not think it hangs. I think it is working! And it cannot works faster than it gets the data it needs from the servers!

Check CPU and Network traffic from Task Manager! And don't be surprised if traffic is only 5-10 KB/sec. That is quite common with the sort of operation on cheap ISP's with a shared database server.

If you work with big databases you should learn to use the and -options effectively.

Refer to:

http://www.webyog.com/articles/Using_SQLyo…L_Databases.pdf

.. here I describe an example where I reduced sync time from 11 hours to 5 minutes doing so!

A detail: SJA does not REPLICATE. It SYNCHRONIZES.

http://www.webyog.com/faq/11_60_en.html

Just a little precision, the TWO servers are in the same physical location, the goal of this is a MailScanner/Mailwatch heartbeat cluster where I want to have a replication of the active node on the second machine even for the MySQL database, and the link between the 2 notes in in Gigabit Ethernet, I checked CPU load during sja works and it vever exceeds 22% of the CPU, during this time, the load average is only at 0,40…

Do you have an idea how we could have a more verbose log of what happens during this sync ?

[peterlaursen]

A more verbose behaviour has been requested by me to!

I think it is the SJA for Linux that you are using? Running form same physical localtion as the servers?

Or from somewhere else?

Still you syould check if the data allows for use of and options.

Could you post the 'CREATE statement for the table's involved ?? That could give an idea if this is normal.

And still SJA does not REPLICATION. <_<

[beblx]

peterlaursen wrote on May 1 2006, 06:26 PM:

A more verbose behaviour has been requested by me to!

I think it is the SJA for Linux that you are using? Running form same physical localtion as the servers?

Or from somewhere else?

Still you syould check if the data allows for use of and options.

Could you post the 'CREATE statement for the table's involved ?? That could give an idea if this is normal.

And still SJA does not REPLICATION. <_<

Yes, I wanted to say Syncronization ;-))

Here is the structure of the table:

CREATE TABLE maillog (

timestamp timestamp(14) NOT NULL,

id text,

size bigint(20) default '0',

from_address text,

from_domain text,

to_address text,

to_domain text,

subject text,

clientip text,

archive text,

isspam tinyint(1) default '0',

ishighspam tinyint(1) default '0',

issaspam tinyint(1) default '0',

isrblspam tinyint(1) default '0',

isfp tinyint(1) default '0',

isfn tinyint(1) default '0',

spamwhitelisted tinyint(1) default '0',

spamblacklisted tinyint(1) default '0',

sascore decimal(7,2) default '0.00',

spamreport text,

virusinfected tinyint(1) default '0',

nameinfected tinyint(1) default '0',

otherinfected tinyint(1) default '0',

report text,

ismcp tinyint(1) default '0',

ishighmcp tinyint(1) default '0',

issamcp tinyint(1) default '0',

mcpwhitelisted tinyint(1) default '0',

mcpblacklisted tinyint(1) default '0',

mcpsascore decimal(7,2) default '0.00',

mcpreport text,

hostname text,

date date default NULL,

time time default NULL,

headers text,

quarantined tinyint(1) default '0',

KEY maillog_datetime_idx (date,time),

KEY maillog_id_idx (id(20)),

KEY maillog_clientip_idx (clientip(20)),

KEY maillog_from_idx (from_address(200)),

KEY maillog_to_idx (to_address(200)),

KEY maillog_host (hostname(30)),

KEY from_domain_idx (from_domain(50)),

KEY to_domain_idx (to_domain(50)),

KEY maillog_quarantined (quarantined)

) TYPE=MyISAM;

This is the table that stucks

And, Yes this is the Linux version, the 2 boxes are distant of 20 cm in the same room connected by a Gigabit Ethernet link on Cisco Switch, the Linux Distro is CentOS 4.2 (RHEL 4.2 Free Clone) each box is DualCore Pentium D with 4 Gigs of RAM hardware is definitely not the bottleneck, here is the datasync-alltables.xml used to sync with sja:

master-mlgw

root

xxxxxx 3306

mailscanner

127.0.0.1

root

xxxxxx 3306

mailscanner

Do you need more infos ?

Thanks a lot for your help…

[peterlaursen]

1) First I notice that there is no Primary Key in the table. You should *NOT* attempt to use SJA without understanding how it uses the Primary Key and what is does it there is not any! You might get quie another result than you expect! The article explains. If possible: add a Primary Key.

2) Need they all be TEXT? 'clientip' could be a char/varchar(15) I think? You may run into a MySQL configuration issue (buffers too small) that makes it very slow to execute the convat_ws() function as the SJA does, because …

3) .. if no options is specified the comcat_ws() takes all columns as argument. With such long table definition you absolutely should use the option if possible – that is if you can specify a unique combinations of columns. Then only the specified columns will be used as argument with concat_ws(). Also the with the 'timestamp' column seems possible. It not defined as 'CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP' Is it a pretty old MySQL version, where this is not possible? However if you appliation does the same that would be the same.

Try to execute

SELECT concat_ws(all columns go as argument) on the source server (where data are) with any client:

Something like: SELECT concat_ws('_',`maillog`.`column1`,`maillog`.`column2` etc.) from tbl_a limit 100;

How long does it take to return a result?

Also:

How many rows are there?

How big are the database files?

[Author]

Posts