Tag Archives: 5.6

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).







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.