June 19, 2013

Post: Benchmarking Percona Server TokuDB vs InnoDB

May 7, 2013 By 32 Comments

… INSERT INTO sbtest (hid, mid, id, k) VALUES ($HID, $MID, $ID, $K); INSERT INTO …selectivity is not good for fast inserts, but it is suitable for range selects…insert-roll-2, command line to run: sysbench –test=insert_roll.lua –oltp-table-size=10000 –mysql-user=root –oltp-tables-count=32 –mysql…

Filed Under: Benchmarks, MySQL, Percona Server Tagged With: , , ,

Comment: MySQL Partitioning - can save you or kill you

April 30, 2013 By 45 Comments

… stay in the buffer_pool.) If I recall correctly, any INSERT/DELETE/etc always opens all partitions, even before thinking about… you find otherwise.) I would argue that single-row queries (SELECT, INSERT, DELETE, UPDATE) are similar in speed between PARTITIONed or non… main use case for PARTITIONing. I go into more details (and code) here: http://mysql.rjweb.org/doc.php/partitionmaint The third…

Filed Under: Insight for DBAs, MySQL Tagged With: , ,

Comment: MySQL Partitioning - can save you or kill you

April 30, 2013 By 45 Comments

… generally other considerations to take into account. He also didn’t answer the ‘Insert‘ part. 1. With a …could easily be quicker on a partitioned table, as Mysql can fire off one thread per partition (at … saying is that its not only about the ‘Selects‘ and Inserts, its also about what you do with …

Filed Under: Insight for DBAs, MySQL Tagged With: , ,

Post: INSERT INTO ... SELECT Performance with Innodb tables.

July 12, 2006 By 22 Comments

…: SELECT * FROM tbl1 INFO OUTFILE ‘/tmp/tbl1.txt’; LOAD DATA INFILE ‘/tmp/tbl1.txt’ INTO TABLE tbl2; instead of: INSERT INTO tbl2 SELECT * from tbl1; INSERT … INTO… size 224576 MySQL thread id 1794751, query id 6994931 localhost root Sending data insert into test select * from sample ——– As you can see INSERT… SELECT has…

Filed Under: Insight for DBAs Tagged With: ,

Post: Eventual Consistency in MySQL

November 18, 2011 By 8 Comments

… are orphans. mysql> INSERT INTO Foo (A,B) VALUES (111,2222), (333,444); mysql> INSERT INTO Bar (ID,X,Y) VALUES (21,333,444); mysql> SET FOREIGN_KEY_CHECKS=0; mysql> INSERT INTO Bar (ID,X,Y) VALUES (42,555,666); — THIS IS AN ORPHAN mysql> SELECT Bar_ibfk…

Filed Under: Insight for DBAs, MySQL

Post: Percona XtraDB Cluster: Failure Scenarios with only 2 nodes

July 25, 2012 By 6 Comments

…: percona1 mysql> insert into percona values (0,’percona1′,’jaime’); Query OK, 1 row affected (0.02 sec) percona1 mysql> select * from percona; +—-+—————+——–+ | id | inserted_from | name…) percona2 mysql> insert into percona values (0,’percona2′,’daniel’); Query OK, 1 row affected (0.02 sec) percona2 mysql> select * from percona; +—-+—————+——–+ | id | inserted_from | name…

Filed Under: MySQL, XtraDB Cluster

Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE

November 29, 2011 By 8 Comments

… and assigns the new value to the column. Prior to MySQL 5.1.22 InnoDB used a method to access that… KEY `uniqname` (`name`) ) ENGINE=InnoDB; Insert a value using a LEFT OUTER JOIN: insert into foo(name) select 1 from mutex left outer join… will see, the INSERT is ignored and no rows are inserted. The same behaviour as INSERT IGNORE: insert into foo(name) select 1 from mutex left…

Filed Under: MySQL

Post: Edge-case behavior of INSERT...ODKU

October 27, 2012 By 6 Comments

…. So, MySQL checks our INSERT, sees …insert a row at the end of the auto-increment range, and then try to insert another one: (root@localhost) [test]> insert into…case. (root@localhost) [test]> select * from update_test; +————+———-+———+———————+ | id | username | host_…

Filed Under: Insight for DBAs, Insight for Developers, MySQL

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

… of the following statements: mysql> insert into dir_test_innodb3 (fts_doc_id, full_name) values (1, ‘john smith’); mysql> select * from dir_test_innodb3… | +—————————+——–+ 10 rows in set (0.00 sec) mysql> insert into dir_test_innodb (full_name, details) SELECT reverse(full_name), details FROM dir_test…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: Concatenating MyISAM files

November 19, 2012 By 5 Comments

… rows. Not small… I calculated that with something like insert into MyISAM_table… select * from Innodb_table… would take about 10 days. The….01 sec) mysql> create table test_concat_part like test_concat; Query OK, 0 rows affected (0.01 sec) mysql> insert into test_concat… affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> insert into test_concat_part (id,data) value (44,’todelete’),(4,’four…

Filed Under: MySQL
« Older Posts