1 / 28

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?

ellery
Download Presentation

Database Trending

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Trending Timothy J Bruce For PDXPug 19 Jan 2012

  2. Why the big deal? • New job • No metrics • Use performance tools

  3. What type of Trending What is management interested in? What am I (the DBA) interested in? Where to these things intersect?

  4. What type of Trending Not Current System / Database Performance Historical System Information

  5. OpenNMS Nagios Zabbix Zenoss Performance Tools Ganglia Hyperic Monit Munin

  6. What's Important How big is my database? How big is my database growing?

  7. 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....

  8. 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;'

  9. 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()

  10. 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

  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).

  12. 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_size_stats.database_size FROM database_size_stats WHERE database_size_stats.date_collected >= now()::date ORDER BY database_size_stats.database_name;

  13. Database Sizing.... My database is growing by around 6 Gb a day. Really? What's causing it to grow?

  14. Table Size So what's my biggest table? And how do I find out which one?

  15. 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 it was analyzed by the autovacuum daemon.

  16. 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()

  17. 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;

  18. 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.

  19. 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

  20. rec_ins | bigint rec_upd | bigint rec_del | bigint rec_seq_scan | bigint rec_seq_read | bigint rec_idx_scan | bigint rec_idx_read | bigint rec_total | bigint table_stats id | integer reloid | oid schemaname | name relname | name last_vacuum | timestamp last_autovacuum | timestamp last_analyze | timestamp last_autoanalyze | timestamp updated | timestamp

  21. Record Count • select reltuples from pg_class

  22. 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, u.last_analyze, u.last_autoanalyze, u.n_tup_ins, u.n_tup_del, u.n_tup_upd, u.seq_scan, u.seq_tup_read, u.idx_scan, u.idx_tup_fetch, ( select reltuples from pg_class c where c.relname = u.relname and c.relnamespace = ( SELECT n.oid FROM pg_namespace n WHERE n.nspname = u.schemaname ) ) as "recs" from pg_stat_user_tables u;

  23. 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).

  24. 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 fi }

  25. 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

  26. Next Steps pg_bloat? (But there are issues with pg_bloat) index sizes and growth?

  27. References PostgreSQL Documentation - http://www.postgresql.org/docs/current/static/monitoring-stats.html#MONITORING-STATS-VIEWS Reference Email: From: Ondrej Ivanič Date: Wed, December 28, 2011 17:38 To: pgsql-general@postgresql.org

  28. Questions Your Turn....

More Related