Archive for category Scripts
User Defined Type Definition
Posted by Vikram Rai in Scripts on February 1, 2013
I get to do this all the time and I thought it would be a good addition to this blog. My case scenario here is that every time there is a db release going out that modifies a user defined type in my database I get nervous because I need to have a quick rollback plan if the release is called for a rollback. There may be a better way to achieve this feel free to share. It can come in handy if you really need to know what CREATE TYPE syntax is being used.
We do know that just using \dT+ might give some information but it does not give the create statement / complete definition of the type:
\dT+ accounts.billing_info List of data types Schema | Name | Internal name | Size | Elements | Description -----------+------------------------------+--------------------+-------+----------+------------- accounts | accounts.billing_info | billing_info | tuple | | (1 row)
To get around this I use the following query that gives me the complete definition of the user defined type. Replace schema and table name in the below to match your requirement.
SELECT 'CREATE TYPE accounts.billing_info as ('||array_to_string(ARRAY(select a.attname||' '|| pg_catalog.format_type(a.atttypid, a.atttypmod) FROM pg_catalog.pg_attribute a WHERE a.attrelid = (select oid from pg_class where relname='billing_info' and relnamespace=(select oid from pg_namespace where nspname='accounts')) AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum),',')||');'; ?column? -------------------------------------------------------------------------------------------------------------------------------------------------------------------- CREATE TYPE accounts.billing_info as (billme character varying(20),serial integer,bill_name character varying(100)); (1 row)
And here I have my complete type definition and can use it as required. Hope this helps!!!
Bloats
Posted by Vikram Rai in Scripts on May 29, 2012
Bloats can cause some serious performance impact on queries and you will see server load rise from what it was at an earlier point in time. The reason for bloats in a table/index could be due to various reasons. Updates rollbacks failed transactions etc. What is important here to identify these bloats and remove them before things get too bad.
I have simplified the process of identifying these bloats using the below script. The result set what you will see is:
relation | reltuples | relpages | otta | bloat | wastedpages | wastedbytes | pwastedbytes | relbytes | prelbytes ---------------------------------+------------+------------+------+-------+------------------+-----------------+-------------------+------------+------------------------------------- account.activation_return_policy | 973111 | 164186 | 5240 | 31.3 | 158946 | 1302085632 | 1242 MB | 1345011712 | 1283 MB billing.search_bill_info | 688832 | 6754 | 3372 | 2.0 | 3382 | 27705344 | 26 MB | 55328768 | 53 MB (2 rows)
Here you see that there are two tables that have 31.3% and 2.0% bloats respectively. It is a good idea to run pgreorg or cluster command depending on the database availability requirement to remove these bloats.
NOTE: CLUSTER command will lock the table for the time it removes the bloat and is slower than pgreorg. Make a practical choice here by looking at the options you have. If you need more information on pgreorg , look though my post about pgreorg.
The sql that I have used to get this information is given below. It is a good idea to put the sql in a crontab and probably send out the result set via email so that you are aware of the bloated tables and can work on reindexing /clustering or using pgreorg at a later time.
SELECT schemaname||'.'||tablename as relation, reltuples::bigint, relpages::bigint, otta, ROUND(CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS bloat, CASE WHEN relpages < otta THEN 0 ELSE relpages::bigint - otta END AS wastedpages, CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes, pg_size_pretty((CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END)::bigint) AS pwastedbytes, bs*relpages::bigint as relbytes, pg_size_pretty((bs*relpages::bigint)::bigint) as prelbytes FROM ( SELECT schemaname, tablename, cc.reltuples, cc.relpages, bs, CEIL((cc.reltuples*((datahdr+ma- (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)) AS otta FROM ( SELECT ma,bs,schemaname,tablename, (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr, (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2 FROM ( SELECT schemaname, tablename, hdr, ma, bs, SUM((1-null_frac)*avg_width) AS datawidth, MAX(null_frac) AS maxfracsum, hdr+( SELECT 1+count(*)/8 FROM pg_stats s2 WHERE null_frac<>0 AND s2.schemaname = s.schemaname AND s2.tablename = s.tablename ) AS nullhdr FROM pg_stats s, ( SELECT (SELECT current_setting('block_size')::numeric) AS bs, CASE WHEN substring(v,12,3) IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr, CASE WHEN v ~ 'mingw32' THEN 8 ELSE 4 END AS ma FROM (SELECT version() AS v) AS foo ) AS constants GROUP BY 1,2,3,4,5 ) AS foo ) AS rs JOIN pg_class cc ON cc.relname = rs.tablename and cc.relkind = 'r' JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname = rs.schemaname AND nn.nspname <> 'information_schema' ) AS sml WHERE sml.relpages - otta > 128 AND ROUND(CASE WHEN otta=0 THEN 0.0 ELSE sml.relpages/otta::numeric END,1) > 1.2 AND CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END > 1024 * 100 ORDER BY wastedbytes DESC;
Long running queries
Posted by Vikram Rai in Scripts on May 24, 2012
Ever wondered if you did not get a call in the middle of the night just for some stupid stuck query that was not supposed to run that long and blocking all the other queries making the database load rise up till its not even manageable?
I did and finally got something to address it. Below is a script that logs all long running queries and kills them with pg_cancel_backend. Finally sends out an email alert, you can modify it according to your production requirement and increase the time of the query that should be killed. Just put it in a cronjob “AFTER TESTING IT PROPERLY”. I am not responsible for any data loss here.
Make sure you have postfix installed and configured for your mail to work.
# The script must run as postgres, since only postgres user has control over every database. if [ `whoami` != "postgres" ]; then exit 0; fi # Selecting relevant columns, getting non-idle queries only where the query runs since at least 6 minutes. psql -c "select procpid, client_addr, query_start, current_query from pg_stat_activity where current_query != '<IDLE>' and current_query != 'COPY' and current_query != 'VACUUM' and query_start + '6 min'::interval < now() and substring(current_query, 1, 11) != 'autovacuum:' order by query_start desc" > $LOGFILE NUMBER_OF_STUCK_QUERIES=`cat $LOGFILE | grep "([0-9]* row[s]*)" | sed 's/(//' | awk '{ print $1}'` if [ $NUMBER_OF_STUCK_QUERIES != 0 ]; then # Getting the first column from the output discarding alfanumeric values (table elements in psql's output). STUCK_PIDS=`cat $LOGFILE | sed "s/([0-9]* row[s]*)//" | awk '{ print $1 }' | sed "s/[^0-9]//g"` for PID in $STUCK_PIDS; do echo -n "Cancelling PID $PID ... " >> $LOGFILE # "t" means the query is successfully cancelled. SUCCESS=`psql -c "SELECT pg_cancel_backend($PID);" | grep " t"` if [ $SUCCESS ]; then SUCCESS="OK."; else SUCCESS="Failed."; fi echo $SUCCESS >> $LOGFILE done cat $LOGFILE | mail -s "Stuck PLpgSQL processes detected and killed that were running over 6 minutes." youremail@whatever.com; fi rm $LOGFILE