jump to navigation

MySQL Series: Performance Engineering – The Goal March 24, 2015

Posted by msrviking in MySQL, Performance.
Tags: , , , ,
add a comment

This post has that one line of description as the goal for performance engineering assignment I had to work on, and about the plan on how I went about. The statement says “Ensure we have acceptable performance in the DB for 5000 concurrent users.” I knew what was waiting from me with this one line statement.

And with no delay, I had put up the plan that would touch the performance soft spots of the MySQL system. I wasn’t sure how much performance improvement will be achieved to scale the system after touching these areas.

Here is what one would want to address, and I believe this will help as reference always.

The table consists of Areas to be looked into, Dependency as in inputs from the team or system for me to assess and the priority of each Area.

# Areas Dependencies Priority
1 Identify poorly performing queries and all application queries

Assess the query performance

Query list High
Plan Queries parameters High
Concurrency Access to the latest production DB in Dev environment High

Assess the table performance

Information on business (application) purpose of the query – application perspective
Index Inputs from existing query benchmarks – production or performance testing environment High
Partition Medium
Structure Medium
2 Identify configuration parameters
2.1 Assess the impact of existing configuration parameters Configuration parameters in production Medium
2.2 Recommend new configuration parameters Architecture setup of MySQL instances in production
3 DB scaling (Up & Out)
3.1 Assess the current performance of Hardware and MySQL Current hardware setup of MySQL instances Medium
3.2 Propose additional hardware based on findings Architecture setup of MySQL instances in production Medium
3.3 Propose change in MySQL architecture Medium


I had to follow this plan all throughout the assignment for project management reporting, tracking the progress, and at the end quantifying the % of performance improvement in each area.

Not to re-emphasize the intent of having this plan is to work bottom-up. The work had to start from query, index, tables, database, instance, and hardware which is the standard for any performance optimization. The following posts would be on most of the above line items.

Happy reading.