Performance Improvement and Data Migration Strategies in mysql -

how take care of performance when have alter column in table billions of rows?

dml operations in large table tedious job requires proper analysis , migration strategies while performing operations. suppose in mysql database have giant table having 600 millions of rows, having schema operation such adding unique key, altering column, adding 1 more column cumbersome process takes hours process , there server time out. in order overcome that, 1 have come migration plan, 1 of jotting below.

1) suppose there table orig_x in have add new column colnew default value 0.

2) dummy table dummy_x created replica of orig_x except new column colnew.

3) data inserted orig_x dummy_x following settings.

4) auto commit set zero, data not committed after each insert statement hindering performance.

5) binary logs set zero, no data written in these logs.

6) after insertion of data bot feature set one.

 set autocommit = 0;  set sql_log_bin = 0;  insert dummy_x(col1, col2, col3, colnew) select col1, col2, col3, orig_x; set sql_log_bin = 1; set autocommit = 1; 

7) primary key can created newly inserted column, part of primary key.

8) unique keys can created. 9) can check status of server issuing following command

show master status 

10) it’s helpful issue flush logs mysql clear old logs.

11) in order boost performance run similar type of queries such above insert statement, 1 should have query cache variable on.

show variables 'have_query_cache'; query_cache_type = 1 

above steps migration strategy large table, below witting steps improve performance of database/queries. 1) remove unnecessary indexes on table, pay particular attention unique indexes these when disable change buffering. don't use unique index if have no reason constraint, prefer regular index.

2) if bulk loading fresh table, delay creating indexes besides primary key. if create them once after data loaded, innodb able apply pre-sort , bulk load process both faster , results in typically more compact indexes.

3) more memory can in performance optimization. if show engine innodb status shows reads/s under buffer pool , memory , number of free buffers (also under buffer pool , memory) zero, benefit more (assuming have sized innodb_buffer_pool_size correctly on server.

4) database table gets re-indexed after every insert. that's heavy lifting database, when queries wrapped inside transaction, table not re-indexed until after entire bulk processed. saving lot of work.

5) mysql servers have query caching enabled. it's 1 of effective methods of improving performance quietly handled database engine. when same query executed multiple times, result fetched cache, quite fast.

6) using explain keyword can give insight on mysql doing execute query. can spot bottlenecks , other problems query or table structures. results of explain query show indexes being utilized, how table being scanned , sorted etc...

7) if application contains many join queries, need make sure columns join indexed on both tables. affects how mysql internally optimizes join operation.

8) in every table have id column primary key, auto_increment , 1 of flavors of int. preferably unsigned, since value cannot negative.

9) if have user’s table has unique username field, not make primary key. varchar fields primary keys slower. , have better structure in code referring users id's internally.

10) when perform query script, wait execution of query finish before can continue. can change using unbuffered queries. saves considerable amount of memory sql queries produce large result sets, , can start working on result set after first row has been retrieved don't have wait until complete sql query has been performed.

11) database engines, disk perhaps significant bottleneck. keeping things smaller , more compact helpful in terms of performance, reduce amount of disk transfer.

12) 2 main storage engines in mysql myisam , innodb. each have own pros , cons.myisam read-heavy applications, doesn't scale when there lot of writes. if updating 1 field of 1 row, whole table gets locked, , no other process can read until query finished. myisam fast @ calculating select count(*) types of queries.innodb tends more complicated storage engine , can slower myisam small applications. supports row-based locking, scales better. supports more advanced features such transactions.