Hello, I think we need two types of profilers: SQL-based and resource-based. We have some SQL-based profilers like slow-query logs (log_min_duration_statement) and contrib/pg_stat_statements in 8.4. For resource-based profilers, we have DTrace probes[1] and continue to extend them[2], but unfortunately DTrace only works on Solaris and limited platforms. Also, it is not so easy for typical users to write profilers using DTrace without performance degradation.
[1] http://developer.postgresql.org/pgdocs/postgres/dynamic-trace.html [2] http://archives.postgresql.org/pgsql-hackers/2009-03/msg00226.php Therefore, I'd like to propose an profiler with sampling approach in 8.5. The attached patch is an experimental model of the profiler. Each backends reports its condtion in PgBackendStatus.st_condition and the stats collector process does polling them every seconds. This is an extension of the st_waiting field, which reports locking condition in pg_stat_activity. There are some advantages in portability and less overhead. Consideration is needed about how to coexist with DTrace. I added codes to push/pop conditions just on the same place as TRACE_POSTGRESQL_*_START/DONE(). So, we could merge the codes of DTrace and the profiler, or implement one of them with another. I would emphasize that an offical profler is required in this area because it enables users to share knowledge and documentaions; information-sharing would be difficult if they use home-made profilers. Comments welcome. ---- Here is a sample output of the profiler with pgbench on Windows: $ pgbench -i -s3 $ psql -c "SELECT pg_save_profiles()" $ pgbench -c4 -T60 -n transaction type: TPC-B (sort of) tps = 401.510694 $ psql -c "SELECT * FROM pg_diff_profiles" profid | profname | percent --------+--------------------+--------- 19 | XLog:Write | 23.04 <- means wal contension 46 | LWLock:WALWrite | 23.04 <- same as the above 32 | Lock:Transaction | 22.61 <- confliction on row locks 15 | Network:Recv | 7.83 21 | Data:Stat | 4.35 <- lseek() is slow on Windows 7 | CPU:Execute | 3.91 3 | CPU | 3.91 1 | Idle:InTransaction | 2.61 5 | CPU:Rewrite | 1.74 16 | Network:Send | 1.74 6 | CPU:Plan | 1.74 31 | Lock:Tuple | 1.74 4 | CPU:Parse | 0.87 11 | CPU:Commit | 0.87 (14 rows) Regards, --- ITAGAKI Takahiro NTT Open Source Software Center
profiler_0309.tar.gz
Description: Binary data
additional_script.sql
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
