…learn when working with large data things in MySQL is patience and careful planning. Both of … if you do not care about Table Locks or transactions). Besides these various trips and …portion of Index BTREE (even MyISAM which builds “normal” indexes by sort builds primary key and unique indexes using…
Post: How much memory Innodb locks really take ?
July 13, 2006 By 11 Comments
…_row_length: 61 Data_length: 100253696 Max_data_length: 0 Index_length: 128974848 Data_free: 0 Auto_increment: 1638401 Create_time… used to lock 1638400 rows, making it less than 3 bits per locked row; Now lets see how much exclusinve locks take: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select…
Post: MySQL Server Variables - SQL layer or Storage Engine specific.
June 8, 2006 By 14 Comments
…and HEAP for in memory tables. So first MySQL versions did not bother naming variables with … can help significantly. delay_key_write Delay updating indexes for MyISAM tables. Good for performance but tables… It works for storage engines with table locks or if LOCK TABLES are used. So in practice …
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…=/var/db/mysql –user=mysql –pid-file=/var/run/mysqld/mysqld.pid –skip-external-locking –port=3306 –socket=/var/db/mysql/mysql.sock consult 28852…. You cannot consider any one of these factors (schema, queries, indexing) in isolation, because they are tightly bound together, and tweaking…
Post: Announcing Percona Server 5.1.66-14.2
January 15, 2013 By Leave a Comment
… from the Percona Software Repositories). Based on MySQL 5.1.66, including all the bug … exclusive (X) row locks to shared (S) locks, these S locks prevent X locks from being taken and … Bug fixed #1039536 (Alexey Kopytov). In cases where indexes with AUTO_INCREMENT columns where correctly detected, …
Post: MySQL Slow query log in the table
May 31, 2007 By 1 Comment
As of MySQL 5.1 get MySQL slow query log logged in mysql.slow_log table instead of the file as you had… type which is used to store query execution time and lock time does not store fractions of the second. Today I… without need to have indexes would be quite handy I also should tell log table implementation in MySQL is well though of…
Post: Write contentions on the query cache
September 5, 2012 By 5 Comments
… 160ms). Of course, I wanted to know more about how MySQL executes this query. So I used commands you’re probably… the execution plan seemed reasonable and that fields were correctly indexed. With SHOW PROFILE, I saw that most of the time… competition for the lock. Of course, the question was: why did MySQL need so many accesses to the query cache lock? The answer…
Post: Table locks in SHOW INNODB STATUS
June 8, 2010 By 1 Comment
… id 1141287232 2 lock struct(s), heap size 368 MySQL thread id 8, query id 164 localhost root TABLE LOCK table `test/t1` trx id 0 4872 lock mode IX… table locks instead. I’ve seen various theories ranging from lock escalation to using table locks in special cases, for example when no indexes…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
… pages afterwards: mysql> select sq.*, pages / (@@innodb_buffer_pool_size / 16384) * 100 pct_buffer_pool from ( select table_name, index_name, count… buffer pool and the other secondary indexes have been pushed out of the buffer pool. mysql> select * from information_schema.innodb_buffer… | 0.000000 | 0 | 0 | 0 | 0 | 8 | 8 | | 7 | System lock | 0.000031 | 0.000031 | 0.000000 | 0.000000 | 0 | 0…
Post: Percona XtraDB Cluster reference architecture with HaProxy
June 20, 2012 By 63 Comments
…log_slave_updates innodb_locks_unsafe_for_binlog=1 innodb_autoinc_lock_mode=2 innodb_buffer…# 18: Primary, number of nodes: 3, my index: 0, protocol version 2 120619 13:20:17…mysql –mysql-engine-trx=yes –mysql-table-engine=innodb –mysql-host=127.0.0.1 –mysql-port=3307 –mysql-user=sbtest –mysql-…
