maria-discuss team mailing list archive

[Benoit Plessis <benoit+one@xxxxxxxxxxxx>]

Hum, it look like the issue is even more simple, if the tmp space is
filled for too long, even if the mysql slave thread has nothing to do i
end up with corrupted binlog ...

On 21/02/2019 09:53, Benoit Plessis wrote:
> Hi,
>
> Yes we are currently using mixed replication, we switched from
> statement two years ago.
>
> I had read mixed return about row based replication with some queries,
> so mixed looked like a good compromise.
> Reading current mysql (8.0) it look like RBR is the defaut now so it
> should be stable indeed.
>
> I need to check but i suppose i could test switch for mixed to row
> wihtout much difficulties.
>
> In RBR there isn't any need of tmp space ? I was looking at using a
> separate tmp space for the slave needs using slave_load_tmpdir
>
> Best,
> Benoit
>
> On 21/02/2019 02:29, Jeff Dyke wrote:
>> Hi based on one statement " If the replication process has to write a
>> tmp file to execute the query", i'd just like to ask are you using
>> STATEMENT or MIXED replication?  If so do you have a reason not to
>> use ROW?  Ultimately, the last is preferred, and i realize this does
>> not directly answer your question, but would be interested in
>> starting here and would far most consistent and only take up space
>> required for the row/dataset.
>>
>> Best,
>> Jeff
>>
>> On Wed, Feb 20, 2019 at 4:40 PM Benoit Plessis
>> <benoit+one@xxxxxxxxxxxx <mailto:benoit%2Bone@xxxxxxxxxxxx>> wrote:
>>
>>     Hi,
>>
>>     I have a few mysql cluster, previously on 5.5 (debian jessie) that we
>>     are upgrading in mariadb 10.1 (debian stretch) for now.
>>
>>     We are experiencing two major issues with the production trafic:
>>
>>         * We saw a huge increase in on disk tmp table space used
>>     (serveurs
>>     with 4Gb of free space in / (including /tmp) had to be added an
>>     additionnal 20Gb /tmp volume and even that is no enough everytime
>>
>>         * If the replication process has to write a tmp file to
>>     execute the
>>     query, and log slave updates is active then in the even of the /tmp
>>     volume being full, the update of the binlog will also fail
>>     erroneouslly
>>     with a "No space" error and will stop every following binlog write.
>>
>>     2019-02-20 15:24:12 140618945124096 [ERROR] Slave SQL: Could not
>>     execute
>>     Write_rows_v1 event on table xxx.yyyy; Error writing file
>>     '/tmp/MLxClNzW' (Errcode: 28 "No space left on device"),
>>     Error_code: 3;
>>     Error writing file '/var/lib/mysql/mysql-bin' (errno: 28 "No
>>     space left
>>     on device"), Error_code: 1026; Writing one row to the row-based
>>     binary
>>     log failed, Error_code: 1534; handler error
>>     HA_ERR_RBR_LOGGING_FAILED;
>>     the event's master log mysql-bin.006843, end_log_pos 42848762, Gtid
>>     0-70-467485, Internal MariaDB error code: 3
>>     2019-02-20 15:24:12 140618945124096 [Warning] Slave: Error
>>     writing file
>>     '/tmp/MLxClNzW' (Errcode: 28 "No space left on device") Error_code: 3
>>     2019-02-20 15:24:12 140618945124096 [Warning] Slave: Error
>>     writing file
>>     '/var/lib/mysql/mysql-bin' (errno: 28 "No space left on device")
>>     Error_code: 1026
>>     2019-02-20 15:24:12 140618945124096 [Warning] Slave: Writing one
>>     row to
>>     the row-based binary log failed Error_code: 15342019-02-20 15:24:12
>>     140618945124096 [ERROR] Error running query, slave SQL thread
>>     aborted.
>>     Fix the problem, and restart the slave SQL thread with "SLAVE
>>     START". We
>>     stopped at log 'mysql-bin.006843' position 42814887
>>
>>     Does someone know if they have been improvement in 10.2/10.3 on
>>     theses
>>     issues ?
>>
>>     _______________________________________________
>>     Mailing list: https://launchpad.net/~maria-discuss
>>     Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
>>     <mailto:maria-discuss@xxxxxxxxxxxxxxxxxxx>
>>     Unsubscribe : https://launchpad.net/~maria-discuss
>>     More help   : https://help.launchpad.net/ListHelp
>>
>