… every MySQL restart, the logs are flushed and a new binary log is created, looking at the current binary logs after restoring the snapshot, I… and skipping the DROP statement at position 336797160: mysql> show binary logs; +——————+———–+ | Log_name | File_size | +——————+———–+ … | mysql-bin.000022 | 336797725 | | mysql-bin.000023…. Because leaving a production server with active snapshots can affect performance, this is not really an advisable backup solution. If your…
Post: Faster Point In Time Recovery with LVM2 Snaphots and Binary Logs
February 23, 2012 By Leave a Comment
Post: MariaDB 5.3 is released as GA!
March 5, 2012 By 17 Comments
…in SHOW PROCESSLIST Group commit for the binary log makes the server much faster with the binary log enabled Faster queries through speedier …different needs and personalities, with different characteristics, and feature and performance improvements are moving rapidly forward for the MySQL world …
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
… gaps locks in your transactions affecting the concurrency and the performance you can disable them in two different ways: 1- Change… to prevent problems with the replication you should change the binary log format to “row”. Depending on the statement, the behavior of…. If your application can deal with phantom reads and your binary log is in row format, changing the ISOLATION to READ COMMITTED…
Comment: Best kept MySQLDump Secret
March 25, 2012 By 11 Comments
…, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER… table to be dumped can cause the SELECT that is performed by mysqldump to retrieve the table contents to obtain incorrect…
Post: MySQL Limitations Part 2: The Binary Log
October 23, 2010 By 11 Comments
… binary log and roll forward the state of your server to a desired point in time. But enabling the binary log reduces MySQL’s performance dramatically. It is not the logging itself that’s the problem — writing the log is usually not…
Post: Storing MySQL Binary logs on NFS Volume
July 30, 2010 By 19 Comments
… such setup. There are good reasons to have binary logs on NFS volume – binary logs is exactly the thing you want to survive the… prolonged period of time and let few GB of binary logs to accumulate the performance on Master will be high even with replication… with master. When I moved the Binary logs to the local storage I got very similar performance but there have been no degradation…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 21 Comments
… is if I’m not using replication and have my binary log disabled ? If replication is not used you can enable innodb… innodb_locks_unsafe_for_binlog option with caution. Note disabling binary logs is not enough to trigger relaxed locks. You have to… well. This is done so enabling binary log does not cause unexpected changes in locking behavior and performance problems. You also can use…
Post: MySQL 4 to MySQL 5 Upgrade performance regressions
June 6, 2007 By 8 Comments
… path if XA is disabled ? Many customers do not flush binary log anyway and use single transactional storage engine so they do… your performance back. XA support has its overhead anyway so you may not get same performance as with MySQL 4.1 Disable Binary Logging This allows to get group commit back but obviously you loose point in time recovery and replication. Use innodb_flush_log…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
… changes that have happened in the database, instead of replaying binary logs directly. Selecting a refresh method The refresh method you select….03 sec) Notice the difference in response time. Refresh method performance comparison For demonstration purposes, I did the following: Created one…
Post: Why audit logging with triggers in MySQL is bad for replication
September 29, 2008 By 10 Comments
… you to quickly parse the binary log files, extracting the relevant statements. mysqlbinlog bin_log.000001 | mysqlsla -lt binary – Check out http://hackmysql.com/mysqlsla_filters for how to filter by statement. Looking through the binary logs… As you can see from the above, the updates were performed in a different order, with the inserts being assigned a…
