Category Archives: Tech

Prepared Statements and pt-query-digest

IMG_6543I know I keep writing about pt-query-digest. It’s one of the best tools for MySQL. It’s complexity is a blessing and a curse and while the documentation is good, it’s always too general. I write these posts so I can find out what I did so I can share with others.

First off, prepared statements – WHO USES THESE? if you read the blog posts like this one:
4 Things to Know About MySQL Prepared Statements

or this one
Re-factoring some internals of prepared statements in 5.7

You would wonder why anyone would use prepared statements pre 5.7 (some of us wonder why we can’t just use 5.7 now? When will Oracle release this beauty to GA???!! )  But as a DB you have to show things like why and without performance_schema’s monitoring of memory leaks (comes in 5.7) it makes the weirdness that is prepared statements pre 5.7 scary. But lets just look at the performance of them. so off to pt-query-digest.

My slow log was not verbose and the long_query_time was set to 10, which means anything in my slow log should be looked at. This was on a staging server where P_S (performance_schema is fully turned on, something I will not do until maybe 5.7 if it is truly the lean machine as blog posts tout). I forgot to turn long_query_time to 0 until after it had been running all morning.

I did the usual

pt-query-digest –report-format=all ./slow.log > slow-log-report.txt

NOTHING related to the prepared statements showed up in the report except under this:

    # MISC 0xMISC              104.5362  2.4% 29940  0.0035   0.0 <435 ITEMS>

Adding –limit 100% added in ALL the queries on ALL the databases, not what I wanted. My prepared statements are in one schema by one user. I choose to filter based on user. I’m pretty sure I could have filtered on schema (or is it db?) but user seemed the easiest and the docs had an example.

pt-query-digest –limit 100%  –filter ‘($event->{user} || “”) =~ m/myuser/’ >qt-newdb.txt

Percona Toolkit 2.2 


dstat – benchmark stats in one place

IMG_9511I’ve been using dstat for awhile now and I figured I should blog about it. I’m always surprised when I meet techies that benchmark but don’t know about dstat? How can that be?? It’s like iostat, vmstat and a bunch of other tools we all use in one. ok I hear ya, when you have a “swiss army knife” of tools using them can be a challenge so I thought I’d blog about the ways I’m using it.

First you may want to look at man page sorts of things:

Because I’m using dstat for benchmarking, that means that biz folks are gonna look at the results and they all want graphs. The cool thing about dstat is that you can output it into a csv file. Graphite, a commonly used monitoring tool uses csv files. But I don’t have access to a Graphite server so I’ll make my graphs with another tool. Here’s what my dstat wrapper looks like (I also have hdparm )


NOW=`date +”%Y%m%d%H%M”`
#DSTAT=”dstat -t –top-io –top-bio -cl”
DSTAT=”dstat -t –top-io –top-bio -cl –disk-util”

## check if stats dir exists
if [ ! -d ${STATS_DIR} ]; then
#echo “no ${STATS_DIR} ”
mkdir -p ${STATS_DIR}

for i in `seq 1 ${MAX}` ; do
(echo `date` >> ${HDPARM_LOG} )
(/sbin/hdparm -Tt /dev/xvda >> ${HDPARM_LOG})
sleep $SLEEP

exit 0


Percona Live 2014 part 2


From Somarts Work MORE! #6 © erin o’neill 2014

Percona Live 2014 part 2.

I did a LOT of networking and missed some talks. But the highlights were: Ryan Lowe on R, Jeremy Cole on his a Journey to the core II (smartly I went to Peter Zaitsev’s Innodb architecture and performance optimization Tutorial), Benchmarking Databases for Scale (Percona), Performance Monitoring at Scale with Yoshinori, RDS with Laine Campbell of Blackbird, the Lightening talks were fun and my favorites were “What not to say to a DBA” by Gillian Gunson,  “Relay Log – the Unsung Hero” by Vishnu Rao (flipkart), Shlomi Noach’s Lightening talk (damn he is one funny dude!).

There were so many talks to go to and too much work. I missed ones I wanted to go to. I love this conference. Can’t wait for next year.


(ps no Booth Babes this year too!)


Percona Live 2014 part 1

IggyPopIMG_1170This year the Percona Live conference started with a free Open Source Appreciation Day. There were two tracks and I went to the OpenStack track where JAY PIPES was speaking!!! OMFG! He is a great speaker and pretty much disappeared into drizzle land to be spit out into OpenStack land. He is still a great speaker. Yea Jay Pipes!  My take away was the Trove is OpenStacks answer to RDS (Jay did not speak about Trove per se, his talk was titled: Tales from the Field: Backend Data Storage in OpenStack Clouds ).

The conference always starts off with tutorials and this year I had a pass for that. I went to two tutorials. The first was the MySQL Break/Fix Lab with mostly René Cannaò (Blackbird, (formerly PalominoDB)), then Gillian Gunson (Blackbird (formerly PalominoDB)) and Alkin Tezuysal (Blackbird, (formerly PalominoDB)) with the shortest time. I wish this had been intermediate rather than beginning. But I wanted to support them. I had great fun tweeting about this session. And it was a great beginner session. I wish I’d gone to it in my first year of being a MySQL DBA.

Then I went to InnoDB Architecture and Performance Optimization with Peter Zaitsev (Percona).  This was an advance talk on the internals which is not something I know a lot about. But I learned at the Oracle MySQL Connect that some of it does sink in and help with things like tuning and indexes.  I was also taking it to prep for Jeremy Cole’s session later in the week. Turns out Jeremy was there to prep for his talk too! AND near the end they had a lively discussion where Jeremy politely told Peter he was wrong about this one thing & Peter paid attention! It was very cool to see two very smart people have a discussion rather than some ego fueled bullshit session. We all benefited from it greatly.

That was the end of day 2 and I was already pretty tired. How can that be? I had 3 more days of this.

When things fail – Server Restores and databases


I like to test backups with a restore. Database restores work pretty well most of the time, as long as the backup file is not corrupt, binary logs can be found, etc. There is a plethora of blog posts on this and software to make it easy. I have some control over that. However, I do not always have control over the server backups.

Server backups are SUPER easy in AWS. I love them. Snapshots may cause problems taking them but the restore – piece of cake. Now I’m in a Rackspace Cloud (RAX) and I’m not sure how this backing up is working? So I push and push to do a restore. Of course I first upgrade the database so we can go back to the old version. I stop mysql and tell the SysAdmin to start the restore.

First off, the restore on this small server took OVER THREE HOURS???!!! WTF? really? And I used to complain about the 15 min AWS would take…  scheesh.

Everything seems to be working but it’s not – only I don’t know this. A week later I go to do the update to the Master/Slave pair (the slave is the one that I tested the upgrade and them RAX restore on). The Master server is going just as planned, everything is so easy. But the slave is throwing errors right and left. Databases can’t be dropped, then after they are created data can’t be loaded into the mysql tables. Really, it’s a mess.

I panic on the drop database mytest; when it fails and leaves behind a couple of ibd files. I know those are special and I’m leery of just running a rm -r mytest. I try a bunch of things but in the end I run the rm -r mytest, then go into mysql and create database mytest; drop database mytest; I’m good to go.

The mysql database was a bigger problem. It was such a mess that I just removed everything. I mean EVERYTHING and then ran: mysql_install_db

After that I started it up with the cd /usr ; /usr/bin/mysqld_safe &

Then I needed the mysql user data. Not to worry, I already had that but the number of columns did not match as they were 5.5 and this server had 5.6 installed. Again no big thing I just went to a server that was still 5.5 and ran this:
mysql -BNr mysql -e “desc user” | cut -f1 > user-cols

Then I added “,” at the end of each line and wrapped it in “( )” and added that into the sql file. Still didn’t work so I added INSERT IGNORE. ran that against the DB:  mysql < mysql-user.sql

I was left with a couple of root users without passwords so I cleaned that up. Then I was good to go. Ran the schema import mysql < mysql-schema.sql and that ran fine. yea.

FINALLY I could run the mysql < mysql-data.sql

That one takes awhile. The final steps will be reset master; show master status; and then grabbing what I need to start the slave.

I did panic for a few seconds but all the problems reminded me to breath deeply, carry a big stick and beat it all into submission. Happily this is the dev servers and not prod as the time to do this took a lot longer than it should have.

Looking back at things I should have run a DB restore of the same day as the server restore and then rebuilt the slave data.

MySQL Percona 5.5 upgrade to 5.6

IMG_7048photo by © erin o’neill 2014 Las Vegas Int’l Airport

I like to prepare for the worst, especially with Server upgrades. I read through the gotchas, about server settings default changes, passwords being a problem and maybe having a slave 5.6 and a master 5.5. But Percona made the upgrade sound super easy. Too easy.

This was a simple setup used for stats. It wasn’t even that large of a dataset. I already wasn’t using old passwords (hey the install was a new 5.5 and I didn’t allow old passwords there). I’d tuned the server a bit, specifically using innodb_file_per_table, but I’ve been using that since I could.

I backed up the my.cnf file, the schema, the mysql data, the data of my tables. I was good to go. I also dropped my databases for good measure. AND I put the show slave status into a file for later.

Since I was already using percona 5.5 I didn’t need the repo. This was on Ubuntu, not my fav OS. In fact I find Ubuntu a pain in the rear and prefer CentOS. But this was not my choice. I just ran the following:

apt-get -y install percona-server-server-5.6 percona-server-client-5.6
apt-get -y install xtrabackup
apt-get install -y percona-toolkit

Turned out that while xtrabackup was up to date, percona-toolkit was not. Then I started mysql. the apt-get turns out to have run the mysql_upgrade so that wasn’t even needed! I checked my mysql error log and there were no serious errors.

Importing the schema was where my big worry lay because this is a data warehouse which means they use evil stored procedures and views. I get it that data warehouse setups really need stored procedures but views?? I’ve already ranted enough about that. The import went fine. That kind of worried me as I thought stored procedures and views might not upgrade well as they usually don’t. And they’d already given me a major headache in the upgrade from 5.1 to 5.5.

Then I imported the data. Again no errors. Hey I thought this upgrade was supposed to be slightly gnarly???!!

Then I ran the CHANGE MASTER which my dump got wrong. No worries I ran a show slave status to a file after stopping the slave so I had the info. I put the proper file and file position in my CHANGE MASTER command. Then it scolded me for using user names and passwords. WTF??!! Oh, I’m supposed to use an auth plugin. I ran start slave as normal and the show slave status showed that the two replication threads were running and included a WHOLE lot more info. Good thing I’m no longer using perl to grab that info as I would have had to modify my scripts! Not a fun exercise at all.

I’m testing the percona mysql 5.5 master to percona mysql 5.6 slave. Next week I’ll upgrade the master. That’ll make the devs happy as they’ll have more diagnostics for their stored procedures. I hope it’s also faster and less CPU intensive. But it is a data warehouse and one can not escape full table scans….

Oh, and MySQL is gonna scold me every time I run a CHANGE MASTER as I don’t have an auth plugin. I’ll continue to ignore it, but it’s nice to know I can add auth.

Strange DB requests

Eclipse 5/21/12

Eclipse 5/21/12

I’m on the market so recruiters call me. I get the usual: “what, you can’t commute?”, “Really it’s not that far from San Francisco!”, “Why don’t you want to relocate to LA?”.

I’m open to small gigs as they can be really fun and you’re not on-call. I was recently asked if I could take SQL Server data and import it into MySQL.

Well, yea. But I wanted to know why. Was this a good idea? What was the reason? I can set up a server and tune it for MySQL. I can recommend hardware. But first I needed some questions answered. Why is the first and foremost question.

A quick aside. Anyone who knows me, knows I’m not a big fan of windows. I’m just not. Windows is always crashing on me and I find the UI cumbersome. I love a command line. So, you’d think I’d be salivating at the chance to move from SQL Server to MySQL.

The answer to my why,  was to save money. Now, it may seem like you can save money by not paying those pesky license fees. Really it seems like a no brainer doesn’t it? But SQL Server is   different from MySQL and solves different problems. MySQL needs someone, anyone that understands it. Many startups have DevOps/System Admins who can handle a fair amount of MySQL needs. They can run backups, make sure replication is running, bring up a crashed server. So technically you can get by without a MySQL DBA. Ok those are really good System Admins who understand MySQL  and they seem to be rather scarce these days.

So I asked about performance and scalability. No need for scalability, only about a dozen or less people use it. Well, that seemed odd to me. MySQL is a great database for the web. It requires a lot from the apps that talk to it. The app is a MS app that does a lot of number crunching in SQL Server and then spits it out to excel. This is an excellent use of SQL Server! Many people need SQL Server just for this.

I had to break the news to them that MySQL would not save them money and probably wouldn’t do nearly as good of a job as SQL Server was doing for them. They would not be happy.

Damn! That advice just cost me some money….