… answers: Q: Can you compare the use of subqueries/multiple joins vs. multiple queries (e.g. temp tables)? For performance, it’s… storing another column, and reinitializing the sequence after making certain insert/update/delete operations. If you have a dataset that changes… were using the tuple syntax to look up values against multiple columns in a compound index, then the order would matter…
Post: Choosing a MySQL HA Solution - Post-Webinar Q&A
June 7, 2013 By Leave a Comment
… which will allow you to stripe a TCP connection over multiple ports, and with a two-NIC bonded pair you’ll… to get the server up with –skip-grant-tables and insert a few rows into an InnoDB table, but trying to…
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
…. It does NOT mean you can’t modify data on multiple cluster nodes simultaneously. You can. It does NOT set an… users_groups, but also updates groups with some metadata: BEGIN; INSERT INTO users_groups (user_id, group_id) VALUES (100, 1…_user_id=100 WHERE id=1; COMMIT; How often might multiple users join the same group? Results In both of the…
Post: Follow these basics when migrating to Percona XtraDB Cluster for MySQL
May 1, 2013 By 10 Comments
… internally within the cluster, this is to avoid collisions when INSERTs are happening on not only a single node. However, this… new cluster especially if you are planning to write on multiple nodes. Have PRIMARY KEYS If you still have tables without…
Post: High Rate insertion with MySQL and Innodb
January 7, 2011 By 17 Comments
… using multiple tables. Using multiple key caches was the good solution at that time and we could get over 200K of inserts/sec….5 and this is what I used for start. Using multiple buffer pools with innodb_buffer_pool_instances=8 was very… your select queries dramatically. The inserts in this case of course are bulk inserts… using single value inserts you would get much lower…
Comment: MySQL File System Fragmentation Benchmarks
July 9, 2008 By 28 Comments
… max write speed, and operating system. Also are you doing multiple inserts per transaction. here is data from my tests: computer 1… multiple inserts per transaction for 1 innodb table (25 inserts or .2 seconds of data to be inserted) I got 1121, 1133, and 1166 inserts per second into that one table. using autocommit for innodb inserts I got 1125, and 1158 inserts per second into…
Comment: Why MySQL could be slow with large tables ?
January 31, 2008 By 141 Comments
…’s intended, but INSERTing in them is a nightmare. I use multiple record INSERTs (150 each time), and the first INSERT usually takes 600 secs (when the task starts). After this one is INSERTed… other tables). Once again, I use multiple inserts into the table, with sets of 150 records each: INSERT INTO T (col1,col2) VALUES…
Post: INSERT ON DUPLICATE KEY UPDATE and REPLACE INTO
January 18, 2007 By 15 Comments
… could set it to replace multiple values at the same time just as you have multiple value INSERT. As a side note: the… acting as update this really will insert NULL value, as col1 is undefined at this stage. INSERT ON DUPLICATE KEY UPDATE is… also number of similar cases when you want ether to insert the new row or update stats for existing row. I…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 8 Comments
… in your auto-increment columns. A simple INSERT IGNORE query creates gaps for every ignored insert, but this is undocumented behaviour. This…) Insert the same value multiple times. As you will see, the INSERT is ignored and no rows are inserted. The same behaviour as INSERT IGNORE: insert…
Post: Edge-case behavior of INSERT...ODKU
October 27, 2012 By 6 Comments
… this table were almost exclusively INSERT … ON DUPLICATE KEY UPDATE (INSERT ODKU), with the columns from the INSERT part of the statement corresponding… the auto-increment range, and then try to insert another one: (root@localhost) [test]> insert into update_test (id,username,host_id… documented behavior; the manual states that our INSERT … ODKU on a table with multiple unique indexes such as this one would be…
