June 18, 2013

Post: MySQL and the SSB - Part 2 - MyISAM vs InnoDB low concurrency

May 22, 2013 By Leave a Comment

…. A star schema presents some unique challenge to the database optimizer. The SSB benchmark consists of four sets of queries. Each… caching of data at the filesystem level. The InnoDB indexes were built using ALTER TABLE fast index creation (merge sort). For the MyISAM… a 10GB key buffer. I used ALTER TABLE DISABLE KEYS and built the keys with sort via ALTER TABLE ENABLE KEYS. my.cnf [mysqld…

Filed Under: Benchmarks, MySQL Tagged With: , , , , , , ,

Post: The small improvements of MySQL 5.6: Duplicate Index Detection

May 31, 2013 By Leave a Comment

… GTID-based replication, InnoDB Fulltext, Memcached integration, a more complete performance schema, online DDL and several other InnoDB and query optimizer improvements. … ALTER TABLE test ADD INDEX (col2); Query OK, 0 rows affected (0.22 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE …

Filed Under: Insight for DBAs, MySQL, On Ground Training, Percona Toolkit, Training Tagged With: , , , , , , , , ,

Post: Percona XtraBackup 2.0.7 for MySQL available for download

May 6, 2013 By 1 Comment

…XtraBackup has implemented support for the InnoDB Buffer Pool Preloading introduced in MySQL…ALTER TABLE IMPORT TABLESPACE on MySQL and Percona Server 5.6 as described in Exporting and Importing Tables…tables and slow IO then XtraBackup can spend a lot of time opening all the tablespaces. Optimization…

Filed Under: MySQL Tagged With: , , ,

Post: Percona XtraBackup 2.1.0 'release candidate' for MySQL available for download

May 7, 2013 By Leave a Comment

…XtraBackup has implemented support for the InnoDB Buffer Pool Preloading introduced in MySQL…ALTER TABLE IMPORT TABLESPACE on MySQL and Percona Server 5.6 as described in Exporting and Importing Tables…tables and slow IO then XtraBackup can spend a lot of time opening all the tablespaces. Optimization…

Filed Under: MySQL, Percona Software Tagged With: , , ,

Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size

June 27, 2012 By 4 Comments

… and index size: mysql> show table status like “sbtest” \G *************************** 1. row *************************** Name: sbtest Engine: InnoDB Version: 10 Row_format:…: If you’re having large tables and need to run ALTER TABLE which rebuilds the table or OPTIMIZE TABLE do not forget to enable …

Filed Under: Insight for DBAs, MySQL

Post: How well does your table fits in innodb buffer pool ?

December 9, 2010 By 12 Comments

… innodb_sys_tables ON table_id = innodb_sys_tables.id JOIN innodb_index_stats ON innodb_index_stats.table_name = innodb_sys_tables.name AND innodb_sys_indexes.name = innodb_… as how buffer pool is affected by batch jobs, alter tables, optimize table etc – the lasting impact these may have on system …

Filed Under: Insight for DBAs, MySQL Tagged With: , , , ,

Post: Improved InnoDB fast index creation

November 6, 2011 By 4 Comments

… reported by SHOW TABLE STATUS versus 265 MB index size with the optimization disabled. OPTIMIZE TABLE OPTIMIZE TABLE is mapped to ALTER TABLE … ENGINE=InnoDB for InnoDB tables and thus, is… –innodb-optimize-keys ignores foreign keys because InnoDB requires a full table rebuild on foreign key changes. So adding them back with a separate ALTER TABLE…

Filed Under: Benchmarks, Insight for DBAs, MySQL

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

…alter table dir_test_innodb ADD FULLTEXT KEY (full_name, details), ADD FULLTEXT KEY (details); ERROR 1795 (HY000): InnoDB…

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,

Post: Thinking about running OPTIMIZE on your Innodb Table ? Stop!

December 9, 2010 By 18 Comments

…The problem is…. OPTIMIZE TABLE for Innodb tables does not get …alter table a drop key c; Query OK, 0 rows affected (0.46 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> optimize table a; +——–+———-+———-+——————————————————————-+ | Table…

Filed Under: Insight for DBAs, MySQL Tagged With: , ,

Post: Tuning InnoDB Concurrency Tickets

May 24, 2010 By 5 Comments

… table scan is performed) Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> ALTER TABLE test_table… Used Query OK, 3 rows affected (0.02 sec) mysql> ALTER TABLE `child` ADD PRIMARY KEY (`id`,`parent_id`); — 0 Tickets Used…). In other words, if you’re optimizing for throughput in this scenario, you will tune innodb_concurrency_tickets to the 99th percentile…

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: , , , ,
« Older Posts