From: Rick James
Date: August 17 2010 5:24pm
Subject: Re: MySQL Replication | SLAVE_IO not running | After tunning master
List-Archive: http://lists.mysql.com/replication/1931
Message-Id: <4C6AC5C4.3090805@yahoo-inc.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 7bit

Is  max_allowed_packet = 1M on both Slave and Master?
server_id is different?
Master did not crash?
Using MyISAM or InnoDB??

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.
>
>    

-- 
Rick James - MySQL Geek