1 of 40

Deep dive into Query Performance �

Peter Zaitsev,

Founder at Percona

10 June 2023

2 of 40

2

3 of 40

Database is a Black Box

3

4 of 40

Meaning

4

You can connect to the Database Service Point, Quickly

Run Queries you need to run

5 of 40

Queries

5

Run them without errors

1

Run them with correct results

2

Run them with required response time

3

6 of 40

Performance

Performance is about Response Time you get for your Queries

6

7 of 40

Great design is not only about Performance

Security
Availability
Costs
Maintability
Impact on other users

7

8 of 40

Response Time - Database View

“I see database responds to queries in 5ms in average”

8

9 of 40

Response Time – Business View

All Users have outstanding performance experience with all their application interactions

9

10 of 40

Downtime

Very Bad Performance is indistinguishable from downtime

10

11 of 40

Forget averages

There once lived a man who tried to cross a river, in average one meter deep

11

12 of 40

12

13 of 40

Percentile

13

99 percentile does not translate in 99% users having great performance

If every user interaction has 10 database queries

User in average has 10 interactions

Roughly 50% of session will have query with p99 response time

14 of 40

Errors

Look at Response time of Successful Queries, do not let “fast errors” to screw up your data
Measure response time of “slow errors” as it contributes to user experience

14

15 of 40

Over Time

15

LOOK AT RESPONSE TIME TRENDS OVER TIME

MINOR SLOWDOWN OFTEN HAPPENS BEFORE POOR PERFORMANCE “DOWNTIME”

PERFORMANCE CAN BE WORSE AT CERTAIN TIMES – BACKUPS, BATCH JOBS, MAINTENANCE

16 of 40

16

17 of 40

Database or Application ?

Instrument on Application side to understand what drives performance of user interactions
Instrument of Database Size to understand what causes queries to be slow and what can be done about it

17

18 of 40

Response Time – Business View

All Users have outstanding performance experience with all their application interactions

18

19 of 40

Enhancing Query Meta Data

SQL Commenter project by Google

https://per.co.na/SQLcommenter

19

20 of 40

Query Meta Data Possibilities

20

Actual User/Tenant

Application/Functionality

Version Information (A/B Testing)

Responsible Engineer/Team

21 of 40

Query

Different Queries have different performance profile
They also correspond to different “user actions”
And may have different acceptable level of Performance

21

22 of 40

Schema and Database

Different Applications/Services may be using different ones
In sharded environment can correspond to application “tenant”

22

23 of 40

Database view in Percona Monitoring and Management

23

24 of 40

Table/�Collection

Can help identify “problematic data”
Indexing changes impact queries hitting object
Maintenance often impacts specific table

24

25 of 40

Database User

25

IDENTIFY SERVICE/APPLICATION

FIND HUMAN TROUBLE MAKERS WITH INTERACTIVE ACCESS

26 of 40

26

27 of 40

Database Host

27

Sharded environments often have multiple hosts handling the same traffic

Yet Problems often can be limited to some hosts

Data/Traffic Balance, configuration, invisible differences

28 of 40

Database Instances

28

29 of 40

App Server/�Web Server/�Service Instance

29

You may expect all instances of the same type causing same even load

It may not be the case

Code versions, configuration, load balancer behavior, security incidents

30 of 40

Client Hosts

30

31 of 40

Custom Tags

31

Assign custom tags to database instances if you can

Instance Type

Database Version

Configuration

32 of 40

Query Plan

One Query Can have Multiple Different Query Plans
Sometimes it is good, in other cases it is a problem
Measure Query Performance by Query Plan
Can take action to correct query plan if this is the issue

32

33 of 40

Where Response Time Comes From ?

33

Data Crunching/CPU

Waits on CPU Availability

Disk IO

Row Locks

Contention

Network

34 of 40

Other Things to Consider

34

35 of 40

“Bad Queries” vs Victims

Query might be slow because it is heavy on its own
Or it might be victim of other queries or their volume

35

36 of 40

Do not forget currently running queries

Response time is measured when query completes
You can write queries which “never” complete
Consider killing runaway queries and whitelisting queries which need to run long

36

37 of 40

Do not Ignore “Invisible”

Database Background Activities
Maintenance Operations
Cloud Noise

37

38 of 40

Avoid Biased Sampling

38

“Let’s Look only on slow queries”

Focus on Outliers

Likely to ignore queries causing most load, typical impact

39 of 40

Good Luck

Get your query performance under control
Do not over-do scaling by Credit Card

39

40 of 40

Thank you, Let’s Connect!

https://www.linkedin.com/in/peterzaitsev/

https://twitter.com/PeterZaitsev

http://www.peterzaitsev.com