Database trending
Download
1 / 28

Database Trending - PowerPoint PPT Presentation


  • 176 Views
  • Uploaded on

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?

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Database Trending' - ellery


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.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Database trending
Database Trending

Timothy J Bruce

For PDXPug 19 Jan 2012


Why the big deal
Why the big deal?

  • New job

  • No metrics

  • Use performance tools


What type of trending
What type of Trending

What is management interested in?

What am I (the DBA) interested in?

Where to these things intersect?


What type of trending1
What type of Trending

Not Current System / Database Performance

Historical System Information


Performance tools

OpenNMS

Nagios

Zabbix

Zenoss

Performance Tools

Ganglia

Hyperic

Monit

Munin


What s important
What's Important

How big is my database?

How big is my database growing?


Pg stat database
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
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
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
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
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
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;


Database sizing
Database Sizing....

My database is growing by around 6 Gb a day.

Really?

What's causing it to grow?


Table size
Table Size

So what's my biggest table?

And how do I find out which one?


Pg stat user tables
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.


Table stats
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 size1
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
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
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 stats1

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


Record count
Record Count

  • select reltuples from pg_class


Real table data
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;


Schedule the jobs
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
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

}


Table analysis cont d
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
Next Steps

pg_bloat? (But there are issues with pg_bloat)

index sizes and growth?


References
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: [email protected]


Questions
Questions

Your Turn....


ad