…-online-schema-change, a tool that enables you to ALTER large tables with no blocking or downtime. As you know, MySQL locks tables for most ALTER operations, but pt-online-schema-change… 2.1 version of the tool removes those limitations and makes the tool fully featured and much easier to use. The…
Post: Give feedback on a pt-online-schema-change update
March 14, 2012 By 2 Comments
… a specification for updating pt-online-schema-change. The outline of the changes I want to make is here: http://www.percona.com/docs/wiki/ptdev:blueprints:redesign-pt-online-schema-change The idea is to make the… are similar to those added to the newly rewritten pt-table-checksum. If you have any comments on it, please write…
Post: Hacking to make ALTER TABLE online for certain changes
October 29, 2007 By 26 Comments
…you could end up waiting days. If you want to make this happen quick – there’s another way. Not documented….00 sec) mysql> SHOW CREATE TABLE `huge_table`\G *************************** 1. row *************************** Table: huge_table Create Table: CREATE TABLE `huge_table` ( `id` int(6) NOT NULL, …
Post: Statistics of InnoDB tables and indexes available in xtrabackup
September 14, 2009 By 6 Comments
… with online (although online is also supported, but you may have inexact results). Let’s see how it works. I have one table… what was filled during about 2.5 years. The table is: CREATE TABLE `link_out104` ( `domain_id` int(10) unsigned NOT NULL… cases we insert into the primary key in order which makes things very predictable, but the inserts into the secondary key…
Post: Heikki Tuuri answers to Innodb questions, Part II
November 4, 2007 By 13 Comments
… that you test reorganizing that table on a separate computer, maybe a replication slave. Can you make the reorganization run in a… a bit. I also like to keep tables small enough, “sharding” to many tables before they become so large it would take… – it should be possible to implement online reorganize which locks and reorganizes small portions of table online. Q38: Seems that adaptive hash indexes…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
…SELECT ….. WHERE [conditions] ORDER BY [sort] LIMIT N,M Make sure it uses index It is very important … For example you may put index on(gender,last_online) assuming most people will have gender specified, as … 2 rows in set (0.00 sec) However if first table has “const” or “system” access type it is …
Post: Aligning IO on a hard disk RAID – the Benchmarks
June 9, 2011 By 11 Comments
…Online 0/0/0 SEAGATE ST3600057SS 558.911 Hard Disk SAS Online 0/0/0 SEAGATE ST3600057SS 558.911 Hard Disk SAS Online…run/mysqld/mysqld.sock innodb_file_per_table = true innodb_data_file_path = ibdata1:10M… a larger RAID5 system where it should make even bigger difference on writes. Another interesting…
Post: MySQL: Followup on UNION for query optimization, Query profiling
August 14, 2006 By 12 Comments
… for age and mediumint for zip which makes 4 right answer for using full key. …by last_online desc limit 10) ORDER BY last_online desc limit 10; +—-+————–+————–+——+—————+——+———+——-+——-+—————-+ | id | select_type | table | type | possible_…
Post: MySQL-Memcached or NOSQL Tokyo Tyrant - part 1
October 15, 2009 By 16 Comments
…anything. Now bumping this up to 2GB actually makes the TPS jump around a lot, sometimes hitting… simple. A classic example is a simple online game. An online game may only require that an application… called email address ). Seems simple enough right? My two tables each contain 5M rows of data. let’s…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… customers who do click analysis, site analytics, search engine marketing, online advertising, user behavior analysis, and many similar types of work… rows. That will make the indexes less than half the size. In real life this technique often makes the table+index much less… can make the application believe it has a custom table, which is really constructed behind the scenes from the name-value tables in…
