DZone
Thanks for visiting DZone today,
Edit Profile
  • Manage Email Subscriptions
  • How to Post to DZone
  • Article Submission Guidelines
Sign Out View Profile
  • Post an Article
  • Manage My Drafts
Over 2 million developers have joined DZone.
Log In / Join
Refcards Trend Reports
Events Video Library
Refcards
Trend Reports

Events

View Events Video Library

Zones

Culture and Methodologies Agile Career Development Methodologies Team Management
Data Engineering AI/ML Big Data Data Databases IoT
Software Design and Architecture Cloud Architecture Containers Integration Microservices Performance Security
Coding Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks
Culture and Methodologies
Agile Career Development Methodologies Team Management
Data Engineering
AI/ML Big Data Data Databases IoT
Software Design and Architecture
Cloud Architecture Containers Integration Microservices Performance Security
Coding
Frameworks Java JavaScript Languages Tools
Testing, Deployment, and Maintenance
Deployment DevOps and CI/CD Maintenance Monitoring and Observability Testing, Tools, and Frameworks

How does AI transform chaos engineering from an experiment into a critical capability? Learn how to effectively operationalize the chaos.

Data quality isn't just a technical issue: It impacts an organization's compliance, operational efficiency, and customer satisfaction.

Are you a front-end or full-stack developer frustrated by front-end distractions? Learn to move forward with tooling and clear boundaries.

Developer Experience: Demand to support engineering teams has risen, and there is a shift from traditional DevOps to workflow improvements.

Related

  • Smarter IoT Systems With Edge Computing and AI
  • Master SQL Performance Optimization: Step-by-Step Techniques With Case Studies
  • MySQL Formatter: How to Make Beautiful Code and Why You Need It
  • Useful System Table Queries in Relational Databases

Trending

  • Software Specs 2.0: An Elaborate Example
  • Serverless IAM: Implementing IAM in Serverless Architectures with Lessons from the Security Trenches
  • The Rise of Self‐Service Platforms: How Cloud Development Environments Are Reshaping Dev Culture
  • How to Install and Set Up Jenkins With Docker Compose
  1. DZone
  2. Software Design and Architecture
  3. Cloud Architecture
  4. Computing 95 Percentile In MySQL

Computing 95 Percentile In MySQL

By 
Peter Zaitsev user avatar
Peter Zaitsev
·
Dec. 03, 08 · News
Likes (0)
Comment
Save
Tweet
Share
15.2K Views

Join the DZone community and get the full member experience.

Join For Free

When doing performance analyzes you often would want to see 95 percentile, 99 percentile and similar values. The "average" is the evil of performance optimization and often as helpful as "average patient temperature in the hospital".

Lets set you have 10000 page views or queries and have average response time of 1 second. What does it mean ? Really nothing - may be one page view was 10000 seconds and the rest was in low milliseconds or may be you had every single page view taking 1 second, which are completely different.

You also do not really care about average performance - the goal of good user experience is majority of users to have good experience and average is not a good fit here. Defining your response time goal in 95 or 99 percentile is much better. Say you say 99 percentile response time should be one second, this means only 1 percent of queries/page views are allowed to take more than that. For larger systems defining (increasing) response times for 99.9 or even 99.99 percentile numbers often make sense.

It also often makes sense to define response time goals separately for different transactions - the AJAX widget response time requirements may be very different from the slow search page.

So you have defined your response time in terms of 95/99 percentile and get your logs in the table, so how to get the data if MySQL only provides you the avg:

mysql> SELECT count(*),avg(wtime) FROM performance_log_081128 WHERE page_type='search';
+----------+-----------------+
| count(*) | avg(wtime)
+----------+-----------------+
| 106859 | 1.4469140766532
+----------+-----------------+
1 row IN SET (2.08 sec)

The average response time here is for example; the real data what we need is number of rows which matches for given query type.

Dividing the count by 100 we get our 1% of values and dividing by 20 5% of values, now we can get the response time we concerned about simply by running following order-by queries:

mysql> SELECT wtime FROM performance_log_081128 WHERE page_type='search' ORDER BY wtime DESC LIMIT 1068,1;
+---------+
| wtime
+---------+
| 10.1007
+---------+
1 row IN SET (2.06 sec)
mysql> SELECT wtime FROM performance_log_081128 WHERE page_type='search' ORDER BY wtime DESC LIMIT 5342,1;
+---------+
| wtime
+---------+
| 5.09297
+---------+
1 row IN SET (2.06 sec)

So for this system the 95 percentile is just over 5 sec (some 3 times more than the average) and 99% percentile is just a bit over 10 seconds (6 times more than average). The both numbers are horrible and system surely needs to be fixed.

These numbers are to illustrate - the percentile numbers can be quite different from average numbers (it is not rare to see 99 percentile to be order of magnitude different from the average) and this is what you really need to focus on.

Looking at the numbers from the business standpoint try to understand what these really are. In some cases I see rather bad percentile on the backend which are not really the problem for the business because there is a cache up front anyway. If 99% of requests are coming from the cache and you observe certain 99 percentile response time on the backend it is often 99.99 percentile response time which is a lot different - you often can afford 1/10000 requests to stall for few seconds, because things outside of your control (like packet loss at client side) would be responsible for larger amount of delays.

Be careful though - the "random" delays, for example if system was busy and delayed servicing request is one thing, "systematic" delays, when response time is always bad in given conditions can be much worse problems. You do not want your best client to suffer for example, even if he is the only one.

Percentile MySQL Computing

Published at DZone with permission of Peter Zaitsev, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

Related

  • Smarter IoT Systems With Edge Computing and AI
  • Master SQL Performance Optimization: Step-by-Step Techniques With Case Studies
  • MySQL Formatter: How to Make Beautiful Code and Why You Need It
  • Useful System Table Queries in Relational Databases

Partner Resources

×

Comments

The likes didn't load as expected. Please refresh the page and try again.

ABOUT US

  • About DZone
  • Support and feedback
  • Community research
  • Sitemap

ADVERTISE

  • Advertise with DZone

CONTRIBUTE ON DZONE

  • Article Submission Guidelines
  • Become a Contributor
  • Core Program
  • Visit the Writers' Zone

LEGAL

  • Terms of Service
  • Privacy Policy

CONTACT US

  • 3343 Perimeter Hill Drive
  • Suite 100
  • Nashville, TN 37211
  • [email protected]

Let's be friends: