…slave before INSERT… SELECT statement and possibly result in different data than on master. Locking rows in the …be done fore replication to work right in MySQL before 5.1. In MySQL 5.1 this as well as…might chop transactions and process rows by small bulks, which do not cause long enough locks …
Post: SELECT LOCK IN SHARE MODE and FOR UPDATE
August 6, 2006 By 51 Comments
… article comparing locking hints in MySQL and SQL Server. In MySQL/Innodb LOCK IN SHARE MODE and SELECT FOR …simple example: SESSION1: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> insert into tst values(1… is memory and CPU overhead. For some bulk operations it would be more efficient to …
Post: Aligning IO on a hard disk RAID – the Benchmarks
June 9, 2011 By 13 Comments
… IO alignment, not different MySQL configurations, I didn’t try out different MySQL versions or settings. … tmp_table_size = 64M key_buffer_size = 8M bulk_insert_buffer_size = 8M myisam_sort_buffer_size = … similar, due to high concurrency response time in the first case is significantly better. It…
Post: MySQL 5.6.7-RC in tpcc-mysql benchmark
October 8, 2012 By 22 Comments
… is there, so I decided to test how it performs in tpcc-mysql workload from both performance and stability standpoints. I can…_instances parameter may change the result significantly, especially in intensive IO workloads. In MySQL 5.6 it is finally possible to achieve stable… = 8M read_buffer_size = 1M read_rnd_buffer_size = 4M bulk_insert_buffer_size = 8M myisam_sort_buffer_size = 8M myisam_max…
Comment: Linux schedulers in tpcc like benchmark
January 31, 2009 By 19 Comments
… lot of refining in order to expose and enforce I/O priorities and arbitration. I’m not sure whether MySQL do leverage… TOS field) which would allow exposing this to consumers (although MySQL offers INSERT DELAYED). Back to the subject, I don’t know… device layout) or is it more of a “plain sequential bulk insert/update on a very few tables” thing? Seekwatcher (http://oss…
Post: How many partitions can you have ?
December 5, 2009 By 19 Comments
… THAN(900001), PARTITION p1000000 VALUES LESS THAN(1000001) ); I used MySQL 5.1.41 for my tests increasing buffer pool to… C column was set same as ID column) using bulk insert statements – 1000 rows in each. Loading the data was taking 9 seconds… UPDATE set c=c+1 to my bulk inserts) the regression became even larger. 1 partition insert took 50 seconds to complete, 10…
Comment: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
Bulk insert buffer can speed things significantly for bulk inserts by avoiding random IO for updating index. It is still … bit more job by parsing the file but in practice overhead is minimal: mysql> load data infile ‘/tmp/z.txt’ into table… Skipped: 0 Warnings: 0 mysql> truncate table two; Query OK, 0 rows affected (0.20 sec) mysql> insert into two select * from one…
Post: Predicting how long data load would take
May 24, 2007 By 14 Comments
… not end here. If indexes are built by inserts in Btree order in which entries are inserted in the index can affect performance dramatically. Table… load speed. Depending on load type MyISAM may benefit from bulk_insert_tree_size increase myisam_sort_buffer_size or key_buffer… Options There two main ways to load data in MySQL you can use Multiple value insert (standard mysqldump output) or LOAD DATA INFILE…
Comment: Edge-case behavior of INSERT...ODKU
October 31, 2012 By 6 Comments
… keys when hitting duplicates in the unique index. I was forced to find another method, and my batch insert / update method was… determine if it will be an insert or a select (and you must do those operations in bulk anyway to offset the overhead… good feature in MySQL. InsODKU would be much better implemented on the server side in the same fashion I have done in my client…
Comment: When is it a time to upgrade memory ?
December 9, 2009 By 11 Comments
…think my server can’t load all indexes in memory and this is slowing it down. …: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql max_connections=600 connect_timeout=…-character-set=utf8 default-storage-engine=InnoDB bulk_insert_buffer_size=8M # Innobd Tuning #innodb_force_…
