… referring to the amazing MySQL manual, especially the option and variable reference table. ….commanual Delayed_errorsblogpercona.commanual delayed_insert_limitblogpercona.commanual Delayed_insert_threadsblogpercona.commanual delayed_insert_timeoutblogpercona.commanual delayed_queue_sizeblogpercona…
Post: Innodb row size limitation
April 7, 2011 By 11 Comments
… TEXT, PRIMARY KEY (id) ) Engine=InnoDB; Now you insert some test data into it: mysql> INSERT INTO example -> VALUES ( ->Â Â NULL, ->Â… variable length columns and the complete row size exceeds this limit, InnoDB chooses variable length columns for off-page storage. In… mentioning that this limit applies to the byte-size of values, not the character-size. This means if you insert a 500…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
… disk tables and HEAP for in memory tables. So first MySQL versions did not bother naming variables with storage engines prefix… will be badly corrupted on crash. delayed_insert_limit, delayed_insert_timeout, delayed_queue_size Delayed inserts configuration. Technically it is not storage engine…
Post: Using Flexviews - part two, change data capture
March 25, 2011 By 11 Comments
… the trigger is immediate and affects every transaction. Finally, MySQL has limited trigger support, some of which is the cause of the… > ) > engine=innodb; Query OK, 0 rows affected (0.00 sec) mysql> insert into test.demo values (1,1); Query OK, 1 row… to `test`.`demo` will automatically be captured. Insert data in one transaction (two rows): mysql> insert into test.demo values (NULL,2),(NULL…
Post: MySQL on Amazon RDS part 1: insert performance
March 15, 2011 By 37 Comments
… Service (RDS) is a cloud-hosted MySQL solution. I’ve had some clients hitting performance limitations on standard EC2 servers with EBS… thought it would be interesting to measure the single-threaded insert throughput as sysbench ran the “prepare” phase and filled the… that it would really benefit them. Finally, a single-threaded insert workload is not very revealing. To understand the sustained write…
Post: MySQL Error Message Nonsenses
March 16, 2008 By 7 Comments
… is a limit as such but what is especially annoying is absolutely misleading error messages produced when this limit is reached: mysql> insert into… running concurrently? I hope with pluggable storage engine interface Sun/MySQL spends some time to make things consistent. There should be…
Post: Recovering Innodb table Corruption
July 4, 2008 By 19 Comments
… series of queries with LIMIT can be handly if you recover manually: mysql> insert ignore into test2 select * from test limit 10; Query OK…: 100 Warnings: 0 mysql> insert ignore into test2 select * from test limit 300; ERROR 2013 (HY000): Lost connection to MySQL server during query As…
Post: Dynamic row format for MEMORY tables
September 6, 2011 By 3 Comments
… engine (also known as HEAP) in MySQL has been one of the long-standing limitations caused by the fact that variable-length… to remove this limitation. In 2008 Igor Chernyshev of eBay implemented true-VARCHAR support for MEMORY tables for MySQL 5.0. Although… CHARSET=latin1; Also we modify the sysbench non-transactional INSERT test to insert strings of random length between 0 and 4096 in…
Post: Performance Schema tables stats
May 2, 2011 By 2 Comments
…: 84087466520 COUNT_INSERT: 4292 SUM_TIMER_INSERT: 1343751174852 MIN_TIMER_INSERT: 31913539 AVG_TIMER_INSERT: 313082268 MAX_TIMER_INSERT: 57121140547 COUNT_UPDATE…that required read IO mysql> select * from file_summary_by_instance order by COUNT_READ desc limit 6G *************************** 1. row …
Post: MySQL Users Conference - Innodb
May 9, 2007 By 6 Comments
… due to statement level MySQL Replication which among other requirements, needs all auto-increment values in multiple value insert to be sequential…. There are probably similar problems in INSERT IGNORE and ON DUPLICATE KEY UPDATE cases. Now In MySQL 5.1+ it is also… a lot of “Gotches” while many of them come from MySQL limits at the time when Innodb was first implemented. Many other…
