Category Archives: MySQL_TOOLS

Performance Schema

IMG_6610Most DBAs wanted something like performance schema  back in 4.0. Percona started to add statistics to information_schema by 5.#s.  I never turned them on. I’m not sure if it was due to being too busy, not knowing what the performance hit would be or just not knowing about them. I admit it. I didn’t turn them on. But when I went to Mark Leith’s talks at the Percona Conference in Santa Clara I wanted to use those new statistics. But clearly I was going to have to upgrade to mysql 5.6 to get the full monty.

First thing I did when I upgraded to 5.6 (percona server) was to turn on performance_schema (P_S) full tilt, even though I’d been warned there would be a performance hit. But hey, I was turning it full on, on these big beasts. I hadn’t even dreamed of servers this big but here I was. I thought the application would only use about 40% so what was 5% more? WRONG! First of all, having never used servers of this size I didn’t know I needed to tune them and neither did my SysAdmin. They were NEVER tuned. After they went into production I had to turn off performance_schema from collecting anything because I needed that 10%+. I also had to do some other crazy stuff I do NOT recommend as it isn’t replication safe. No one seemed to care but me.

However, I did turn P_S full on on the testing server. I wanted to see if it would help me slay some easy low hanging fruit sort of problems. The first thing I found was that to really use it in a meaningful way I needed to install views and triggers that make up the sys schema. I hate views and triggers as they make upgrading more challenging and they can slow down the server. I wondered if this was where the majority of the performance hit came from ? HA! That was an additional hit to the 10% I was experiencing. I only installed the sys package by Mark Leith when I wanted to use it. They’re very helpful if you’re following one of Mark’s slideshows or blog posts about sys schema. They’re also fairly easy to install and uninstall.

I ran some queries that I got from those blog posts but interestingly nothing was really new. I’d already looked at the DDL of the largest tables, I’d seen the queries and I knew what was wrong. And like my experience told me, fixing the DDL and queries would boost the performance. I put P_S away while I was busting my butt on other problems.  But I longed for something like innotop so I could just glance at it throughout the day. Ah ha! I was not the only one. ps_top to the rescue.

I stumbled on a blog post by someone else who needed/wanted a ps_top program. I’m self taught so I learned UNIX by man pages (back when they seemed better written). I’ve compiled some gnarly programs as we didn’t have things like apt-get or yum. So what if I never compiled a GO app before? I can read documentation. But as an early adopter it means the documentation isn’t written for people like me (those that have never experienced GO before). Luckily I worked with the creator who worked on the documentation and I got ps-top to compile and run. Suddenly P_S was coming in handy with each new release. I could see the IO spike suddenly on a new table. Or the IO wait time. It was really handy for a new app that was being created.

I can’t wait to use mysql 5.7 and see how P_S has changed (rumor has it there is less of a performance hit and even MORE status). But in the meantime here are some cool URLs I’ve collected and want to be able to go back to (in no particular order).







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 

Keep This Place CLEAN!

Keep This Place CLEAN!

query-digest tool

First off DISCLAIMER! Always test this to be sure your servers can handle these tools. I try not to ever run percona toolkit tools on the production database server. There are ways to get around this with a little thought. Also, remember that passwords run on the command line will show up in a ps command.

I’ve always used maatkit’s query-digest tool but I think that percona toolkit has made it better. It’s one of my fav mysql tools. I think the documentation is lacking for all the really great things you can do with this tool but it’s ability to warm up a cache on passive master or new slave is priceless.

Generally startups do not hire a DBA in the beginning. Let’s face it, it’s usually one or two engineers with hopefully a marketing or PM in the beginning. First hires tend to be on the business end and more engineers. Everyone thinks that DBAs aren’t really necessary in the beginning. They’ll finally hire a backend engineer to fix some of the queries before they get a DBA. Then the database begins to bottleneck as the site gets more popular. That’s usually when the DBA comes in, along with the architect. Fair enough.

So while the backend re-architecture begins the shoring up of the current system has to happen. This usually involves more servers. Perhaps in the beginning there was a master, a backup slave and a couple of read slaves in the system. The CEO is trying to keep the budget down but is freaking out about the server cost because database servers are more expensive than WebServers/AppServers. But the system needs help and to keep it up more servers are thrown at the problem. This is where query-digest really shines.

First I always add a passive master (which is nothing more than a slave that no one reads from). It’s insurance – just in case there’s a hardware failure a fully caught up slave is ready to take the fallen master’s place. Or is it?

Generally the first thing I do is upgrade the version of MySQL so that the app can get  prepared for the new architecture. I’ve never had this involve one upgrade. But it’s good practice. You want to be sure that the app can handle the newer versions which are faster and have fewer bugs so minor upgrades are started. This means I need 2 servers (ok I can get by with one extra but I usually need an extra read slave by now). The first thing I do is a mysqldump and import into a new slave with a newer version of MySQL. You really don’t have to do this with some upgrades unless you’re also cleaning up charsets. And when you’re not working with a MySQL specialist you almost ALWAYS have to clean up charsets. That means – mysqldump–>import.sql. But does your new slave have all the data that the master has?

That means you need to run pt-table-checksum first, which means you need to run this at a slow time on your master. Once you are sure that your new slave has the same data as your master you are ready to use this new slave, or are you?

Of course you’re not! That’s when I run pt-query-digest. I choose a slave that I’m gonna grab the read queries from and on the NEW SLAVE (this is important!) I run this command:

/usr/local/bin/pt-query-digest –noreport –processlist h=my_running_slave -u myuser -psecret –filter ‘$event->{fingerprint} =~ m/^select/’ –print | mysql -f >/dev/null &

Well, actually I script it and have a variable that runs the query digest for a certain number of minutes or hours & then kills it. I usually have a ~/.my.cnf for this user so I don’t have to have the user/password on the command line. This is just for documentation. I highly suggest you take security precautions.

After I run this cache warmup for a couple of hours (I’m usually sleeping to be honest), I make sure that replication is alive and well and no writes got into this slave. I see how long a typical query takes on this server.  I check one of the gnarly queries to see if it too is speedy. If all is well I put the slave into the read pool and watch it like a hawk.

Once all the slaves are upgraded, including the passive master, it’s time to make the passive master the active master. Just do the same as you would to a slave, but this time you have to grab the queries from the master. That makes it a bit stickier as you need to watch that you’re not overloading your master. I tend to do it in spurts, 2-5 min every 15 min or so. I have to be awake for this to keep an eye on it, after the re-architecture I won’t have to (well I just set up a nagios alert that will wake me up if things go wrong and sleep with my laptop). Viola! I will do this a number of times as re-architecture takes time.

The other cool thing to do with query digest is to use it with tcpdump. I found that the docs for tcpdump were not all that helpful either. I love tcpdump for those servers that are so overloaded I can’t log slow queries. Usually I create a dump file that I’ll offload to another server to look at. Here’s how I get my dump file

/usr/sbin/tcpdump -i eth0 port 3306 -s 65535 -xnq -tttt > tcpdump.out
/usr/sbin/tcpdump -i eth0 port 3306 -s 0 -xnq -tttt > tcpdump.out

You hit <ctrl>-c to kill this and it writes to the tcpdump.out file. I’ve used both of the above and I’m not sure if I got more of the slow queries into the tcpdump outfile? tcpdump will truncate super long queries but it does give you a feeling for the query. I usually talk to an engineer who can give me the full query so I can see if there’s any sort of ORDER BY or GROUP BY.

First Post!

old chair in kitchen

I’m finally getting around to blogging in the techsphere. It’s about time I know but that’s cause my job usually keeps me too busy. Then there’s the side volunteer gig as a co-host of (sf MySQL meetup!). That takes a chunk of time (lately more than I’d like, but that’s another post/rant).

I keep up on my blogs via google reader. Generally MySQL planet keeps me up to date. Lately there’s been a lot of talk about what happens when the master database fails (whether it’s really failing or scripts think it’s failing).  The biggest surprise is that maintenance is causing problems when promoting the passive master (really just a slave no apps read from) due to a cold InnoDB cache. I mean, REALLY FOLKS!! There’s a tool for that!

I think Percona needs to blog about how to use their tools more. At a gig where I was working with this cracker-jack SysAdmin who was always on my case  to push the envelope, I told him about a script that used  maatkit’s mk-query-digest for a bit now & it works well. (Ryan Lowe gave me some hints at the talk he gave at & I slightly modified it. ) In the past this is how I warmed up the passive master. We didn’t even have a passive master at the time & I was making a case for it. So he gave me one. I piped all my reads over to the passive master.  I also modified all writes to be reads and piped those over. We did a table checksum. All was good with the data. Then we failed over to the passive master.

Whoa! no dip in response time. Granted we piped over this traffic for 4 hours (or was it 6?). But it worked. I wondered why we couldn’t just keep that script running all the time. We hadn’t removed the analytics stuff yet so there was too much traffic but we crontab’d it to happen a couple of times in the day & more so during slow hours. If I know I’m doing maintenance to a Master, I always, always warm up the cache. And yes, there’s a tool for that! (thank you maatkit authors & maintainers & thank you Percona!)

The doc for pt-query-digest is here: In the near future I’ll add parts of the script!

Ps. I’m currently looking for a MySQL DBA position in the CITY of San Francisco. I’m a startup freak because I like to do a bit more than just admin the mysql servers. I’m interested in admin’ing Redis, MongoDB (other NoSQL technology) and scalability of data. I love learning new technology that works so well with MySQL!