Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nZw2r-0001ng-74 for pgsql-performance@arkaria.postgresql.org; Thu, 31 Mar 2022 14:45:05 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1nZw2q-0000zv-3p for pgsql-performance@arkaria.postgresql.org; Thu, 31 Mar 2022 14:45:04 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1nZw2p-0000zl-Oc for pgsql-performance@lists.postgresql.org; Thu, 31 Mar 2022 14:45:03 +0000 Received: from mail-qt1-x82b.google.com ([2607:f8b0:4864:20::82b]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1nZw2m-00043t-Lg for pgsql-performance@lists.postgresql.org; Thu, 31 Mar 2022 14:45:02 +0000 Received: by mail-qt1-x82b.google.com with SMTP id t2so21551815qtw.9 for ; Thu, 31 Mar 2022 07:45:00 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=message-id:date:mime-version:user-agent:subject:content-language:to :references:from:in-reply-to; bh=592uJb2w+vcBfNu0IRvADTQ6S1502PDiog+CnVVk8E0=; b=hmWYl6vpFlO2IJboe3naeGMS+h2g4tiE2EEkhfFpkthLIE+t1Ohe1S7bTldf9VfMm7 Hw+ABtGLYilnVYxeRYtX8eZrhinzb77BLmxPRQ4pzugjZpmcDYlL2f7LTHn2xN1CQtBm eTYOjRmHz9NdDd8h16A0eb/TgwDuMeD72taJj3XyMQvsabP8e/do9omb+B4APIGxnScb c499q27vadXU5w/wYvpyKbGhPRkaOZ97iAqCD6yE0Y/KwH4hWPPgxusJNooZ3mxa+JJ2 vhZNNi9hdL1hP0Tv/KtJnksKDgrNCmvySttTutCPrzzXihRdsIB5nTkJSh2BSAXHOO3y RU+A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=x-gm-message-state:message-id:date:mime-version:user-agent:subject :content-language:to:references:from:in-reply-to; bh=592uJb2w+vcBfNu0IRvADTQ6S1502PDiog+CnVVk8E0=; b=dDdI1djkxP4UvvkbF3eFALiAdnO7LMGZDEUFW4vMC8FXliqciginieaM1PPrTKbcpH Hx1NTQvLKzZGrDvzvLDlSWazIrYPeLokADUP2prVo6jdIReyBo5MO9RvASHKJz4ilTNW 7Y/lLaqATJldV/p82e4YBUuQHbBtBJrH1sAZTE03izQuBi49mTN2ZOzwZmA2BsuJ3HEK 9qG+vluayHNCUo8yT+wM+QSDAXdbmJpTxMMm3pdvZGtdhAUBREClYspECqJTEzETWKzb j4i+4XpFj0ps1KwYtLYRujQ+741FCqFmn4PhQHmf+HUvpzjk3tKXQu/SHcv6AzhaxYH3 eNfw== X-Gm-Message-State: AOAM530KxMwrgvOIp3NqHxEvHF/JUrmbUzALWRuYV4gEYSl4RmCPsNn/ 8rqedieHip/67ztPqlOXRNtSBBpPm8SzPA== X-Google-Smtp-Source: ABdhPJzbX1Vlprx16aynttiH1AlJF/7kh1PMnhjylrI8Jh5Z5UcMI0PxSDAmUNhIERep31doT8C4ng== X-Received: by 2002:ac8:5855:0:b0:2eb:87af:ab50 with SMTP id h21-20020ac85855000000b002eb87afab50mr4615744qth.406.1648737898566; Thu, 31 Mar 2022 07:44:58 -0700 (PDT) Received: from [192.168.2.100] (pool-72-79-19-93.nwrknj.east.verizon.net. [72.79.19.93]) by smtp.gmail.com with ESMTPSA id a9-20020ac85b89000000b002e2072c9dedsm20959741qta.67.2022.03.31.07.44.57 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 31 Mar 2022 07:44:57 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------MT5xvbIjyQ1vocsTOX0HluWc" Message-ID: Date: Thu, 31 Mar 2022 10:44:57 -0400 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101 Thunderbird/91.7.0 Subject: Re: HIGH IO and Less CPU utilization Content-Language: en-US To: pgsql-performance@lists.postgresql.org References: From: Mladen Gogala In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------MT5xvbIjyQ1vocsTOX0HluWc Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit On 3/29/22 14:04, Rambabu g wrote: > Hi All, > > We have an issue with high load and IO Wait's but less cpu on postgres > Database, The emp Table size is around 500GB, and the connections are > very less. > > Please suggest to us do we need to change and config parameters at > system level or Postgres configuration. The "emp" table is 500 GB? You're doing something wrong, The "emp" table should have 14 rows and the "dept" table should have 4 rows The "bonus" and "salgrade" tables should also be very small. The guy named Bruce Scott could probably help you with that schema. Other than that, do you have a SQL causing all this ruckus and a detailed explain plan ("explain (analyze,costs,buffers)") for the SQL using most of the time? You can analyze the log file with PgBadger to get the queries consuming the most time. -- Mladen Gogala Database Consultant Tel: (347) 321-1217 https://dbwhisperer.wordpress.com --------------MT5xvbIjyQ1vocsTOX0HluWc Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit
On 3/29/22 14:04, Rambabu g wrote:
Hi All,

We have an issue with high load and IO Wait's but less cpu on postgres Database, The emp Table size is around 500GB, and the connections are very less.

Please suggest to us do we need to change and config parameters at system level or Postgres configuration.


The "emp" table is 500 GB? You're doing something wrong, The "emp" table should have 14 rows and the "dept" table should have 4 rows The "bonus" and "salgrade" tables should also be very small. The guy named Bruce Scott could probably help you with that schema. Other than that, do you have a SQL causing all this ruckus and a detailed explain plan ("explain (analyze,costs,buffers)") for the SQL using most of the time? You can analyze the log file with PgBadger to get the queries consuming the most time.

-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com
--------------MT5xvbIjyQ1vocsTOX0HluWc--