pt-online-schema-change

Percona has a tool called the Percona Online Schema change which is part of the Percona Toolkit. I've heard it referred to as OSC. It is super useful for adding indexes, changing the storage engine of a table, adding a column, etc, when you want to be able to do the operation "online" without causing the table to lockup and essentially be unavailable for the duration of the operation. On large tables this can take a long time, I've done it on tables with 500 million rows and it has taken 6 hours to perform the operation.

The tool works by creating a new table, performing the changes on the empty table and then moving the data from the old table to the new table in chunks. Once the data is all moved over, the tool syncs up the two tables one last time so that the new data written to the old table while it was moving the other data gets moved over and then it renames the new table to the same name as the old table and then drops the old table. One of the added bonuses of doing all this work is the table also get optimized at the same time. If you do something on a large table that sees a lot of DELETES and UPDATES then is is likely fragmented. I've seen situations where a 100 GB table shrunk down to 50 GB after adding an index because it was so highly fragmented. However, within a few weeks the fragmentation was back.

The syncing of data works by means of triggers so the tool cannot be used on any tables that already have triggers. There are a number of options you can use when running the tool such as "Dry Run", and telling the tool if the server gets too busy to pause. You can also change the chunk size to be as small or large as you see fit. I typically use 10000 as the chunk size. I've tried a chunk size of 1000 and it is much slower for large tables. You could get away with using 50000 but I'd be careful of something too large.

I set a thresholds if 400 Threads_running which is really high in my examples. Threads_running of 50 might be high enough on your server that you would want the tool to pause and wait until the server is less busy to continue syncing data. The tool won't actually perform the operation unless you specify "--execute" (see below).

The tool also has a number of safety features where it checks replication lag on all the slaves and it won't run until replication lag catches up. Example output from using Online Schema Change is below.

Here are two examples I've done frequently:


Example adding an index to a table:

# non prod
## dry run
./pt-online-schema-change --host=non_prod.awesome.com D=my_non_prod_db,t=my_table --ask-pass -u'!me' --critical-load Threads_running=400 --max-load Threads_running=375 --chunk-size=10000 --dry --alter 'ADD INDEX idx_colum1_column2 (column_1, column_1)' > np_add_index_dry_results.log

## actual
./pt-online-schema-change --host=non_prod.awesome.com D=my_non_prod_db,t=my_table --ask-pass -u'!me' --critical-load Threads_running=400 --max-load Threads_running=375 --chunk-size=10000 --execute --alter 'ADD INDEX idx_column_1_column_2 (column_1, column_2)' > np_add_index_actual_results.log


# prod
## dry run
./pt-online-schema-change --host=prod.awesome.com D=my_prod_db,t=my_table --ask-pass -u'!me' --critical-load Threads_running=400 --max-load Threads_running=375 --chunk-size=10000 --dry --alter 'ADD INDEX idx_column_1_column_2 (column_1, column_2)' > add_index_dry_results.log

## actual
./pt-online-schema-change --host=prod.awesome.com D=my_prod_db,t=my_table --ask-pass -u'!me' --critical-load Threads_running=400 --max-load Threads_running=375 --chunk-size=10000 --execute --alter 'ADD INDEX idx_column_1_column_2 (column_1, column_2)' > add_index_actual_results.log

Example changing storage engine from MyISAM to InnoDB:

# non prod
## dry run
./pt-online-schema-change --host=non_prod.awesome.com D=my_database,t=my_table -u'!me' --ask-pass --critical-load Threads_running=400 --max-load Threads_running=375 --chunk-size=10000 --dry --alter "ENGINE = InnoDB" > non_prod_dry_my_table_change_table_engine.log
# actual
./pt-online-schema-change --host=non_prod.awesome.com D=my_database,t=my_table -u'!me' --ask-pass --critical-load Threads_running=400 --max-load Threads_running=375 --chunk-size=10000 --execute --alter "ENGINE = InnoDB" > non_prod_actual_my_table_change_table_engine.log

# prod
## dry run
./pt-online-schema-change --host=prod.awesome.com D=my_database,t=my_table -u'!me' --ask-pass --critical-load Threads_running=400 --max-load Threads_running=375 --chunk-size=10000 --dry --alter "ENGINE = InnoDB" > prod_dry_my_table_change_table_engine.log
## actual
./pt-online-schema-change --host=prod.awesome.com D=my_database,t=my_table -u'!me' --ask-pass --critical-load Threads_running=400 --max-load Threads_running=375 --chunk-size=10000 --execute --alter "ENGINE = InnoDB" > prod_dry_my_table_change_table_engine.log

Found 5 slaves:
  db_server_2
  db_server_3
  db_server_4
  db_server_5
  db_server_6
Will check slave lag on:
  db_server_2
  db_server_3
  db_server_4
  db_server_5
  db_server_6

Example output when changing table from MyISAM to InnoDB:

Operation, tries, wait:
  copy_rows, 10, 0.25
  create_triggers, 10, 1
  drop_triggers, 10, 1
  swap_tables, 10, 1
  update_foreign_keys, 10, 1
Altering `my_database`.`my_table`...
Creating new table...
Created new table my_database._my_table_new OK.
Altering new table...
Altered `my_database`.`_my_table_new` OK.
2015-12-07T17:53:43 Creating triggers...
2015-12-07T17:53:43 Created triggers OK.
2015-12-07T17:53:43 Copying approximately 188366 rows...
2015-12-07T17:54:07 Copied rows OK.
2015-12-07T17:54:07 Swapping tables...
2015-12-07T17:54:07 Swapped original and new tables OK.
2015-12-07T17:54:07 Dropping old table...
2015-12-07T17:54:08 Dropped old table `my_database`.`_my_table_old` OK.
2015-12-07T17:54:08 Dropping triggers...
2015-12-07T17:54:08 Dropped triggers OK.
Successfully altered `my_database`.`my_table`.

Why do developers sometimes forget to add a primary key to tables?

I work a lot with developers and I've discovered that their experience with data modeling or just database work in general ranges from knowing nearly nothing to being experts beyond my own experience. Some have taken courses in college, some are self trained and some have just picked things up on the job.

One of the first things I did two years ago when I started working at my company was to create a list of standards that all new database development in MySQL would adhere to. For example, all tables would use InnoDB as the default storage engine, utf8 as the default collation, no more enums allowed, all tables must have primary keys and lots of other standards.

The item about developers not putting primary keys on tables has been a mystery to me. It just seems very basic to me. We've got a legacy app that largely uses MyISAM tables and the developers that created these tables long before I ever got hired never put primary keys on some of them. There was no database code review process at that time and so they just got away with it.

I've been preaching ever since I got here that all the tables need to be converted to InnoDB and those tables need primary keys. But 24 months later, it still hasn't changed. If it ain't broke, don't fix it right?

I don't have any benchmarks but this is Percona's take scalability problems if your InnoDB tables don't have primary keys:

https://www.percona.com/blog/2013/10/18/innodb-scalability-issues-tables-without-primary-keys/
https://dzone.com/articles/innodb-scalability-issues-due

Here are some others:
http://www.psce.com/blog/2012/04/04/how-important-a-primary-key-can-be-for-mysql-performance/
http://blog.jcole.us/2013/05/02/how-does-innodb-behave-without-a-primary-key/


Finding tables is pretty easy, I copied some code from the data charmer blog to get this:

SELECT table_schema, table_name
FROM information_schema.tables
WHERE (table_catalog, table_schema, table_name) NOT IN
(SELECT table_catalog, table_schema, table_name
FROM information_schema.table_constraints
WHERE constraint_type in ('PRIMARY KEY'))
AND table_schema NOT IN ('information_schema', 'mysql');

Here I want to find all tables that do not have a PRIMARY key. Even if it has a UNIQUE key, I still want to get the table name.

One of the big use case on why the tables need to be converted is that several development managers want to try using Percona Cluster but having all these tables without Primary Keys isn't going to fly.

Happy Thanksgiving

Thankful to all those that do blog about MySQL. I don't have any mentors at work because there aren't any Senior or Principal DBAs at my company. I learn from Percona Consultants, Percona Live seminars, webinars and lots of blogs.

Big Data Conference and SQL saturday

I attended several great lectures at SQL Saturday and Big Data topics. One of the presenters said that everyone will soon be expected to be a Data Scientist to some degree. Just as typing was once a rare skill, the coming generation will be expected to program, use databases, mine data, perform statistical analytics on data and be able to present the data in meaningful ways.

Before and after upgrade to Percona 5.6

Last week we had a production server that wasn't doing so well on MySQL Oracle Community 5.5. Upgraded it to MySQL Percona 5.6 with the thread pool enabled. Here is the before and after looking at threads:

BEFORE:

AFTER:

Huge difference eh? The after graph has looked the same with no spikes in threads_running or slow queries for three days now. CPU has also lowered a lot. We did double the number of connections allowed from 1200 to 2400. The next day we never got close to that but RAM usage did increase.

The server only had 32 GB of RAM before and after the upgrade. Even though the server was doing much better with Percona Server 5.6, memory started to swap. Doubled it to 64 GB.

Here is CPU before and after:

New Musical called Kill the Query starring DBAs, NOC, Systems

Slightly bored at work, I wrote a little musical to the tune of Disney's Kill the Beast...

[DBAs:] The query will make off with your data.
[NOC:] {gasp}
[DBAs:] It'll come before the backups are done in the night.
[Developer:] No!
[DBAs:] We're not safe till its execution plan is mounted on my wall! I Say we kill the query!
[NOC:] Kill it!

[NOC I:] We're not safe until it's dead
[NOC ii:] It'll come stalking us in the slow query logs
[Manager:] Set to sacrifice our performance to its monstrous appetite
[NOC iii:] It'll wreak havoc on our servers if we let it wander free
[DBAs:] So it's time to take some action, boys
It's time to follow me

Through the red tape
Through the office floor plan
Through the open collaborators
It's a nightmare but it's one exciting ride
Say a prayer
Then we're there
At the command line or a GUI
And there's something truly terrible inside
It's a slow query
It's blocking all the updates
It locking all the tables
Massive joins
It is creating so much i/o
See it is doing full table scans
See it bring down our app
But we're not coming home
'Til it's dead
Good and dead
Kill the query!

[Developer:] No! I won't let you do this!
[DBAs:] If you're not with us, you're against us!
Bring the developer team lead!
[Team lead:] Get your hands off me!
[DBAs:] We can't have them running off to spawn more instances of the query.
[Developer:] Let us out!
[DBAs:] We'll rid the servers of this query. Who's with me?
[NOC:] I am! I am! I am! )

Turn on your device
Boot up your computer
[DBAs:] Write your documentation to the JIRA board
[NOC:] We're counting on the DBAs to lead the way
Through the red table
Through an open office floor plan
Where within a stalling database
Something's lurking that you shouldn't see ev'ry day
It's a slow query
One as slow as slug
We won't rest
'Til its's good and deceased
Sally forth
Tally ho
Grab your keyboard
Grab your mouse
Praise the monitoring system and here we type!

[DBAs:] We'll lay siege to the database and bring back the query execution plan!
[Developer:] I have to warn the other developers! This is all my fault! Oh, What are we going to do?
[Team Lead:] Now, now, we'll think of something.

[NOC:] We don't like
What we don't understand
In fact it scares us
And this monster is mysterious at least
Bring your query profilers
Bring your log files
Save your workers and their jobs
We'll save our customers from slow performance.
We'll kill the query!

[Systems:] I knew it! I knew it was foolish to get our hopes up.
[Systems:] Maybe it would have been better if we had never hired DBAs at all.
Could it be?
[Systems:] Is it them?
[Systems:] Sacre Bleu! DBAs!
[Systems:] Encroachers!
[Systems:] And they have access to the databases!
[Systems:] Warn the Managers! If it's a fight they want, we'll be
Ready for them! Who's with me?
[DBAs:] Take whatever monitoring you can find. But remember, the
Slow Query is mine!

[Systems:] Hearts ablaze
Banners high
We go marching into battle
Unafraid although the danger just increased
[NOC:] Raise the flag
Sing the song
Here we come, we're three strong
And three DBAs can't be wrong
Let's kill the Query!

[Systems:] Pardon me, Management.
[Management:] Leave me in peace.
[Systems:] But sir! The database is under attack!

[NOC:] Kill the Query!
Kill the Query

[Systems:] This isn't working!
[Systems:] Oh no, we must do something!
[Systems:] Wait, I know! )

[NOC:] Kill the Query!
Kill the Query!

[Systems] What shall we do, Management?
[Management:] It doesn't matter now. Just let the DBAs come.

[NOC:] Kill the Query!
Kill the Query!
Kill the Query!

Moving the bottleneck after adding indexes

A few weeks ago, I identified half a dozen indexes that were missing from database tables for an important application at work. There was no rush to get them added because we typically have all changes including database indexes go through dev, qa, alpha, beta and then to prod. Performance started to get so bad for this application that management approved adding the indexes directly to production.

Several of the queries I identified were were doing full table scans frequently and it was clear that these queries would benefit from the index. The application was seeing some slowness and sometimes hit max connections because these queries were not clearing quickly enough.

I got permission to have the indexes added. At night time I added the indexes, one of which I used Percona Online Schema Change.

The next day, the throughput on the server had increased so much that the application was severely taxing the resources on the database system with high CPU and 200~600 something threads running all of these well tuned queries. It was interesting to see what a huge change those indexes made but also that the bottleneck moved from slow queries to too many queries for that version of MySQL to realistically handle with the number of CPU/cores on the system.