Re: [ADMIN] Server Hardware Configuration
On Monday 21 November 2005 12:12, you wrote: > On Mon, Nov 21, 2005 at 09:41:36AM -0500, Michael D. Sofka wrote: > You're talking about a 16G database that you expect to grow to 64G. That > would fit happily in a RAID1 (mirror) of two SCSI 72G drives. I haven't > priced that kind of stuff out recently, but I believe you're looking at > $300-$500. If that doesn't provide enough performance, go to a RAID10 > and add more drives. If you're doing much writing at all, spring for a > battery-backed controller so you can enable write caching. More like $7k, till you add in the JBOD, the Perc card and 15,000 rpm SCSI disks, with hot spars (the journal mirror goes on the same JBOD). But you're right, we will have sufficient disks on the JBOD for RAID 1+0. Mike -- Michael D. Sofka [EMAIL PROTECTED] C&CT Sr. Systems ProgrammerEmail, TeX, epistemology. Rensselaer Polytechnic Institute, Troy, NY. http://www.rpi.edu/~sofkam/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[ADMIN] Problems when initdb on WinXP with SP2.
Hi, I use Postgresql 8.03/ 8.04 on windows xp with sp2, all have same problems. run the windows cmd by: runas /user:postgres cmd and run the initdb program like: initdb --locale=C --username=postgres -W -A md5 -E UNICODE -D ../data then it says: creating directory ../data/ ... ok creating directory ../data/global ... ok creating directory ../data/pg_xlog ... ok creating directory ../data/pg_xlog/archive_status ... ok creating directory ../data/pg_clog ... ok creating directory ../data/pg_subtrans ... ok creating directory ../data/base ... ok creating directory ../data/base/1 ... ok creating directory ../data/pg_tblspc ... ok selecting default max_connections and the command window stayed there! I'm newbie at postgresql, anyone can give me some help, thanks a lot. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[ADMIN] pgstattuple, vacuum and free_space
Hi:
I did the following after installing the pgstattuple contrib code:
select * from pgstattuple('wind');
-[ RECORD 1 ]--+--
table_len | 224854016
tuple_count| 1492601
tuple_len | 207535124
tuple_percent | 92.3
dead_tuple_count | 11569
dead_tuple_len | 1361848
dead_tuple_percent | 0.61
free_space | 3311416
free_percent | 1.47
vacuum verbose wind;
INFO: vacuuming "public.wind"
INFO: index "wind_pkey" now contains 1492601 row versions in 4152 pages
DETAIL: 11569 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.56s/1.77u sec elapsed 36.73 sec.
INFO: index "wind_measurement_date_index" now contains 1492601 row
versions in 4156 pages
DETAIL: 11569 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.43s/1.83u sec elapsed 35.90 sec.
INFO: "wind": removed 11569 row versions in 371 pages
DETAIL: CPU 0.03s/0.03u sec elapsed 2.38 sec.
INFO: "wind": found 11569 removable, 1492601 nonremovable row versions
in 27448 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 2.01s/3.95u sec elapsed 79.39 sec.
VACUUM
select * from pgstattuple('wind');
-[ RECORD 1 ]--+--
table_len | 224854016
tuple_count| 1492601
tuple_len | 207535124
tuple_percent | 92.3
dead_tuple_count | 0
dead_tuple_len | 0
dead_tuple_percent | 0
free_space | 4673256
free_percent | 2.08
My question: when you vacuum a table and generate 'free_space', who is
allowed to consume this 'free_space'? Is it released to the OS for
general use? Or is it reserved just for the database? If the latter, is
it reserved just for 'wind' (in this case)?
Thanks again!
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[ADMIN] query planning and partitioned tables
Hi:
I have a 440 million row table that I'm attempting to partition. The
table is named 'pressure' and holds pressure data from an undersea
sensor measuring wave height.
The sensor reports every half second.
The partitions are named 'p0', 'p1' ... 'pN' and are divvied according
to measurement date.
See the following:
Table "public.pressure"
Column |Type
| Modifiers
--+-+---
pressure_id | integer | not null default
nextval(('pressure_id_seq'::text)::regclass)
row_entry_date | timestamp with time zone|
sensor_id| integer | not null
measurement_date | timestamp without time zone |
pressure | double precision|
Indexes:
"pressure_pkey" PRIMARY KEY, btree (pressure_id)
"pressure_measurement_date_index" btree (measurement_date)
Foreign-key constraints:
"sensor_id_exists" FOREIGN KEY (sensor_id) REFERENCES
sensor(sensor_id) ON DELETE RESTRICT
Tablespace: "diskvol2"
Table "public.p0"
Column |Type
| Modifiers
--+-+---
pressure_id | integer | not null default
nextval(('pressure_id_seq'::text)::regclass)
row_entry_date | timestamp with time zone|
sensor_id| integer | not null
measurement_date | timestamp without time zone |
pressure | double precision|
Indexes:
"p0_pressure_id_index" UNIQUE, btree (pressure_id), tablespace
"diskvol2"
"p0_measurement_date_index" btree (measurement_date), tablespace
"diskvol2"
Check constraints:
"p0_measurement_date_check" CHECK (measurement_date >= '1999-08-02
00:00:00'::timestamp without time zone AND measurement_date <
'2000-01-06 00:00:00'::timestamp without time zone)
Foreign-key constraints:
"sensor_id_exists" FOREIGN KEY (sensor_id) REFERENCES
sensor(sensor_id) ON DELETE RESTRICT
Inherits: pressure
Tablespace: "diskvol2"
My question involves how the database performs the following queries:
explain select max(measurement_date) from pressure;
QUERY
PLAN
---
Aggregate (cost=480311.50..480311.51 rows=1 width=8)
-> Append (cost=0.00..425345.20 rows=21986520 width=8)
-> Seq Scan on pressure (cost=0.00..22.30 rows=1230 width=8)
-> Seq Scan on p0 pressure (cost=0.00..425322.90
rows=21985290 width=8)
(4 rows)
explain select max(measurement_date) from p0;
QUERY
PLAN
---
Result (cost=0.02..0.03 rows=1 width=0)
InitPlan
-> Limit (cost=0.00..0.02 rows=1 width=8)
-> Index Scan Backward using p0_measurement_date_index on
p0 (cost=0.00..531221.19 rows=21985290 width=8)
Filter: (measurement_date IS NOT NULL)
(5 rows)
The optimizer doesn't do as well with the former as the latter. Is that
the expected behavior or have I screwed things up?
Also, consider the following:
explain select * from pressure where measurement_date < '2000-01-01';
QUERY
PLAN
--
Result (cost=0.00..480293.25 rows=21092480 width=32)
-> Append (cost=0.00..480293.25 rows=21092480 width=32)
-> Index Scan using pressure_measurement_date_index on
pressure (cost=0.00..7.13 rows=69 width=32)
Index Cond: (measurement_date < '2000-01-01
00:00:00'::timestamp without time zone)
-> Seq Scan on p0 pressure (cost=0.00..480286.12
rows=21092411 width=32)
Filter: (measurement_date < '2000-01-01
00:00:00'::timestamp without time zone)
(6 rows)
explain select * from p0 where measurement_date < '2000-01-01';
QUERY
PLAN
---
Seq Scan on p0 (cost=0.00..480286.12 rows=21092411 width=32)
Filter: (measurement_date < '2000-01-01 00:00:00'::timestamp without
time zone)
(2 rows)
The query planner seems to be ignoring p0's index on measurement_date.
Contrast that to the plan made for a similar query made on a different
table
Re: [ADMIN] pgstattuple, vacuum and free_space
On 11/24/05, Colton Smith <[EMAIL PROTECTED]> wrote: My question: when you vacuum a table and generate 'free_space', who isallowed to consume this 'free_space'? Is it released to the OS forgeneral use? Or is it reserved just for the database? If the latter, isit reserved just for 'wind' (in this case)? as far as i know this space is mapped as free witinh data-files of table "wind". thus effectivelyu - your OS doesnt get more free space, nor is (generally speaking) the database. benefit of this "free space" is that next inserts to wind table will fit inside of this free space - thus stopping bloat of datafiles of this table. if you would like to reclaim the disk-space, you should consider using vacuum full. depesz
Re: [ADMIN] Problems when initdb on WinXP with SP2.
"Johnson Zhao" <[EMAIL PROTECTED]> wrote > > run the windows cmd by: > > runas /user:postgres cmd > > and run the initdb program like: > > initdb --locale=C --username=postgres -W -A md5 -E UNICODE -D ../data > > then it says: > > creating directory ../data/ ... ok > creating directory ../data/global ... ok > creating directory ../data/pg_xlog ... ok > creating directory ../data/pg_xlog/archive_status ... ok > creating directory ../data/pg_clog ... ok > creating directory ../data/pg_subtrans ... ok > creating directory ../data/base ... ok > creating directory ../data/base/1 ... ok > creating directory ../data/pg_tblspc ... ok > selecting default max_connections > > and the command window stayed there! > I can't reproduce your problem. Did you set some odd permission to user "postgres"? Let's simplify it first, is there any problem if you login to windows as "postgres" first, then do your initdb? Regards, Qingqing ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [ADMIN] Problems when initdb on WinXP with SP2.
Johnson Zhao wrote: Hi, I use Postgresql 8.03/ 8.04 on windows xp with sp2, all have same problems. run the windows cmd by: runas /user:postgres cmd and run the initdb program like: initdb --locale=C --username=postgres -W -A md5 -E UNICODE -D ../data then it says: creating directory ../data/ ... ok creating directory ../data/global ... ok creating directory ../data/pg_xlog ... ok creating directory ../data/pg_xlog/archive_status ... ok creating directory ../data/pg_clog ... ok creating directory ../data/pg_subtrans ... ok creating directory ../data/base ... ok creating directory ../data/base/1 ... ok creating directory ../data/pg_tblspc ... ok selecting default max_connections and the command window stayed there! I have no idea whether this matters or not, but I got bit during an 8.1 install by permissions issues. The postgres user requires read/write permissions for the data folder and below, but it also requires read permissions all the way back to the root of the drive (including the root directory). The specific situation for us was that we were trying to install to D:\PostgreSQL\Data, but the postgres user didn't have read permissions on the root of D:\ (or D:\PostgreSQL). ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
