headers
Marcin Jessa | 2 Feb 2006 01:06
Favicon

PostgreSQL

Hi guys.

I read some emails on one of the FreeBSD mailings list about MySQL performance
 on FreeBSD vs Linux and it seems like Linux performs better compared to 
FreeBSD 4.x and 6.x with different threading models. There are some benchmarks avaliable
but unfortunatelly they do not include NetBSD.
Now, my O.S of choice is NetBSD and my open source database of choice is PostgreSQL.
I am working on a project which will use a pretty buisy database with many simultanous selects, 
inserts and update queries running on 64-bit Opteron CPU's. 
I have little experience with running buisy databases and I basically used
MySQL for things like small scale email services, user authentication 
with radius and web frontends (couple of hundred users). 
I was wondering if any of you have experience with PostgreSQL's performance on 
NetBSD 3.0 or CURRENT compared to other open source O.S's. 
What threading model will be used and how does database generally perform on NetBSD?
Please be honest, I promice not to cry if you suggest a different O.S :)

Cheers,
Marcin Jessa.

P.S Sorry for cross-mailing, I am really not sure what mailing list suits best for questions like that.
And we do not have any ISP-specific list.
Permalink | Reply | 5 comments |
headers
Greg 'groggy' Lehey | 2 Feb 2006 01:34
Picon

Re: PostgreSQL

On Thursday,  2 February 2006 at  0:06:35 +0000, Marcin Jessa wrote:
> Hi guys.
>
> I read some emails on one of the FreeBSD mailings list about MySQL
> performance on FreeBSD vs Linux and it seems like Linux performs
> better compared to FreeBSD 4.x and 6.x with different threading
> models. There are some benchmarks avaliable but unfortunatelly they
> do not include NetBSD.

For those of you who don't know, one of my other hats is being a
developer for MySQL.  Unlike FreeBSD, NetBSD is not a "fully
supported" platform for MySQL.  If anybody wants to change that, I'll
do what I can to help.

> Now, my O.S of choice is NetBSD and my open source database of
> choice is PostgreSQL.  I am working on a project which will use a
> pretty buisy database with many simultanous selects, inserts and
> update queries running on 64-bit Opteron CPU's.  I have little
> experience with running buisy databases and I basically used MySQL
> for things like small scale email services, user authentication with
> radius and web frontends (couple of hundred users).  I was wondering
> if any of you have experience with PostgreSQL's performance on
> NetBSD 3.0 or CURRENT compared to other open source O.S's.  What
> threading model will be used and how does database generally perform
> on NetBSD?  Please be honest, I promice not to cry if you suggest a
> different O.S :)

I'll follow the discussion with interest :-)

Greg
(Continue reading)

Permalink | Reply | 0 comments |
headers
Curt Sampson | 2 Feb 2006 01:43
Gravatar

Re: PostgreSQL

On Thu, 2 Feb 2006, Marcin Jessa wrote:

> I was wondering if any of you have experience with PostgreSQL's
> performance on NetBSD 3.0 or CURRENT compared to other open source
> O.S's.

I have been using PostgreSQL extensively on NetBSD since 7.3. I don't
have much comparison data, but what I do have indicates that performance
is roughly the same across NetBSD, FreeBSD and Linux, assuming similar
hardware and configuration. This isn't surprising since PostgreSQL
doesn't actually use much in the way of sophisticated services from the
OS. The most important thing would be the filesystem code.

Unless your database is very small (i.e., only a few GB) database, by
far the most important factor in performance will be the configuration
of your disk storage system and how you design your schema and queries.
The OS won't make anywhere near as much difference as either of those.

> What threading model will be used

PostgreSQL does not use threads; it uses a separate process for each
connection. So how well or poorly the OS performs with threads is
irrelevant.

BTW, it's best, if you're looking at PostgreSQL, not even to consider
data about MySQL's comparative performance on different OSes. MySQL is
a completely different animal--it's not in fact a real DBMS at all, but
more a dumb data storage system that can be queried with SQL. So the
underlying techniques it uses can be quite different.
(Continue reading)

Permalink | Reply | 8 comments |
headers
segv | 2 Feb 2006 01:49

Re: PostgreSQL

To be honest I'm not a database guru, but the following are my personal views
on this topic:

1. I was reading somewhere that PostgreSQL was not multithreaded. This can have
a significant impact if you have a busy database server. Multicore CPUs
designed for executing 10s and 100s of threads simultaneously are just around
the corner (Sun's T1 processors). So fine grained threaded applications are the
future.

The following points are more related to OS characteristics, rather than an SQL
database:

2. In regard to SMP machines, BSDs don't scale as well as Solaris. FreeBSD are
doing a lot of work in making their kernel more SMP friendly, but I don't know
how stable it is, or how much progress they made. I would expect NetBSD and
OpenBSD to lag behind Linux and FreeBSD. I'm running NetBSD-current on dual
Penitum3 and I have experienced frequent system lock-ups, which some people
think are attributed to race conditions in the kernel. I think Solaris is the
best OS for SMP hardware. Linux and FreeBSD scale well to a small number of
processors 2-8, but beyond that Solaris would be a much better choice.

I quite like NetBSD, but I think it needs a lot of improvement in regard to
SMP. Maybe adopt a microkernel design? Redesign major kernel subsystems to
support fine grained threading?
Permalink | Reply | 6 comments |
headers
Daniel Carosone | 2 Feb 2006 01:56
Picon

Re: PostgreSQL

On Thu, Feb 02, 2006 at 12:06:35AM +0000, Marcin Jessa wrote:
> Now, my O.S of choice is NetBSD and my open source database of choice is PostgreSQL.
> I was wondering if any of you have experience with PostgreSQL's performance on 
> NetBSD 3.0 or CURRENT compared to other open source O.S's. 

To ensure you're getting fair comparisons, you need to make sure that
the installations you're testing have the same policy for things like
flushing writes to disk and syncing disk caches.  This is important
for both the DBMS config as well as OS settings. MySQL/PostgreSQL and
NetBSD/Linux all have different defaults and tunables, and not paying
attention to these can skew your comparisons.

Once you get past that, there's little important difference between
the OS choices.  Of far greater importance to overall performance is
the general database tuning that applies regardless of OS:

 * configuration of disk hardware subsystem for concurrency 
   (spindles, raid styles, tablespace layouts, etc)
 * memory allocation and similar sizing for WAL, cache, etc
 * indexes, query optimisation and everything else from EXPLAIN

--
Dan.
Permalink | Reply | 0 comments |
headers
Alfred Perlstein | 2 Feb 2006 01:59

Re: PostgreSQL

* Curt Sampson <cjs <at> cynic.net> [060201 16:45] wrote:
> On Thu, 2 Feb 2006, Marcin Jessa wrote:
> 
> >I was wondering if any of you have experience with PostgreSQL's
> >performance on NetBSD 3.0 or CURRENT compared to other open source
> >O.S's.
> 
> I have been using PostgreSQL extensively on NetBSD since 7.3. I don't
> have much comparison data, but what I do have indicates that performance
> is roughly the same across NetBSD, FreeBSD and Linux, assuming similar
> hardware and configuration. This isn't surprising since PostgreSQL
> doesn't actually use much in the way of sophisticated services from the
> OS. The most important thing would be the filesystem code.
> 
> Unless your database is very small (i.e., only a few GB) database, by
> far the most important factor in performance will be the configuration
> of your disk storage system and how you design your schema and queries.
> The OS won't make anywhere near as much difference as either of those.

There is one important thing that FreeBSD has, I am unsure if NetBSD
has it, but someone may want to chime in and let me know if it does.

Basically, FreeBSD has a setting 'kern.ipc.shm_use_phys' under sysctl
that will tell the kernel not to make SysV shared memory pageable.  This
is VERY handy when you are going to use very large shared segments.  Solaris
does this by default, on FreeBSD you need to enable it like so:

sysctl kern.ipc.shm_use_phys=1

Before starting postgresql.
(Continue reading)

Permalink | Reply | 0 comments |
headers
Neil Conway | 2 Feb 2006 02:16

Re: PostgreSQL

On Thu, 2006-02-02 at 00:49 +0000, segv <at> netctl.net wrote:
> 1. I was reading somewhere that PostgreSQL was not multithreaded. This can have
> a significant impact if you have a busy database server. Multicore CPUs
> designed for executing 10s and 100s of threads simultaneously are just around
> the corner (Sun's T1 processors). So fine grained threaded applications are the
> future.

Whether threads or processes are used to implement concurrency does not
really affect how fine-grained the concurrency is. That is, I don't see
how using multiple processes and fork() makes an application inherently
less suitable to multicore servers than an application using threads.

-Neil
Permalink | Reply | 3 comments |
headers
Alfred Perlstein | 2 Feb 2006 03:58

Re: PostgreSQL

* Neil Conway <neilc <at> samurai.com> [060201 18:49] wrote:
> On Thu, 2006-02-02 at 00:49 +0000, segv <at> netctl.net wrote:
> > 1. I was reading somewhere that PostgreSQL was not multithreaded. This can have
> > a significant impact if you have a busy database server. Multicore CPUs
> > designed for executing 10s and 100s of threads simultaneously are just around
> > the corner (Sun's T1 processors). So fine grained threaded applications are the
> > future.
> 
> Whether threads or processes are used to implement concurrency does not
> really affect how fine-grained the concurrency is. That is, I don't see
> how using multiple processes and fork() makes an application inherently
> less suitable to multicore servers than an application using threads.

Ok, I'm on the fence about threads, but the reason why they are a
gain over processes, on multi-core in particular is that the shared
address space means that if you switch between two threads you do
not have to invalidate tlbs or caches during the context switch.

Also, thread to thread synchronization is somewhat faster than process
to process synchronization.  Honestly, the default thread synchronization
primitives typically blow the inter-process ones out of the water,
although there are some OS's and libraries that provide relatively
fast/cheap inter process syntonization.

Also, I'm pretty sure that synchronization between threads doesn't
require locked bus cycles, only atomic ops, again another saving.

But threads aren't always the best thing out there, the code gets
a lot more complicated and crash prone and you wind up sometimes
getting screwed because third party code isn't thread safe...
(Continue reading)

Permalink | Reply | 7 comments |
headers
Ignatios Souvatzis | 2 Feb 2006 12:36
Picon

Re: PostgreSQL

On Wed, Feb 01, 2006 at 06:58:04PM -0800, Alfred Perlstein wrote:

> Also, I'm pretty sure that synchronization between threads doesn't
> require locked bus cycles, only atomic ops, again another saving.

If this was the case, I'd be very interested in your definition of
"locked bus cycle" vs.  "atomic ops".

In a multi-CPU (with non-shared cache) system, you have to implement
atomic operations via some sort of locked bus cycles.

This is independent of the thread vs. processes-with-a-shared-variable
question.

Regards,
	-is

P.S.: Why, exactly, is this discussion on tech-net, among others?
--

-- 
seal your e-mail: http://www.gnupg.org/
Permalink | Reply | 1 comment |
headers
Johnny Billquist | 2 Feb 2006 13:02
Picon
Picon

Re: PostgreSQL

Um? Not entirely true.
You don't need locked bus cycles for atomic operations which actually 
are atomic on the bus.
A write is a write is a write. It can never be split. It's atomic. Can't 
be anything else.
However, a read-modify-write operation needs to be interlocked if it is 
to appear to be atomic.

	Johnny

Ignatios Souvatzis wrote:
> On Wed, Feb 01, 2006 at 06:58:04PM -0800, Alfred Perlstein wrote:
> 
> 
>>Also, I'm pretty sure that synchronization between threads doesn't
>>require locked bus cycles, only atomic ops, again another saving.
> 
> 
> If this was the case, I'd be very interested in your definition of
> "locked bus cycle" vs.  "atomic ops".
> 
> In a multi-CPU (with non-shared cache) system, you have to implement
> atomic operations via some sort of locked bus cycles.
> 
> This is independent of the thread vs. processes-with-a-shared-variable
> question.
> 
> 
> Regards,
> 	-is
(Continue reading)

Permalink | Reply | 1 comment |

Gmane