Thread: index file bloating still in 7.4 ?
Hi, I downloaded PostgreSQL 7.4beta4 and tried it out. It turns out that the index file is still bloating even after running vacuum or vacuum analyze on the table. Still, only reindex will claim the space back. Is the index bloating issue still not resolved in 7.4beta4 ? Thanks. Gan -- +--------------------------------------------------------+ | Seum-Lim GAN email : slgan@lucent.com | | Lucent Technologies | | 2000 N. Naperville Road, 6B-403F tel : (630)-713-6665 | | Naperville, IL 60566, USA. fax : (630)-713-7272 | | web : http://inuweb.ih.lucent.com/~slgan | +--------------------------------------------------------+
Gan, > Is the index bloating issue still not resolved in 7.4beta4 ? No, it should be. Please post your max_fsm_pages setting, and the output of a sample VACUUM VERBOSE ANALYZE. You probably don't have your FSM set right. -- -Josh Berkus Aglio Database Solutions San Francisco
Hi Josh,
Sample verbose analyze:
VACUUM VERBOSE ANALYZE hello_rda_or_key;
INFO: vacuuming "craft.hello_rda_or_key"
INFO: index "hello242_1105" now contains 740813 row versions in 2477 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.42s/0.13u sec elapsed 4.76 sec.
INFO: "hello_rda_or_key": found 0 removable, 740813 nonremovable row
versions in 12778 pages
DETAIL: 440813 dead row versions cannot be removed yet.
There were 0 unused item pointers.
0 pages are entirely empty.
CPU 0.78s/0.66u sec elapsed 6.41 sec.
INFO: analyzing "craft.hello_rda_or_key"
INFO: "hello_rda_or_key": 12778 pages, 3000 rows sampled, 39388
estimated total rows
VACUUM
Here is my postgresql.conf file:
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
# name = value
#
# (The '=' is optional.) White space may be used. Comments are introduced
# with '#' anywhere on a line. The complete list of option names and
# allowed values can be found in the PostgreSQL documentation. The
# commented-out settings shown in this file represent the default values.
#
# Any option can also be given as a command line switch to the
# postmaster, e.g. 'postmaster -c log_connections=on'. Some options
# can be changed at run-time with the 'SET' SQL command.
#
# This file is read on postmaster startup and when the postmaster
# receives a SIGHUP. If you edit the file on a running system, you have
# to SIGHUP the postmaster for the changes to take effect, or use
# "pg_ctl reload".
#---------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#---------------------------------------------------------------------------
# - Connection Settings -
#tcpip_socket = false
#max_connections = 100
max_connections = 600
# note: increasing max_connections costs about 500 bytes of shared
# memory per connection slot, in addition to costs from shared_buffers
# and max_locks_per_transaction.
#superuser_reserved_connections = 2
#port = 5432
port = 5333
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777 # octal
#virtual_host = '' # what interface to listen on; defaults to any
#rendezvous_name = '' # defaults to the computer name
# - Security & Authentication -
#authentication_timeout = 60 # 1-600, in seconds
#ssl = false
#password_encryption = true
#krb_server_keyfile = ''
#db_user_namespace = false
#---------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#---------------------------------------------------------------------------
# - Memory -
#shared_buffers = 1000 # min 16, at least max_connections*2, 8KB each
shared_buffers = 1200 # min 16, at least max_connections*2, 8KB each
#sort_mem = 1024 # min 64, size in KB
sort_mem = 40960 # min 64, size in KB
#vacuum_mem = 8192 # min 1024, size in KB
vacuum_mem = 81920 # min 1024, size in KB
# - Free Space Map -
#max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each
max_fsm_pages = 50000 # min max_fsm_relations*16, 6 bytes each
#max_fsm_relations = 1000 # min 100, ~50 bytes each
max_fsm_relations = 1000 # min 100, ~50 bytes each
# - Kernel Resource Usage -
#max_files_per_process = 1000 # min 25
#preload_libraries = ''
#---------------------------------------------------------------------------
# WRITE AHEAD LOG
#---------------------------------------------------------------------------
# - Settings -
#fsync = true # turns forced synchronization on or off
fsync = false # turns forced synchronization on or off
#wal_sync_method = fsync # the default varies across platforms:
# fsync, fdatasync, open_sync, or open_datasync
#wal_buffers = 8 # min 4, 8KB each
# - Checkpoints -
#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 300 # range 30-3600, in seconds
#checkpoint_warning = 30 # 0 is off, in seconds
#commit_delay = 0 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000
#---------------------------------------------------------------------------
# QUERY TUNING
#---------------------------------------------------------------------------
# - Planner Method Enabling -
#enable_hashagg = true
#enable_hashjoin = true
#enable_indexscan = true
#enable_mergejoin = true
#enable_nestloop = true
#enable_seqscan = true
#enable_sort = true
#enable_tidscan = true
# - Planner Cost Constants -
#effective_cache_size = 1000 # typically 8KB each
#random_page_cost = 4 # units are one sequential page fetch cost
#cpu_tuple_cost = 0.01 # (same)
#cpu_index_tuple_cost = 0.001 # (same)
#cpu_operator_cost = 0.0025 # (same)
# - Genetic Query Optimizer -
#geqo = true
#geqo_threshold = 11
#geqo_effort = 1
#geqo_generations = 0
#geqo_pool_size = 0 # default based on tables in statement,
# range 128-1024
#geqo_selection_bias = 2.0 # range 1.5-2.0
# - Other Planner Options -
#default_statistics_target = 10 # range 1-1000
#from_collapse_limit = 8
#join_collapse_limit = 8 # 1 disables collapsing of explicit JOINs
#---------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#---------------------------------------------------------------------------
# - Syslog -
#syslog = 0 # range 0-2; 0=stdout; 1=both; 2=syslog
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
# - When to Log -
#client_min_messages = notice # Values, in order of decreasing detail:
# debug5, debug4, debug3, debug2, debug1,
# log, info, notice, warning, error
#log_min_messages = notice # Values, in order of decreasing detail:
# debug5, debug4, debug3, debug2, debug1,
# info, notice, warning, error, log, fatal,
# panic
#log_error_verbosity = default # terse, default, or verbose messages
#log_min_error_statement = panic # Values in order of increasing severity:
# debug5, debug4, debug3, debug2, debug1,
# info, notice, warning, error, panic(off)
#log_min_duration_statement = 0 # Log all statements whose
# execution time exceeds the value, in
# milliseconds. Zero disables.
#silent_mode = false # DO NOT USE without Syslog!
# - What to Log -
#debug_print_parse = false
#debug_print_rewritten = false
#debug_print_plan = false
#debug_pretty_print = false
#log_connections = false
#log_duration = false
#log_pid = false
#log_statement = false
#log_timestamp = false
#log_hostname = false
#log_source_port = false
#---------------------------------------------------------------------------
# RUNTIME STATISTICS
#---------------------------------------------------------------------------
# - Statistics Monitoring -
#log_parser_stats = false
#log_planner_stats = false
#log_executor_stats = false
#log_statement_stats = false
# - Query/Index Statistics Collector -
#stats_start_collector = true
#stats_command_string = false
#stats_block_level = false
#stats_row_level = false
#stats_reset_on_server_start = true
#---------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#---------------------------------------------------------------------------
# - Statement Behavior -
#search_path = '$user,public' # schema names
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = false
#statement_timeout = 0 # 0 is disabled, in milliseconds
# - Locale and Formatting -
#datestyle = 'iso, mdy'
#timezone = unknown # actually, defaults to TZ environment setting
#australian_timezones = false
#extra_float_digits = 0 # min -15, max 2
#client_encoding = sql_ascii # actually, defaults to database encoding
# These settings are initialized by initdb -- they may be changed
lc_messages = 'C' # locale for system error message strings
lc_monetary = 'C' # locale for monetary formatting
lc_numeric = 'C' # locale for number formatting
lc_time = 'C' # locale for time formatting
# - Other Defaults -
#explain_pretty_print = true
#dynamic_library_path = '$libdir'
#max_expr_depth = 10000 # min 10
#---------------------------------------------------------------------------
# LOCK MANAGEMENT
#---------------------------------------------------------------------------
#deadlock_timeout = 1000 # in milliseconds
#max_locks_per_transaction = 64 # min 10, ~260*max_connections bytes each
#---------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#---------------------------------------------------------------------------
# - Previous Postgres Versions -
#add_missing_from = true
#regex_flavor = advanced # advanced, extended, or basic
#sql_inheritance = true
# - Other Platforms & Clients -
#transform_null_equals = false
At 2:58 pm -0700 2003/10/18, Josh Berkus wrote:
>Gan,
>
>> Is the index bloating issue still not resolved in 7.4beta4 ?
>
>No, it should be. Please post your max_fsm_pages setting, and the output of a
>sample VACUUM VERBOSE ANALYZE. You probably don't have your FSM set right.
>
>
>--
>-Josh Berkus
> Aglio Database Solutions
> San Francisco
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 7: don't forget to increase your free space map settings
--
+--------------------------------------------------------+
| Seum-Lim GAN email : slgan@lucent.com |
| Lucent Technologies |
| 2000 N. Naperville Road, 6B-403F tel : (630)-713-6665 |
| Naperville, IL 60566, USA. fax : (630)-713-7272 |
| web : http://inuweb.ih.lucent.com/~slgan |
+--------------------------------------------------------+
Seum-Lim Gan <slgan@lucent.com> writes:
> Sample verbose analyze:
> VACUUM VERBOSE ANALYZE hello_rda_or_key;
> INFO: vacuuming "craft.hello_rda_or_key"
> INFO: index "hello242_1105" now contains 740813 row versions in 2477 pages
So what's the problem? That doesn't seem like a particularly bloated
index. You didn't say what datatype the index is on, but making the
most optimistic assumptions, index entries must use at least 16 bytes
each. You're getting about 300 entries per page, compared to the
theoretical limit of 512 ... actually more, since I'm not allowing for
upper btree levels in this calculation ... which says to me that the
page loading is right around the expected btree loading of 2/3.
regards, tom lane
Hi Tom, I did that when I have stopped my updates. Now, I am doing updates below is the output of vacuum. After doing the vacuum verbose analyze, it reported the following : INFO: vacuuming "craft.dsperf_rda_or_key" INFO: index "hello242_1105" now contains 1792276 row versions in 6237 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.61s/0.36u sec elapsed 17.92 sec. INFO: "hello_rda_or_key": found 0 removable, 1791736 nonremovable row versions in 30892 pages DETAIL: 1492218 dead row versions cannot be removed yet. There were 0 unused item pointers. 0 pages are entirely empty. CPU 1.95s/1.99u sec elapsed 26.95 sec. INFO: analyzing "craft.dsperf_rda_or_key" INFO: "hello_rda_or_key": 30909 pages, 3000 rows sampled, 93292 estimated total rows VACUUM Gan At 10:21 pm -0400 2003/10/18, Tom Lane wrote: >Seum-Lim Gan <slgan@lucent.com> writes: >> Sample verbose analyze: > >> VACUUM VERBOSE ANALYZE hello_rda_or_key; >> INFO: vacuuming "craft.hello_rda_or_key" >> INFO: index "hello242_1105" now contains 740813 row versions in 2477 pages > >So what's the problem? That doesn't seem like a particularly bloated >index. You didn't say what datatype the index is on, but making the >most optimistic assumptions, index entries must use at least 16 bytes >each. You're getting about 300 entries per page, compared to the >theoretical limit of 512 ... actually more, since I'm not allowing for >upper btree levels in this calculation ... which says to me that the >page loading is right around the expected btree loading of 2/3. > > regards, tom lane -- +--------------------------------------------------------+ | Seum-Lim GAN email : slgan@lucent.com | | Lucent Technologies | | 2000 N. Naperville Road, 6B-403F tel : (630)-713-6665 | | Naperville, IL 60566, USA. fax : (630)-713-7272 | | web : http://inuweb.ih.lucent.com/~slgan | +--------------------------------------------------------+
Seum-Lim Gan <slgan@lucent.com> writes:
> INFO: vacuuming "craft.dsperf_rda_or_key"
> INFO: index "hello242_1105" now contains 1792276 row versions in 6237 pages
> DETAIL: 0 index pages have been deleted, 0 are currently reusable.
> CPU 0.61s/0.36u sec elapsed 17.92 sec.
> INFO: "hello_rda_or_key": found 0 removable, 1791736 nonremovable
> row versions in 30892 pages
> DETAIL: 1492218 dead row versions cannot be removed yet.
You still haven't got an index-bloat problem. I am, however, starting
to wonder why you have so many dead-but-unremovable rows. I think you
must have some client process that's been holding an open transaction
for a long time.
regards, tom lane
Hi Tom, Thanks for info. I stoped the update and removed the process that's doing the update and did vacuum analyze. This time the result says the index row has been removed : vacuum verbose analyze dsperf_rda_or_key; INFO: vacuuming "scncraft.dsperf_rda_or_key" INFO: index "dsperf242_1105" now contains 300000 row versions in 12387 pages DETAIL: 3097702 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU 2.86s/25.49u sec elapsed 54.16 sec. INFO: "dsperf_rda_or_key": removed 3097702 row versions in 53726 pages DETAIL: CPU 6.29s/26.05u sec elapsed 78.23 sec. INFO: "dsperf_rda_or_key": found 3097702 removable, 300000 nonremovable row versions in 58586 pages DETAIL: 0 dead row versions cannot be removed yet. There were 5 unused item pointers. 0 pages are entirely empty. CPU 10.23s/53.79u sec elapsed 135.78 sec. INFO: analyzing "scncraft.dsperf_rda_or_key" INFO: "dsperf_rda_or_key": 58586 pages, 3000 rows sampled, 176830 estimated total rows VACUUM However, when I check the disk space usage, it has not changed. Before and after the vacuum, it stayed the same : /pg 822192 21% Sun Oct 19 09:34:25 CDT 2003 table /pg/data/base/17139/34048 Size=479936512 (relfilenode for table) index /pg/data/base/17139/336727 Size=101474304 (relfilenode for index) Any idea here ? Another question, if we have a process that has different threads trying to update PostgreSQL, is this going to post a problem if we do not have the thread-safety option during configure ? Thanks. Gan At 1:48 am -0400 2003/10/19, Tom Lane wrote: >Seum-Lim Gan <slgan@lucent.com> writes: >> INFO: vacuuming "craft.dsperf_rda_or_key" >> INFO: index "hello242_1105" now contains 1792276 row versions in 6237 pages >> DETAIL: 0 index pages have been deleted, 0 are currently reusable. >> CPU 0.61s/0.36u sec elapsed 17.92 sec. >> INFO: "hello_rda_or_key": found 0 removable, 1791736 nonremovable >> row versions in 30892 pages >> DETAIL: 1492218 dead row versions cannot be removed yet. > >You still haven't got an index-bloat problem. I am, however, starting >to wonder why you have so many dead-but-unremovable rows. I think you >must have some client process that's been holding an open transaction >for a long time. > > regards, tom lane -- +--------------------------------------------------------+ | Seum-Lim GAN email : slgan@lucent.com | | Lucent Technologies | | 2000 N. Naperville Road, 6B-403F tel : (630)-713-6665 | | Naperville, IL 60566, USA. fax : (630)-713-7272 | | web : http://inuweb.ih.lucent.com/~slgan | +--------------------------------------------------------+
Seum-Lim Gan <slgan@lucent.com> writes:
> vacuum verbose analyze dsperf_rda_or_key;
> INFO: vacuuming "scncraft.dsperf_rda_or_key"
> INFO: index "dsperf242_1105" now contains 300000 row versions in 12387 pages
> DETAIL: 3097702 index row versions were removed.
> 0 index pages have been deleted, 0 are currently reusable.
Hm, interesting that you deleted 90% of the entries and still had no
empty index pages at all. What was the pattern of your deletes and/or
updates with respect to this index's key?
> However, when I check the disk space usage, it has not changed.
It won't in any case. Plain VACUUM is designed for maintaining a
steady-state level of free space in tables and indexes, not for
returning major amounts of space to the OS. For that you need
more-invasive operations like VACUUM FULL or REINDEX.
regards, tom lane
Hi Tom, The key is a range from 1 to 30000 and picked randomly. Oh, so in order to reclaim the disk space, we must run reindex or vacuum full ? This will lock out the table and we won't be able to do anything. Looks like this is a problem. It means we cannot use it for 24x7 operations without having to stop the process and do the vacuum full and reindex. Is there anything down the road that these operations will not lock out the table ? I let the process ran overnight. The last email I sent you with the vacuum analyze output just about an hour ago, that was after I removed the process that does the updates. However, I search through all the vacuum I did just before I went to bed and found that earlier vacuum did say 5 indexes deleted and 5 reusable. It has been pretty constant for about 1 to 2 hours and then down to zero and has been like this since. Sun Oct 19 00:50:07 CDT 2003 INFO: vacuuming "scncraft.dsperf_rda_or_key" INFO: index "dsperf242_1105" now contains 402335 row versions in 7111 pages DETAIL: 5 index pages have been deleted, 5 are currently reusable. CPU 1.32s/0.17u sec elapsed 22.44 sec. INFO: "dsperf_rda_or_key": found 0 removable, 401804 nonremovable row versions in 35315 pages DETAIL: 101802 dead row versions cannot be removed yet. There were 1646275 unused item pointers. 0 pages are entirely empty. CPU 2.38s/0.71u sec elapsed 27.09 sec. INFO: analyzing "scncraft.dsperf_rda_or_key" INFO: "dsperf_rda_or_key": 35315 pages, 3000 rows sampled, 156124 estimated total rows VACUUM Sleep 60 seconds Sun Oct 19 00:51:40 CDT 2003 INFO: vacuuming "scncraft.dsperf_rda_or_key" INFO: index "dsperf242_1105" now contains 411612 row versions in 7111 pages DETAIL: 5 index pages have been deleted, 5 are currently reusable. CPU 1.28s/0.22u sec elapsed 23.38 sec. INFO: "dsperf_rda_or_key": found 0 removable, 410889 nonremovable row versions in 35315 pages DETAIL: 110900 dead row versions cannot be removed yet. There were 1637190 unused item pointers. 0 pages are entirely empty. CPU 2.13s/0.92u sec elapsed 27.13 sec. INFO: analyzing "scncraft.dsperf_rda_or_key" INFO: "dsperf_rda_or_key": 35315 pages, 3000 rows sampled, 123164 estimated total rows VACUUM Sleep 60 seconds . . . Sun Oct 19 02:14:41 CDT 2003 INFO: vacuuming "scncraft.dsperf_rda_or_key" INFO: index "dsperf242_1105" now contains 1053582 row versions in 7112 pages DETAIL: 5 index pages have been deleted, 5 are currently reusable. CPU 0.58s/0.29u sec elapsed 21.63 sec. INFO: "dsperf_rda_or_key": found 0 removable, 1053103 nonremovable row versions in 35315 pages DETAIL: 753064 dead row versions cannot be removed yet. There were 995103 unused item pointers. 0 pages are entirely empty. CPU 1.54s/1.35u sec elapsed 26.17 sec. INFO: analyzing "scncraft.dsperf_rda_or_key" INFO: "dsperf_rda_or_key": 35315 pages, 3000 rows sampled, 106627 estimated total rows VACUUM Sleep 60 seconds Sun Oct 19 02:16:16 CDT 2003 INFO: vacuuming "scncraft.dsperf_rda_or_key" INFO: index "dsperf242_1105" now contains 1065887 row versions in 7119 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 0.71s/0.36u sec elapsed 21.12 sec. INFO: "dsperf_rda_or_key": found 0 removable, 1065357 nonremovable row versions in 35315 pages DETAIL: 765328 dead row versions cannot be removed yet. There were 982849 unused item pointers. 0 pages are entirely empty. CPU 1.70s/1.42u sec elapsed 26.65 sec. INFO: analyzing "scncraft.dsperf_rda_or_key" INFO: "dsperf_rda_or_key": 35315 pages, 3000 rows sampled, 106627 estimated total rows VACUUM Sleep 60 seconds . . . Thanks. Gan At 11:47 am -0400 2003/10/19, Tom Lane wrote: >Seum-Lim Gan <slgan@lucent.com> writes: >> vacuum verbose analyze dsperf_rda_or_key; >> INFO: vacuuming "scncraft.dsperf_rda_or_key" >> INFO: index "dsperf242_1105" now contains 300000 row versions in >>12387 pages >> DETAIL: 3097702 index row versions were removed. > > 0 index pages have been deleted, 0 are currently reusable. > >Hm, interesting that you deleted 90% of the entries and still had no >empty index pages at all. What was the pattern of your deletes and/or >updates with respect to this index's key? > >> However, when I check the disk space usage, it has not changed. > >It won't in any case. Plain VACUUM is designed for maintaining a >steady-state level of free space in tables and indexes, not for >returning major amounts of space to the OS. For that you need >more-invasive operations like VACUUM FULL or REINDEX. > > regards, tom lane -- +--------------------------------------------------------+ | Seum-Lim GAN email : slgan@lucent.com | | Lucent Technologies | | 2000 N. Naperville Road, 6B-403F tel : (630)-713-6665 | | Naperville, IL 60566, USA. fax : (630)-713-7272 | | web : http://inuweb.ih.lucent.com/~slgan | +--------------------------------------------------------+
Gan, > Oh, so in order to reclaim the disk space, we must run > reindex or vacuum full ? > This will lock out the table and we won't be able to do anything. > Looks like this is a problem. It means we cannot use it for > 24x7 operations without having to stop the process and do the vacuum full > and reindex. Is there anything down the road that these operations > will not lock out the table ? I doubt it; the amount of page-shuffling required to reclaim 90% of the space in an index for a table that has been mostly cleared is substantial, and would prevent concurrent access. Also, you seem to have set up an impossible situation for VACUUM. If I'm reading your statistics right, you have a large number of threads accessing most of the data 100% of the time, preventing VACUUM from cleaning up the pages. This is not, in my experience, a realistic test case ... there are peak and idle periods for all databases, even webservers that have been slashdotted. -- Josh Berkus Aglio Database Solutions San Francisco
Hi all, Does anyone have any experience with putting PostgreSQL data on a NAS device? I am asking this because a NAS device is much cheaper to set up than a couple of SCSI disks. I would like to use a relatively cheap NAS device which uses four IDE drives (7.200 rpm), like the Dell PowerVault 725N. The disks themselves would be much slower than SCSI disks, I know, but a NAS device can be equipped with 3 Gb of memory, so this would make a very large disk cache, right? If this NAS would be dedicated only to PostgreSQL, would this be slower/faster than a SCSI RAID-10 setup of 6 disks? It would be much cheaper... Any advice on this would be appreciated :) Kind regards, Alexander Priem.
On Mon, 20 Oct 2003 09:12:35 +0200 "Alexander Priem" <ap@cict.nl> wrote: > I am asking this because a NAS device is much cheaper to set up than a > couple of SCSI disks. I would like to use a relatively cheap NAS > device which uses four IDE drives (7.200 rpm), like the Dell > PowerVault 725N. The disks themselves would be much slower than SCSI > disks, I know, but a NAS device can be equipped with 3 Gb of memory, > so this would make a very large disk cache, right? If this NAS would > be dedicated only to PostgreSQL, would this be slower/faster than a > SCSI RAID-10 setup of 6 disks? It would be much cheaper... > The big concern would be the network connection, unless you are going fiber. You need to use _AT LEAST_ gigabit. _at least_. If you do go that route it'd be interesting to see bonnie results. And the other thing - remember that just because you are running NAS doesn't mean you can attach another machine running postgres and have a cluster. (See archives for more info about this). I suppose it all boils down to your budget (I usually get to work with a budget of $0). And I mentioned this in another post- If you don't mind refurb disks(or slightly used) check out ebay - you can get scsi disks by the truckload for cheap. -- Jeff Trout <jeff@jefftrout.com> http://www.jefftrout.com/ http://www.stuarthamm.net/
Thanks for your reply, Jeff. If we are going to use a NAS device for storage, then it will be attached through a gigabit ethernet connection. Fiber will not be an option, since that would negate the savings we can make by using an IDE NAS device instead of SCSI-RAID, fiber's pretty expensive, right? Using a NAS device (that is used only by PostgreSQL, so it's dedicated) with 3Gb of RAM and four 7200 rpm IDE harddisks, connected using a gigabit ethernet connection to the PostgreSQL server, do you think it will be a match for a SCSI-RAID config using 4 or 6 15000rpm disks (RAID-10) through a SCSI-RAID controller having 128mb of writeback cache (battery-backed)? The SCSI-RAID config would be a lot more expensive. I can't purchase both configs and test which one wil be faster, but if the NAS solution would be (almost) as fast as the SCSI-RAID solution, it would be cheaper and easier to maintain... About clustering: I know this can't be done by hooking multiple postmasters to one and the same NAS. This would result in data corruption, i've read... Kind regards, Alexander. ----- Original Message ----- From: "Jeff" <threshar@torgo.978.org> To: "Alexander Priem" <ap@cict.nl> Cc: <pgsql-performance@postgresql.org> Sent: Monday, October 20, 2003 2:20 PM Subject: Re: [PERFORM] PostgreSQL data on a NAS device ? > On Mon, 20 Oct 2003 09:12:35 +0200 > "Alexander Priem" <ap@cict.nl> wrote: > > > I am asking this because a NAS device is much cheaper to set up than a > > couple of SCSI disks. I would like to use a relatively cheap NAS > > device which uses four IDE drives (7.200 rpm), like the Dell > > PowerVault 725N. The disks themselves would be much slower than SCSI > > disks, I know, but a NAS device can be equipped with 3 Gb of memory, > > so this would make a very large disk cache, right? If this NAS would > > be dedicated only to PostgreSQL, would this be slower/faster than a > > SCSI RAID-10 setup of 6 disks? It would be much cheaper... > > > > The big concern would be the network connection, unless you are going > fiber. You need to use _AT LEAST_ gigabit. _at least_. If you do > go that route it'd be interesting to see bonnie results. And the > other thing - remember that just because you are running NAS doesn't > mean you can attach another machine running postgres and have a > cluster. (See archives for more info about this). > > I suppose it all boils down to your budget (I usually get to work with > a budget of $0). And I mentioned this in another post- If you don't mind > refurb disks(or slightly used) check out ebay - you can get scsi disks > by the truckload for cheap. > > > -- > Jeff Trout <jeff@jefftrout.com> > http://www.jefftrout.com/ > http://www.stuarthamm.net/
Alexander Priem wrote: > About clustering: I know this can't be done by hooking multiple postmasters > to one and the same NAS. This would result in data corruption, i've read... Only if they are reading same data directory. You can run 4 different data installations of postgresql, each one in its own directory and still put them on same device. Shridhar
Even better than the four-disk NAS I mentioned earlier is the following: Promise UltraTrak RM8000. This is a so-called SCSI-to-IDE RAID system. Basically it's a RAID setup of eight IDE disks, using a hardware RAID engine, that's connected to (in this case) the PostgreSQL server via a SCSI Ultra160 interface (!). So the server won't know any better than that there's a SCSI disk attached, but in reality it's a IDE RAID setup. It supports RAID levels 0, 1, 0+1, 5, 50 and JBOD and supports hot-swapping. Such a NAS config would cost around EUR 3700 (ex. VAT), using 8x40 Gb IDE disks (7200rpm). A SCSI RAID-10 setup using 6x18Gb (15000rpm) disks would cost around EUR 6000 (ex. VAT) so it's a big difference... Does anyone have experience with this NAS device or other "SCSI-to-IDE" RAID systems? Are they OK in terms of performance and reliability? Kind regards, Alexander.
Alexander Priem kirjutas E, 20.10.2003 kell 15:29: > Thanks for your reply, Jeff. > > If we are going to use a NAS device for storage, then it will be attached > through a gigabit ethernet connection. Fiber will not be an option, since > that would negate the savings we can make by using an IDE NAS device instead > of SCSI-RAID, fiber's pretty expensive, right? > > Using a NAS device (that is used only by PostgreSQL, so it's dedicated) with > 3Gb of RAM and four 7200 rpm IDE harddisks, connected using a gigabit > ethernet connection to the PostgreSQL server, do you think it will be a > match for a SCSI-RAID config using 4 or 6 15000rpm disks (RAID-10) through a > SCSI-RAID controller having 128mb of writeback cache (battery-backed)? I sincerely don't know. But if NAS is something that involves TCP (like iSCSI) then you should take a look at some network card and TCP/IP stack that offloads the protocol processing to the coprocessor on network card. (or just have some extra processors free to do the protocol processing ) --------------- Hannu
Alexander Priem kirjutas E, 20.10.2003 kell 16:04: > Even better than the four-disk NAS I mentioned earlier is the following: > > Promise UltraTrak RM8000. This is a so-called SCSI-to-IDE RAID system. While you are at it, you could also check out http://www.3ware.com/ I guess one of these with 10000 rpm 36GB SATA drivest would be pretty fast and possibly cheaper than SCSI raid. -------------- Hannu
Hello Alexander, On Mon, 2003-10-20 at 06:04, Alexander Priem wrote: > Even better than the four-disk NAS I mentioned earlier is the following: > > Promise UltraTrak RM8000. This is a so-called SCSI-to-IDE RAID system. > Basically it's a RAID setup of eight IDE disks, using a hardware RAID > engine, that's connected to (in this case) the PostgreSQL server via a SCSI > Ultra160 interface (!). So the server won't know any better than that > there's a SCSI disk attached, but in reality it's a IDE RAID setup. It > supports RAID levels 0, 1, 0+1, 5, 50 and JBOD and supports hot-swapping. We have a Promise FasTrak 4000 in our development server connected to 120 Gig western digital 8mb cache drives. Basically the fastest drives we could get for an ide configuration. This system works well, however there are a few things you need to consider. The biggest is that you have very limited control over your devices with the Promise controllers. The bios of the raid controller doesn't have many options on it. You basically plug everything together, and just hope it works. It usually does, but there have been times in the past that really gave us a scare. And we had a situation that in a hard poweroff ( UPS died ) we suffered complete corruptions of 2 of our 4 drives. Performance wise it is =okay= but definitely not on par with either our Megaraid elite 1650 controller or a solution I'm going to suggest to you later in this mail. Your biggest hit is going to be multiple simultaneous accesses. The controller and drives just can't keep up to it. Realistically with my experiences I cannot recommend this solution for a production machine, even with the budget constraints you have put forth. > > Such a NAS config would cost around EUR 3700 (ex. VAT), using 8x40 Gb IDE > disks (7200rpm). > > A SCSI RAID-10 setup using 6x18Gb (15000rpm) disks would cost around EUR > 6000 (ex. VAT) so it's a big difference... I'm not sure where you have your figures, but I would like to propose the following solution for you. for your boot device use either a single ide drive and keep an exact duplicate of the drive in the event of a drive failure, or use 2 drives and use software raid to mirror the two. In this manner you can spend approx $100 USD for each drive and no additional cost for your controller as you will use the motherboards IDE controller. For your postgresql partition or even /var use software raid on an adaptec 29320-R SCSI controller. ( http://www.adaptec.com/worldwide/product/proddetail.html?sess=no&language=English+US&prodkey=ASC-39320-R&cat=%2fTechnology%2fSCSI%2fUltra320+SCSI ) cost: $399 USD IF you bought it from adaptec Match this with 6 Seagate 10k 36G Cheetah U320 scsi drives: ( http://www.c-source.com/csource/newsite/ttechnote.asp?part_no=207024 ) for a cost of $189 USD per drive. If you have 6 of them it brings the total price for your drives to $1134 USD. Total cost for this would be approx $1633 before shipping costs. We use this configuration in our two file servers and have nothing but positive results. If you are totally unable to use software raid you could still buy 6 of those drives, and spend approx $900 USD on an LSI Megaraid 1650 controller. I really believe you'll find either of those options to be superior in terms of price for you. Sincerely, Will LaShell > Does anyone have experience with this NAS device or other "SCSI-to-IDE" RAID > systems? Are they OK in terms of performance and reliability? > Kind regards, > Alexander. > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings
Attachment
Hi Josh, Tom,
OK. As I understand it, vacuum does not release the space
used by the index file.
However, it should be able to reuse the space for indexing.
I have observed that during initial updates of the table,
the index file did not grow and was steady but it did not last long
and keeps growing afterwards. Vacuum/vacuum analyze did not help.
In all the update testing, vacuum analyze was done every 1 minute.
Tom, something caught your attention the last time.
Any insight so far ? Is it a bug ?
Thanks.
Gan
Tom Lane wrote:
Seum-Lim Gan <slgan@lucent.com> writes:
> vacuum verbose analyze dsperf_rda_or_key;
> INFO: vacuuming "scncraft.dsperf_rda_or_key"
> INFO: index "dsperf242_1105" now contains 300000 row versions in 12387 pages
> DETAIL: 3097702 index row versions were removed.
> 0 index pages have been deleted, 0 are currently reusable.
Hm, interesting that you deleted 90% of the entries and still had no
empty index pages at all. What was the pattern of your deletes and/or
updates with respect to this index's key?
> However, when I check the disk space usage, it has not changed.
It won't in any case. Plain VACUUM is designed for maintaining a
steady-state level of free space in tables and indexes, not for
returning major amounts of space to the OS. For that you need
more-invasive operations like VACUUM FULL or REINDEX.
regards, tom lane
At 12:04 pm -0700 2003/10/19, Josh Berkus wrote:
>Gan,
>
>> Oh, so in order to reclaim the disk space, we must run
>> reindex or vacuum full ?
>> This will lock out the table and we won't be able to do anything.
>> Looks like this is a problem. It means we cannot use it for
>> 24x7 operations without having to stop the process and do the vacuum full
>> and reindex. Is there anything down the road that these operations
>> will not lock out the table ?
>
>I doubt it; the amount of page-shuffling required to reclaim 90% of the space
>in an index for a table that has been mostly cleared is substantial, and
>would prevent concurrent access.
>
>Also, you seem to have set up an impossible situation for VACUUM. If I'm
>reading your statistics right, you have a large number of threads accessing
>most of the data 100% of the time, preventing VACUUM from cleaning up the
>pages. This is not, in my experience, a realistic test case ... there are
>peak and idle periods for all databases, even webservers that have been
>slashdotted.
>
>--
>Josh Berkus
>Aglio Database Solutions
>San Francisco
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
+--------------------------------------------------------+
| Seum-Lim GAN email : slgan@lucent.com |
| Lucent Technologies |
| 2000 N. Naperville Road, 6B-403F tel : (630)-713-6665 |
| Naperville, IL 60566, USA. fax : (630)-713-7272 |
| web : http://inuweb.ih.lucent.com/~slgan |
+--------------------------------------------------------+
On Mon, 20 Oct 2003, Alexander Priem wrote: > Hi all, > > Does anyone have any experience with putting PostgreSQL data on a NAS > device? > > I am asking this because a NAS device is much cheaper to set up than a > couple of SCSI disks. I would like to use a relatively cheap NAS device > which uses four IDE drives (7.200 rpm), like the Dell PowerVault 725N. The > disks themselves would be much slower than SCSI disks, I know, but a NAS > device can be equipped with 3 Gb of memory, so this would make a very large > disk cache, right? If this NAS would be dedicated only to PostgreSQL, would > this be slower/faster than a SCSI RAID-10 setup of 6 disks? It would be much > cheaper... > > Any advice on this would be appreciated :) How important is this data? With a local SCSI RAID controller and SCSI drives, you can pull the power cord out the back of the machine during 1000 transactions, and your database will come back up in a coherent state. If you need that kind of reliability, then you'll likely want to use local SCSI drives. Note that you should test your setup to be sure, i.e. pull the network cord and see how the machine recovers (if the machine recovers). Running storage on a NAS is a bit of a tightrope act with your data, as is using IDE drives with write cache enabled. But depending on your application, using NAS may be a good solution. So, what's this database gonna be used for?
Hi Tom, Josh, We tried one more thing: with the table not being updated at all and we did vacuum. Each time a vacuum is done, the index file becomes bigger. This is probably what is contributing to the index file growing as well. Thanks. Gan At 11:04 am -0500 2003/10/20, Seum-Lim Gan wrote: >Hi Josh, Tom, > >OK. As I understand it, vacuum does not release the space >used by the index file. >However, it should be able to reuse the space for indexing. > >I have observed that during initial updates of the table, >the index file did not grow and was steady but it did not last long >and keeps growing afterwards. Vacuum/vacuum analyze did not help. > >In all the update testing, vacuum analyze was done every 1 minute. > >Tom, something caught your attention the last time. > >Any insight so far ? Is it a bug ? > >Thanks. > >Gan > >Tom Lane wrote: > >Seum-Lim Gan <slgan@lucent.com> writes: >> vacuum verbose analyze dsperf_rda_or_key; >> INFO: vacuuming "scncraft.dsperf_rda_or_key" >> INFO: index "dsperf242_1105" now contains 300000 row versions in >>12387 pages >> DETAIL: 3097702 index row versions were removed. >> 0 index pages have been deleted, 0 are currently reusable. > >Hm, interesting that you deleted 90% of the entries and still had no >empty index pages at all. What was the pattern of your deletes and/or >updates with respect to this index's key? > >> However, when I check the disk space usage, it has not changed. > >It won't in any case. Plain VACUUM is designed for maintaining a >steady-state level of free space in tables and indexes, not for >returning major amounts of space to the OS. For that you need >more-invasive operations like VACUUM FULL or REINDEX. > > regards, tom lane > >At 12:04 pm -0700 2003/10/19, Josh Berkus wrote: >>Gan, >> >>> Oh, so in order to reclaim the disk space, we must run >>> reindex or vacuum full ? >>> This will lock out the table and we won't be able to do anything. >>> Looks like this is a problem. It means we cannot use it for >>> 24x7 operations without having to stop the process and do the vacuum full >>> and reindex. Is there anything down the road that these operations >>> will not lock out the table ? >> >>I doubt it; the amount of page-shuffling required to reclaim 90% of the space >>in an index for a table that has been mostly cleared is substantial, and >>would prevent concurrent access. >> >>Also, you seem to have set up an impossible situation for VACUUM. If I'm >>reading your statistics right, you have a large number of threads accessing >>most of the data 100% of the time, preventing VACUUM from cleaning up the >>pages. This is not, in my experience, a realistic test case ... there are >>peak and idle periods for all databases, even webservers that have been >>slashdotted. >> >>-- >>Josh Berkus >>Aglio Database Solutions >>San Francisco >> >>---------------------------(end of broadcast)--------------------------- >>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org > > >-- >+--------------------------------------------------------+ >| Seum-Lim GAN email : slgan@lucent.com | >| Lucent Technologies | >| 2000 N. Naperville Road, 6B-403F tel : (630)-713-6665 | >| Naperville, IL 60566, USA. fax : (630)-713-7272 | >| web : http://inuweb.ih.lucent.com/~slgan | >+--------------------------------------------------------+ > >---------------------------(end of broadcast)--------------------------- >TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html -- +--------------------------------------------------------+ | Seum-Lim GAN email : slgan@lucent.com | | Lucent Technologies | | 2000 N. Naperville Road, 6B-403F tel : (630)-713-6665 | | Naperville, IL 60566, USA. fax : (630)-713-7272 | | web : http://inuweb.ih.lucent.com/~slgan | +--------------------------------------------------------+
Seum-Lim Gan <slgan@lucent.com> writes:
> We tried one more thing: with the table not being updated
> at all and we did vacuum. Each time a vacuum is done,
> the index file becomes bigger.
It is not possible for plain vacuum to make the index bigger.
VACUUM FULL possibly could make the index bigger, since it has to
transiently create duplicate index entries for every row it moves.
If you want any really useful comments on your situation, you're going
to have to offer considerably more detail than you have done so far ---
preferably, a test case that lets someone else reproduce your results.
So far, all we can do is guess on the basis of very incomplete
information. When you aren't even bothering to mention whether a vacuum
is FULL or not, I have to wonder whether I have any realistic picture of
what's going on.
regards, tom lane
Seum-Lim Gan <slgan@lucent.com> writes:
> [ successive outputs from VACUUM ANALYZE ]
FWIW, I don't think your problem is really index bloat at all, it's
more like too-many-dead-rows bloat. Note that the number of "dead row
versions" is climbing steadily from run to run:
> DETAIL: 101802 dead row versions cannot be removed yet.
> DETAIL: 110900 dead row versions cannot be removed yet.
> DETAIL: 753064 dead row versions cannot be removed yet.
> DETAIL: 765328 dead row versions cannot be removed yet.
It's hardly the index's fault that it's growing, when it has to keep
track of an ever-increasing number of rows.
The real question is what you're doing that requires the system to keep
hold of these dead rows instead of recycling them. I suspect you have
a client process somewhere that is holding an open transaction for a
long time ... probably not doing anything, just sitting there with an
unclosed BEGIN ...
regards, tom lane
On Mon, Oct 20, 2003 at 05:42:52PM -0400, Tom Lane wrote:
> hold of these dead rows instead of recycling them. I suspect you have
> a client process somewhere that is holding an open transaction for a
> long time ... probably not doing anything, just sitting there with an
> unclosed BEGIN ...
Which could be because you're doing something nasty with one of the
"autocommit=off" clients. Most of the client libraries implement
this by doing "commit;begin;" at every commit. This means you have
way more idle in transaction connections than you think. Look in
pg_stat_activity, assuming you've turned on query echoing.
A
--
----
Andrew Sullivan 204-4141 Yonge Street
Afilias Canada Toronto, Ontario Canada
<andrew@libertyrms.info> M2P 2A8
+1 416 646 3304 x110
Andrew Sullivan wrote: > On Mon, Oct 20, 2003 at 05:42:52PM -0400, Tom Lane wrote: > > >>hold of these dead rows instead of recycling them. I suspect you have >>a client process somewhere that is holding an open transaction for a >>long time ... probably not doing anything, just sitting there with an >>unclosed BEGIN ... > > > Which could be because you're doing something nasty with one of the > "autocommit=off" clients. Most of the client libraries implement > this by doing "commit;begin;" at every commit. This means you have > way more idle in transaction connections than you think. Look in > pg_stat_activity, assuming you've turned on query echoing. Or is enough do a ps -eafwww | grep post to see the state of the connections Regards Gaetano Mendola
The machine is going to be used for a pretty large database (well over 100 tables with some of them containing over a million records from the start, number of tables and records will grow (much?) larger in the future). This database is going to be used by a pretty large number of employees. The number of concurrent users will vary between 1 - 100 or so, depending on the time of day etc. This will be a database containing client and supplier data as well as product descriptions and prices/ingredients/labels/brands etc. Database use will include lots of SELECTS but also lots of INSERTS/UPDATES, i.e. the database will be pretty active during bussiness hours... I think you (Scott and Will) are right when you say that NAS devices are not ideal for this kind of thing. I have been thinking about the hardware configuration for this machine for some time now (and had a lot of hints through this list already) and decided to go for a SCSI RAID config after all. The extra costs will be worth it :) The machine I have in mind now is like this : Dell PowerEdge 1750 machine with Intel Xeon CPU at 3 GHz and 4 GB of RAM. This machine will contain a PERC4/Di RAID controller with 128MB of battery backed cache memory. The O/S and logfiles will be placed on a RAID-1 setup of two 36Gb SCSI-U320 drives (15.000rpm). Database data will be placed on a Dell PowerVault 220S rack-module containing six 36Gb SCSI-U320 drives (15.000rpm) in a RAID-10 setup. This PowerVault will be connected to the DB server via a SCSI cable... This machine will be a bit more expensive than I thought at first (it's going to be about EUR 14.000, but that's including 3 years of on-site support from Dell (24x7, 4-hour response) and peripherals like UPS etc... Do you think this machine wil be OK for this task? Thanks for your help so far :) Kind regards, Alexander Priem.
Alexander Priem wrote: > Dell PowerEdge 1750 machine with Intel Xeon CPU at 3 GHz and 4 GB of RAM. > This machine will contain a PERC4/Di RAID controller with 128MB of battery > backed cache memory. The O/S and logfiles will be placed on a RAID-1 setup > of two 36Gb SCSI-U320 drives (15.000rpm). Database data will be placed on a > Dell PowerVault 220S rack-module containing six 36Gb SCSI-U320 drives > (15.000rpm) in a RAID-10 setup. This PowerVault will be connected to the DB > server via a SCSI cable... > This machine will be a bit more expensive than I thought at first (it's > going to be about EUR 14.000, but that's including 3 years of on-site > support from Dell (24x7, 4-hour response) and peripherals like UPS etc... Check opteron as well.. I don't know much about european resellers. IBM sells eserver 325 which has opterons. Apparently they scale much better at higher load. Of course pricing,availability and support are most important. http://theregister.co.uk/content/61/33378.html http://www.pc.ibm.com/us/eserver/opteron/325/ Any concrete benchmarks for postgresql w.r.t xeons and opterons? A collection would be nice to have..:-) Shridhar
I have considered Opteron, yes. But I think there are too many uncertainties, like : * It's a new CPU that has not proven itself yet. * I don't think I can buy directly from IBM (according to their site), so how about support (24x7) ? This will be very important to our client. * I need to install and configure a 64bit Linux flavour which I don't know (yet) Any suggestions about the usability of the system I described before? Here is the description again: Dell PowerEdge 1750 machine with Intel Xeon CPU at 3 GHz and 4 GB of RAM. This machine will contain a PERC4/Di RAID controller with 128MB of battery backed cache memory. The O/S and logfiles will be placed on a RAID-1 setup of two 36Gb SCSI-U320 drives (15.000rpm). Database data will be placed on a Dell PowerVault 220S rack-module containing six 36Gb SCSI-U320 drives (15.000rpm) in a RAID-10 setup. This PowerVault will be connected to the DB server via a SCSI cable... I have never worked with a XEON CPU before. Does anyone know how it performs running PostgreSQL 7.3.4 / 7.4 on RedHat 9 ? Is it faster than a Pentium 4? I believe the main difference is cache memory, right? Aside from cache mem, it's basically a Pentium 4, or am I wrong? Kind regards, Alexander.
Alexander Priem wrote: > I have considered Opteron, yes. But I think there are too many > uncertainties, like : > > * It's a new CPU that has not proven itself yet. > * I don't think I can buy directly from IBM (according to their site), so > how about support (24x7) ? This will be very important to our client. > * I need to install and configure a 64bit Linux flavour which I don't know > (yet) See http://www.monarchcomputer.com/ they custom build operton systems and preload them with Linux. You don't pay the Microsoft tax. -- Until later, Geoffrey esoteric@3times25.net
On Tue, 21 Oct 2003, Alexander Priem wrote: > The machine is going to be used for a pretty large database (well over 100 > tables with some of them containing over a million records from the start, > number of tables and records will grow (much?) larger in the future). This > database is going to be used by a pretty large number of employees. The > number of concurrent users will vary between 1 - 100 or so, depending on the > time of day etc. This will be a database containing client and supplier data > as well as product descriptions and prices/ingredients/labels/brands etc. > Database use will include lots of SELECTS but also lots of INSERTS/UPDATES, > i.e. the database will be pretty active during bussiness hours... > > I think you (Scott and Will) are right when you say that NAS devices are not > ideal for this kind of thing. I have been thinking about the hardware > configuration for this machine for some time now (and had a lot of hints > through this list already) and decided to go for a SCSI RAID config after > all. The extra costs will be worth it :) > > The machine I have in mind now is like this : > > Dell PowerEdge 1750 machine with Intel Xeon CPU at 3 GHz and 4 GB of RAM. > This machine will contain a PERC4/Di RAID controller with 128MB of battery > backed cache memory. The O/S and logfiles will be placed on a RAID-1 setup > of two 36Gb SCSI-U320 drives (15.000rpm). Database data will be placed on a > Dell PowerVault 220S rack-module containing six 36Gb SCSI-U320 drives > (15.000rpm) in a RAID-10 setup. This PowerVault will be connected to the DB > server via a SCSI cable... Funny, we're looking at the same basic type of system here, but with a Perc3/CI controller. We have a local supplier who gives us machines with a 3 year warranty and looks to be $1,000 to $2,000 lower than the Dell. We're just going to run two 73 Gig drives in a RAID1 to start with, with battery backed RAM. So that brings up my question, which is better, the Perc4 or Perc3 controllers, and what's the difference between them? I find Dell's tendency to hide other people's hardware behind their own model numbers mildly bothersome, as it makes it hard to comparison shop.
Hi Tom, 1.) OK. We have narrowed it down. We did a few (like 5 to 8 times) vacuum analyze <tablename> (no full), the pg_statistics relfilenode grew. There was no database operation when we did this, no other client connections except the one that does the vacuum. If we do plain simple "vacuum <tablename>" (again no full), we see pg_statistics_relid_att_index relfilenode grew instead of pg_statistics. So, overtime, these files will grow if we do vacuum. Are these expected ? The question now is, if we are not doing anything to the database, why would they grow after a few vacuums ? 2.) The other problem we have with > DETAIL: 101802 dead row versions cannot be removed yet. > DETAIL: 110900 dead row versions cannot be removed yet. > DETAIL: 753064 dead row versions cannot be removed yet. > DETAIL: 765328 dead row versions cannot be removed yet. We will collect more data and see what we can get from the the process. Offhand, the process is connecting to the database through ODBC and we don't use any BEGIN in our updates, just doing plain UPDATE repeatedly with different keys randomly. The database is defaulted to autocommit=true in postgresql.conf. Thanks. Gan At 5:25 pm -0400 2003/10/20, Tom Lane wrote: >Seum-Lim Gan <slgan@lucent.com> writes: >> We tried one more thing: with the table not being updated >> at all and we did vacuum. Each time a vacuum is done, >> the index file becomes bigger. > >It is not possible for plain vacuum to make the index bigger. > >VACUUM FULL possibly could make the index bigger, since it has to >transiently create duplicate index entries for every row it moves. > >If you want any really useful comments on your situation, you're going >to have to offer considerably more detail than you have done so far --- >preferably, a test case that lets someone else reproduce your results. >So far, all we can do is guess on the basis of very incomplete >information. When you aren't even bothering to mention whether a vacuum >is FULL or not, I have to wonder whether I have any realistic picture of >what's going on. > > regards, tom lane > >---------------------------(end of broadcast)--------------------------- >TIP 4: Don't 'kill -9' the postmaster -- +--------------------------------------------------------+ | Seum-Lim GAN email : slgan@lucent.com | | Lucent Technologies | | 2000 N. Naperville Road, 6B-403F tel : (630)-713-6665 | | Naperville, IL 60566, USA. fax : (630)-713-7272 | | web : http://inuweb.ih.lucent.com/~slgan | +--------------------------------------------------------+
Seum-Lim Gan <slgan@lucent.com> writes:
> We did a few (like 5 to 8 times) vacuum analyze <tablename> (no full), the
> pg_statistics relfilenode grew.
Well, sure. ANALYZE puts new rows into pg_statistic, and obsoletes old
ones. You need to vacuum pg_statistic every so often (not to mention
the other system catalogs).
> If we do plain simple "vacuum <tablename>" (again no full), we see
> pg_statistics_relid_att_index relfilenode grew instead of
> pg_statistics.
Don't think I believe that. Plain vacuum won't touch pg_statistic
at all (unless it's the target table of course). I'd expect ANALYZE
to make both the stats table and its index grow, though.
regards, tom lane
On Tue, 2003-10-21 at 08:40, scott.marlowe wrote: <SNIP> > So that brings up my question, which is better, the Perc4 or Perc3 > controllers, and what's the difference between them? I find Dell's > tendency to hide other people's hardware behind their own model numbers > mildly bothersome, as it makes it hard to comparison shop. Perc4 has n LSI 1030 chip http://docs.us.dell.com/docs/storage/perc4di/en/ug/features.htm Perc3 depending on the model can be a couple of things but I think they are all U160 controllers and not U320 <SNIP> Will
Attachment
On 21 Oct 2003, Will LaShell wrote: > On Tue, 2003-10-21 at 08:40, scott.marlowe wrote: > <SNIP> > > So that brings up my question, which is better, the Perc4 or Perc3 > > controllers, and what's the difference between them? I find Dell's > > tendency to hide other people's hardware behind their own model numbers > > mildly bothersome, as it makes it hard to comparison shop. > > Perc4 has n LSI 1030 chip > http://docs.us.dell.com/docs/storage/perc4di/en/ug/features.htm > > > Perc3 > depending on the model can be a couple of things but I think they are > all U160 controllers and not U320 Thanks. I googled around and found this page: http://www.domsch.com/linux/ Which says what each model is. It looks like the "RAID" controller they wanna charge me for is about $500 or so, so I'm guessing it's the medium range Elite 1600 type controller, i.e. U160, which is plenty for the machine / drive number we'll be using. Has anyone played around with the latest ones to get a feel for them? I want a battery backed controller that runs well under linux and also BSD that isn't gonna break the bank. I'd heard bad stories about the performance of the Adaptec RAID controllers, but it seems the newer ones aren't bad from what I've found googling.
On Tue, 2003-10-21 at 13:36, scott.marlowe wrote: > On 21 Oct 2003, Will LaShell wrote: > > > On Tue, 2003-10-21 at 08:40, scott.marlowe wrote: > > <SNIP> > > > So that brings up my question, which is better, the Perc4 or Perc3 > > > controllers, and what's the difference between them? I find Dell's > > > tendency to hide other people's hardware behind their own model numbers > > > mildly bothersome, as it makes it hard to comparison shop. > > > > Perc4 has n LSI 1030 chip > > http://docs.us.dell.com/docs/storage/perc4di/en/ug/features.htm > > > > > > Perc3 > > depending on the model can be a couple of things but I think they are > > all U160 controllers and not U320 > > Thanks. I googled around and found this page: > > http://www.domsch.com/linux/ > > Which says what each model is. It looks like the "RAID" controller they > wanna charge me for is about $500 or so, so I'm guessing it's the medium > range Elite 1600 type controller, i.e. U160, which is plenty for the > machine / drive number we'll be using. > > Has anyone played around with the latest ones to get a feel for them? I > want a battery backed controller that runs well under linux and also BSD > that isn't gonna break the bank. I'd heard bad stories about the > performance of the Adaptec RAID controllers, but it seems the newer ones > aren't bad from what I've found googling. We own 2 Elite 1650 and we love them. It would be nice to have had U320 capable controllers but the cards are completely reliable. I recommend the LSI controllers to everyone because I've never had a problem with them.
Attachment
So I guess the PERC4/Di RAID controller is pretty good. It seems that RedHat9 supports it out-of-the-box (driver 1.18f), but I gather from the sites mentioned before that upgrading this driver to 1.18i would be better...
Heya On Wed, 2003-10-22 at 01:13, Alexander Priem wrote: > So I guess the PERC4/Di RAID controller is pretty good. It seems that > RedHat9 supports it out-of-the-box (driver 1.18f), but I gather from the > sites mentioned before that upgrading this driver to 1.18i would be > better... Actually upgrading to the Megaraid_2 driver would be even better. There are a -ton- of performance enhancements with it. Depending on your performance needs and testing capabilities, I would highly recommend trying it out. Will
Attachment
I have been searching (www.lsil.com) for this megaraid_2 driver you mentioned. What kind of MegaRaid card does the Perc4/Di match? Elite1600? Elite1650? I picked Elite1600 and the latest driver I found was version 2.05.00. Is this one OK for RedHat 9? The README file present only mentions RedHat8... Kind regards, Alexander.
Hi guys, This basically continues the other thread about the PERC4 RAID controller, but since it is a bit off-topic I thought to start another thread. Thanks for all your help so far :) Earlier today I read about the newly released RedHat Enterprise Linux ES version 3. This version should include out-of-the-box megaraid_2 drivers, so it would support the Dell PERC4/Di RAID controller. However, it is very much more expensive than RedHat Linux 9. RH Linux 9 is free and the Enterpise ES edition will cost between 400 and several 1.000's of dollars, depending on the support you want to go with it. Do any of you guys have experience with the previous version of Enterprise Linux (that would be version 2.1) or even better, are any of you already using version 3? Would you recommend this over RedHat Linux 9? I think that with RH Linux 9 it would be easier to get all the latest versions of components I need (RPMs for PostgreSQL, Apache, Samba etc.), while my guess would be that Enterprise Linux would be more difficult to upgrade... Also, I cannot find any list of packages included in Enterprise Linux 2.1 / 3. Does anyone know if PostgreSQL is included and if so, what version? Kind regards, Alexander Priem.
On Thu, 2003-10-23 at 01:40, Alexander Priem wrote: > Hi guys, > > This basically continues the other thread about the PERC4 RAID controller, > but since it is a bit off-topic I thought to start another thread. Thanks > for all your help so far :) > > Earlier today I read about the newly released RedHat Enterprise Linux ES > version 3. This version should include out-of-the-box megaraid_2 drivers, so > it would support the Dell PERC4/Di RAID controller. > > However, it is very much more expensive than RedHat Linux 9. RH Linux 9 is > free and the Enterpise ES edition will cost between 400 and several 1.000's > of dollars, depending on the support you want to go with it. > > Do any of you guys have experience with the previous version of Enterprise > Linux (that would be version 2.1) or even better, are any of you already > using version 3? > > Would you recommend this over RedHat Linux 9? I think that with RH Linux 9 > it would be easier to get all the latest versions of components I need (RPMs > for PostgreSQL, Apache, Samba etc.), while my guess would be that Enterprise > Linux would be more difficult to upgrade... The reason to get RHEL over RH9 or the upcoming Fedora releases is for stability. They have a -much- longer stability period, release cycle, and support lifetime. You get RHEL if you want a distribution that you can get commercial support for, install the server and then not touch it. For production machines of this nature you'll pretty much never have the latest and greatest packages. Instead you'll have the most completely stable packages. The two distribution types are really apples and oranges. They are both fruit ( they are both linux distros ) but they sure taste different. > Also, I cannot find any list of packages included in Enterprise Linux 2.1 / > 3. Does anyone know if PostgreSQL is included and if so, what version? You have two options as I understand it for PG under RHEL. You can install the PG source from Postgres themselves, or you can use the Postgresql Red Hat Edition. Bruce I think can give you more information on this product. http://sources.redhat.com/rhdb/index.html This is the link to it. > > Kind regards, > Alexander Priem. Hope this helps, Will
Attachment
On Thu, 23 Oct 2003, Alexander Priem wrote: > I have been searching (www.lsil.com) for this megaraid_2 driver you > mentioned. > > What kind of MegaRaid card does the Perc4/Di match? Elite1600? Elite1650? > > I picked Elite1600 and the latest driver I found was version 2.05.00. Is > this one OK for RedHat 9? The README file present only mentions RedHat8... I would guess it's a MegaRaid320-2 card, listed here: http://www.lsilogic.com/products/stor_prod/raid/3202.html Since the Elite1600/1650 seem to be U160 cards and the Perc/4Di would seem to be listed as a U320 card at this page: http://www.domsch.com/linux/
On Thu, 2003-10-23 at 11:27, Will LaShell wrote: > > Also, I cannot find any list of packages included in Enterprise Linux > 2.1 / > > 3. Does anyone know if PostgreSQL is included and if so, what version? > > You have two options as I understand it for PG under RHEL. You can > install the PG source from Postgres themselves, or you can use the > Postgresql Red Hat Edition. Bruce I think can give you more information > on this product. http://sources.redhat.com/rhdb/index.html This is the > link to it. > Bruce works for SRA, not Red Hat, so he's probably not your best option to talk to on PRHE... While there are Red Hat employees floating around these lists, I'd first suggest reading over the website and then either emailing the PRHE lists or one of it's team members depending on the specifics of any questions. Robert Treat -- Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
On Thu, 2003-10-23 at 08:44, Robert Treat wrote: > On Thu, 2003-10-23 at 11:27, Will LaShell wrote: > > > Also, I cannot find any list of packages included in Enterprise Linux > > 2.1 / > > > 3. Does anyone know if PostgreSQL is included and if so, what version? > > > > You have two options as I understand it for PG under RHEL. You can > > install the PG source from Postgres themselves, or you can use the > > Postgresql Red Hat Edition. Bruce I think can give you more information > > on this product. http://sources.redhat.com/rhdb/index.html This is the > > link to it. > > > > Bruce works for SRA, not Red Hat, so he's probably not your best option > to talk to on PRHE... While there are Red Hat employees floating around Gah that's right. *beats self* > these lists, I'd first suggest reading over the website and then either > emailing the PRHE lists or one of it's team members depending on the > specifics of any questions. Don't forget you can always call the RedHat sales people as well. They usually have good product knowledge especially since you are talking about the Advanced Server lines. > Robert Treat > -- > Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL Will
Attachment
Robert Treat wrote: > On Thu, 2003-10-23 at 11:27, Will LaShell wrote: > > > Also, I cannot find any list of packages included in Enterprise Linux > > 2.1 / > > > 3. Does anyone know if PostgreSQL is included and if so, what version? > > > > You have two options as I understand it for PG under RHEL. You can > > install the PG source from Postgres themselves, or you can use the > > Postgresql Red Hat Edition. Bruce I think can give you more information > > on this product. http://sources.redhat.com/rhdb/index.html This is the > > link to it. > > > > Bruce works for SRA, not Red Hat, so he's probably not your best option > to talk to on PRHE... While there are Red Hat employees floating around > these lists, I'd first suggest reading over the website and then either > emailing the PRHE lists or one of it's team members depending on the > specifics of any questions. Way off topic, but let's do Red Hat a favor for employing PostgreSQL folks --- here is a nice URL I read yesterday on the topic: http://news.com.com/2100-7344-5094774.html?tag=nl -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
> I have never worked with a XEON CPU before. Does anyone know how it performs > running PostgreSQL 7.3.4 / 7.4 on RedHat 9 ? Is it faster than a Pentium 4? > I believe the main difference is cache memory, right? Aside from cache mem, > it's basically a Pentium 4, or am I wrong? Well, see the problem is of course, there's so many flavors of P4s and Xeons that it's hard to tell which is faster unless you specify the exact model. And even then, it would depend on the workload. Would a Xeon/3GHz/2MB L3/400FSB be faster than a P4C/3GHz/800FSB? No idea as no one has complete number breakdowns on these comparisons. Oh yeah, you could get a big round number that says on SPEC or something one CPU is faster than the other but whether that's faster for Postgres and your PG app is a totally different story. That in mind, I wouldn't worry about it. The CPU is probably plenty fast for what you need to do. I'd look into two things in the server: memory and CPU expandability. I know you already plan on 4GB but you may need even more in the future. Few things can dramatically improve performance more than moving disk access to disk cache. And if there's a 2nd socket where you can pop another CPU in, that would leave you extra room if your server becomes CPU limited.