June 19, 2013

Post: MySQL Upgrade Webinar Questions Followup

June 28, 2012 By 7 Comments

… Server with enabled expand_fast_index_creation feature, as I just wrote this option can help creating tables a lot faster. You… depreciated in future MySQL versions also MyISAM tables are essentially at the state they were in MySQL 4.1 while Innodb storage engine got… changed to Innodb in MySQL 5.5, meaning you might need to set storage_engine=MYISAM if you’re relaying on tables to be…

Filed Under: Insight for DBAs, MySQL

Post: Hijacking Innodb Foreign Keys

January 2, 2012 By 2 Comments

… keys requires table rebuild. Yes you get it right even though Innodb is able to drop indexes without rebuilding table since MySQL 5.1 (Innodb Plugin) dropping foreign keys which should only require meta data change needs a table rebuild…) Innodb will enforce foreign key constrains to non existing table the same way as when there are non existing rows – if you’re…

Filed Under: Insight for DBAs, MySQL

Post: Data compression in InnoDB for text and blob fields

May 30, 2012 By 2 Comments

…InnoDB’s new Barracuda file format (available since InnoDB plugin 1.1 or MySQL 5.5) you can now leverage table…index page).  Based on our analysis 75% of the blobs stored in the table…

Filed Under: Insight for DBAs, Insight for Developers, MySQL, Percona Software

Post: ANALYZE: MyISAM vs Innodb

September 3, 2008 By 9 Comments

… stats are but how good plans you’re getting for your queries. If you’re getting as good plans as with perfect… index scans to find number of exact values in the table. Now let us populate antest_innodb table which is same but uses Innodb format: mysql… row in set (0.00 sec) mysql> show index from antest_innodb; +—————+————+———-+————–+————-+———–+————-+———-+——–+——+————+———+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub…

Filed Under: Insight for DBAs Tagged With: ,

Post: SHOW INNODB STATUS walk through

July 17, 2006 By 40 Comments

…are still however quite helpful if you’re just looking at output. Next sections in …, thread declared inside InnoDB 166 mysql tables in use 1, locked 0 MySQL thread id 8078,…index Innodb builds for some pages to speed up row lookup replacing btree search with hash search. This section shows hash table…

Filed Under: Insight for DBAs Tagged With:

Post: Side load may massively impact your MySQL Performance

November 13, 2011 By 7 Comments

…mysql –mysql-host=localhost –mysql-table-engine=innodb –mysql-db=test –oltp-table-name=md_cache_test_small –oltp-table-size=1100000 –mysql-user=msandbox –mysql-password=msandbox –mysql-socket=/tmp/mysql…

Filed Under: Benchmarks, Insight for DBAs, MySQL

Post: InnoDB Full-text Search in MySQL 5.6: Part 2, The Queries!

March 4, 2013 By 7 Comments

…InnoDB* table with a single VARCHAR column named “value”. OK, sounds easy enough: mysql: SHOW CREATE TABLE innodb_myisam_stopword\G *************************** 1. row *************************** Table: innodb_myisam_stopword Create Table…index on the InnoDB table…

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

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

February 26, 2013 By 1 Comment

… InnoDB table that has never had a full-text index on it, and then I create one, the following warning is generated: mysql> alter table…, even if you drop all of the FT indexes for a given table, you’re still left with all of the other FTS…_S.INNODB_FT_* tables. In particular, the INNODB_FT_CONFIG table is supposed to “display metadata about the FULLTEXT index and associated processing for an InnoDB table…

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

Post: High-Performance Click Analysis with MySQL

December 22, 2008 By 13 Comments

…InnoDB tables — don’t use GUIDs.  Which brings me to my next point: Use InnoDB Assuming that you will use the stock MySQL…Be Careful With Data Integrity When you’re storing several levels of aggregation, and …indexes less than half the size. In real life this technique often makes the table+index…

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

Post: How Percona does a MySQL Performance Audit

November 24, 2008 By 17 Comments

…much greater improvements, for example. If you’re familiar with Malcolm Gladwell’s excellent book … that there are InnoDB tables whose .frm files exist but have been dropped from InnoDB, for example. …users in the mysql.* tables, running mk-duplicate-key-checker to find redundant indexes, and so on….

Filed Under: Insight for Developers Tagged With:
« Older Posts
Newer Posts »