…to know it is not MySQL problem, might be even not problem with your MySQL configuration, queries and hardware, …from happening ? The answer is easy. Throttle the side load so it does not consume too much system resources…. For example if I need to delete old data instead of DELETE FROM TBL WHERE…
Comment: How to load large files safely into InnoDB with LOAD DATA INFILE
May 9, 2012 By 28 Comments
… differently in MySQL 5.5 (vanilla version). “The big LOAD DATA INFILE clogs the binary log and slows replication down. If the load takes… master LOAD DATA INFILE ‘/tmp/data.out’ INTO TABLE some_data_table; Using this strategy, replication continues to happen without a hitch and the LOAD DATA…
Comment: How to load large files safely into InnoDB with LOAD DATA INFILE
May 10, 2012 By 28 Comments
… row-based replication will indeed transfer the data, whereas statement-based might send the actual LOAD DATA INFILE command. There may even be differences based on the database engine and/or MySQL version. Regards, Stefan
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take…
Post: Join Optimizations in MySQL 5.6 and MariaDB 5.5
April 4, 2012 By 2 Comments
…warmed up, so that the relevant pages were already loaded in the buffer pool. Note that as mentioned …look at the status counters. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ join_buffer_size=6M &…are values for Innodb_buffer_pool_reads and Innodb_data_read. We can see that with appropriately sized …
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… warmed up, so that the relevant pages were already loaded in the buffer pool. Note that as mentioned at… IO bound workload, mentioned above. Counter Name MySQL 5.5 MySQL 5.6 MySQL 5.6 w/ read_rnd_bufer_size… 1.89G 1.53G 1.89G 1.53G Innodb_data_reads 120552 123872 100551 103011 77213 Innodb_pages_read…
Post: InnoDB's gap locks
March 27, 2012 By 14 Comments
…This feature provides better concurrency under heavy write load but needs additional precautions to avoid phantom … shared gap lock preventing the insertion of data to other sessions. How to troubleshoot gap ….1/en/innodb-locks-set.html. Conclusion MySQL uses REPEATABLE READ as the default isolation …
Comment: mysql-proxy, urgh performance and scalability ?
March 7, 2012 By 27 Comments
… heavy traffic, i am still afraid it might crash under load. Although not tried stress testing on it, would do once… you i guess mysql proxy would be a suitable option, spoken to a solution architect at pier1 data centre and she says most of our clients are using mysql proxy….. Are you aware of any hardware based load balancer…
Post: How to load large files safely into InnoDB with LOAD DATA INFILE
July 3, 2008 By 28 Comments
… customer ask me about loading two huge files into InnoDB with LOAD DATA INFILE. The goal was to load this data on many servers without… chunk: time mysql -e “set foreign_key_checks=0; set sql_log_bin=0; set unique_checks=0; load data local infile ‘infile… ]; do time mysql -e “set foreign_key_checks=0; set sql_log_bin=0; set unique_checks=0; load data local infile ‘/tmp…
Post: Resyncing table on MySQL Slave
June 29, 2008 By 10 Comments
… * FROM table INTO OUTFILE ‘/tmp/tbl.txt’; DELETE FROM tbl; LOAD DATA INFILE ‘tmp/tbl.txt’ INTO TABLE tbl; UNLOCK TABLES; This….01 sec) mysql> create table rep_new like rep_maint; Query OK, 0 rows affected (0.00 sec) mysql> load data infile ‘/tmp/rep….00 sec) This way you never actually delete any data and besides same data loaded to “rep” table you will get the copy…
