June 19, 2013

Post: Small things are better

October 8, 2006 By 15 Comments

… failures. Sometimes corruption kind be mild, so ALTER TABLE to rebuild the table fixes it. Sometimes table needs to be dropped and recovered from… tables allow per table backup and recovery to happen faster. With MySQL and blocking ALTER TABLE there is yet another reason to keep tables small… at 256 groups of tables. Current data size is small enough so even single table would not be big problem but it is…

Filed Under: Insight for Developers Tagged With: , ,

Post: Find and remove duplicate indexes

June 20, 2012 By 9 Comments

…: mysql> alter table t add index(name); mysql> alter table t add index(name); mysql> alter table t add index(name); mysql> show create table t\G [...] KEY…: mysql> alter table t add index key_for_first_name(name); Query OK, 0 rows affected (0.01 sec) mysql> alter table t add… execution plan changes to Using where. There is also a big difference in the number of Handler_read_next requests, so…

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

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

February 26, 2013 By 1 Comment

I’ve never been a very big fan of MyISAM; I would argue that in most situations, … then I create one, the following warning is generated: mysql> alter table dir_test_innodb ADD FULLTEXT KEY (full_name, details); Query… time. If you try it, this is what happens: mysql> alter table dir_test_innodb ADD FULLTEXT KEY (full_name, details), ADD…

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

Post: High-Performance Click Analysis with MySQL

December 22, 2008 By 13 Comments

… huge MyISAM tables and taking downtime, I would not use MyISAM for anything but read-only tables when things get big.  And…, it doesn’t save you from the downtime caused by ALTER TABLE — a typical reason to think about master-master with failover… row-based replication in a way. When you’re updating big aggregate tables, don’t work with giant chunks of them at…

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

Post: Quickly finding unused indexes (and estimating their size)

December 5, 2012 By 2 Comments

…table, then you can rebuild the tablespace for your table by simply doing: mysql> alter table…

Filed Under: Insight for DBAs, MySQL, Percona Software

Post: How Percona does a MySQL Performance Audit

November 24, 2008 By 17 Comments

… | | Bytes_sent | 216162991863 | 7796961 | | Com_admin_commands | 255868807 | 11893 | | Com_alter_db | 0 | 0 | …snip This output is very useful when…_tmp_tables, I’ll paste in mysql> show global variables like ‘%table_size%’; +———————+———–+ | Variable_name | Value | +———————+———–+ | max_heap_table_size | 268435456 | | tmp_table_size… be fixed and bring big gains. Schema, query, and index optimization Analyzing and optimizing a server’s table and index structures, and…

Filed Under: Insight for Developers Tagged With:

Post: How to load large files safely into InnoDB with LOAD DATA INFILE

July 3, 2008 By 29 Comments

… I recall). When InnoDB loads the file, it creates one big transaction with a lot of undo log entries. This has… a million rows each. (InnoDB internally does operations such as ALTER TABLE in 10k row chunks, by the way; I chose 1M… one big 4.1GB transaction, then I did it 1 million rows at a time. Here’s the CREATE TABLE: CREATE TABLE load…

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

Post: Filtered MySQL Replication

November 7, 2007 By 8 Comments

To get this straight – I’m not a big fan of filtered or partial MySQL Replication (as of version … add more tables to be replicated to the slaves. Another issue is of course creating and altering tables. If you have Innodb tables you… same with MyISAM tables, because MyISAM can’t be disabled and so if you create tables as MYISAM or ALTER them to MyISAM…

Filed Under: Insight for DBAs Tagged With:

Post: Should you move from MyISAM to Innodb ?

January 12, 2009 By 59 Comments

…, problems dealing with very large number of tables, slow data load and ALTER TABLE and others. Another big one is COUNT(*) without where clause… is good for MyISAM kills Innodb, such as copying binary tables between the servers. It is important the team understands Innodb… pick one storage engine (typically Innodb) and when use other tables when it really gives substantial gains. I would not switch…

Filed Under: Insight for DBAs Tagged With: , ,

Post: Percona Toolkit 2.1.9 is ready

February 15, 2013 By Leave a Comment

…Fixed bug 1099665: pt-heartbeat 2.1.8 reports big time drift with UTC_TIMESTAMP The previous … option. * Fixed bug 918056: pt-table-sync false-positive error “Cannot nibble table because MySQL chose no index…primary key is tricky, the tool stops if –alter contains “DROP PRIMARY KEY”, and you have to …

Filed Under: Events and Announcements, Percona Software, Percona Toolkit Tagged With: , ,
« Older Posts
Newer Posts »