Category Archives: Performance_Schema

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