… consistent. If you execute SELECT * FROM id > 1000 FOR UPDATE twice, you expect to get… we try to insert another value on the second session: transaction2 > START TRANSACTION; transaction2 > INSERT INTO t ….1/en/innodb-locks-set.html. Conclusion MySQL uses REPEATABLE READ as the default isolation…
Post: MySQL 5.1 Command line client improvements
June 16, 2007 By 11 Comments
Just found this little handy feature today: mysql> insert into c select rand()*1000, sha1(rand()) from c; Query aborted by Ctrl+C… execution was interrupted So now if you press CTRL-C MySQL Command Line Client will not exit but will terminate query… assumed CTRL-C would also abort running query in previous MySQL versions and I’ve seen many monstrous queries left running…
Post: MySQL File System Fragmentation Benchmarks
March 21, 2008 By 28 Comments
… i in 1 10 100 1000 10000; do ./benchmark.php $i 10000000; mysql -e’drop database test1′; mysql -e’create database test1′; done… of tables and does specified number of inserts going to random tables. I used default MySQL settings for MyISAM (table_cache=64… from MyISAM results (above) the insert speeds does not degrade that badly until going from 1000 to 10000 tables, even though table…
Post: AUTO_INCREMENT and MERGE TABLES
September 11, 2008 By Leave a Comment
… affected (0.00 sec) mysql> insert into a1 values(2); Query OK, 1 row affected (0.00 sec) mysql> insert into a2 values(1…: mysql> alter table am auto_increment=1000; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show… Duplicates: 0 Warnings: 0 mysql> insert into am values(null); Query OK, 1 row affected (0.00 sec) mysql> select * from am; +—+ | i…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
… tables and HEAP for in memory tables. So first MySQL versions did not bother naming variables with storage engines … bulk inserts (multiple value inserts). Really helps if you have really bulk inserts 100-1000+ values per single insert statement. concurrent_insert – Enables concurrent insert (…
Post: Using VIEW to reduce number of tables used
September 28, 2007 By 12 Comments
… use VIEWs to reduce number of tables dramatically – merging say 1000 of users to the same table as VIEWs are significantly… insert data in the view which will be invisible. The VIEW approach works for SELECT, UPDATE, DELETE queries but not for INSERT: mysql> insert… so you could write triggers to perform insert inserts to base tables instead of views. BEFORE INSERT triggers would not help because the…
Post: Side load may massively impact your MySQL Performance
November 13, 2011 By 7 Comments
…=mysql –mysql-host=localhost –mysql-table-engine=innodb –mysql-db=test –oltp-table-name=md_cache_test_small –oltp-table-size=1100000 –mysql-user=msandbox –mysql… on this test system. It is worth to note MySQL actually uses midpoint insertion for its buffer pool replacement policy . Unfortunately by… pool. Lets repeat the benchmark with innodb_old_blocks_time=1000 which will correspond to 1 sec. Separate Sysbench gives about…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…enable logging atomically, not just for new connections as in MySQL. This is very helpful for measurement as otherwise we might…11ms 48us 119us 134us 36us # Rows sent 105.76k 0 1000 5.39 9.83 32.69 0 # Rows examine …0449 0.6% 260 0.0002 1.00 0.00 INSERT poller_output # MISC 0xMISC 0.8137 10.0% 3853 0…
Post: MySQL Server Memory Usage
May 17, 2006 By 18 Comments
…usage might be even larger – bulk inserts may allocate bulk_insert_buffer_size bytes of memory if …impractical. Here is why: List of rarely considered MySQL Server Memory Requirements Thread buffers can be …_buffer_size from 1MB to 4MB and 1000 max_connections increases peak memory consumption just …
Post: Impact of the sort buffer size in MySQL
October 25, 2010 By 8 Comments
… I inserted 100k rows with this simple script: #!/bin/bash NUMROW=100000 COUNT=0 while [ "$NUMROW" -gt "$COUNT" ] do UUID=`uuidgen` mysql test -e “insert into sorttest value (‘$UUID’);” let “COUNT=COUNT+1″ done…: #!/bin/bash for i in `seq 1 1000` do START=`date +%s.%N` OUT=`mysql -e “set session sort_buffer_size=32…
