CODEWITHSUNDEEP
postgresqlamazon-rds
Damian Gądziak
Damian Gądziak

Reputation: 925

Simple queries, locks after migration

After database migration from self-hosted PostgreSQL instance to Amazon RDS we are encountering some weird issues with queries to 12M rows table. Queries which were working before, now are locking with wait_event_type=LWLockTranche and wait_event=buffer_io (even the simplest queries without any JOINs). All INDEXes and Execution Plans seems to be fine. Explain Analyze didn't show anything weird except the query execution time.

Example query:

explain (verbose, buffers, analyze) SELECT * FROM "products_product" WHERE ("products_product"."category_id" = 43);

A result from self-hosted PostgreSQL:

Index Scan using products_product_b583a629 on public.products_product  (cost=0.43..5256.40 rows=5667 width=1758) (actual time=24.372..298.822 rows=29342 loops=1)
Output: id, title, description, image_path, image_source_url, website_source, date_created, date_updated, afi_url, afi_price_currency, afi_recognize_id, afi_price, afi_old_price_currency,  meta_link, afi_old_price, meta_published, meta_admin_note, afi_id, brand_id, category_id, retailer_id, afi_promotion, afi_stock, search_vector, original_category_id, search_vector_pl, title
_pl, description_pl, owner_id
Index Cond: (products_product.category_id = 43)
Buffers: shared hit=71 read=22261 
I/O Timings: read=233.266
Planning time: 0.271 ms
Execution time: 310.205 ms

and result from Amazon RDS for the same query:

Index Scan using products_product_b583a629 on public.products_product  (cost=0.43..27905.30 rows=30563 width=1753) (actual time=26.084..179652.029 rows=29342 loops=1)
Output: id, title, description, image_path, image_source_url, website_source, date_created, date_updated, afi_url, afi_price_currency, afi_recognize_id, afi_price, afi_old_price_currency, meta_link, afi_old_price, meta_published, meta_admin_note, afi_id, brand_id, category_id, retailer_id, afi_promotion, afi_stock, search_vector, original_category_id, search_vector_pl, title_pl, description_pl, owner_id    
Index Cond: (products_product.category_id = 43)
Buffers: shared hit=2532 read=19856
Planning time: 0.093 ms
Execution time: 179665.121 ms

RDS: CPU usage stable at 20-30% level, DB Connections 2-40, Free memory 50% (3GB), Write IOPS 1-10, Read IOPS 650-750, Free storage 100GB.

What can cause this difference? What can we check more?

Upvotes: 3

Views: 4087

Answers (1)

M.W.
M.W.

Reputation: 26

Reference this, https://docs.aws.amazon.com/AWSEC2/latest/UserGuide/EBSVolumeTypes.html

AWS caps the iops depends on your storage type. If it's gp2, you get 3 iops per GB of storage. If your index is a type int, with 12M records, the index size is probably at 150MB. At 700 iops, it'd take a while even with no other sessions running. If you got other sessions taking up the iops, you get buffer_io wait.

Upvotes: 1

Related Questions