Most 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).
- https://github.com/MarkLeith/mysql-sys/blob/master/views/p_s/io_by_thread_by_latency.sql
- https://fosdem.org/2015/schedule/event/pfs_troubleshooting/attachments/slides/605/export/events/attachments/pfs_troubleshooting/slides/605/P_S.pdf
- http://avdeo.com/2015/01/18/mysql-performance-schema/
- http://www.pythian.com/blog/mysql-query-profiling-with-performance-schema/?utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+pythian%2FzvUj+%28Pythian+-+Data+Experts+Blog%29
- https://www.percona.com/blog/2015/04/16/profiling-mysql-queries-from-performance-schema/
- https://www.percona.com/blog/2014/02/11/performance_schema-vs-slow-query-log/
- http://githubengineering.com/using-mysql-performance-schema-for-workload-analysis/
- http://www.slideshare.net/spil-engineering/mysql-performance-monitoring-using-statsd-and-graphite-pluk2013
- https://vividcortex.com/blog/2014/11/03/mysql-query-performance-statistics-in-the-performance-schema/
- http://www.markleith.co.uk/2012/07/24/a-mysql-replication-load-average-with-performance-schema/
- http://www.tusacentral.net/joomla/index.php/mysql-blogs/172-performance-schema-how-to-part1.html
- http://www.databasejournal.com/features/mysql/an-overview-of-the-mysql-performance-schema.html
- http://mysqlserverteam.com/category/mysql/performance-schema/
- http://mysqlintheenterprise.com/2013/03/21/a-visual-guide-to-the-mysql-performance-schema/
- http://www.psce.com/blog/2015/01/22/tracking-mysql-query-history-in-long-running-transactions/
- http://www.slideshare.net/oysteing/how-to-analyze-and-tune-sql-queries-for-better-performance-percona15