… inside InnoDB 334 mysql tables in use 4, locked 4 3264 lock struct(s), heap size 440760, 799203 row lock(s) MySQL thread id 38, OS thread handle 0x2b3c1c776940, query id 16838101 localhost root Copying to tmp table create table t as SELECT…, cai.inventory_id All 4 tables in the query are locked!
Post: trx descriptors: MySQL performance improvements in Percona Server 5.5.30-30.2
April 12, 2013 By 3 Comments
… following kinds of transactions: MySQL transactions started with START TRANSACTION READ ONLY MySQL transactions created by a non-locking SELECT statement in the autocommit mode. The relevant optimization in MySQL 5.6 boils…
Post: SELECT LOCK IN SHARE MODE and FOR UPDATE
August 6, 2006 By 51 Comments
…: mysql> commit; Query OK, 0 rows affected (0.01 sec) SESSION2: mysql> select * from tst; Empty set (0.00 sec) mysql> select * from tst lock in share mode; +—+ | i | +—+ | 1 | +—+ 1 row in set (0.00 sec) mysql> select * from tst for update; +—+ | i | +—+ | 1 | +—+ 1 row in set (0.00 sec) #Standard SELECT does not see rows while SELECT for…
Post: Scaling problems still exist in MySQL 5.5 and Percona Server 5.5
May 20, 2011 By 11 Comments
…_event_wait_low,sync_array_wait_event,rw_lock_s_lock_spin,pfs_rw_lock_s_lock_fu nc,btr_search_build_page_hash…_multi_range_next,QUICK_RANGE_SELECT::get _next,rr_quick,sub_select,do_select,JOIN::exec,mysql_select,handle_select,execute_sqlcom_select,mysql_execute_command,my sql…_next,QUICK_RANGE_SELECT::get_next,rr_quick,sub_select,do_select,JOIN::exec,mysql_select,handle_select,execu te_sqlcom_select,mysql_execute_command,mysql_parse,dispatch_command…
Post: Helgrinding MySQL with InnoDB for Synchronisation Errors, Fun and Profit
November 24, 2011 By 2 Comments
…6238) ==9090== by 0x6A5E0C: JOIN::exec() (sql_select.cc:1863) ==9090== by 0x6A7D72: mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List
Post: Innodb Table Locks
July 31, 2012 By 4 Comments
… locks. Let me start with some examples. First lets run SELECT Query: —TRANSACTION 12303, ACTIVE 26 sec mysql tables in use 2, locked 0 MySQL… same select but add LOCK IN SHARE MODE so it performs locking reads to validate our theory: —TRANSACTION 12305, ACTIVE 9 sec mysql tables… lock(s) MySQL thread id 53173, OS thread handle 0x7ff75963b700, query id 3936362 localhost root Sending data select count(*) from sbtest,sbtest x lock…
Post: Eventual Consistency in MySQL
November 18, 2011 By 8 Comments
… generate an exclusion-join query for each foreign key relationship: mysql> SELECT CONCAT( ‘SELECT ‘, GROUP_CONCAT(DISTINCT CONCAT(K.CONSTRAINT_NAME, ‘.’, P.COLUMN…); mysql> SET FOREIGN_KEY_CHECKS=0; mysql> INSERT INTO Bar (ID,X,Y) VALUES (42,555,666); — THIS IS AN ORPHAN mysql> SELECT Bar… and detect orphans early so you can correct them. 1Innodb locking and Foreign Keys 2Instrumentation and the cost of Foreign Keys
Post: Percona Server on the Raspberry Pi: Your own MySQL Database Server for Under $80
February 25, 2013 By 10 Comments
…”). Select the image file (about 1.8GB .img file) and select the destination drive to write the image to. Make sure you selected… | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES | [...] mysql> SELECT “Hello World!” AS “Success!” \G *************************** 1. row…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
…created which temporary tables (both in memory and not): mysql> select * from information_schema.global_temporary_tables \G *************************** 1. row …cache 512619219 (509995888 + 2623331) File system 294352 (82672 + 211680) Lock system 318875832 (318747272 + 128560) Recovery system 0 (0 + 0…
Post: How to convert MySQL's SHOW PROFILES into a real profile
February 20, 2012 By 12 Comments
… with the Sakila sample database to demonstrate: mysql> SET profiling=1; mysql> pager cat > /dev/null mysql> SELECT * FROM nicer_but_slower_film_list… partially: SET @query_id := 1; SELECT STATE, SUM(DURATION) AS Total_R, ROUND( 100 * SUM(DURATION) / (SELECT SUM(DURATION) FROM INFORMATION_SCHEMA… | | closing tables | 0.000014 | 0.01 | 2 | 0.0000070000 | | System lock | 0.000010 | 0.01 | 1 | 0.0000100000 | | query end | 0…
