…take a look at the following excerpt from the binary log of a…DROP tmp_table. Let’s see another example, this time trying a…| ALTER TABLE test_2 DROP COLUMN c | | 7 | msandbox | localhost | …MySQL versions prior to 5.5: I remember a customer case where there is a reporting slave that daily runs a long…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
…a partner site, and you upload bad data there, you’ll be getting bad data back for a long time. And every time… an ad that gets a million impressions takes one row, and an… gain us anything? We dropped one column, and we just moved those…okay with long queries that read a lot of rows — MySQL can’t…
Post: Find unused indexes
June 30, 2012 By Leave a Comment
…MySQL we should try another approach. Percona Toolkit has a… DROP…column. Take in account that depending on the size of the log the server would need lot of process time…taking a long enough period of logs or statistics to analyze all relevant workload. In some cases there are processes that run once a…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…_STATS table and the ‘index_total_pages’ column. A page in Innodb is (usually) 16k, so… view to use that information: mysql> drop view if exists droppable_indexes; mysql> create view droppable_indexes… a large table can take quite some time. All the normal tricks and tips about doing a long blocking …
Post: Faster Point In Time Recovery with LVM2 Snaphots and Binary Logs
February 23, 2012 By 3 Comments
…taking a consistent backup of your MySQL databases – but did you know that you can now restore directly from a…mysql> drop table salaries; — Ooops! Query OK, 0 rows affected (0.49 sec) mysql> alter table employees add column…
Post: ALTER TABLE: Creating Index by Sort and Buffer Pool Size
June 27, 2012 By 4 Comments
…column to have bunch of long…: mysql> show table status like …Dropping column for 128M buffer pool, sized to illustrate the case of index being significantly larger than amount of memory. Blue Columns…a table and when builds all indexes by sort. The speedup we can observe is over 20 times…. Take Away…
Post: Small things are better
October 8, 2006 By 15 Comments
… can get corrupted because of MySQL bugs, OS Bugs or hardware bugs…. Sometimes table needs to be dropped and recovered from backup but… add extra column to 500GB Innodb table. It will probably take long hours…takes several days do you really can afford having no box to fall back to for such a long time…
Post: Announcing Percona Server 5.1.66-14.2
January 15, 2013 By Leave a Comment
…). Based on MySQL 5.1.66… code via a long argument to the…a new option innodb_locking_fake_changes which, when set to FALSE, makes fake transactions not to take… have been dropped, as that will already…indexes with AUTO_INCREMENT columns where correctly detected…this caused Query_time to increase …
Post: Find and remove duplicate indexes
June 20, 2012 By 9 Comments
…’ column. To take benefit from a composite index MySQL doesn’t need to use all the columns … if full index is a lot longer when both long and short index might…ALTER TABLE `test`.`t` DROP INDEX `name_2`; # name_3 is a left-prefix of name_4…help us to improve the execution time and decrease the size of …
Post: Drilling down to the source of the problem
November 8, 2008 By 7 Comments
… both reads and writes taking long time to complete, with progress almost paused (dropping from thousands to tens … this lock, suggest upgrading to MySQL 5.1 and call it a day. Learning more about …-increment columns and general updates. The stall was not caused by row level locks too. Taking a …
