Tag Archives: block

INFO: task blocked for more than 120 seconds

This is a know bug. By default Linux uses up to 40% of the available memory for file system caching. After this mark has been reached the file system flushes all outstanding data to disk causing all following IOs going synchronous. For flushing out this data to disk this there is a time limit of 120 seconds by default. In the case here the IO subsystem is not fast enough to flush the data withing 120 seconds. This especially happens on systems with a lof of memory.

The problem is solved in later kernels and there is not “fix” from Oracle. I fixed this by lowering the mark for flushing the cache from 40% to 10% by setting “vm.dirty_ratio=10″ in /etc/sysctl.conf. This setting does not influence overall database performance since you hopefully use Direct IO and bypass the file system cache completely.

echo «vm.dirty_ratio=10» >> /etc/sysctl.conf
sysctl -p

proof link: http://blog.ronnyegner-consulting.de/2011/10/13/info-task-blocked-for-more-than-120-seconds/

PostgreSQL: useful commands

show count of active connections

select datname, count(*) from pg_stat_activity group by datname;

show size of db

SELECT pg_size_pretty(pg_database_size('DATABASE_NAME'));

show size of all dbs

SELECT pg_size_pretty(sum(pg_database_size(datname))) from pg_database;

show table size

SELECT relname, pg_total_relation_size(oid) / (1024 * 1024) as sizeM
from pg_class
order by 2 desc
limit 100

show tables size, also it calculates sizes of segmented tables and doesn't show segments in result.

select s.schemaname, s.relname,pg_size_pretty(sm) from (select oid, sum(sm) as sm from (select oid,pg_total_relation_size(oid) as sm from pg_class where relkind='r' and oid not in (select inhrelid from pg_inherits) union (select c.oid,sum(pg_total_relation_size(i.inhrelid)) as sm from pg_class c, pg_inherits i where c.oid=i.inhparent group by c.oid)) t group by oid) tt, pg_stat_user_tables s where s.relid=tt.oid order by sm desc;

show current requests and they duration time

SELECT datname, NOW() - query_start AS duration, procpid, current_query FROM pg_stat_activity ORDER BY duration DESC;

show list of blocks

SELECT l.mode, d.datname, c.relname, l.granted, l.transactionid FROM pg_locks AS l
LEFT JOIN pg_database AS d ON l.database= d.oid
LEFT JOIN pg_class AS c ON l.relation = c.oid;