… be down. Basically I have a problem regarding long running select query. A query runs for say 300 seconds and because…, other ‘innocuous’ queries which perform small insert or updates into completely different table (unrelated to the select query) also get hang up. If… server where I have 6GB innodb_buffer_pool_size split into 3 innodb_buffer_pool_instances. The entire main db would…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
… $C, $PAD = random_string() BEGIN INSERT INTO sbtest (id, k, c, pad) VALUES ($ID, $K, $C, $PAD); INSERT INTO sbtest_r10 (id, k) VALUES…) // distributed by pareto distribution BEGIN INSERT INTO sbtest (hid, mid, id, k) VALUES ($HID, $MID, $ID, $K); INSERT INTO sbtest_r10 (hid, mid, id… `hid`,`mid` is low selectivity is not good for fast inserts, but it is suitable for range selects by `id`. However it will…
Comment: Using Flexviews - part two, change data capture
March 22, 2013 By 26 Comments
…. In my case, insert into r select null, table_name,action,now() from r; Test it with continue try, until select * from binlog_consumer…
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… my experience, the main use case for PARTITIONing. I go into more details (and code) here: http://mysql.rjweb.org/doc…
Comment: MySQL Partitioning - can save you or kill you
April 30, 2013 By 45 Comments
… there are generally other considerations to take into account. He also didn’t answer the ‘Insert‘ part. 1. With a standard numeric… come out of the partition, not the whole table. 3. Insert on Duplicate Key (I dont do replace… ever)… if across… am really saying is that its not only about the ‘Selects‘ and Inserts, its also about what you do with the table…
Comment: MySQL Partitioning - can save you or kill you
April 25, 2013 By 45 Comments
… increase in your Selects, as you will be querying on a smaller dataset. You shoould have a faster insert as well, as you will be adding into a smaller dataset/index. Partitions are… the DB finding out which ‘chunk’ to work on, then inserting or reading your data. The only area you need to…
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…, query id 6994931 localhost root Sending data insert into test select * from sample ——– As you can see INSERT… SELECT has a lot of lock structs…
Post: Concurrent inserts on MyISAM and the binary log
May 14, 2008 By 7 Comments
… inserted into (and selected from). Instead, the blocked statements were because of INSERT… SELECT statements that were running against the table, selecting data from it and inserting into… and concurrent inserts into tbl2 are running fine. If you now run the following query, INSERT INTO tbl1 SELECT * FROM tbl2 The concurrent inserts into tbl2 can…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 8 Comments
…. The problems are even worse with long running queries like INSERT INTO table1 … SELECT … FROM table2. In version 5.1.22 and later… 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…
Post: Edge-case behavior of INSERT...ODKU
October 27, 2012 By 6 Comments
…insert a row at the end of the auto-increment range, and then try to insert another one: (root@localhost) [test]> insert into… this isn’t the case. (root@localhost) [test]> select * from update_test; +————+———-+———+———————+ | id | username | host_id | last_modified |…
