Re: FreeBSD: SMP and PostgreSQL

Hi all,

The FreeBSD 5.0 released recently. Some phrases from release notes:
". . . SMP support has been largely reworked, incorporating code from
BSD/OS 5.0. One of the main features of SMPng (``SMP Next Generation'')
is to allow more processes to run in kernel, without the need for
spin locks that can dramatically reduce the efficiency of multiple
processors . . .".

Reading this release notes I see only this great improvement vs 4.7 version,
which can help for SQL server. On other hand FreeBSD 5.0 have totally
redesigned kernel, I m afraid to setup productional DB to it.
We had bought PC with 2xPIII, which will be dedicated PostgreSQL server.
Old releases (4.7, for example) also supports SMP, but worse comparative with
version 5 as described in above mentioned release notes. Please say,
if anybody test SMP in FreeBSD for PostgreSQL - really Postgres with v5.0
will "dramatically" increase SQL server performance?

Thanks a lot for any advance for this question.

--
best regards,
Ruslan A Dautkhanov

>>>>> "RAD" == Ruslan A Dautkhanov <rusland(at)scn(dot)ru> writes:

RAD> Old releases (4.7, for example) also supports SMP, but worse
RAD> comparative with version 5 as described in above mentioned
RAD> release notes. Please say, if anybody test SMP in FreeBSD for
RAD> PostgreSQL - really Postgres with v5.0 will "dramatically"
RAD> increase SQL server performance?

I run Postgres 7.2 on FreeBSD 4.7 with dual procs. Works fine. I
will wait until about FreeBSD 5.2 before I upgrade this server.

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: khera(at)kciLink(dot)com Rockville, MD +1-240-453-8497
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

I've been playing with PG on a new FreeBSD 5.0 SMP box and I can't say
the performance is dramatic, but I haven't done serious benchmarking
yet. I will post the results if there is a gigantic leap..

On Thursday, February 6, 2003, at 02:21 PM, Vivek Khera wrote:

>>>>>> "RAD" == Ruslan A Dautkhanov <rusland(at)scn(dot)ru> writes:
>
> RAD> Old releases (4.7, for example) also supports SMP, but worse
> RAD> comparative with version 5 as described in above mentioned
> RAD> release notes. Please say, if anybody test SMP in FreeBSD for
> RAD> PostgreSQL - really Postgres with v5.0 will "dramatically"
> RAD> increase SQL server performance?
>
> I run Postgres 7.2 on FreeBSD 4.7 with dual procs. Works fine. I
> will wait until about FreeBSD 5.2 before I upgrade this server.
>
>
> --
> =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
> Vivek Khera, Ph.D. Khera Communications, Inc.
> Internet: khera(at)kciLink(dot)com Rockville, MD +1-240-453-8497
> AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to
> majordomo(at)postgresql(dot)org)
>

-Mitch

Mitch wrote:
> I've been playing with PG on a new FreeBSD 5.0 SMP box and I can't say
> the performance is dramatic, but I haven't done serious benchmarking
> yet. I will post the results if there is a gigantic leap..

> On Thursday, February 6, 2003, at 02:21 PM, Vivek Khera wrote:

>>>>>>>"RAD" == Ruslan A Dautkhanov <rusland(at)scn(dot)ru> writes:
>>RAD> Old releases (4.7, for example) also supports SMP, but worse
>>RAD> comparative with version 5 as described in above mentioned
>>RAD> release notes. Please say, if anybody test SMP in FreeBSD for
>>RAD> PostgreSQL - really Postgres with v5.0 will "dramatically"
>>RAD> increase SQL server performance?
>>
>>I run Postgres 7.2 on FreeBSD 4.7 with dual procs. Works fine. I
>>will wait until about FreeBSD 5.2 before I upgrade this server.

FreeBSD prior to 5.0 does not do SMP within a single process, i.e.
light-weight threads like SunOS/Solaris. However, this is almost
irrelevant with respect to PostgreSQL, since each connection to
PostgreSQL gets its own process. Thus, multi-CPUs on FreeBSD 4.x will
run the multiple PostgreSQL processes just in an SMP manner just fine.

Well, almost. SMP really means Symmetric Multi-Processing, not just
multiprocessors, and the FreeBSD 4.x kernel is not multi-threaded, so
during system calls, all processes are single threaded through the kernel.

FreeBSD 5.0 changes this and allows most, but not all, of the kernel to
be multi-threaded.

So to the extent that your performance is limited by kernel "bandwidth"
moving to FreeBSD 5.x will help. Chances are you'll be disk IO limited
long before you get kernel limited, though.

On Thu, 6 Feb 2003, Chris Johnson wrote:

> So to the extent that your performance is limited by kernel "bandwidth"
> moving to FreeBSD 5.x will help. Chances are you'll be disk IO limited
> long before you get kernel limited, though.

I have to agree with Chris on this one on that the disk subsystem will
likely be a bigger player than switching from 4.X to 5.X

In my case I have a series of daily processes which I keep track or their
time of execution.

I looked at time improvements from going from a single CPU machine to a
dual CPU machine running Raid 5 and then the same machine running Raid 10.

Moving from single CPU to Dual CPU with Raid gave me somewhere in the 20%
improvement. However, when we switched to Raid 0+1 on the same SMP box I
saw performance jump to almost 3 times faster.

Although not exactly an answer to your question, but a testament that
a disk subsystem can be a real differentiator..

Also It realy would be best to wait until the 5.X branch has matured. I
may move my backup machine to 5 to give it a test/trial, but none of my
production machines are moving to it regardless of whether it is going to
be faster or not.

On Sun, 9 Feb 2003, Francisco J Reyes wrote:

> Moving from single CPU to Dual CPU with Raid gave me somewhere in the 20%
> improvement. However, when we switched to Raid 0+1 on the same SMP box I
> saw performance jump to almost 3 times faster.
>
> Although not exactly an answer to your question, but a testament that
> a disk subsystem can be a real differentiator..
>
> Also It realy would be best to wait until the 5.X branch has matured. I
> may move my backup machine to 5 to give it a test/trial, but none of my
> production machines are moving to it regardless of whether it is going to
> be faster or not.

Thanks for this info. I've just started playing with FreeBSD 5.0, and as
my BSD experience is rather dated, I figured I'd just learn BSD5.0 while
it matures and we'll both be ready to deploy in a few more months. :-)

I've got to say it looks from this report as if BSD has a better disk
subsystem than Linux in this regard, because there I was never able to get
much better performance from multi-layered RAID than I could with
whichever was the "slowest" layer method mixed in. I.e. if RAID1 was
limited to X performance, and RAID0 was capable of 4X, then going to RAID
1+0 or 0+1 would only be X fast.

Just to make it fun, I'm playing with it on an old Dual CPU PPro box
that'll dual boot linux for comparison.

Any tips for a newbie besides reading the online docs (which I'm in the
middle of) ???

On Fri, 14 Feb 2003, scott.marlowe wrote:

> Any tips for a newbie besides reading the online docs (which I'm in the
> middle of) ???

For FreeBSD or for PostgreSQL. Not sure which one you need more info to
get familiar with..

There are some very good intro books for both FreeBSD and PostgreSQL.
Beyond the basics, to be honest with you, I have not found a good
replacement for just trying what you need and asking questions. There are
just so many variations of how people use an OS/DB that it would be hard
for an authod to think up all the situations.

However, searching google and google groups many times point you in the
right direction since some questions, although not common, have been asked
before in forums, newsgroups and other placed scanned by google.