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!