Re: [ADMIN] db size and tables size difference
Isabella Ghiurea isabella.ghiu...@nrc-cnrc.gc.ca writes: SELECT nspname || '.' || relname AS relation,pg_size_pretty(pg_total_relation_size(nspname || '.' || relname)) AS s ize FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) ORDER BY pg_relation_size(nspname || '.' || relname) DESC LIMIT 20; I think maybe you'd better ORDER BY pg_total_relation_size instead. Also, maybe look further than 20 rows ... maybe the issue is many thousands of little tables? regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] db size and tables size difference
MB | 2150 MB
caom | plane | 413 MB | 677 MB
caom | positionsample | 110 MB | 219 MB
caom | simpleobservation | 165 MB | 202 MB
caom | temporalentity | 77 MB | 86 MB
caom | spectralentity | 68 MB | 73 MB
caom | metric | 33 MB | 70 MB
caom | polarizationentity | 29 MB | 33 MB
caom | harvestskip | 1576 kB | 2056 kB
caom | harveststate| 840 kB | 856 kB
caom | positionhole| 48 kB | 584 kB
*** OR : check for the biggest tables+index size:
SELECT ' Top 20 biggest tables and indexes'
;
SELECT nspname || '.' || relname AS relation,
pg_size_pretty(pg_relation_size(nspname || '.' || relname)) AS size
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND nspname !~ '^pg_toast'
AND pg_relation_size(nspname || '.' || relname)0
ORDER BY pg_relation_size(nspname || '.' || relname) DESC
LIMIT 20;
(1 row)
relation | size
---+-
caom.spatialentity| 3053 MB
caom.artifact | 1979 MB
caom.plane|413 MB
caom.artifact_i1 | 171 MB
caom.simpleobservation| 165 MB
caom.spatialentity_i1 | 162 MB
caom.positionsample | 110 MB
caom.plane_psi2 | 86 MB
caom.temporalentity | 77 MB
caom.spectralentity | 68 MB
caom.plane_energy_i1 | 67 MB
caom.plane_time_i1| 58 MB
caom.plane_position_i2| 48 MB
caom.metric | 33 MB
caom.polarizationentity | 29 MB
caom.simpleobservation_i2 | 25 MB
caom.plane_psi1 | 23 MB
caom.metric_i2| 18 MB
Thank you
Isabella
--
View this message in context:
http://www.nabble.com/Re%3A--ADMIN--db-size-and-tables-size-difference-tp25531211p25531211.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] db size and tables size difference
Isabella Ghiurea isabella.ghiu...@nrc-cnrc.gc.ca writes: SELECT nspname || '.' || relname AS relation,pg_size_pretty(pg_total_relation_size(nspname || '.' || relname)) AS s ize FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) ORDER BY pg_total_relation_size(nspname || '.' || relname) DESC LIMIT 1000; Hmph ... I can't see anything wrong with that query, so it seems that we're left with the conclusion that there are files in the database directory that don't match any entry in the catalogs. AFAIK this'd only be possible if you'd had a crash while deleting tables or some similar problem. What you'll need to do next is poke around in the data directory and see if you can identify any large files that do not correspond to any entry in pg_class.relfilenode. You should read the internals docs first, if you're not familiar with this chapter: http://www.postgresql.org/docs/8.3/static/storage.html regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] db size and tables size difference
(pg_database_size('db1'));
pg_size_pretty
12 GB
(1 row)
*** Check for tables size :
SELECT schemaname, tablename,
pg_size_pretty(size) AS size_pretty,
pg_size_pretty(total_size) AS total_size_pretty
FROM (SELECT *,
pg_relation_size(schemaname||'.'||tablename) AS
size,pg_total_relation_size(schemaname||'.'||tablename) AS
total_size FROM pg_tables) AS TABLES where schemaname='caom'
ORDER BY total_size DESC;
schemaname | tablename | size_pretty | total_size_pretty
+-+-+---
caom | spatialentity | 3053 MB | 3216 MB
caom | artifact| 1979 MB | 2150 MB
caom | plane | 413 MB | 677 MB
caom | positionsample | 110 MB | 219 MB
caom | simpleobservation | 165 MB | 202 MB
caom | temporalentity | 77 MB | 86 MB
caom | spectralentity | 68 MB | 73 MB
caom | metric | 33 MB | 70 MB
caom | polarizationentity | 29 MB | 33 MB
caom | harvestskip | 1576 kB | 2056 kB
caom | harveststate| 840 kB | 856 kB
caom | positionhole| 48 kB | 584 kB
*** OR : check for the biggest tables+index size:
SELECT ' Top 20 biggest tables and indexes'
;
SELECT nspname || '.' || relname AS relation,
pg_size_pretty(pg_relation_size(nspname || '.' || relname)) AS size
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog', 'information_schema')
AND nspname !~ '^pg_toast'
AND pg_relation_size(nspname || '.' || relname)0
ORDER BY pg_relation_size(nspname || '.' || relname) DESC
LIMIT 20;
(1 row)
relation | size
---+-
caom.spatialentity| 3053 MB
caom.artifact | 1979 MB
caom.plane|413 MB
caom.artifact_i1 | 171 MB
caom.simpleobservation| 165 MB
caom.spatialentity_i1 | 162 MB
caom.positionsample | 110 MB
caom.plane_psi2 | 86 MB
caom.temporalentity | 77 MB
caom.spectralentity | 68 MB
caom.plane_energy_i1 | 67 MB
caom.plane_time_i1| 58 MB
caom.plane_position_i2| 48 MB
caom.metric | 33 MB
caom.polarizationentity | 29 MB
caom.simpleobservation_i2 | 25 MB
caom.plane_psi1 | 23 MB
caom.metric_i2| 18 MB
Thank you
Isabella
--
View this message in context:
http://www.nabble.com/Re%3A--ADMIN--db-size-and-tables-size-difference-tp25531211p25531314.html
Sent from the PostgreSQL - admin mailing list archive at Nabble.com.
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] db size and tables size difference
Isabella Ghiurea isabella.ghiu...@nrc-cnrc.gc.ca writes:
The issue may be with pg_size_pretty() results, I don't have details
knowledge of this function.
I doubt it, that's a pretty simple function ... but if you don't trust
it, just remove the pg_size_pretty call and look directly at the output
of the size functions.
select pg_size_pretty(pg_database_size('db1'));
Just to double check here ... you're sure you're naming the correct
database in this call?
regards, tom lane
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] db size and tables size difference
Isabella Ghiurea isabella.ghiu...@nrc-cnrc.gc.ca writes: I'm trying to understand why there are GB's difference when checking for db size using pg_size_pretty() and querying for tables + indexes size. . You are not counting everything --- the total DB size is clearly 12GB, so the question is where are the other 5.5GB? Your first query shows that schema caom accounts for 6+GB, but the second one does not prove that schema caom contains all the big hogs. My guesses are: 1. Toast tables for tables that aren't in caom --- you used pg_relation_size not pg_total_relation_size, and excluded toast tables, so you are missing those. 2. pg_largeobject ... got any large objects? 3. Bloat in other system catalogs. 5GB of catalog bloat would be pretty awful, but maybe that's what it is. Try that last query without the namespace restrictions. regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] db size and tables size difference
Naomi Walker nwal...@eldocomp.com writes: How would one check for catalog bloat? Like I said: Try that last query without the namespace restrictions. Isabella excluded pg_catalog, so if that's where the problem is, that's why she didn't see it. But let's see the data before discussing how to fix it... regards, tom lane -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
Re: [ADMIN] db size and tables size difference
How would one check for catalog bloat? And, if bloated, would unloading and reloading the database fix it? Naomi 3. Bloat in other system catalogs. 5GB of catalog bloat would be pretty awful, but maybe that's what it is. Try that last query without the namespace restrictions. regards, tom lane -- Naomi Walker Chief Information Officer Eldorado Computing, Inc nwal...@eldocomp.com ---An EDS Company 602-604-3100 Hard things are put in our way, not to stop us, but to call out our courage and strength. -- Anonymous -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
