Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gVJGh-00023S-FP for pgsql-admin@arkaria.postgresql.org; Fri, 07 Dec 2018 16:46:23 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1gVJGf-0005ix-MI for pgsql-admin@arkaria.postgresql.org; Fri, 07 Dec 2018 16:46:21 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gVJGf-0005iq-9E for pgsql-admin@lists.postgresql.org; Fri, 07 Dec 2018 16:46:21 +0000 Received: from mail-oi1-x241.google.com ([2607:f8b0:4864:20::241]) by makus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1gVJGc-00053V-D6 for pgsql-admin@lists.postgresql.org; Fri, 07 Dec 2018 16:46:20 +0000 Received: by mail-oi1-x241.google.com with SMTP id h25so3892200oig.1 for ; Fri, 07 Dec 2018 08:46:18 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-language; bh=1UKfcBMToZa4UmyOUbaXi0MC75DKsAv6889NiVjF7TI=; b=UB1jYblaj58ZRkGlRPE1MdCeoo6jrzebYQKniqxdvlI/Yg7uuzWvJ39xSyfQW1X/Zq ZGkX/Sze1vaRZG2Q6fjYxGPjwbsMX9o5Dz2qnnM67ZCh2t8zcG1/4qANk3RiKUHCxIyl 2aGrgsol3+v6C8+YuY8grTMQfkyuLus5Tm+vRIv0HezBdEaMoxGipRlKXVb1Epu/O4Nf dSCmQrYSBQu9doaJ0TD86XrMIyyGoKubLEvqL1tvnfVtje+ykRyj2LALEjAANLYZ9HV4 FEFwBYAvdrz1f/aqHJYztRyJSPmqePDC7zadVj+8gH1UPYYJpZgL9BM1ktLUgsNAQSvc Ugbg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-language; bh=1UKfcBMToZa4UmyOUbaXi0MC75DKsAv6889NiVjF7TI=; b=hFqJVnFov/8wZBfxDBGHnFdic0n2aC0isgl1WBmCztG65bS8CfDOJQKIZdgChzB7XX upmKlu7XTUgE0ux/hvslrMZetpsEN9+xJKpbq4dJnKhNGu2/GXvJZywzIbw1hTZyQlJq jhZkD11aQOxaqW0dMpAU1FlYi9wnkSCsmoTuE6zOATamgdlKdjh5Kz6q6ccZGAatTjDY T6AQXZbchVZAoJh2PMD/Or6yce5zyXv2io9lR2nOgAPSvu8LSbw1+p1SDe2Q0qWKKuNQ cELzXb61+QwtXqp4WyKu2kyhbcZIiDEuknoj6H4EGcv79MGvrUJkGqaVRQvRq+34aZ2g yutA== X-Gm-Message-State: AA+aEWY7UJJ9GWzoqYrA5k5nIlsio0trqZBlMq/F0MnvxKGDngFDLmWM XCo2F8yrAda4Z6igz4frWCAZQ7ra X-Google-Smtp-Source: AFSGD/XQK1/WzHwp/LD2MTfLzMaZnEIAsE6NtaZcsIYHCq9lwAT9nE96znSJhtiGUnBBvxxSYKfaVA== X-Received: by 2002:aca:2ecf:: with SMTP id u198mr1749375oiu.338.1544201176738; Fri, 07 Dec 2018 08:46:16 -0800 (PST) Received: from [192.168.1.10] (ip70-171-116-89.no.no.cox.net. [70.171.116.89]) by smtp.googlemail.com with ESMTPSA id s186sm1740242oif.0.2018.12.07.08.46.15 for (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Fri, 07 Dec 2018 08:46:15 -0800 (PST) Subject: Re: Database stats To: pgsql-admin@lists.postgresql.org References: <1B1BD261-2B29-4B3A-84CA-4C57C125B712@illinois.edu> From: Ron Message-ID: <998701ae-f141-66c0-cda9-f7f20306309d@gmail.com> Date: Fri, 7 Dec 2018 10:46:15 -0600 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Thunderbird/52.9.1 MIME-Version: 1.0 In-Reply-To: <1B1BD261-2B29-4B3A-84CA-4C57C125B712@illinois.edu> Content-Type: multipart/alternative; boundary="------------109927D937466FB3E2285B44" Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk This is a multi-part message in MIME format. --------------109927D937466FB3E2285B44 Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit On 12/07/2018 10:38 AM, Campbell, Lance wrote: > > PostgreSQL 10.X > > Is there a query we can run to answer the following questions: > > 1. How many SQL commands have been executed in the last 24 hours? > 2. How many insert SQL commands have been executed in the last 24 hours? > 3. How many update SQL commands have been executed in the last 24 hours? > > OR > > Instead of 2 or 3 maybe how many row updates have occurred in the last 24 > hours? > > OR > > Something that gives me an idea of rows reads and written.  Or rows read, > written and created in 24 hours. > Look in pg_stat_all_tables.  It keeps a running count since the cluster was started, but if you capture a snapshot every X hours then you should be able to approximate what you want. https://www.postgresql.org/docs/9.6/monitoring-stats.html#PG-STAT-ALL-TABLES-VIEW > There might be other ways to get to general levels of usage base on a date > or date range. > > Thanks, > > Lance > -- Angular momentum makes the world go 'round. --------------109927D937466FB3E2285B44 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: 8bit On 12/07/2018 10:38 AM, Campbell, Lance wrote:

PostgreSQL 10.X

Is there a query we can run to answer the following questions:

  1. How many SQL commands have been executed in the last 24 hours?
  2. How many insert SQL commands have been executed in the last 24 hours?
  3. How many update SQL commands have been executed in the last 24 hours?

 

OR

Instead of 2 or 3 maybe how many row updates have occurred in the last 24 hours?

 

OR

Something that gives me an idea of rows reads and written.  Or rows read, written and created in 24 hours.


Look in pg_stat_all_tables.  It keeps a running count since the cluster was started, but if you capture a snapshot every X hours then you should be able to approximate what you want.

https://www.postgresql.org/docs/9.6/monitoring-stats.html#PG-STAT-ALL-TABLES-VIEW

 

There might be other ways to get to general levels of usage base on a date or date range.

 

Thanks,

 

Lance


--
Angular momentum makes the world go 'round.
--------------109927D937466FB3E2285B44--