360 likes | 373 Views
Top N Query: How to use STOPKEY. Presented by Dalibor Mar č eta. Overview. Producer – Consumer Common misconceptions Top N query Pagination Iteration Index range scan Multiple tables Explain plan Quick check. Producer - Consumer. Amazon has service-oriented architecture
E N D
Top N Query: How to use STOPKEY Presented by Dalibor Marčeta Amazon Confidential
Overview Producer – Consumer Common misconceptions Top N query Pagination Iteration Index range scan Multiple tables Explain plan Quick check Amazon Confidential
Producer - Consumer • Amazon has service-oriented architecture • Services communicate with each other by passing data from one service to another • Identifying components • The Producer generates data and stores it into the queue for consumption by the consumer • The Consumer consumes data from the queue • The Queue is some common table used as a communication queue between the producer and consumer loop Amazon Confidential
Components Amazon Confidential
Most likely implementation Amazon Confidential
Target implementations Amazon Confidential
Scaling producers Amazon Confidential
Queue table Amazon Confidential
Scaling consumers Doesn’t scale: • Optimistic locking • Pessimistic locking, wait • Pessimistic locking, no wait Scales: • Iteration • Iteration skip locked Amazon Confidential
Optimistic Amazon Confidential
Pessimistic Amazon Confidential
Pessimistic nowait Amazon Confidential
Select for update nowait Amazon Confidential
Select for update skip locked Amazon Confidential
Top N query Teams frequently have a need to display top N or last N rows from a table or group of joined tables with some criteria applied. Engineers commonly make the following mistake when designing queries and indexes to solve the top N query problem. They only create an index matching the lead predicate. Amazon Confidential
Common misconceptions • Order of the columns in index is not important • When building an index we choose columns with good selectivity used as a query predicate. • Select * from table where rownum < N order by creationDate desc - will decrease response time. • Service has been tested and it works until customer with many records logs in. Amazon Confidential
How to identify problem • Latency • High user CPU • High I/O If Top N is not implemented or if it is not implemented properly query can cause problems for application and for database. It usually can be recognized by the following manifestations: Amazon Confidential
Implementation • Incorrect implementation • Correct inefficient • Correct efficient • Pagination • Iteration Amazon Confidential
Incorrect implementation Amazon Confidential
Correct inefficient Amazon Confidential
Correct efficient Amazon Confidential
Pagination incorrect Amazon Confidential
Pagination correct Amazon Confidential
Iteration I Amazon Confidential
Iteration II Amazon Confidential
Range scan I before Amazon Confidential
Range scan I after Amazon Confidential
Range scan II Amazon Confidential
Range scan II Amazon Confidential
Multiple tables Amazon Confidential
Multiple tables Amazon Confidential
Bad Execution Plan Amazon Confidential
Good Execution Plan Amazon Confidential
Rule Amazon Confidential
Quick check Amazon Confidential
Questions? Amazon Confidential