List:Replication« Previous MessageNext Message »
From:Andrej Pintar Date:August 17 2010 7:17pm
Subject:Re: MySQL Replication | SLAVE_IO not running | After tunning master
View as plain text  
Rick James wrote:
> Is  max_allowed_packet = 1M on both Slave and Master?
> server_id is different?
> Master did not crash?
> Using MyISAM or InnoDB??
>
Increased Max_allowed packet on slave servers.
Master 1M - Slave 12M (because warnings, high loads)
server id is unique on every server
master did not crash, works fine.
MyISAM tables. InnoDB not used.


> On 8/17/10 8:40 AM, Andrej Pintar wrote:
>> 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