…. Do you think you can show a program where row-locks are used by one user to lock records for edit/update…
Comment: Multi Column indexes vs Index Merge
May 10, 2012 By 30 Comments
… | prjid_ix,item_desc | 4,1 | NULL | 294 | Using intersect(prjid_ix,item_desc); Using where | | 1 | SIMPLE | PersonTbl2 | eq_ref | PRIMARY | PRIMARY…_jan02_db.LinksTbl2.treeid | 1 | Using where | +—-+————-+————-+————-+————————————————————–+——————–+———+———————————-+——+————————————————–+ 4 rows in set (0…
Post: Load management Techniques for MySQL
May 6, 2012 By 3 Comments
… too much system resources. Here are some specific techniques to use. Do push concurrency too high Many developers will test script… concurrency to where it does not overload the system. Unless it is really time critical process I would not use more than… need to delete old data instead of DELETE FROM TBL WHERE ts
Comment: InnoDB's gap locks
May 7, 2012 By 15 Comments
… sec) root@localhost:test 14:35:22>delete from t where i=25; Query OK, 1 row affected (0.00 sec… no 2909, OS thread id 1100101952 inserting mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap…
Comment: How to change innodb_log_file_size safely
May 2, 2012 By 14 Comments
…) This works on my site on a Debian Squeeze system where I have to edit: /etc/mysql/my.cnf Also make… not move them). The end result was: I could only use mysqldump to import all the data into a clean/fresh…
Comment: Announcement of Percona XtraDB Cluster 5.5.20 GA release
May 1, 2012 By 10 Comments
Hi, I use percona server 5.5.20, I would be testing xtradb … release, and noticed the configuration, but i did not understand where did all of the configuration go for example default_storage…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… i_o_orderdate 4 NULL 232722 100.00 Using where; Rowid-ordered scan; Using temporary; Using filesort 1 SIMPLE customer eq_ref PRIMARY,i_c_nationkey PRIMARY 4 dbt3.orders.o_custkey 1 100.00 Using where….o_orderkey 2 100.00 Using where So during cold query runs the optimizer would switch to using plan ‘a’, which does not…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… as range scans, where index parts after the part on which range condition is applied cannot be used for filtering records. For…` (`l_partkey`,`l_quantity`,`l_shipmode`,`l_shipinstruct`) and the WHERE condition defined as: l_partkey = x and l_quantity >= 1…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… memory is being used by current MEMORY tables: mysql> select sum(data_length+index_length) from information_schema.tables where engine=’memory…(data_length+index_length) from information_schema.global_temporary_tables where engine=’memory’; +——————————-+ | sum(data_length+index_length) | +——————————-+ | 126984 | +——————————-+ 1 row…
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
…rows. For example: transaction1> START TRANSACTION; transaction1> SELECT * FROM t WHERE i > 20 FOR UPDATE; +——+ | i | +——+ | 21 | | 25 | | 30 | +——+ transaction2> … gap locks? Is possible to detect those gap locks using SHOW ENGINE INNODB STATUS: —TRANSACTION 72C, ACTIVE 755 sec …
