{"id":1970,"date":"2015-05-07T02:20:45","date_gmt":"2015-05-06T23:20:45","guid":{"rendered":"http:\/\/www.profelis.com.tr\/tr\/blog\/?p=1970"},"modified":"2023-02-01T10:58:01","modified_gmt":"2023-02-01T07:58:01","slug":"postgresql-101-sorgularim-neden-yavasliyor","status":"publish","type":"post","link":"https:\/\/profelis.com.tr\/en\/2015\/05\/07\/postgresql-101-sorgularim-neden-yavasliyor\/","title":{"rendered":"PostgreSQL 101: Why Are My Queries Slow?"},"content":{"rendered":"<h2>PostgreSQL 101: Why Are My Queries Slow?<\/h2>\n<p>We are not talking about slow queries; let's talk about queries that we noticed have slowed down suddenly, out of nowhere, while they were working normally until yesterday. These are queries that used to take milliseconds but now take seconds, or used to take a few seconds and now take minutes.<\/p>\n<p>Let's first take and eliminate a few points that will catch our attention at first glance:<\/p>\n<h2>What changed compared to yesterday?<\/h2>\n<p><a title=\"Matrix\" href=\"http:\/\/matrix.wikia.com\/wiki\/Matrix\" target=\"_blank\" rel=\"noopener\">Matrix<\/a>\u2018We call the repeating images that occur when a change is made to it:\u00a0<strong>d\u00e9j\u00e0 vu!<\/strong>\u00a0The first checkpoints we will look at will be the recent changes. Usually, the likely suspects behind these slowdowns are these changes.<\/p>\n<ul>\n<li>Did we ingest new data, or is there a sudden growth? It should be checked whether the queries are returning significantly more data compared to yesterday.<\/li>\n<li>Did we mess with an index? If you keep a change log, you should check it. In such cases, it is not unusual for the person responsible to be sitting right in the passenger seat \ud83d\ude42<\/li>\n<li>Did we add a monitor\/monitoring tool? Could monitoring tools like Cacti or Munin have started collecting information such as CPU, memory, and disk usage too frequently?<\/li>\n<\/ul>\n<p>If we already have a pre-installed monitoring tool, checking for changes in values such as CPU, memory, disk, and network usage can be a good starting point this time. For example, one of the disks in a RAID system may have failed and a spare disk is currently being added to the system, which could result in very high I\/O values.<\/p>\n<h2>Database Analysis<\/h2>\n<p>If questioning the usual suspects didn't yield anything, let's take a look at our database:<\/p>\n<pre>db_01=# analyze;\nANALYZE\nTime: 6331.113 ms\n<\/pre>\n<p>Although it is a cheap and widely used method, it is the simplest one that usually does not fix a problem, meaning what we call the \"Turkish style\" of giving two pats on the head. But if you observe a remarkable improvement, then know that <strong>autovacuum<\/strong> It means it is not fulfilling its function properly. In this case, you can direct your attention to this issue.<\/p>\n<p>Sometimes, due to reasons like external data insertion, the data size of certain tables may have increased suddenly, but the table statistics might not have been updated. In such a case, the statistics-based optimization engine might have chosen a poor plan for the queries in question. In this situation, running the ANALYZE command manually updates the table statistics, and the queries start running with the correct plans.<\/p>\n<p>You can try isolating and running the slow queries to test them. Do they exhibit the same performance if you have development, test, etc. systems or on your read-only replica? Queries running concurrently compete with each other for memory. Your query might be losing this competition.<\/p>\n<p>Is there still a performance loss when the same query is executed a second time? This situation indicates a memory-related issue, as we explained above. The results may not fit in memory or may be evicted from memory by other queries.<\/p>\n<h2>Table and Index Bloating<em>Index Bloat<\/em>)<\/h2>\n<div class=\"page\" title=\"Page 223\">\n<div class=\"layoutArea\">\n<div class=\"column\">\n<p>One of the problems that can occur over time is the table bloating issue caused by maintenance operations not running correctly. <a href=\"https:\/\/wiki.postgresql.org\/wiki\/MVCC\" target=\"_blank\" rel=\"noopener noreferrer\">MVCC<\/a>\u2018Due to the operating principles of , tables continue to store a large amount of older version data. If these old versions are not deleted in some way, the problem of table bloat occurs.<\/p>\n<p>There can be several different reasons for this problem, almost all of which are related to INSERT, UPDATE, and DELETE statements. During these operations, auto-vacuum (<em>autovacuum<\/em>) if it cannot perform the removal of the old data, and even if it has performed this function and deleted the old versions, the table [contains] the old versions (<em>tuples<\/em>) and remains with the data size. So much so that it is not unheard of for a table containing only a single row of data to appear to be several gigabytes in size.<\/p>\n<p>When such situations are encountered, the following query will be helpful:<\/p>\n<pre>SELECT\n   pg_relation_size(relid) as tablesize, \n   schemaname, relname, n_live_tup\nFROM pg_stat_user_tables\nWHERE relname = ;\n<\/pre>\n<p>Show table size <strong>table size<\/strong> how much <strong>n_live_tup<\/strong>\u00a0You must observe the relationship between the value such that if the table size is relatively large compared to the table structure, but the number of rows is very small, there is likely a table bloat issue. In such a case, the indexes in the table (<em>indices<\/em>) it is inevitable that it will swell and cause queries to slow down.<\/p>\n<p><strong>Attention:<\/strong> Depending on the table structure, relative size refers to the presence of blobs or large text fields; please note that the table may already store large-sized data. Make a size comparison based on the volume of the stored data.<\/p>\n<\/div>\n<\/div>\n<\/div>","protected":false},"excerpt":{"rendered":"<p>We are not talking about slow queries; let's talk about queries that we noticed have slowed down suddenly, out of nowhere, while they were working normally until yesterday. These are queries that used to take milliseconds but now take seconds, or used to take a few seconds and now take minutes.<\/p>","protected":false},"author":3,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[75,45],"tags":[48,60],"class_list":["post-1970","post","type-post","status-publish","format-standard","hentry","category-postgresql","category-yazi","tag-enterprisedb","tag-postgresql"],"_links":{"self":[{"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/posts\/1970","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/comments?post=1970"}],"version-history":[{"count":0,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/posts\/1970\/revisions"}],"wp:attachment":[{"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/media?parent=1970"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/categories?post=1970"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/profelis.com.tr\/en\/wp-json\/wp\/v2\/tags?post=1970"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}