Обсуждение: Where to get column length
I know it is in the docs, but right now I can't see it.
How do I pull the length of a specific varchar column? I have just found a problem with our schema being out of sync on one specific column, and need to find out how many of our 200+ databases need to be fixed.
Here is what I have so far:
select *
from pg_class, pg_attribute
where pg_class.relkind = 'r' and
pg_attribute.attrelid = pg_class.oid and
pg_class.relname = 'mytable' and
pg_attribute.attname = 'problem_column' and
???? != 256; -- need to report anytime this column is not a varchar(256).
Thanks,
Chris
How do I pull the length of a specific varchar column? I have just found a problem with our schema being out of sync on one specific column, and need to find out how many of our 200+ databases need to be fixed.
Here is what I have so far:
select *
from pg_class, pg_attribute
where pg_class.relkind = 'r' and
pg_attribute.attrelid = pg_class.oid and
pg_class.relname = 'mytable' and
pg_attribute.attname = 'problem_column' and
???? != 256; -- need to report anytime this column is not a varchar(256).
Thanks,
Chris
Chris Hoover wrote: > I know it is in the docs, but right now I can't see it. > > How do I pull the length of a specific varchar column? I have just found a > problem with our schema being out of sync on one specific column, and need > to find out how many of our 200+ databases need to be fixed. > > Here is what I have so far: > select * > from pg_class, pg_attribute > where pg_class.relkind = 'r' and > pg_attribute.attrelid = pg_class.oid and > pg_class.relname = 'mytable' and > pg_attribute.attname = 'problem_column' and > ???? != 256; -- need to report anytime this column is not a varchar(256). pg_attribute.atttypmod <> 256 + 4 (your 256 plus fixed 4 bytes of overhead for all variable length attributes). -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
I am using Postgresql 8.1.4 on Windows 2003; platform is Dell Precision 330, 1.8 Ghz CPU, 1 gByte of RAM. This database is subject to 'vacuum full analyze' once / day. I am watching a recently created database grow; as it grows, I am finding that some of the performance statistics appear to be falling out of bed. The functions I have used to capture this information are the standard functions that come with PostgreSQL: date commits rollbks dsk_reads mem_reads pct_mem_hits db_size 2006-06-19 94115102 64 553053905 13126498559 95.9600 "1674 MB" (server restarted) 2006-06-20 4383600 26 24129603 1162150532 97.9700 "1471 MB" 2006-06-21 9179799 27 56084142 2456950412 97.7700 "1587 MB" 2006-06-22 14447111 28 89452397 3875993962 97.7400 "1710 MB" 2006-06-23 20233946 47 128309666 5553425498 97.7400 "1858 MB" 2006-06-26 34252036 4765 237496776 9024547005 97.4400 "2218 MB" 2006-06-27 40290065 403368 273062334 9156477077 97.1000 "2401 MB" 2006-06-28 46436389 870211 307983449 9260208418 96.7800 "2497 MB" 2006-06-29 52251198 1352370 348552701 9367093206 96.4100 "2575 MB" 2006-06-30 58585373 1839034 392822069 9477661079 96.0200 "2647 MB" 2006-07-03 75747589 3318388 551767504 9816311112 94.6800 "2815 MB" 2006-07-05 87768328 4524047 678763032 10076029919 93.6900 "2973 MB" (server restarted - shared_buffers changed) 2006-07-06 4841006 326389 50641814 121507743 70.5800 "3031 MB" 2006-07-07 10711794 732981 113816094 274683161 70.7000 "3076 MB" 2006-07-10 19428743 1300797 287848710 517770353 64.2700 "3452 MB" My questions concerning these statistics: (1) This application monitors networks. On 6/26, it began monitoring a number of new network nodes (traffic increase of 40 - 50%); but the application itself has not been changed, either in terms of number of users, program or database changes, or other changes. Yet, the rollback column has increased substantially. QUESTION: is there a database setting that can affect this statistic? (2) I noticed that pct_mem_hits was dropping in early July at about the same time that the platform started monitoring additional network nodes. On late July 5th, increased shared_buffers from default value of 1000 to 1500, but pct_mem_hits continue to decline. QUESTION: I evidently touched the wrong thing. What should I be adjusting to help keep the database in memory? All other statistics appear to be normal.
Check the stats at the end of your vacuum to ensure your max_fsm_pages (free space map) is large enough. Also check work_mem and maintenance_work_mem are not running at defaults that may be too small. If you have many updates, increase the number of wal_buffers and checkpoint_segments.
On 7/11/06, Lane Van Ingen <lvaningen@esncc.com> wrote:
I am using Postgresql 8.1.4 on Windows 2003; platform is Dell Precision 330,
1.8 Ghz
CPU, 1 gByte of RAM. This database is subject to 'vacuum full analyze' once
/ day.
I am watching a recently created database grow; as it grows, I am finding
that some of the performance statistics appear to be falling out of bed. The
functions I have used
to capture this information are the standard functions that come with
PostgreSQL:
date commits rollbks dsk_reads mem_reads pct_mem_hits
db_size
2006-06-19 94115102 64 553053905 13126498559 95.9600 "1674
MB"
(server restarted)
2006-06-20 4383600 26 24129603 1162150532 97.9700 "1471
MB"
2006-06-21 9179799 27 56084142 2456950412 97.7700 "1587
MB"
2006-06-22 14447111 28 89452397 3875993962 97.7400 "1710
MB"
2006-06-23 20233946 47 128309666 5553425498 97.7400 "1858
MB"
2006-06-26 34252036 4765 237496776 9024547005 97.4400 "2218
MB"
2006-06-27 40290065 403368 273062334 9156477077 97.1000 "2401
MB"
2006-06-28 46436389 870211 307983449 9260208418 96.7800 "2497
MB"
2006-06-29 52251198 1352370 348552701 9367093206 96.4100 "2575
MB"
2006-06-30 58585373 1839034 392822069 9477661079 96.0200 "2647
MB"
2006-07-03 75747589 3318388 551767504 9816311112 94.6800 "2815
MB"
2006-07-05 87768328 4524047 678763032 10076029919 93.6900 "2973
MB"
(server restarted - shared_buffers changed)
2006-07-06 4841006 326389 50641814 121507743 70.5800 "3031
MB"
2006-07-07 10711794 732981 113816094 274683161 70.7000 "3076
MB"
2006-07-10 19428743 1300797 287848710 517770353 64.2700 "3452
MB"
My questions concerning these statistics:
(1) This application monitors networks. On 6/26, it began monitoring a
number of
new network nodes (traffic increase of 40 - 50%); but the application
itself
has not been changed, either in terms of number of users, program or
database
changes, or other changes. Yet, the rollback column has increased
substantially.
QUESTION: is there a database setting that can affect this statistic?
(2) I noticed that pct_mem_hits was dropping in early July at about the same
time
that the platform started monitoring additional network nodes. On late
July
5th, increased shared_buffers from default value of 1000 to 1500, but
pct_mem_hits
continue to decline.
QUESTION: I evidently touched the wrong thing. What should I be
adjusting to
help keep the database in memory?
All other statistics appear to be normal.
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
It looks like I could cut down on max_fsm_relations (but I don't know if this should includes system tables
or not).
Don't know how to interpret max_fsm_pages (see INFO message below); either:
- I am within 16 pages of running out (19984 vs 20000), or
- I need 19984 pages more
How should the following be read (data is from vacuum)?
INFO: free space map: 163 relations, 19708 pages stored; 19984 total pages needed
DETAIL: Allocated FSM size: 1000 relations + 20000 pages = 186 kB shared memory
DETAIL: Allocated FSM size: 1000 relations + 20000 pages = 186 kB shared memory
Attached a file showing a lot of the rest of vacuum results; it looked OK to me. Do you agree?
work_mem and maintenance_work_mem are running at default values (1000 and 16384) at present;
is there any way I can know if these are inadequate?
-----Original Message-----
From: adey [mailto:adey11@gmail.com]
Sent: Monday, July 10, 2006 8:24 PM
To: Lane Van Ingen
Cc: pgsql-admin@postgresql.org
Subject: Re: [ADMIN] Performance Slowly Decreasing As Database Grows
Check the stats at the end of your vacuum to ensure your max_fsm_pages (free space map) is large enough. Also check work_mem and maintenance_work_mem are not running at defaults that may be too small. If you have many updates, increase the number of wal_buffers and checkpoint_segments.
On 7/11/06, Lane Van Ingen <lvaningen@esncc.com> wrote:
I am using Postgresql 8.1.4 on Windows 2003; platform is Dell Precision 330,
1.8 Ghz CPU, 1 gByte of RAM. This database is subject to 'vacuum full analyze' once
/ day.
I am watching a recently created database grow; as it grows, I am finding
that some of the performance statistics appear to be falling out of bed. The
functions I have used to capture this information are the standard functions
that come with PostgreSQL:
date commits rollbks dsk_reads mem_reads pct_mem_hits
db_size
2006-06-19 94115102 64 553053905 13126498559 95.9600 "1674
MB
(server restarted)
2006-06-20 4383600 26 24129603 1162150532 97.9700 1471
MB
2006-06-21 9179799 27 56084142 2456950412 97.7700 1587
MB
2006-06-22 14447111 28 89452397 3875993962 97.7400 1710
MB
2006-06-23 20233946 47 128309666 5553425498 97.7400 1858
MB
2006-06-26 34252036 4765 237496776 9024547005 97.4400 2218
MB
2006-06-27 40290065 403368 273062334 9156477077 97.1000 2401
MB
2006-06-28 46436389 870211 307983449 9260208418 96.7800 2497
MB
2006-06-29 52251198 1352370 348552701 9367093206 96.4100 2575
MB
2006-06-30 58585373 1839034 392822069 9477661079 96.0200 2647
MB
2006-07-03 75747589 3318388 551767504 9816311112 94.6800 2815
MB
2006-07-05 87768328 4524047 678763032 10076029919 93.6900 2973
MB
(server restarted - shared_buffers changed)
2006-07-06 4841006 326389 50641814 121507743 70.5800 3031
MB
2006-07-07 10711794 732981 113816094 274683161 70.7000 3076
MB
2006-07-10 19428743 1300797 287848710 517770353 64.2700 3452
MB
My questions concerning these statistics:
(1) This application monitors networks. On 6/26, it began monitoring a
number of new network nodes (traffic increase of 40 - 50%); but the application
itself has not been changed, either in terms of number of users, program or
database changes, or other changes. Yet, the rollback column has increased
substantially.
QUESTION: is there a database setting that can affect this statistic?
(2) I noticed that pct_mem_hits was dropping in early July at about the same
time that the platform started monitoring additional network nodes. On late
July5th, increased shared_buffers from default value of 1000 to 1500, but
pct_mem_hits continues to decline.
QUESTION: I evidently touched the wrong thing. What should I be
adjusting to help keep the database in memory?
All other statistics appear to be normal.