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.
|1||Identify poorly performing queries and all application queries|
Assess the query performance
|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|
|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.