From jdetate@gmail.com Mon Mar 24 22:57:05 2008 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 7A23E2E0051 for ; Mon, 24 Mar 2008 19:57:05 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 87465-09 for ; Mon, 24 Mar 2008 19:57:02 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.244]) by postgresql.org (Postfix) with ESMTP id 282142E005D for ; Mon, 24 Mar 2008 19:57:02 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id c36so670910ana.33 for ; Mon, 24 Mar 2008 15:57:01 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:from:to:subject:date:message-id:mime-version:content-type:x-mailer:thread-index:content-language; bh=nisPgtK0g0RLq91sYkNV4ArC/mhWH1UGwtZqCuJ5yu8=; b=dU4lYDaoow3gYWPzxCP3jZunU4IzMIKfLIheZW0gHxDp0FPhDiNXAUhm1pqOPC8/nD/FFe+6H7rQ7RolTKfi0/A/OisCP8RY3jqSWVjo8tmPuXzlZoYOG6gzddi69MmcJ3BwrFmNZvYo91apX0qrcou/WXXlaqgd0z2M0xkZUJ8= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=from:to:subject:date:message-id:mime-version:content-type:x-mailer:thread-index:content-language; b=YT37UAuAWH7lcvfXFNdkpfyG9Dda5V/7lZDKbsZrQcNtTI+vTYL+ZGLUTF4aSL3aMTD0O8LIqoMO6koLH/JZmYrrSg6HJ/oAX6gSRMo8zQNNMc2Sn1yXDge9jMJnq33pCf+6vunwh9VqXjUF0qAasECBRGiKgKKDj/Ew0XjOJik= Received: by 10.100.140.15 with SMTP id n15mr19117713and.87.1206399421109; Mon, 24 Mar 2008 15:57:01 -0700 (PDT) Received: from JackMobile ( [166.129.253.31]) by mx.google.com with ESMTPS id v26sm15022625ele.7.2008.03.24.15.56.58 (version=TLSv1/SSLv3 cipher=OTHER); Mon, 24 Mar 2008 15:56:59 -0700 (PDT) From: "Jack" To: Subject: Tuning Date: Mon, 24 Mar 2008 15:56:42 -0700 Message-ID: <002601c88e02$5629fc10$027df430$@com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0027_01C88DC7.A9CB2410" X-Mailer: Microsoft Office Outlook 12.0 Thread-Index: AciOAlONBkWeseSeTnyb1PdbIgCqDw== Content-Language: en-us X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.001 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200803/213 X-Sequence-Number: 28696 This is a multipart message in MIME format. ------=_NextPart_000_0027_01C88DC7.A9CB2410 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit Is there a Postgres equivalent of tkprofs for tuning? ------=_NextPart_000_0027_01C88DC7.A9CB2410 Content-Type: text/html; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable

Is there a Postgres equivalent of tkprofs for = tuning?

------=_NextPart_000_0027_01C88DC7.A9CB2410-- From scott.marlowe@gmail.com Tue Mar 25 15:29:41 2008 Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 559D92E006A for ; Tue, 25 Mar 2008 12:29:41 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 86379-03 for ; Tue, 25 Mar 2008 12:29:37 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from el-out-1112.google.com (el-out-1112.google.com [209.85.162.177]) by postgresql.org (Postfix) with ESMTP id 777C82E0076 for ; Tue, 25 Mar 2008 12:29:37 -0300 (ADT) Received: by el-out-1112.google.com with SMTP id z25so1924889ele.0 for ; Tue, 25 Mar 2008 08:29:36 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=i2zI9gGggcQ4kcbhtgNaTuOHP4STD8vRzJn54IguliQ=; b=oQzeEjgeISBjSh5+3y5sw8QVCFt+7d3L7ein3tAfdlnG3M8efYzOWMJ7Km0oH0V4AW5jEI5GjmJ1/UMlq752RFeirhHcoD/S1tR0VO2x1FSfeXD2A9MV+7g+Vb/chX/ZFPiMOC+OWRk4rgUDkrLFChw4LNVa9IaAiKukCZQYawY= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=pf+LX9zL/SXW7eKjaXwFZFuTGwWcFtrmSaZQkr9yBFpOgXrWlE54N1xCOlDITeRE3S2tBxV8Wkti0b3bhKiLcWXLwjTLIuH9CBGW5FGdXbX0clyf0fapXL1HCh80/cGqUeJeRHA9byHx/xxaqMINCxxfTbEjYv2oZZ6US9YzxZI= Received: by 10.141.88.3 with SMTP id q3mr3408589rvl.46.1206458976366; Tue, 25 Mar 2008 08:29:36 -0700 (PDT) Received: by 10.114.137.7 with HTTP; Tue, 25 Mar 2008 08:29:36 -0700 (PDT) Message-ID: Date: Tue, 25 Mar 2008 09:29:36 -0600 From: "Scott Marlowe" To: Jack Subject: Re: Tuning Cc: pgsql-admin@postgresql.org In-Reply-To: <002601c88e02$5629fc10$027df430$@com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <002601c88e02$5629fc10$027df430$@com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/214 X-Sequence-Number: 28697 On Mon, Mar 24, 2008 at 4:56 PM, Jack wrote: > > > > > Is there a Postgres equivalent of tkprofs for tuning? you might want to provide an explanation of what tkprofs is and which parts you're interested in emulating in pgsql. Have you read up on explain, explain analyze, and the various pg_stat_* tables? Do any of those help? From maxottovonstirlitz@gmail.com Wed Mar 26 12:24:46 2008 Received: from localhost (unknown [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 5AF872E007C for ; Wed, 26 Mar 2008 09:24:46 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 98683-07 for ; Wed, 26 Mar 2008 09:24:33 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wr-out-0506.google.com (wr-out-0506.google.com [64.233.184.231]) by postgresql.org (Postfix) with ESMTP id DAC592E0062 for ; Wed, 26 Mar 2008 09:24:33 -0300 (ADT) Received: by wr-out-0506.google.com with SMTP id 76so3086274wra.10 for ; Wed, 26 Mar 2008 05:24:32 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references:x-google-sender-auth; bh=V9ZmS6st2uzsitPs6pimFDKZb71oX5U1RJQy6go8jio=; b=ZnxSOwNXk+rCvjISC7AvUBO3ltWHOl6OSMYdoNZZeMDs3cssT7Sb02TYlRoAfBxWSroK7qpr+P4F5X9NQqUVT9Ui21p0fyKodhNSbzV8dwpfu6zLMXjgKocBZFAAiG0NlarfLTNmXHdslfz6l19f0igu4vRTcuZcZlbOBWDv4bw= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=message-id:date:from:sender:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references:x-google-sender-auth; b=Zb0z6SmzHKq9DtaaOAL0qAhIscmpXuCVitxQz1zLbWvwPSxeyBfbT6cZf5lmbCCWs9g+DVYImfCStA0j+uimu2JoOj9EiB91r0pk9SjpGv6u9Y6ejymMRCruGAaag6ObiKSHw/8JskIP0Itmt+KmE1Cbzi3ESeP2+Oz4NOegaxY= Received: by 10.140.169.6 with SMTP id r6mr4356015rve.210.1206534271603; Wed, 26 Mar 2008 05:24:31 -0700 (PDT) Received: by 10.141.195.9 with HTTP; Wed, 26 Mar 2008 05:24:31 -0700 (PDT) Message-ID: Date: Wed, 26 Mar 2008 13:24:31 +0100 From: "Peter Kovacs" To: "Scott Marlowe" Subject: Re: Tuning Cc: Jack , pgsql-admin@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <002601c88e02$5629fc10$027df430$@com> X-Google-Sender-Auth: 5a9017277dc3a5e5 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/223 X-Sequence-Number: 28706 tkprof generates performance characteristics for an arbitrary number of SQL statements. The output includes measurements for the individual SQL statements as well as cumulated values for the sequence of SQL statements being measured. Data is presented in a two dimensional format with the first dimension being the type of operation (parsing, compiling, executing, fetching) and the second dimension being the time spent in CPU or IO. You can see a sample output here: http://www.jlcomp.demon.co.uk/tkprof_01.html . The trick is that the Oracle code is instrumented to track all the information necessary for these measurements. When tracing is enabled, the measure values are output sequentially and this output is the post-processed by tkprof. The raw (tracing) output is also fairly readable, so if you want to dig deeper than the tkprof output, you can use it (eventually post-processing it in your own way). I am not familiar with PostgreSQL's explain analyze method, but as far as I remember, it can be used only for one SQL statement and requires a significant amount of practice to interpret easily. BTW, I had a bookmark for a good tutorial on explain analyze, but the page is no longer available: http://www.pervasive-postgres.com/instantkb13/article.aspx?id=10120 . Can you suggest a good tutorial? Thanks Peter On Tue, Mar 25, 2008 at 4:29 PM, Scott Marlowe wrote: > On Mon, Mar 24, 2008 at 4:56 PM, Jack wrote: > > > > > > > > > > Is there a Postgres equivalent of tkprofs for tuning? > > you might want to provide an explanation of what tkprofs is and which > parts you're interested in emulating in pgsql. > > Have you read up on explain, explain analyze, and the various > pg_stat_* tables? Do any of those help? > > -- > Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-admin > From milen@radev.net Wed Mar 26 12:39:29 2008 Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 652882E007D for ; Wed, 26 Mar 2008 09:39:29 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 20204-04 for ; Wed, 26 Mar 2008 09:39:09 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from ciao.gmane.org (main.gmane.org [80.91.229.2]) by postgresql.org (Postfix) with ESMTP id 14F262E0085 for ; Wed, 26 Mar 2008 09:39:16 -0300 (ADT) Received: from list by ciao.gmane.org with local (Exim 4.43) id 1JeUuP-0003p4-O9 for pgsql-admin@postgresql.org; Wed, 26 Mar 2008 12:39:14 +0000 Received: from pirus.securax.be ([213.16.46.130]) by main.gmane.org with esmtp (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Wed, 26 Mar 2008 12:39:13 +0000 Received: from milen by pirus.securax.be with local (Gmexim 0.1 (Debian)) id 1AlnuQ-0007hv-00 for ; Wed, 26 Mar 2008 12:39:13 +0000 X-Injected-Via-Gmane: http://gmane.org/ To: pgsql-admin@postgresql.org From: "Milen A. Radev" Subject: Re: Tuning Date: Wed, 26 Mar 2008 14:39:00 +0200 Lines: 16 Message-ID: References: <002601c88e02$5629fc10$027df430$@com> Mime-Version: 1.0 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit X-Complaints-To: usenet@ger.gmane.org X-Gmane-NNTP-Posting-Host: pirus.securax.be User-Agent: Thunderbird 2.0.0.12 (Windows/20080213) In-Reply-To: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/224 X-Sequence-Number: 28707 Peter Kovacs написа: [...] > BTW, I had a bookmark for a good tutorial on explain analyze, but the > page is no longer available: > http://www.pervasive-postgres.com/instantkb13/article.aspx?id=10120 . > Can you suggest a good tutorial? I'm not sure what was the tutorial at the above URL but this what I know: http://redivi.com/~bob/oscon2005_pgsql_pdf/OSCON_Explaining_Explain_Public.pdf (google for "explaining explain") [...] -- Milen A. Radev From jonah.harris@gmail.com Wed Mar 26 13:15:47 2008 Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id BB1702E0056 for ; Wed, 26 Mar 2008 10:15:47 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 31822-02 for ; Wed, 26 Mar 2008 10:15:29 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from qb-out-0506.google.com (qb-out-0506.google.com [72.14.204.226]) by postgresql.org (Postfix) with ESMTP id 3073D2E004A for ; Wed, 26 Mar 2008 10:15:37 -0300 (ADT) Received: by qb-out-0506.google.com with SMTP id o12so3949718qba.17 for ; Wed, 26 Mar 2008 06:15:35 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=ssEMC31H0k5bTX6q1m5GTMhzaTefMz5r2hZBx/zM/dw=; b=QXKtVwx5S4jGPnr051f9nljdQCtt/RPqbP0iM70e6jRfDNexT78wkb9t66AJFiUUEq6LnSVIaEWZiH97KKsWMDuo6DFbka7gK6xOMvTBOdyU6hUWThvlPwVrfelY0mm5Lx29mqM/ylIsmvs+/NiTW8ZxEnVZ930bORRJSIpkMWM= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=mjR+cJNX1d7r4HBxdiquS3g2W4uF/zj6jUWnLWufzrYpgpxGRo6rkQUdmrg8KozVZnKPDPQ3IFiMaA3AeYQCs/LZrSriZ+ahL+Jidz6P12ERsAO5iF9DrP6eJi6BJRzb3KIGyhi6wZWncEf9aA7XopnUyYkHwv+ihE0Qd4FxjHA= Received: by 10.150.190.9 with SMTP id n9mr1929ybf.127.1206537335027; Wed, 26 Mar 2008 06:15:35 -0700 (PDT) Received: by 10.150.198.15 with HTTP; Wed, 26 Mar 2008 06:15:34 -0700 (PDT) Message-ID: <36e682920803260615v125638bdu5089600955fb2474@mail.gmail.com> Date: Wed, 26 Mar 2008 09:15:34 -0400 From: "Jonah H. Harris" To: Jack Subject: Re: Tuning Cc: pgsql-admin@postgresql.org In-Reply-To: <002601c88e02$5629fc10$027df430$@com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <002601c88e02$5629fc10$027df430$@com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200803/225 X-Sequence-Number: 28708 On Mon, Mar 24, 2008 at 6:56 PM, Jack wrote: > Is there a Postgres equivalent of tkprofs for tuning? No. But what you could do is combine several Postgres things to try and get the same information. IIRC, you could use: - log_parser_stats - log_planner_stats - log_executor_stats while running EXPLAIN ANALYZE on the query. psql -U user1 -d postgres > /tmp/pgprof.out 2>&1 <; Mon, 7 Apr 2008 12:33:29 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 00735-01 for ; Mon, 7 Apr 2008 12:33:20 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from belushi.uits.indiana.edu (belushi.uits.indiana.edu [129.79.1.188]) by developer.postgresql.org (Postfix) with ESMTP id 6FB322E0038 for ; Mon, 7 Apr 2008 12:33:20 -0300 (ADT) Received: from mail-relay.iu.edu (stjoseph.uits.indiana.edu [129.79.1.78]) by belushi.uits.indiana.edu (8.14.2/8.13.8/IU Messaging Team) with ESMTP id m37FXFVc005235 for ; Mon, 7 Apr 2008 11:33:15 -0400 Received: from [129.79.36.69] (walter.slis.indiana.edu [129.79.36.69]) (authenticated bits=0) by mail-relay.iu.edu (8.13.6/8.12.10/IUPO) with ESMTP id m37FXEQ6002059 (version=TLSv1/SSLv3 cipher=AES128-SHA bits=128 verify=NO) for ; Mon, 7 Apr 2008 11:33:15 -0400 (EDT) Mime-Version: 1.0 (Apple Message framework v753) Content-Transfer-Encoding: 7bit Message-Id: <6F737A76-FC25-4876-A6E0-163B87ECFAB0@indiana.edu> Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed To: pgsql-admin@postgresql.org From: Carol Walter Subject: Tuning Date: Mon, 7 Apr 2008 11:33:13 -0400 X-Mailer: Apple Mail (2.753) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200804/52 X-Sequence-Number: 28816 I have some questions about tuning. The PostgreSQL documentation says that you don't need to worry about index maintenance and tuning with PostgreSQL. I'm used to systems that work a lot better if they are periodically re-orged or re-indexed. Is it true that one need not be concerned with this? I'm certain that the databases must require some human intervention. What kind of tuning or other intervention are you doing. What kind of tools are available and are being used. Carol Walter From scott.marlowe@gmail.com Mon Apr 7 16:27:16 2008 Received: from localhost (unknown [200.46.204.184]) by developer.postgresql.org (Postfix) with ESMTP id B620E2E005B for ; Mon, 7 Apr 2008 13:27:16 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 14076-02-4 for ; Mon, 7 Apr 2008 13:27:11 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.178]) by developer.postgresql.org (Postfix) with ESMTP id 0755D2E008F for ; Mon, 7 Apr 2008 13:27:02 -0300 (ADT) Received: by wa-out-1112.google.com with SMTP id m38so1760244waf.5 for ; Mon, 07 Apr 2008 09:27:02 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=wWzB8MWWalErk9JMm/73J/m95YC8TuP51YU+5P/MYrc=; b=jwUYIg7sxoGsKqRwtZmrLWmfE5iljXsIumQ8I7BCzzu+ZdVjN+1LpgsSpa4P4Iw1rNMwTDKUYcRGbFp/yJ056mnfFFMRiWN712+ZtueBU6J6cHVDWqZTyalAajdkrupnm/ZB9fEbvUusvS7bj1DOUjNjIr5yElH0JyPksviEpNg= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=aPXfg/jNIEDMqO0YoKv6AseFI1ysAPxU/FMBoh9KrVhWZ9eiTPvUSE8MHp6go+xobJ81ippfOTKeIkvVrm9T+5IKT0MTf6yKj0AOqF3JMmq9E/vQoYgtWyUWCTJLu28Ctsn+aWg0FfX1mezfjfEiB8D4BxrAKW9OGpt9MdQgCUM= Received: by 10.114.191.1 with SMTP id o1mr6172730waf.117.1207585622053; Mon, 07 Apr 2008 09:27:02 -0700 (PDT) Received: by 10.114.137.7 with HTTP; Mon, 7 Apr 2008 09:27:02 -0700 (PDT) Message-ID: Date: Mon, 7 Apr 2008 10:27:02 -0600 From: "Scott Marlowe" To: "Carol Walter" Subject: Re: Tuning Cc: pgsql-admin@postgresql.org In-Reply-To: <6F737A76-FC25-4876-A6E0-163B87ECFAB0@indiana.edu> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <6F737A76-FC25-4876-A6E0-163B87ECFAB0@indiana.edu> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200804/54 X-Sequence-Number: 28818 On Mon, Apr 7, 2008 at 9:33 AM, Carol Walter wrote: > I have some questions about tuning. The PostgreSQL documentation says that > you don't need to worry about index maintenance and tuning with PostgreSQL. > I'm used to systems that work a lot better if they are periodically re-orged > or re-indexed. Is it true that one need not be concerned with this? I'm > certain that the databases must require some human intervention. What kind > of tuning or other intervention are you doing. What kind of tools are > available and are being used. Not sure where in the docs it exactly says to never worry about your indexes. While most access patterns can indeed be handled fine by autovacuum, you can use the reindex command in pgsql to optimize your indexes if they're chock full of empty space that can't be reclaimed by vacuum. You can monitor things like index and table bloat with the pg_stat_* series of views. From cbbrowne@acm.org Mon Apr 7 17:25:20 2008 Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id B12892E005D for ; Mon, 7 Apr 2008 14:25:20 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 18985-03 for ; Mon, 7 Apr 2008 14:25:17 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by developer.postgresql.org (Postfix) with ESMTP id C650D2E006B for ; Mon, 7 Apr 2008 14:25:12 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.14.1/8.14.1) with ESMTP id m37HPBWa094220 for ; Mon, 7 Apr 2008 14:25:11 -0300 (ADT) (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.14.1/8.14.1/Submit) id m37H45d0084373 for pgsql-admin@postgresql.org; Mon, 7 Apr 2008 14:04:05 -0300 (ADT) (envelope-from news) From: Chris Browne X-Newsgroups: pgsql.admin Subject: Re: Tuning Date: Mon, 07 Apr 2008 12:45:38 -0400 Organization: cbbrowne Computing Inc Lines: 35 Message-ID: <60myo51uml.fsf@dba2.int.libertyrms.com> References: <6F737A76-FC25-4876-A6E0-163B87ECFAB0@indiana.edu> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Complaints-To: usenet@news.hub.org User-Agent: Gnus/5.1008 (Gnus v5.10.8) XEmacs/21.4.21 (linux) Cancel-Lock: sha1:/xqV65Z1iLF1czX8/Yi07LfM81c= To: pgsql-admin@postgresql.org X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200804/55 X-Sequence-Number: 28819 walterc@indiana.edu (Carol Walter) writes: > I have some questions about tuning. The PostgreSQL documentation > says that you don't need to worry about index maintenance and tuning > with PostgreSQL. I'm used to systems that work a lot better if they > are periodically re-orged or re-indexed. Is it true that one need > not be concerned with this? I'm certain that the databases must > require some human intervention. What kind of tuning or other > intervention are you doing. What kind of tools are available and are > being used. Well, there is one side to things where yes, indeed, "maintenance is quite necessary," and that being in the area of "vacuuming." The need to 'reindex' or 'reorg' tables is not non-existent, however it is needed *way* less frequently than was the case in much older versions of PostgreSQL. e.g. - with v7.2, there were patterns of updates that would leave portions of indexes not usable, but the issue was rectified in ~7.4, and people have not been observing problems relating to this former scenario. Back when we had systems on v7.2, we had to shut down every few months and reindex some tables in order to keep performance OK. That's no longer the case with systems running on v8.1, and as we bring 8.3 into production, I expect even less need for manual interventions. If you are running VACUUM and ANALYZE often enough, and autovacuum pretty much does so, now, then there shouldn't be much need to do "re-orging" of the system. -- let name="cbbrowne" and tld="acm.org" in String.concat "@" [name;tld];; http://linuxfinances.info/info/nonrdbms.html In the name of the Lord-High mutant, we sacrifice this suburban girl -- `Future Schlock' From ajs@crankycanuck.ca Mon Apr 7 17:43:49 2008 Received: from localhost (unknown [200.46.204.184]) by developer.postgresql.org (Postfix) with ESMTP id B00BE2E00C5 for ; Mon, 7 Apr 2008 14:43:49 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 48738-01-4 for ; Mon, 7 Apr 2008 14:43:41 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from main2.mycybernet.net (main2.mycybernet.net [209.222.63.140]) by developer.postgresql.org (Postfix) with ESMTP id 070822E006B for ; Mon, 7 Apr 2008 14:39:54 -0300 (ADT) Received: from 227-54-222-209.mycybernet.net ([209.222.54.227] helo=crankycanuck.ca) by main2.mycybernet.net with esmtp (Exim 4.62) (envelope-from ) id 1JivJy-0003Rx-BF for pgsql-admin@postgresql.org; Mon, 07 Apr 2008 13:39:54 -0400 Received: by crankycanuck.ca (Postfix, from userid 1000) id 211A95014A; Mon, 7 Apr 2008 13:39:33 -0400 (EDT) Date: Mon, 7 Apr 2008 13:39:33 -0400 From: Andrew Sullivan To: pgsql-admin@postgresql.org Subject: Re: Tuning Message-ID: <20080407173933.GA7301@crankycanuck.ca> Mail-Followup-To: Andrew Sullivan , pgsql-admin@postgresql.org References: <6F737A76-FC25-4876-A6E0-163B87ECFAB0@indiana.edu> <60myo51uml.fsf@dba2.int.libertyrms.com> MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <60myo51uml.fsf@dba2.int.libertyrms.com> User-Agent: Mutt/1.5.13 (2006-08-11) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200804/56 X-Sequence-Number: 28820 On Mon, Apr 07, 2008 at 12:45:38PM -0400, Chris Browne wrote: > versions of PostgreSQL. e.g. - with v7.2, there were patterns of > updates that would leave portions of indexes not usable, but the issue > was rectified in ~7.4, and people have not been observing problems > relating to this former scenario. There remain use patterns that will leave the indexes in pretty bad shape. This is an inherent limiation with btrees, though -- if you just unbalanced the tree with a large number of deletes, there's nothing you can do except REINDEX. A From michael.monnerie@it-management.at Tue Apr 8 07:17:55 2008 Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id 8DB2C2E003A for ; Tue, 8 Apr 2008 04:17:55 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 73279-03 for ; Tue, 8 Apr 2008 04:17:34 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mailsrv1.zmi.at (mailsrv1.zmi.at [212.69.162.198]) by developer.postgresql.org (Postfix) with ESMTP id 3CE652E005D for ; Tue, 8 Apr 2008 04:17:33 -0300 (ADT) Received: from mailsrv.i.zmi.at (h081217054243.dyn.cm.kabsi.at [81.217.54.243]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (Client CN "mailsrv.i.zmi.at", Issuer "mailsrv.i.zmi.at" (not verified)) by mailsrv1.zmi.at (Postfix) with ESMTP id 826CE9C3 for ; Tue, 8 Apr 2008 09:17:29 +0200 (CEST) Received: from saturn.i.zmi.at (saturn.i.zmi.at [10.0.0.2]) by mailsrv.i.zmi.at (Postfix) with ESMTP id 58C5ABFBB for ; Tue, 8 Apr 2008 09:16:48 +0200 (CEST) From: Michael Monnerie Organization: it-management http://it-management.at To: pgsql-admin@postgresql.org Subject: Re: Tuning Date: Tue, 8 Apr 2008 09:17:22 +0200 User-Agent: KMail/1.9.6 (enterprise 20070904.708012) References: <6F737A76-FC25-4876-A6E0-163B87ECFAB0@indiana.edu> In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/signed; boundary="nextPart6983682.LBUjt8z26o"; protocol="application/pgp-signature"; micalg=pgp-sha1 Content-Transfer-Encoding: 7bit Message-Id: <200804080917.27689@zmi.at> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.028 tagged_above=0 required=5 tests=AWL=0.028 X-Spam-Level: X-Archive-Number: 200804/63 X-Sequence-Number: 28827 --nextPart6983682.LBUjt8z26o Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On Montag, 7. April 2008 Scott Marlowe wrote: > You can monitor things like index and table bloat with the pg_stat_* > series of views. Are there scripts to automate this look at stats? Or could one draw graphs from that values, to visualize how well the db=20 is? E.g., if you could calculate a % value, you could make RRD stats to=20 see it's change over time. Is there any project on this? mfg zmi =2D-=20 // Michael Monnerie, Ing.BSc ----- http://it-management.at // Tel: 0676/846 914 666 .network.your.ideas. // PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import" // Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4 // Keyserver: www.keyserver.net Key-ID: 1C1209B4 --nextPart6983682.LBUjt8z26o Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part. -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.4-svn0 (GNU/Linux) iD8DBQBH+xwHzhSR9xwSCbQRAukFAJ45OpGae3lG0eQZVKm7q5wCkJGFlQCdH9o+ hUly8HQgq6xVqcanhVU1EE8= =i2qP -----END PGP SIGNATURE----- --nextPart6983682.LBUjt8z26o-- From rivers.paul@gmail.com Tue Apr 8 07:38:24 2008 Received: from localhost (unknown [200.46.204.184]) by developer.postgresql.org (Postfix) with ESMTP id 34B982E003D for ; Tue, 8 Apr 2008 04:38:24 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 74285-05 for ; Tue, 8 Apr 2008 04:37:56 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from wa-out-1112.google.com (wa-out-1112.google.com [209.85.146.177]) by developer.postgresql.org (Postfix) with ESMTP id 973CD2E0048 for ; Tue, 8 Apr 2008 04:37:58 -0300 (ADT) Received: by wa-out-1112.google.com with SMTP id m38so2037043waf.5 for ; Tue, 08 Apr 2008 00:37:56 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding; bh=vQweplLdPfPTjkGW7dJxkueX9DaWbrgSL/44cK85c9E=; b=OlGRHjzQphWztj5trSoWCJY2a69Papta+XpGwY71W69ugucldu2pEQ2Z5jDuaf0MTW2jrQvcPBrUx0W70VdDVbNlBi802vfl89lahcuDRuyBRJvV4pFXXE1LbWMJB5tDEByvHek8C7WilhJy4z0AeFBYlwvcmiLyf/wba+bl4js= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding; b=qXGnNToNKOXzLucYZOOrHGmVdgziSuFAmuNRvCxv/3eeBHEDNifYXv5rSqZL0enLE0bfsSrIgtmLJWaoQoYtai7oD7QR0bQ1W75+AMSk8QaiIaKSgmwrfL/LgEaQVyO7gRK9rhIYwl6aMTt6VT9bd+xCCE/0TQx6W6agrQFTwYk= Received: by 10.115.61.1 with SMTP id o1mr404349wak.94.1207640276427; Tue, 08 Apr 2008 00:37:56 -0700 (PDT) Received: from ?192.168.1.102? ( [24.7.64.54]) by mx.google.com with ESMTPS id m30sm16635376wag.21.2008.04.08.00.37.55 (version=TLSv1/SSLv3 cipher=RC4-MD5); Tue, 08 Apr 2008 00:37:55 -0700 (PDT) Message-ID: <47FB20D4.3050504@gmail.com> Date: Tue, 08 Apr 2008 00:37:56 -0700 From: paul rivers User-Agent: Thunderbird 2.0.0.12 (X11/20080227) MIME-Version: 1.0 To: Michael Monnerie CC: pgsql-admin@postgresql.org Subject: Re: Tuning References: <6F737A76-FC25-4876-A6E0-163B87ECFAB0@indiana.edu> <200804080917.27689@zmi.at> In-Reply-To: <200804080917.27689@zmi.at> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200804/64 X-Sequence-Number: 28828 Michael Monnerie wrote: > On Montag, 7. April 2008 Scott Marlowe wrote: > >> You can monitor things like index and table bloat with the pg_stat_* >> series of views. >> > > Are there scripts to automate this look at stats? > Or could one draw graphs from that values, to visualize how well the db > is? E.g., if you could calculate a % value, you could make RRD stats to > see it's change over time. Is there any project on this? > > mfg zmi > I don't see a direct way to monitor bloat from pg_stat*. If I'm wrong, please set me straight. For example, monitoring index bloat would involve deciding how many pages an index would ideally consume, based on either sampling the table yourself or raiding stats, and making assumptions. Then you'd compare to actual (or approximated actual) and decide if you were within whatever threshold you think is OK. Paul From jeff@frostconsultingllc.com Tue Apr 8 07:48:33 2008 Received: from localhost (unknown [200.46.204.184]) by developer.postgresql.org (Postfix) with ESMTP id D4E3E2E0035 for ; Tue, 8 Apr 2008 04:48:33 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 77286-02 for ; Tue, 8 Apr 2008 04:48:16 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from glacier.frostconsultingllc.com (glacier.frostconsultingllc.com [69.36.227.170]) by developer.postgresql.org (Postfix) with ESMTP id DE2072E003A for ; Tue, 8 Apr 2008 04:48:18 -0300 (ADT) Received: from [32.172.234.212] (port=1060 helo=[10.184.48.21]) by glacier.frostconsultingllc.com with esmtpsa (TLSv1:RC4-MD5:128) (Exim 4.69) (envelope-from ) id 1Jj8Yr-00021M-Io; Tue, 08 Apr 2008 00:48:10 -0700 From: "Jeff Frost" To: "Michael Monnerie" CC: pgsql-admin@postgresql.org MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Message-ID: <200804080047580000@3254630908> Subject: Re: Tuning Date: Tue, 8 Apr 2008 00:47:57 -0700 Content-Transfer-Encoding: 8bit X-SA-Exim-Connect-IP: 32.172.234.212 X-SA-Exim-Mail-From: jeff@glacier.frostconsultingllc.com X-SA-Exim-Scanned: No (on glacier.frostconsultingllc.com); SAEximRunCond expanded to false X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200804/65 X-Sequence-Number: 28829 > Are there scripts to automate this look at stats? > Or could one draw graphs from that values, to visualize how well the db > is? E.g., if you could calculate a % value, you could make RRD stats to > see it's change over time. Is there any project on this? I don't know about rrd graphing it, but there are some great nagios plugins for this at http://bucardo.org/nagios You could have a look at the queries used in those and roll your own mrtg plugins. If you do, please share. From michael.monnerie@it-management.at Tue Apr 8 08:55:16 2008 Received: from localhost (unknown [200.46.204.183]) by developer.postgresql.org (Postfix) with ESMTP id E27C72E0035 for ; Tue, 8 Apr 2008 05:55:16 -0300 (ADT) Received: from developer.postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 10641-10 for ; Tue, 8 Apr 2008 05:55:09 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mailsrv1.zmi.at (mailsrv1.zmi.at [212.69.162.198]) by developer.postgresql.org (Postfix) with ESMTP id 9C32F2E004A for ; Tue, 8 Apr 2008 05:55:05 -0300 (ADT) Received: from mailsrv.i.zmi.at (h081217054243.dyn.cm.kabsi.at [81.217.54.243]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (Client CN "mailsrv.i.zmi.at", Issuer "mailsrv.i.zmi.at" (not verified)) by mailsrv1.zmi.at (Postfix) with ESMTP id B3C42EA2 for ; Tue, 8 Apr 2008 10:55:02 +0200 (CEST) Received: from saturn.i.zmi.at (saturn.i.zmi.at [10.0.0.2]) by mailsrv.i.zmi.at (Postfix) with ESMTP id C7B05BFBB for ; Tue, 8 Apr 2008 10:55:01 +0200 (CEST) From: Michael Monnerie Organization: it-management http://it-management.at To: pgsql-admin@postgresql.org Subject: Re: Tuning Date: Tue, 8 Apr 2008 10:55:02 +0200 User-Agent: KMail/1.9.6 (enterprise 20070904.708012) References: <6F737A76-FC25-4876-A6E0-163B87ECFAB0@indiana.edu> <200804080917.27689@zmi.at> <47FB20D4.3050504@gmail.com> In-Reply-To: <47FB20D4.3050504@gmail.com> MIME-Version: 1.0 Content-Type: multipart/signed; boundary="nextPart1290577.odDdKSKeRH"; protocol="application/pgp-signature"; micalg=pgp-sha1 Content-Transfer-Encoding: 7bit Message-Id: <200804081055.02620@zmi.at> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.026 tagged_above=0 required=5 tests=AWL=0.026 X-Spam-Level: X-Archive-Number: 200804/66 X-Sequence-Number: 28830 --nextPart1290577.odDdKSKeRH Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On Dienstag, 8. April 2008 paul rivers wrote: > I don't see a direct way to monitor bloat from pg_stat*. =A0 If I'm > wrong, please set me straight. > > For example, monitoring index bloat would involve deciding how many > pages an index would ideally consume, based on either sampling the > table yourself or raiding stats, and making assumptions. =A0Then you'd > compare to actual (or approximated actual) and decide if you were > within whatever threshold you think is OK. If there's no rule of thumb, how could you manually decide on actions,=20 other than by reading from a crystal ball? Also, if there are no tools, almost no admin can/will do anything.=20 Except for the specialist having too much free time to dig into the db=20 just for fun and reading tons of pg_stat* tables and values *g* As most won't have that funny time, tools which show possible problems=20 or performance losses would greatly help. On Dienstag, 8. April 2008 Jeff Frost wrote: > I don't know about rrd graphing it, but there are some great nagios > plugins for this at http://bucardo.org/nagios > > You could have a look at the queries used in those and roll your own > mrtg plugins. If you do, please share. Thank you. I'll have a look at that nagios plugins. mfg zmi =2D-=20 // Michael Monnerie, Ing.BSc ----- http://it-management.at // Tel: 0676/846 914 666 .network.your.ideas. // PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import" // Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4 // Keyserver: www.keyserver.net Key-ID: 1C1209B4 --nextPart1290577.odDdKSKeRH Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part. -----BEGIN PGP SIGNATURE----- Version: GnuPG v2.0.4-svn0 (GNU/Linux) iD8DBQBH+zLmzhSR9xwSCbQRAplPAJ9DJMO5NtvleM3XhCu67lsYqXtv0ACeICp9 so70PECLUIYmtI0t0CYTZHA= =H1YE -----END PGP SIGNATURE----- --nextPart1290577.odDdKSKeRH-- From ankushchawla03@gmail.com Fri Dec 18 03:54:14 2020 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 1kq6r8-0008Oq-MX for pgsql-admin@arkaria.postgresql.org; Fri, 18 Dec 2020 03:55:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kq6r6-0001JX-1K for pgsql-admin@arkaria.postgresql.org; Fri, 18 Dec 2020 03:55:00 +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 1kq6r5-0001JP-Jy for pgsql-admin@lists.postgresql.org; Fri, 18 Dec 2020 03:54:59 +0000 Received: from mail-lf1-x12f.google.com ([2a00:1450:4864:20::12f]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kq6qy-00078f-OL for pgsql-admin@postgresql.org; Fri, 18 Dec 2020 03:54:58 +0000 Received: by mail-lf1-x12f.google.com with SMTP id o13so1990246lfr.3 for ; Thu, 17 Dec 2020 19:54:52 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:from:date:message-id:subject:to; bh=/zbfxHr0TAf6vvEl5rMq5THm61urLQHAPqZpIzowmLg=; b=hznsDILYAJLt6b0Xec9Z+oZkz05lZ18BNmLkQEmmvVFNp6xmvJt3/VRuDqV13bvkX4 pMeFUE2akDipRyBAQEFoVGLKis7Zg3Jcuh55VaxRVyz7BVt71MhGNCksMeXWpUls9B23 d1O5PVw81fYOeIkQEU1Uf/gsg06Qnnzast3rJZ36SRCxRqu4XUGAGOc7BbuvONnvureB V2nQADIdFnks1xFl2fGa0j4Ua5sti2hF/pGJKnPqlIcjMQoo/AAeSIA3GDKRMfoeJR4R Wu0gkn48iQ3fRHMZDWnY4gR+rcCF1TJ0PJdMFHLLnUeCt0PTJukTFNvEfkV2RQ4M6rM8 JoBw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:from:date:message-id:subject:to; bh=/zbfxHr0TAf6vvEl5rMq5THm61urLQHAPqZpIzowmLg=; b=IWM0b/t6Ln3CgaWs5eEMU2AINgsFmxHtXC+KpT3Y+Ho1ceB4Jv3jWZzv04rfoKohNZ poLrw2b9/fIKE2iMQ24GYVZVfdHd/K/RiLMnJSBAIEcZl8Oz9F4dm/3b2UZC2ssPZ13t lCIO3SqwSuZy/vG47mo6GtL8ymXxxPF9+hvcJ+ZV3lnd/ob7vo0MVEIYcdMO5t40c9xt p52BwMRoz8w7jn+EnBLIo/DlcetTo5k1lJcmcrUzhddA7Xo1BnGExobA4gEiZudIsqUD WRCveVKlY8aZjsYuUlXYE/7ZL6oGTp6kEAU/S6vQTM8yYsxfseyzIzlpxyf8nTl3h2ch hwuw== X-Gm-Message-State: AOAM531iLOixvQbzpe52I9vWJsqpluXx5EsLvMMr2bDzvF74sqXhsYYw +2DxQMqXcwEYX1+S2dcTOnwnm1dJtKHC9yhVdXmxyyH2DJM= X-Google-Smtp-Source: ABdhPJwq5NL/3uWbIIK25I6Dm7WcCwxYjVoZhNHcwwNFTAk3gGP+qvTYQXme5VAtNtJqLx/shFmFzWbtPDGZk3jCNfE= X-Received: by 2002:a19:c3c5:: with SMTP id t188mr708174lff.505.1608263689857; Thu, 17 Dec 2020 19:54:49 -0800 (PST) MIME-Version: 1.0 From: Ankush Chawla Date: Fri, 18 Dec 2020 09:24:14 +0530 Message-ID: Subject: tuning To: pgsql-admin Content-Type: multipart/alternative; boundary="000000000000f0702b05b6b511ad" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --000000000000f0702b05b6b511ad Content-Type: text/plain; charset="UTF-8" hi all how to find the candidates tables or indexes for reindex or vacuum full -- Best Regards, Ankush Chawla --000000000000f0702b05b6b511ad Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
hi all
how to find the candidates tables or indexes fo= r reindex or vacuum=C2=A0full


--=
Best R= egards,
Ankush Chawla

<= /div> --000000000000f0702b05b6b511ad-- From keith@keithf4.com Fri Dec 18 06:45:15 2020 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 1kq9Wc-0005ZF-JZ for pgsql-admin@arkaria.postgresql.org; Fri, 18 Dec 2020 06:46:02 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kq9Wa-0002Hc-Op for pgsql-admin@arkaria.postgresql.org; Fri, 18 Dec 2020 06:46:00 +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 1kq9Wa-0002HV-CS for pgsql-admin@lists.postgresql.org; Fri, 18 Dec 2020 06:46:00 +0000 Received: from mail-pf1-x436.google.com ([2607:f8b0:4864:20::436]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kq9WT-0000j7-HN for pgsql-admin@postgresql.org; Fri, 18 Dec 2020 06:45:59 +0000 Received: by mail-pf1-x436.google.com with SMTP id m6so953509pfm.6 for ; Thu, 17 Dec 2020 22:45:53 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=keithf4-com.20150623.gappssmtp.com; s=20150623; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=LthcbkP7Wdjt8ldMPtUztfGpjhfprhWhUm4+jyfMfls=; b=qr9hlEPwrmXbr7C4ZcoPqh++Lte4opCNModIVh94eP0GaijRcyg17Ui8EJaayB6otq FQz9sW4fgXT0QA/Ls3fYuNrjps3fxLekTEulC8nxujl70F8EoXbkYrfG9RQhlEzNsL4S Zga/mITEat4ODGG0AXJxDMn4lLn7nAegy/BtjAzSpPwu151GvILePmvZJzALaKTKwzSt XUuV9lvDiw/GkuAAF+HS12ZhWyo7KBQe8D5vDAksNkJR+QpouL8ViKwU1QIuNpJEi9ce pHrTOTfgnNqixyius86OKWCF6NF3dTFjI6Eo7x5fsqT2zVgCRehKO+qnS6zI6l3tZhKA PZ/g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=LthcbkP7Wdjt8ldMPtUztfGpjhfprhWhUm4+jyfMfls=; b=CfA30NbuZERqVgNnOdfY4mgOR74iSODiQibgR/BRu1Mj/ghjwXY0f2E1cpsEd8Qpls 6WGXMMGWXuGV5TA53mB/oyFP/P3Hd7UagrkT7AAR+rWn3Km+0r0Pp9ADM4JTqvhjFCRI dynRAVzi+FYuQv7KPUyvPxn5SOnausjKqdvvCYA8CUohN2Vixcclt0rwQQer50KYlLy3 U201v2GRvm/9qHPzaCxEH/TRxe6Dsr4obDj909n5TnF5LzYvT6/7CEyToydYUw8AhZdU zw1dFVgqvlHxZi3CyzFE5xYj4op9uNI3f2KzYv23OszOCOI0leh5bD/ovuu1C3pcLsvk ot3Q== X-Gm-Message-State: AOAM530azmGa1SLHmSkwlUKInUwrOH/YHCMArZiAuxbyX21kh5BCs/ZZ rx0IVSIOLagu7UP7qhySDnhCs1zjq9gkjcOBURSVSQ== X-Google-Smtp-Source: ABdhPJxrXi16XBlYem920BU1TOkko4AlJ2LAveXaKf8sCKMYnk4fBnQsPHhHqeMjxyN73WUVJKMWDMZLwf3MnCSF1aM= X-Received: by 2002:a63:643:: with SMTP id 64mr2834544pgg.422.1608273952154; Thu, 17 Dec 2020 22:45:52 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: Keith Date: Fri, 18 Dec 2020 01:45:15 -0500 Message-ID: Subject: Re: tuning To: Ankush Chawla Cc: pgsql-admin Content-Type: multipart/alternative; boundary="0000000000009ebde205b6b7759e" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --0000000000009ebde205b6b7759e Content-Type: text/plain; charset="UTF-8" On Thu, Dec 17, 2020 at 10:55 PM Ankush Chawla wrote: > hi all > how to find the candidates tables or indexes for reindex or vacuum full > > > -- > Best Regards, > Ankush Chawla > > I've written a python script to help check for bloat which would indicate a possible need for a reindex/vacuum. https://github.com/keithf4/pg_bloat_check Keith --0000000000009ebde205b6b7759e Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable


=
On Thu, Dec 17, 2020 at 10:55 PM Anku= sh Chawla <ankushchawla03@gm= ail.com> wrote:
hi all
how to find the candidates tables or ind= exes for reindex or vacuum=C2=A0full


<= /div>--
Best Regards,
Ankush Chawla


I've written= a python script to help check for bloat which would indicate a possible ne= ed for a reindex/vacuum.

--0000000000009ebde205b6b7759e-- From bangalore.raj@gmail.com Fri Dec 18 07:12:04 2020 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 1kq9wA-0006ZU-4k for pgsql-admin@arkaria.postgresql.org; Fri, 18 Dec 2020 07:12:26 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1kq9w8-0001vK-OF for pgsql-admin@arkaria.postgresql.org; Fri, 18 Dec 2020 07:12:24 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1kq9w8-0001vB-Eb for pgsql-admin@lists.postgresql.org; Fri, 18 Dec 2020 07:12:24 +0000 Received: from mail-il1-x131.google.com ([2607:f8b0:4864:20::131]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1kq9w1-0002U7-DC for pgsql-admin@postgresql.org; Fri, 18 Dec 2020 07:12:23 +0000 Received: by mail-il1-x131.google.com with SMTP id n9so1295100ili.0 for ; Thu, 17 Dec 2020 23:12:16 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=d2a8U1qy4f4Ys8nyp4C/u00OHyB99OPWijF2JlubkuI=; b=T+xvbqLXJbOFJ8AXaEVuBpXtzNyUXAgdlgO7rcmiQ51m0bakolP4xPTh8JeIAxlzxP /43q2RTcSFu3G+KA/Z1BPL6b7X+t9G+Mml6RLLVhjXba9TnL9P0ObQjoenjxSkTA5EPc kf86uySXHfJqzZllvVLgmt0UJsV3+Pk70/0HYMCiYyDMKgpVuhh9UPICg02Dm2li4tdl QIRF6igtmZbvARV2p/+4DLsHjapaiQT61hXe09Hp90FCV9KUhGX88+EYCYtotexZ77RG oPY4Hy8hGBCYtx2sM55BUCvUK4rUFxgk6QBx65aNUpjc6kCbl7R8lkMdWQP3xwFEfbUU goGw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=d2a8U1qy4f4Ys8nyp4C/u00OHyB99OPWijF2JlubkuI=; b=JCpyWEvGemiv8tTU39NpAqeSBpU4epBD3jY0ezcuWnkccffC7oCzYjVLsN8/XCxa0i XhVBSAVWbskNQB7rfuXdfijOUwVQAG18vDfeUIVXoN1BwY3bK2KGjShQnU0255mYQVc8 ARzf5iwkvneXeFme6WzMtaNBScaEaZHqXaizN6oEF7T+dnneYBJXBwQYaMkb9v+P9MUw 4Lx73YNJvNDnUz1j8o9bIxQ5J+s9POz9YkZRP4mY4gqPM3IlXbCNxyjzM4KD7WC6jvWR MAUit9MOLau90t7jG9vkRGfRQ7nECgS4YLa7bivRbSpoGm2FcB7Qf+qiYZvuvyGWTJYZ 2ogw== X-Gm-Message-State: AOAM532Yu4oKN7HqbH3lXTF0ZL6PL2zh/cGe9XYoBTMO5z5ksNVubsJg nrrgw3B5zaey4gi7JmUcjK0wJba8Dg77Ej6os4s= X-Google-Smtp-Source: ABdhPJwKorFEXzYBWiYSCArrteEre6146eR3ATwKhwZZm20+b6s6TAMaIijqyvjSFag3ABoXDN/6kF7SQnPzlRGmXCY= X-Received: by 2002:a05:6e02:188f:: with SMTP id o15mr2497552ilu.302.1608275535322; Thu, 17 Dec 2020 23:12:15 -0800 (PST) MIME-Version: 1.0 References: In-Reply-To: From: bangalore umesh Date: Fri, 18 Dec 2020 12:42:04 +0530 Message-ID: Subject: Re: tuning To: Keith Cc: Ankush Chawla , pgsql-admin Content-Type: multipart/alternative; boundary="000000000000fbe44e05b6b7d3a8" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --000000000000fbe44e05b6b7d3a8 Content-Type: text/plain; charset="UTF-8" Hi Please share the document how to do migration for oracle to postgres using ora2pgtool. I am working on Migration Project. Thanks and Regards Umesh On Fri, Dec 18, 2020 at 12:16 PM Keith wrote: > > > On Thu, Dec 17, 2020 at 10:55 PM Ankush Chawla > wrote: > >> hi all >> how to find the candidates tables or indexes for reindex or vacuum full >> >> >> -- >> Best Regards, >> Ankush Chawla >> >> > I've written a python script to help check for bloat which would indicate > a possible need for a reindex/vacuum. > > https://github.com/keithf4/pg_bloat_check > > Keith > -- Thanks and Regards Rajshekhariah Umesh --000000000000fbe44e05b6b7d3a8 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Hi=C2=A0

Please share the document how = to do migration for oracle to postgres
using=C2=A0ora2pgtool.

I am working on Migration Project.

Thanks and Regards
Umesh

On Fri, Dec 18, 2020 at 12:1= 6 PM Keith <keith@keithf4.com&g= t; wrote:


On Thu, Dec 17, 2020 at 10:55 PM Ankush Cha= wla <ankus= hchawla03@gmail.com> wrote:
hi all
how to find the candidates t= ables or indexes for reindex or vacuum=C2=A0full


--
Best Regards,
Ankush Chawla


I= 9;ve written a python script to help check for bloat which would indicate a= possible need for a reindex/vacuum.


Keith


--
Thanks and Regards
Rajshekhariah Umesh
--000000000000fbe44e05b6b7d3a8--