Re: [PERFORM] Choosing a filesystem

[Matthew Wakeling]

On Thu, 11 Sep 2008, Greg Smith wrote:
If you want your database to perform well on writes, the first thing you do 
is select a disk controller that performs well, has a well-known stable 
driver for your OS, has a reasonably large cache (>=256MB), and has a battery 
backup on it.


Greg, it might be worth you listing a few good RAID controllers. It's 
almost an FAQ. From what I'm hearing, this Intel one doesn't sound like it 
would be on the list.


Matthew

--
Riker: Our memory pathways have become accustomed to your sensory input.
Data:  I understand - I'm fond of you too, Commander. And you too Counsellor

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Choosing a filesystem

[Guillaume Cottenceau]

Craig James  writes:

> The performance improvement of a BB cache is amazing.

Could some of you share the insight on why this is the case? I
cannot find much information on it on wikipedia, for example.
Even http://linuxfinances.info/info/diskusage.html doesn't
explain *why*.

Out of the blue, is it just because when postgresql fsync's after
a write, on a normal system the write has to really happen on
disk and waiting for it to be complete, whereas with BBU cache
the fsync is almost immediate because the write cache actually
replaces the "really on disk" write?

-- 
Guillaume Cottenceau, MNC Mobile News Channel SA, an Alcatel-Lucent Company
Av. de la Gare 10, 1003 Lausanne, Switzerland - direct +41 21 317 50 36

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Choosing a filesystem

[Greg Smith]

On Fri, 12 Sep 2008, Matthew Wakeling wrote:

Greg, it might be worth you listing a few good RAID controllers. It's almost 
an FAQ.


I started doing that at the end of 
http://wiki.postgresql.org/wiki/SCSI_vs._IDE/SATA_Disks , that still needs 
some work.  What I do periodically is sweep through old messages here that 
have useful FAQ text and dump them into the appropriate part of 
http://wiki.postgresql.org/wiki/Performance_Optimization


--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Effects of setting linux block device readahead size

[James Mansion]

Scott Carey wrote:
Consumer drives will often read-ahead much more than server drives 
optimized for i/o per second.

...
The Linux readahead setting is _definitely_ in the kernel, definitely 
uses and fills the page cache, and from what I can gather, simply 
issues extra I/O's to the hardware beyond the last one requested by an 
app in certain situations.  It does not make your I/O request larger, 
it just queues an extra I/O following your request.
So ... fiddling with settings in Linux is going to force read-ahead, but 
the read-ahead data will hit the controller cache and the system buffers.


And the drives use their caches for cyclinder caching implicitly (maybe 
the SATA drives appear to preread more because the storage density per 
cylinder is higher?)..


But is there any way for an OS or application to (portably) ask SATA, 
SAS or SCSI drives to read ahead more (or less) than their default and 
NOT return the data to the controller?


I've never heard of such a thing, but I'm no expert in the command sets 
for any of this stuff.


James



On Thu, Sep 11, 2008 at 12:54 PM, James Mansion 
<[EMAIL PROTECTED] > 
wrote:


Greg Smith wrote:

The point I was trying to make there is that even under
impossibly optimal circumstances, you'd be hard pressed to
blow out the disk's read cache with seek-dominated data even
if you read a lot at each seek point.  That idea didn't make
it from my head into writing very well though.

Isn't there a bigger danger in blowing out the cache on the
controller and causing premature pageout of its dirty pages?

If you could get the readahead to work on the drive and not return
data to the controller, that might be dandy, but I'm sceptical.

James



-- 
Sent via pgsql-performance mailing list

(pgsql-performance@postgresql.org
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance





--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Choosing a filesystem

[Greg Smith]

On Fri, 12 Sep 2008, Guillaume Cottenceau wrote:

Out of the blue, is it just because when postgresql fsync's after a 
write, on a normal system the write has to really happen on disk and 
waiting for it to be complete, whereas with BBU cache the fsync is 
almost immediate because the write cache actually replaces the "really 
on disk" write?


That's the main thing, and nothing else you can do will accelerate that. 
Without a useful write cache (which usually means RAM with a BBU), you'll 
at best get about 100-200 write transactions per second for any one 
client, and something like 500/second even with lots of clients (queued up 
transaction fsyncs do get combined).  Those numbers increase to several 
thousand per second the minute there's a good caching controller in the 
mix.


You might say "but I don't write that heavily, so what?"  Even if the 
write volume is low enough that the disk can keep up, there's still 
latency.  A person who is writing transactions is going to be delayed a 
few milliseconds after each commit, which drags some types of data loading 
to a crawl.  Also, without a cache in places mixes of fsync'd writes and 
reads can behave badly, with readers getting stuck behind writers much 
more often than in the cached situation.


The final factor is that additional layers of cache usually help improve 
physical grouping of blocks into ordered sections to lower seek overhead. 
The OS is supposed to be doing that for you, but a cache closer to the 
drives themselves helps smooth things out when the OS dumps a large block 
of data out for some reason.  The classic example in PostgreSQL land, 
particularly before 8.3, was when a checkpoint happens.


--
* Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD

--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Re: [PERFORM] Choosing a filesystem

[Merlin Moncure]

On Fri, Sep 12, 2008 at 5:11 AM, Greg Smith <[EMAIL PROTECTED]> wrote:
> On Fri, 12 Sep 2008, Guillaume Cottenceau wrote:
>
> That's the main thing, and nothing else you can do will accelerate that.
> Without a useful write cache (which usually means RAM with a BBU), you'll at
> best get about 100-200 write transactions per second for any one client, and
> something like 500/second even with lots of clients (queued up transaction
> fsyncs do get combined).  Those numbers increase to several thousand per
> second the minute there's a good caching controller in the mix.

While this is correct, if heavy writing is sustained, especially on
large databases, you will eventually outrun the write cache on the
controller and things will start to degrade towards the slow case.  So
it's fairer to say that caching raid controllers burst up to several
thousand per second, with a sustained write rate somewhat better than
write-through but much worse than the burst rate.

How fast things degrade from the burst rate depends on certain
factors...how big the database is relative to the o/s read cache in
the controller write cache, and how random the i/o is generally.  One
thing raid controllers are great at is smoothing bursty i/o during
checkpoints for example.

Unfortunately when you outrun cache on raid controllers the behavior
is not always very pleasant...in at least one case I've experienced
(perc 5/i) when the cache fills up the card decides to clear it before
continuing.  This means that if fsync is on, you get unpredictable
random freezing pauses while the cache is clearing.

merlin

-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] Postgres Performance on CPU limited Platforms

[George McCollister]

I'm trying to optimize postgres performance on a headless solid state
hardware platform (no fans or disks). I have the database stored on a
USB 2.0 flash drive (hdparm benchmarks reads at 10 MB/s). Performance is
limited by the 533Mhz CPU.

Hardware:
IXP425 XScale (big endian) 533Mhz 64MB RAM
USB 2.0 Flash Drive
 
Software:
Linux 2.6.21.4
postgres 8.2.5

I created a fresh database using initdb, then added one table.

Here is the create table:
CREATE TABLE archivetbl
(
  "DateTime" timestamp without time zone,
  "StationNum" smallint,
  "DeviceDateTime" timestamp without time zone,
  "DeviceNum" smallint,
  "Tagname" character(64),
  "Value" double precision,
  "Online" boolean
)
WITH (OIDS=FALSE);
ALTER TABLE archivetbl OWNER TO novatech;

I've attached my postgresql.conf

I populated the table with 38098 rows.

I'm doing this simple query:
select * from archivetbl;
 
It takes 79 seconds to complete the query (when postgres is compiled
with -O2). I'm running the query from pgadmin3 over TCP/IP.

top shows CPU usage is at 100% with 95% being in userspace. oprofile
shows memset is using 58% of the CPU cycles!

CPU: ARM/XScale PMU2, speed 0 MHz (estimated)
Counted CPU_CYCLES events (clock cycles counter) with a unit mask of
0x00 (No unit mask) count 10
samples  %app name symbol name
288445   57.9263  libc-2.5.so  memset
33273 6.6820  vmlinux  default_idle
27910 5.6050  vmlinux  cpu_idle
12611 2.5326  vmlinux  schedule
8803  1.7678  libc-2.5.so  __printf_fp
7448  1.4957  postgres dopr
6404  1.2861  libc-2.5.so  vfprintf
6398  1.2849  oprofiled(no symbols)
4992  1.0025  postgres __udivdi3
4818  0.9676  vmlinux  run_timer_softirq


I was having trouble getting oprofile to give a back trace for memset
(probably because my libc is optimized). So I redefined MemSet to call this:
void * gmm_memset(void *s, int c, size_t n)
{
int i=0;
unsigned char * p = (unsigned char *)s;
for(i=0; i# -
# 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.
#
# Please note that re-commenting a setting is NOT sufficient to revert it
# to the default value, unless you restart the server.
#
# Any option can also be given as a command line switch to the server,
# e.g., 'postgres -c log_connections=on'.  Some options can be changed at
# run-time with the 'SET' SQL command.
#
# This file is read on server startup and when the server receives a
# SIGHUP.  If you edit the file on a running system, you have to SIGHUP the
# server for the changes to take effect, or use "pg_ctl reload". Some
# settings, which are marked below, require a server shutdown and restart
# to take effect.
#
# Memory units:  kB = kilobytes MB = megabytes GB = gigabytes
# Time units:ms = milliseconds s = seconds min = minutes h = hours d = days


#---
# FILE LOCATIONS
#---

# The default values of these variables are driven from the -D command line
# switch or PGDATA environment variable, represented here as ConfigDir.

#data_directory = 'ConfigDir'   # use data in another directory
# (change requires restart)
#hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file
# (change requires restart)
#ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file
# (change requires restart)

# If external_pid_file is not explicitly set, no extra PID file is written.
#external_pid_file = '(none)'   # write an extra PID file
# (change requires restart)


#---
# CONNECTIONS AND AUTHENTICATION
#---

# - Connection Settings -

#listen_addresses = 'localhost' # what IP address(es) to listen on; 
# comma-separated list of addresses;
# defaults to 'localhost', '*' = all
# (change requires restart)
listen_addresses = '*'
#port = 5432# (change requires restart)
max_connections = 10# (change requires restart)
# Note: incr

Re: [PERFORM] Postgres Performance on CPU limited Platforms

[H. Hall]

George McCollister wrote:

I'm trying to optimize postgres performance on a headless solid state
hardware platform (no fans or disks). I have the database stored on a
USB 2.0 flash drive (hdparm benchmarks reads at 10 MB/s). Performance is
limited by the 533Mhz CPU.

Hardware:
IXP425 XScale (big endian) 533Mhz 64MB RAM
USB 2.0 Flash Drive
  


Hmmm  ARM/XScale, 64MB.  Just curious. Are you running a Postgres server 
on a pocket pc or possibly a cell phone?


 
Software:

Linux 2.6.21.4
postgres 8.2.5

I created a fresh database using initdb, then added one table.

Here is the create table:
CREATE TABLE archivetbl
(
  "DateTime" timestamp without time zone,
  "StationNum" smallint,
  "DeviceDateTime" timestamp without time zone,
  "DeviceNum" smallint,
  "Tagname" character(64),
  "Value" double precision,
  "Online" boolean
)
WITH (OIDS=FALSE);
ALTER TABLE archivetbl OWNER TO novatech;

I've attached my postgresql.conf

I populated the table with 38098 rows.

I'm doing this simple query:
select * from archivetbl;
 
It takes 79 seconds to complete the query (when postgres is compiled

with -O2). I'm running the query from pgadmin3 over TCP/IP.

top shows CPU usage is at 100% with 95% being in userspace. oprofile
shows memset is using 58% of the CPU cycles!

CPU: ARM/XScale PMU2, speed 0 MHz (estimated)
Counted CPU_CYCLES events (clock cycles counter) with a unit mask of
0x00 (No unit mask) count 10
samples  %app name symbol name
288445   57.9263  libc-2.5.so  memset
33273 6.6820  vmlinux  default_idle
27910 5.6050  vmlinux  cpu_idle
12611 2.5326  vmlinux  schedule
8803  1.7678  libc-2.5.so  __printf_fp
7448  1.4957  postgres dopr
6404  1.2861  libc-2.5.so  vfprintf
6398  1.2849  oprofiled(no symbols)
4992  1.0025  postgres __udivdi3
4818  0.9676  vmlinux  run_timer_softirq


I was having trouble getting oprofile to give a back trace for memset
(probably because my libc is optimized). So I redefined MemSet to call this:
void * gmm_memset(void *s, int c, size_t n)
{
int i=0;
unsigned char * p = (unsigned char *)s;
for(i=0; isamples  %image name   app name
symbol name

---
  15.2e-04  postgres postgres
LockAcquire
  15.2e-04  postgres postgres
set_ps_display
  200.0103  postgres postgres
pg_vsprintf

  116695   60.2947  postgres postgres dopr
116717   60.3061  postgres postgres
gmm_memset
  116717   60.3061  postgres postgres
gmm_memset [self]

---
2030410.4908  oprofiledoprofiled(no
symbols)
  2030410.4908  oprofiledoprofiled   
(no symbols) [self]

---
  4587  2.3700  vmlinux  vmlinux 
rest_init
  6627  3.4241  vmlinux  vmlinux 
cpu_idle
11214 5.7941  vmlinux  vmlinux 
default_idle
  11214 5.7941  vmlinux  vmlinux 
default_idle [self]

---
  16151 8.3450  vmlinux  vmlinux 
rest_init

9524  4.9209  vmlinux  vmlinux  cpu_idle
  9524  4.9209  vmlinux  vmlinux 
cpu_idle [self]
  6627  3.4241  vmlinux  vmlinux 
default_idle

---
5111  2.6408  oprofile oprofile (no
symbols)
  5111  2.6408  oprofile oprofile
(no symbols) [self]


oprofile shows dopr is making most of the calls to memset.

Are these results typical? If memset is indeed using over 50% of the CPU
something seems seriously wrong.

Should I be expecting more performance from this hardware than what I'm
getting in these tests?

Regards,
George McCollister

  

  



--
H. Hall
ReedyRiver Group LLC
http://www.reedyriver.com


--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance