Transcript Database Trending

Database Trending
Timothy J Bruce
For PDXPug
19 Jan 2012
Why the big deal?

New job

No metrics

Use performance tools
What type of Trending
What is management interested in?
What am I (the DBA) interested in?
Where to these things intersect?
What type of Trending
Not Current System / Database Performance
Historical System Information
Performance Tools
Ganglia
OpenNMS
Hyperic
Nagios
Monit
Zabbix
Munin
Zenoss
What's Important
How big is my database?
How big is my database growing?
pg_stat_database
One row per database, showing database OID,
database name, number of active server
processes connected to that database, number
of transactions committed and rolled back in
that database, total disk blocks read, and total
buffer hits (i.e., block read requests avoided by
finding the block already in buffer cache).
So I can get the OID. And use the
pg_database_size (OID) function....
Database Size
So I can use this SQL command to get the size:
SELECT datname,
cast(sum(pg_database_size(datid))/1024 as
bigint ) FROM pg_stat_database GROUP BY
datname;'
database_size_stats
Column
|
Type
|
Modifiers
----------------+--------------------------+-----------------------------server_name
| name
| default 'dbserver01'::name
database_name | name
database_size | bigint
|
|
date_collected | timestamp with time zone | default now()
Size Results
server_name | database_name | database_size | collected
--------------+---------------+---------------+-----------dbserver01 | proddb
| 2,752,157,754 | 2012-01-18
dbserver01 | proddb
| 2,746,933,858 | 2012-01-17
dbserver01 | proddb
| 2,742,249,994 | 2012-01-16
dbserver01 | proddb
| 2,736,916,578 | 2012-01-15
dbserver01 | proddb
| 2,732,626,274 | 2012-01-14
dbserver01 | proddb
| 2,714,683,898 | 2012-01-13
dbserver01 | proddb
| 2,696,561,922 | 2012-01-12
dbserver01 | proddb
| 2,691,771,402 | 2012-01-11
Size Process



Create two views

One for ”today's size”

One for ”Yesterday's size”
Why? I'm interested in what it looked like
yesterday compared to today....
And if they're views, I can ”subtract” yesterday
from today and display the total (difference).
Today's Database Size
Column
| Type | Modifiers
---------------+--------+----------server_name | name |
database_name | name |
database_size | bigint |
View definition:
SELECT database_size_stats.server_name,
database_size_stats.database_name,
Database Sizing....
My database is growing by around 6 Gb a day.
Really?
What's causing it to grow?
Table Size
So what's my biggest table?
And how do I find out which one?
pg_stat_user_tables
For each table in the current database (including
TOAST tables), the table OID, schema and
table name, number of sequential scans
initiated, number of live rows fetched by
sequential scans, number of index scans
initiated (over all indexes belonging to the
table), number of live rows fetched by index
scans, numbers of row insertions, updates,
and deletions, the last time the table was
vacuumed manually, the last time it was
vacuumed by the autovacuum daemon, the last
time it was analyzed manually, and the last time
table_stats
Column
| Type
|
Modifiers
------------------+----------+--------------------id
| integer | not null default
nextval('analyze_stats_id_seq'::regclass)
reloid
| oid
|
schemaname
| name |
relname
| name |
rec_ins
| bigint |
rec_upd
| bigint |
rec_del
| bigint |
updated
| timestamp| default now()
Table Size
So I can use this SQL command to get the size:
insert into table_stats ( reloid, schemaname,
relname, rec_ins, rec_del, rec_upd )
select u.relid, u.schemaname, u.relname,
u.n_tup_ins, u.n_tup_del, u.n_tup_upd, from
pg_stat_user_tables u;
Table Size Process



Again - Create two views

One for ”today's size”

One for ”Yesterday's size”
Why? Frequently I'm looking at today's size
AND I'm interested in what it looked like
yesterday compared to today....
And since they're views, I can ”subtract”
yesterday from today and display the total
(difference) and email it out.
Table Size Process Side-Effect
I can collect the number of records read as well.

number of sequential scans initiated

number of live rows fetched by sequential scans


number of index scans initiated (over all
indexes belonging to the table)
number of live rows fetched by index scans
table_stats
id
reloid
| integer
rec_ins
| oid
rec_upd
schemaname
name
relname
|
| name
last_vacuum
timestamp
|
last_autovacuum |
timestamp
last_analyze
|
rec_del
| bigint
| bigint
| bigint
rec_seq_scan
bigint
|
rec_seq_read
bigint
|
rec_idx_scan
| bigint
rec_idx_read
| bigint
Record Count

select reltuples from pg_class
Real Table Data
So I can use this SQL command to get the size:
insert into table_stats ( reloid, schemaname,
relname, last_vacuum, last_autovacuum,
last_analyze, last_autoanalyze, rec_ins,
rec_del, rec_upd, rec_seq_scan, rec_seq_read,
rec_idx_scan, rec_idx_read, rec_total )
select u.relid, u.schemaname, u.relname,
u.last_vacuum, u.last_autovacuum,
Schedule the Jobs


An account that has access to read all the
information (public schema) and insert the data
into my private schema.
Job is scheduled for early in the morning – just
after midnight.


A view called ”Today” looks at the data as of today's
morning run (yesterday's data).
A view called ”Yesterday” looks at the data from
yesterday's morning run (the day before's data).
Table Analysis
CMD="SELECT * FROM dba.v_table_info_change "
function SQL {
COLUMN=$1
CMD1="$CMD "
CMD2=" WHERE $COLUMN IS NOT NULL ORDER BY $COLUMN DESC LIMIT 10;
"
SQLCMD="$CMD1 $CMD2"
echo "$SQLCMD" >> $TMP_LOG_FILE
$PSQL -c "$SQLCMD" pfprod >> $RESULTS 2>&1
If [ "$?” != "0" ]; then
echo "Table Stats Analysis failed for $COLUMN " >> $TMP_LOG_FILE
Table Analysis cont'd
echo `/usr/ucb/hostname` " :: Table Stats started at `date`" >> $TMP_LOG_FILE
date > $RESULTS
SQL ins
SQL del
SQL upd
SQL seq_read
SQL idx_read
SQL total
echo "Database Size Analysis completed at `date`" >> $TMP_LOG_FILE
mailx -s "Table Stats Analysis" $MAIL_USERS < $RESULTS
Next Steps
pg_bloat? (But there are issues with pg_bloat)
index sizes and growth?
References
PostgreSQL Documentation http://www.postgresql.org/docs/current/static/mo
nitoring-stats.html#MONITORING-STATSVIEWS
Reference Email:
From:
Ondrej Ivanič
Date:
Wed, December 28, 2011 17:38
To:
[email protected]
Questions
Your Turn....