… I chose 8 x 200G EBS volumes in software RAID 10. Now let’s come to the MySQL configuration. MySQL Configuration I used…_threads = 8 innodb_write_io_threads = 4 innodb_file_per_table = 1 ## Disabling query cache query_cache_size = 0 query_cache_type = 0… share is that, the size of the table without secondary indexes is 56G while the size of the table with secondary indexes is 181G…
Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
… key_column, pk_column FROM tbl WHERE key_column=x ORDER BY key_column (Note that secondary keys in…MySQL 5.6 w/ read_rnd_bufer_size=4M MariaDB 5.5 MariaDB 5.5 w/ mrr_buffer_size=4M Created_tmp_disk_tables…Using where 1 SIMPLE nation eq_ref PRIMARY PRIMARY 4 dbt3.customer.c_nationkey 1 100.00 1 SIMPLE…
Post: Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact
March 12, 2012 By 12 Comments
… in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a… optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5 Now let’s take…`,`l_shipinstruct`) and the WHERE condition defined as: l_partkey = x and l_quantity >= 1 and l_quantity = 1 and l…
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
…, ACTIVE 755 sec 4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1 MySQL thread id 3, OS thread handle 0x7f84a78ba700, query id 163 localhost msandbox TABLE LOCK table `test`.`t` trx… id 19 page no 4 n bits 80 index `age` of table `test`.`t` trx id 72C lock_mode X RECORD LOCKS space…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…tables and Created_disk_tmp_tables, I’ll paste in mysql> show global variables like ‘%table_size%’; +———————+———–+ | Variable_name | Value | +———————+———–+ | max_heap_table_size | 268435456 | | tmp_table_size…X…
Post: Scaling problems still exist in MySQL 5.5 and Percona Server 5.5
May 20, 2011 By 11 Comments
… this: SELECT * FROM table WHERE deleted = 0 AND …size IN lists is typical for a lot of modern applications. The system I used for test was 4…mysql_execute_command,mysql_parse,dispatch_command,do_command,do_handle_one_conn ection,handle_one_connection,start_thread,clone 4…func,pfs_mute x_enter_func,buf…
Post: INSERT INTO ... SELECT Performance with Innodb tables.
July 12, 2006 By 21 Comments
… of table `test/sample` trx id 0 42304626 lock_mode X …; asc 0c88dedb358cd96c9069b73a57682a;…(truncated); 1: len 4; hex 00000005; asc ;; —————— —TRANSACTION 0 …mysql tables in use 1, locked 1 2539 lock struct(s), heap size 224576 MySQL…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
…72 index `PRIMARY` of table `test/iz` trx id 0 42313619 lock_mode X locks rec but … inside InnoDB 500 mysql tables in use 1, locked 1 3 lock struct(s), heap size 320 MySQL thread id…4 sec, process no 3396, OS thread id 1148250464, thread declared inside InnoDB 442 mysql tables in use 1, locked 0 MySQL…
Post: Updated msl (microslow) patch, installation walk-through!
April 20, 2008 By 17 Comments
…tables‘ ‘–enable-assembler’ ‘–enable-local-infile’ ‘–with-mysqld-user=mysql‘ ‘–with-unix-socket-path=/var/lib/mysql/mysql…size=4 -m64 -mtune=generic’ ‘CXXFLAGS=-O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector –param=ssp-buffer-size=4…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
… loss of performance with 5.0.X. I’m hopeful that 5.1.X will be better, but still not …>= 30 should be better than 4.1.xx. PZ: I should note MySQL 5.0 vs 4.1 has a lot of …“show innodb statusâ€. select * from table where id=5 show innodb status: Hash table size 10624987, used cells 1, node heap …
