On Thu, Sep 29, 2016 at 3:45 PM, Haribabu Kommi <kommi.harib...@gmail.com> wrote:
>
>
> On Thu, Sep 22, 2016 at 3:05 AM, Alvaro Herrera <alvhe...@2ndquadrant.com>
> wrote:
>
>> Peter Eisentraut wrote:
>>
>> > How about having the tag not be a column name but a row entry. So you'd
>> > do something like
>> >
>> > SELECT * FROM pg_stat_sql WHERE tag = 'ALTER VIEW';
>> >
>> > That way, we don't have to keep updating (and re-debating) this when new
>> > command types or subtypes are added. And queries written for future
>> > versions will not fail when run against old servers.
>>
>> Yeah, good idea.
>>
>
> Yes, Having it as a row entry is good.
>
>
>
>> Let's also discuss the interface from the stats collector. Currently we
>> have some 20 new SQL functions, all alike, each loading the whole data
>> and returning a single counter, and then the view invokes each function
>> separately. That doesn't seem great to me. How about having a single C
>> function that returns the whole thing as a SRF instead, and the view is
>> just a single function invocation -- something like pg_lock_status
>> filling pg_locks in one go.
>>
>> Another consideration is that the present patch lumps together all ALTER
>> cases in a single counter. This isn't great, but at the same time we
>> don't want to bloat the stat files by having hundreds of counters per
>> database, do we?
>
>
> Currently, The SQL stats is a fixed size counter to track the all the
> ALTER
> cases as single counter. So while sending the stats from the backend to
> stats collector at the end of the transaction, the cost is same, because of
> it's fixed size. This approach adds overhead to send and read the stats
> is minimal.
>
> With the following approach, I feel it is possible to support the counter
> at
> command tag level.
>
> Add a Global and local Hash to keep track of the counters by using the
> command tag as the key, this hash table increases dynamically whenever
> a new type of SQL command gets executed. The Local Hash data is passed
> to stats collector whenever the transaction gets committed.
>
> The problem I am thinking is that, Sending data from Hash and populating
> the Hash from stats file for all the command tags adds some overhead.
>
I tried changing the pg_stat_sql into row mode and ran the regress suite to
add different type of SQL commands to the view and ran the pgbench test
on my laptop to find out any performance impact with this patch.
HEAD PATCH
pgbench - select 828 816
Here I attached the pg_stat_sql patch to keep track of all SQL commands
based on the commandTag and their counts. I attached the result of this
view that is run on the database after "make installcheck" just for
reference.
Regards,
Hari Babu
Fujitsu Australia
tag | count ----------------------------------+------- ALTER TABLE | 505 DROP COLLATION | 2 ALTER POLICY | 13 CREATE TABLESPACE | 2 DROP POLICY | 12 CREATE TYPE | 78 CREATE MATERIALIZED VIEW | 24 ALTER INDEX | 8 CREATE TEXT SEARCH PARSER | 5 SELECT | 8509 REINDEX | 7 SET | 638 SHOW | 63 CREATE AGGREGATE | 70 DROP FUNCTION | 180 DROP OPERATOR | 12 DROP USER MAPPING | 23 COMMENT | 55 GRANT ROLE | 9 ALTER FOREIGN TABLE | 54 CREATE DOMAIN | 59 DROP TABLE | 481 DROP SERVER | 9 DROP DOMAIN | 38 INSERT | 2663 DROP INDEX | 56 ALTER SERVER | 17 CREATE VIEW | 260 CREATE TABLE | 1023 CREATE TEXT SEARCH CONFIGURATION | 11 ROLLBACK | 166 ALTER DATABASE | 5 DISCARD ALL | 1 CLOSE CURSOR ALL | 2 DROP EVENT TRIGGER | 11 SAVEPOINT | 62 ALTER OPERATOR CLASS | 9 DROP RULE | 26 DROP LANGUAGE | 3 CREATE OPERATOR FAMILY | 20 ALTER USER MAPPING | 12 DROP SCHEMA | 29 LISTEN | 2 DROP TEXT SEARCH PARSER | 2 CREATE TEXT SEARCH TEMPLATE | 5 DEALLOCATE | 7 CREATE FUNCTION | 512 REVOKE | 68 CREATE TABLE AS | 52 DROP MATERIALIZED VIEW | 4 ALTER DEFAULT PRIVILEGES | 13 CREATE CAST | 10 CREATE OPERATOR | 25 ALTER FOREIGN DATA WRAPPER | 11 REVOKE ROLE | 2 ALTER TYPE | 62 CREATE TRIGGER | 121 CREATE ACCESS METHOD | 1 COMMIT | 138 CREATE DATABASE | 1 ANALYZE | 37 ALTER FUNCTION | 27 CREATE INDEX | 224 DROP TABLESPACE | 2 DELETE | 195 DROP ACCESS METHOD | 2 DROP TRIGGER | 29 DROP CONVERSION | 5 DROP TEXT SEARCH TEMPLATE | 2 ALTER OPERATOR | 9 ALTER RULE | 1 ALTER EVENT TRIGGER | 6 DROP TEXT SEARCH DICTIONARY | 3 CREATE CONVERSION | 8 DROP OPERATOR CLASS | 2 BEGIN | 267 CREATE RULE | 90 CREATE USER MAPPING | 31 COPY | 151 ALTER TEXT SEARCH TEMPLATE | 3 UPDATE | 318 MOVE | 3 DROP CAST | 8 DROP EXTENSION | 1 DEALLOCATE ALL | 1 ALTER AGGREGATE | 11 CLOSE CURSOR | 35 ALTER DOMAIN | 22 EXPLAIN | 462 ALTER VIEW | 11 DROP DATABASE | 1 DISCARD SEQUENCES | 2 NOTIFY | 1 DO | 13 DROP OPERATOR FAMILY | 12 UNLISTEN | 2 DROP SEQUENCE | 17 CREATE TEXT SEARCH DICTIONARY | 13 SET CONSTRAINTS | 7 CREATE TRANSFORM | 1 DROP AGGREGATE | 7 ALTER OPERATOR FAMILY | 25 CREATE OPERATOR CLASS | 6 LOCK TABLE | 28 CREATE FOREIGN TABLE | 6 TRUNCATE TABLE | 35 ALTER CONVERSION | 5 CREATE SCHEMA | 46 LOAD | 1 FETCH | 147 REFRESH MATERIALIZED VIEW | 17 CREATE EVENT TRIGGER | 10 EXECUTE | 118 DROP ROLE | 101 ALTER TEXT SEARCH DICTIONARY | 5 DROP TYPE | 29 ALTER TABLESPACE | 4 CLUSTER | 10 DECLARE CURSOR | 83 CREATE POLICY | 71 CREATE FOREIGN DATA WRAPPER | 13 ALTER SEQUENCE | 6 DROP OWNED | 12 DROP TEXT SEARCH CONFIGURATION | 3 CREATE SERVER | 28 REASSIGN OWNED | 2 CREATE SEQUENCE | 37 DROP FOREIGN TABLE | 5 RELEASE | 14 START TRANSACTION | 1 ALTER SCHEMA | 2 RESET | 184 ALTER MATERIALIZED VIEW | 1 ALTER LANGUAGE | 4 ALTER ROLE | 60 CREATE LANGUAGE | 2 VACUUM | 35 CREATE ROLE | 94 GRANT | 149 ALTER TEXT SEARCH CONFIGURATION | 11 PREPARE TRANSACTION | 8 DROP FOREIGN DATA WRAPPER | 13 PREPARE | 33 DISCARD TEMP | 1 ALTER TEXT SEARCH PARSER | 3 DROP VIEW | 71 (146 rows)
pg_stat_sql_row_mode_1.patch
Description: Binary data
-- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
