… test ADD INDEX (col2); Query OK, 0 rows affected (0.25 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> SHOW CREATE TABLE… Duplicates: 0 Warnings: 1 mysql> SHOW WARNINGS\G *************************** 1. row *************************** Level: Note Code: 1831 Message: Duplicate index ‘col2_2′ defined on the table…
Post: MySQL Query Patterns, Optimized - Webinar questions followup
June 3, 2013 By 2 Comments
… okay because each correlation name (k1, k2, k3) is an index lookup. Look at the EXPLAIN output — it accesses these first… would have to match the order of columns in the index. Example: WHERE (last_name, first_name) = (‘Karwin’, ‘Bill’); Q: On… were trying to determine the last episode of each TV show. Couldn’t you have used a SUBQUERY to fetch and…
Post: More on MySQL transaction descriptors optimization
April 26, 2013 By Leave a Comment
… views. However, the more general descriptors optimization in Percona Server showed fairly close results in our tests. In Dimitri’s tests…-test-mode=nontrx –oltp-read-only=off –oltp-index-updates=0 –oltp-non-index-updates=0 run POINT_SELECT + UPDATE QPS test…=on –oltp-test-mode=nontrx –oltp-read-only=off –oltp-index-updates=1 –oltp-non-index-updates=0 run
Post: How to recover table structure from InnoDB dictionary
April 22, 2013 By 4 Comments
…_table is ON Frm file corrupt, zeroed out, lost or SHOW CREATE TABLE crashes MySQL There is yet another source of… example is 1679: mysql> SELECT * FROM SYS_FIELDS WHERE INDEX_ID = 1679; +———-+—–+———-+ | INDEX_ID | POS | COL_NAME | +———-+—–+———-+ | 1679 | 0 | actor_id | +———-+—–+———-+ Which…. ./bin/constraints_parser.SYS_FIELDS -4f pages-ibdata1/FIL_PAGE_INDEX/<index_id> 4. Load dumps of the dictionary tables into some…
Post: Galera Flow Control in Percona XtraDB Cluster for MySQL
May 2, 2013 By 5 Comments
… translates into number of unique key lookups into the certification index, which is a hash table. A small fc_limit does however keep the certification index smaller in memory. It keeps the certification interval small, which… tell flow control came from this node because ‘Flow snt’ shows a message sent as soon as flow control is engaged…
Post: Announcing Percona XtraBackup 2.1.1 GA
May 15, 2013 By Leave a Comment
…of threads started by XtraBackup when rebuilding secondary indexes on innobackupex –apply-log –rebuild-indexes. This allows parallel processing of individual tables…perform the Incremental Backups now. Bugs Fixed: innobackupex is using SHOW MASTER STATUS to obtain binlog file and position. This …
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
…, pad CHAR(60) DEFAULT ” NOT NULL, PRIMARY KEY (id) ) CREATE INDEX k on sbtest$I(k) and tables sbtest$I_r10… stable, that is why I also show 5-minute moving averages to TokuDB. So TokuDB shows about 2.8x better throughput, and…
Post: MySQL and Percona Server in LinkBench benchmark
May 8, 2013 By 18 Comments
… 5.6.11 top mutexes is &buf_pool->mutex,&new_index->lock. I profiled 5.6.11 in this IO bound… MySQL still has performance issues around mutexes and Percona Server shows much better results Configurations and how to run benchmark: [mysqld…-innodb_io_capacity_max = 15000 #Percona only innodb_adaptive_hash_index_partitions=8 innodb_buffer_pool_instances=1 innodb_adaptive_flushing…
Post: Percona Server 5.5.30-30.2 rerelease fixes non-restart issue
April 24, 2013 By Leave a Comment
… MySQL will now be shipped with the libjemalloc library. Benchmark showing the impact of memory allocators on MySQL performance can be… explicitly named foreign key constraint which implied an implicit secondary index with the same name. Fixed by detecting such cases and…
Post: Is your MySQL buffer pool warm? Make it sweat!
April 16, 2013 By 6 Comments
… traffic except for replication. The buffer pool and adaptive hash index on the standby server will not have recently accessed pages… looked like this (we did 39 measurements), the next graph shows chunk 4’s workload warmed up with chunk 3. The…
