List:Replication« Previous MessageNext Message »
From:Andrej Pintar Date:August 17 2010 3:40pm
Subject:MySQL Replication | SLAVE_IO not running | After tunning master
View as plain text  
Hello friends,

    *Today I decided to tune MySQL Master Server by adjusting some 
variables:*
       -join_buffer bigger (1-2MB)
       -read_buffer bigger  (8MB)
       -read_rnd_buffer bigger (2MB)
       -connect_timeout bigger (20-25)
       -tmp_table_size 4x bigger (512MB)
       -sort_buffer bigger (2MB)
       -table_cache 2x bigger (512)

    Server* is not* on heavy load so I can afford high values. 
4cpu-8cores, 8gb ram, sas 15k hdd.
    Local application installed.
  
    I tested MonYog for monitoring and I use MySQL report on linux. Both 
installed on Linux. Before I could expect MySQL
    improvement after service restart and wait at least a day to see 
status variables Replication crashes.

    *The error was like few years ago. Error description:*
    Error 1236 on slave. Could not read binary log (corrupt)?
    SLAVE_IO is on not running. SLAVE_SQL running.
    MySQL master version : 5.0.51b win64
    MySQL slave version: 5.0.51b win36 and 5.0.67 (linux x64)

    Since nothing has cheanged at least a year and I mostly do not 
restart replication for at least 6 months period. This occurred
    an hour or more after tunning.

    Now my question is do some of these variables really affect replication?

    *Slave servers are connected to master using following:*

    MASTER (3306) - SERVER01 (on direct line, static ip) - 3309 (ip 
filter, using CCproxy) - SLAVE (global, location DE)

    Master and Server01 are inside the company on a LAN (1Gbps). Direct 
Line speed is 5/5 Mbps. SLAVE is 100Mbps (Web Server). I doubt that its 
related to network problems because it works perfect.
    Slave connects to proxy on 3309, Server01 forwards request to MASTER.

  *  Reason for optimize:*
       -table locks, waited(Select queries), state DEAD displayed in 
process list - query does finish in a lot of time, concurrent query's
       while user presses F5 in browser makes it a little worse
       -table scans
       -joins
       -tryed to improve server by tunning because of those reasons
   
    -query cache not used because of big query's
    -index hit rate 99,96%  

    -slow query time: *200s,* lock times:* 60s*

   * Query status:*
       -lot's of slow queries which I can't  rewrite  because its not in 
my jurisdiction
             -notified the programmers and sent slow queries
          -indexes used
          -tryed to make some status with mk-query-filter
          -Explain

    Generally this Question may not be useful but I only wanted to know 
replication connections with
    these variables that i modified and gave myself additional work to 
fix replication. 

*    Conclusion:
   
    *Replication does not like* high buffer* values and high 
max_allowed_packet on master. Our max_allowed_packet
    stayed as default at 1M which is ok and works fine (this variable 
was not changed in my tunning).
    max_allowed_packet=1M (because of LOAD DATA command), high values 
broke replication with same error 1236.
    MySQL phenomenon errors once more.

Thank you.  

Sorry for a long long description.

-- 
Andrej Pintar
email : api984@stripped
          andrej@stripped
          api984@stripped
web: http://www.api984.net
contact cell: 00385 98 790 639
home server: http://api984.ath.cx
ICQ: 191748772
Skype: api9841
MSN: fatallord@stripped
IRC: api984, freenode.net
::Software is like sex: it's better when it's free::


Thread
MySQL Replication | SLAVE_IO not running | After tunning masterAndrej Pintar17 Aug
  • Re: MySQL Replication | SLAVE_IO not running | After tunning masterRick James17 Aug
    • Re: MySQL Replication | SLAVE_IO not running | After tunning masterAndrej Pintar17 Aug