SEO

Hi!

What do you suggest for the next problem?
We have complex databases with some 100million rows (2-3million new
records per month). Our current servers are working on low resposibility
in these days, so we have to buy new hardver for database server. Some
weeks ago we started to work with PostgreSQL8.1, which solved the
problem for some months.
There are some massive, hard query execution, which are too slow (5-10
or more minutes). The parallel processing is infrequent (rarely max. 4-5
parallel query execution).
So we need high performance in query execution with medium parallel
processability.
What's your opinion what productions could help us? What is the best or
only better choice?
The budget line is about 30 000$ - 40 000$.

Regards, Atesz

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Tue, Dec 20, 2005 at 07:27:15PM +0100, Antal Attila wrote:
> We have complex databases with some 100million rows (2-3million new

How much space does that equate to?

> records per month). Our current servers are working on low resposibility
> in these days, so we have to buy new hardver for database server. Some
> weeks ago we started to work with PostgreSQL8.1, which solved the
> problem for some months.
> There are some massive, hard query execution, which are too slow (5-10
> or more minutes). The parallel processing is infrequent (rarely max. 4-5
> parallel query execution).
> So we need high performance in query execution with medium parallel
> processability.
> What's your opinion what productions could help us? What is the best or
> only better choice?
> The budget line is about 30 000$ - 40 000$.

Have you optimized the queries?

Items that generally have the biggest impact on performance in
decreasing order:
1. System architecture
2. Database design
3. (for long-running/problem queries) Query plans
4. Disk I/O
5. Memory
6. CPU

So, I'd make sure that the queries have been optimized (and that
includes tuning postgresql.conf) before assuming you need more hardware.

Based on what you've told us (very little parallelization), then your
biggest priority is probably either disk IO or memory (or both). Without
knowing the size of your database/working set it's difficult to provide
more specific advice.
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

On Dec 20, 2005, at 1:27 PM, Antal Attila wrote:

> The budget line is about 30 000$ - 40 000$.

Like Jim said, without more specifics it is hard to give more
specific recommendations, but I'm architecting something like this
for my current app which needs ~100GB disk space. I made room to
grow in my configuration:

dual opteron 2.2GHz
4GB RAM
LSI MegaRAID 320-2X
14-disk SCSI U320 enclosure with 15k RPM drives, 7 connected to each
channel on the RAID.
1 pair in RAID1 mirror for OS + pg_xlog
rest in RAID10 with each mirrored pair coming from opposite SCSI
channels for data

I run FreeBSD but whatever you prefer should be sufficient if it is
not windows.

I don't know how prices are in Hungary, but around here something
like this with 36GB drives comes to around $11,000 or $12,000.

The place I concentrate on is the disk I/O bandwidth which is why I
prefer Opteron over Intel XEON.


---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

Can you elaborate on the reasons the opteron is better than the Xeon when it
comes to disk io? I have a PostgreSQL 7.4.8 box running a DSS. One of our
tables is about 13 million rows. I had a number of queries against this
table that used innner joins on 5 or 6 tables including the 13 million row
one. The performance was atrocious. The database itself is about 20 gigs
but I want it to scale to 100 gigs. I tuned postgresql as best I could and
gave the server huge amounts of memory for caching as well. I also tweaked
the cost parameters for a sequential scan vs an index scan of the query
optimizer and used the query explain mechanism to get some idea of what the
optimizer was doing and where I should index the tables. When I added the
sixth table to the inner join the query performance took a nose dive.
Admittedly this system is a single PIII 1000Mhz with 1.2 gigs of ram and no
raid. I do have two Ultra 160 scsi drives with the database tables mount
point on a partition on one physical drive and pg_xlog mount point on another
partition of the second drive. I have been trying to get my employer to
spring for new hardware ($8k to $10k) which I had planned to be a dual - dual
core opteron system from HP. Until they agree to spend the money I resorted
to writing a plpgsql functions to handle the queries. Inside plpgsql I can
break the query apart into seperate stages each of which runs much faster. I
can use temporary tables to store intermediate results without worrying about
temp table collisions with different users thanks to transaction isolation.
I am convinced we need new hardware to scale this application *but* I agree
with the consensus voiced here that it is more important to optimize the
query first before going out to buy new hardware. I was able to do things
with PostgreSQL on this cheap server that I could never imagine doing with
SQL server or even oracle on such a low end box. My OS is Fedora Core 3 but
I wonder if anyone has tested and benchmarked PostgreSQL on the new Sun x64
servers running Solaris 10 x86.

Thanks,
Juan

On Tuesday 20 December 2005 16:08, Vivek Khera wrote:
> On Dec 20, 2005, at 1:27 PM, Antal Attila wrote:
> > The budget line is about 30 000$ - 40 000$.
>
> Like Jim said, without more specifics it is hard to give more
> specific recommendations, but I'm architecting something like this
> for my current app which needs ~100GB disk space. I made room to
> grow in my configuration:
>
> dual opteron 2.2GHz
> 4GB RAM
> LSI MegaRAID 320-2X
> 14-disk SCSI U320 enclosure with 15k RPM drives, 7 connected to each
> channel on the RAID.
> 1 pair in RAID1 mirror for OS + pg_xlog
> rest in RAID10 with each mirrored pair coming from opposite SCSI
> channels for data
>
> I run FreeBSD but whatever you prefer should be sufficient if it is
> not windows.
>
> I don't know how prices are in Hungary, but around here something
> like this with 36GB drives comes to around $11,000 or $12,000.
>
> The place I concentrate on is the disk I/O bandwidth which is why I
> prefer Opteron over Intel XEON.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Tue, 20 Dec 2005, Juan Casero wrote:

> Date: Tue, 20 Dec 2005 19:50:47 -0500
> From: Juan Casero <caseroj@comcast.net>
> To: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] What's the best hardver for PostgreSQL 8.1?
>
> Can you elaborate on the reasons the opteron is better than the Xeon when it
> comes to disk io?

the opteron is cheaper so you have more money to spend on disks :-)

also when you go into multi-cpu systems the front-side-bus design of the
Xeon's can easily become your system bottleneck so that you can't take
advantage of all the CPU's becouse they stall waiting for memory accesses,
Opteron systems have a memory bus per socket so the more CPU's you have
the more memory bandwidth you have.


> The database itself is about 20 gigs
> but I want it to scale to 100 gigs.

how large is the working set? in your tests you ran into swapping on your
1.2G system, buying a dual opteron with 16gigs of ram will allow you to
work with much larger sets of data, and you can go beyond that if needed.

David Lang

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Jim C. Nasby wrote:

>How much space does that equate to?
>
>
>Have you optimized the queries?
>
>Items that generally have the biggest impact on performance in
>decreasing order:
>1. System architecture
>2. Database design
>3. (for long-running/problem queries) Query plans
>4. Disk I/O
>5. Memory
>6. CPU
>
>So, I'd make sure that the queries have been optimized (and that
>includes tuning postgresql.conf) before assuming you need more hardware.
>
>Based on what you've told us (very little parallelization), then your
>biggest priority is probably either disk IO or memory (or both). Without
>knowing the size of your database/working set it's difficult to provide
>more specific advice.
>
>
Hi!

We have 3 Compaq Proliant ML530 servers with dual Xeon 2.8GHz
processors, 3 GB DDR RAM, Ultra Wide SCSI RAID5 10000rpm and 1000Gbit
ethernet. We partitioned our databases among these machines, but there
are cross refrences among the machines theoretically. Now the size of
datas is about 100-110GB. We've used these servers for 3 years with
Debian Linux. We have already optimized the given queries and the
postgresql.conf. We tried more tricks and ideas and we read and asked on
mailing lists. We cannot do anything, we should buy new server for the
databases, because we develop our system for newer services, so the size
will grow along. After that we need better responsiblility and shorter
execution time for the big queries (These queries are too complicated to
discuss here, and more times we optimized with plpgsql stored procedures.).
The PostgreSQL 8.1 solved more paralellization and overload problem, the
average load is decreased significantly on our servers. But the big
queries aren't fast enough. We think the hardver is the limit. Generally
2 parallel guery running in working hours, after we make backups at night.

Regards, Atesz


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Juan Casero wrote:
> Can you elaborate on the reasons the opteron is better than the Xeon when it
> comes to disk io? I have a PostgreSQL 7.4.8 box running a DSS. One of our

Opterons have 64-bit IOMMU -- Xeons don't. That means in 64-bit mode,
transfers to > 4GB, the OS must allocated the memory < 4GB, DMA to that
block and then the CPU must do extra work in copying the memory to >
4GB. Versus on the Opteron, it's done by the IO adaptor using DMA in the
background.

I just sent my boss an email asking him for a Sun v20z with dual 2.2 Ghz
opterons, 2 Gigs of RAM and RAID 1. I would have liked a better server
capable of RAID but that seems to be out of his budget right now. Ok so I
assume I get this Sun box. Most likely I will go with Linux since it is a
fair bet he doesn't want to pay for the Solaris 10 x86 license. Although I
kind of like the idea of using Solaris 10 x86 for this. I will assume I
need to install the x64 kernel that comes with say Fedora Core 4. Should I
run the Postgresql 8.x binaries in 32 bit mode or 64 bit mode? My instinct
tells me 64 bit mode is most efficient for our database size about 20 gigs
right now but may grow to 100 gigs in a year or so. I just finished loading
a 20 gig database on a dual 900 Mhz Ultrasparc III system with 2 gigs of ram
and about 768 megs of shared memory available for the posgresql server
running Solaris 10. The load has smoked a P4 3.2 Ghz system I am using also
with 2 gigs of ram running postgresql 8.0.3. I mean I started the sparc
load after the P4 load. The sparc load has finished already rebuilding the
database from a pg_dump file but the P4 system is still going. The p4 has
1.3 Gigs of shared memory allocated to postgresql. How about them apples?


Thanks,
Juan

On Wednesday 21 December 2005 18:57, William Yu wrote:
> Juan Casero wrote:
> > Can you elaborate on the reasons the opteron is better than the Xeon when
> > it comes to disk io? I have a PostgreSQL 7.4.8 box running a DSS. One
> > of our
>
> Opterons have 64-bit IOMMU -- Xeons don't. That means in 64-bit mode,
> transfers to > 4GB, the OS must allocated the memory < 4GB, DMA to that
> block and then the CPU must do extra work in copying the memory to >
> 4GB. Versus on the Opteron, it's done by the IO adaptor using DMA in the
> background.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

AFAIK there are no licensing costs for solaris, unless you are talking
about a software support agreement, which is not required.

Juan Casero wrote:

>I just sent my boss an email asking him for a Sun v20z with dual 2.2 Ghz
>opterons, 2 Gigs of RAM and RAID 1. I would have liked a better server
>capable of RAID but that seems to be out of his budget right now. Ok so I
>assume I get this Sun box. Most likely I will go with Linux since it is a
>fair bet he doesn't want to pay for the Solaris 10 x86 license. Although I
>kind of like the idea of using Solaris 10 x86 for this. I will assume I
>need to install the x64 kernel that comes with say Fedora Core 4. Should I
>run the Postgresql 8.x binaries in 32 bit mode or 64 bit mode? My instinct
>tells me 64 bit mode is most efficient for our database size about 20 gigs
>right now but may grow to 100 gigs in a year or so. I just finished loading
>a 20 gig database on a dual 900 Mhz Ultrasparc III system with 2 gigs of ram
>and about 768 megs of shared memory available for the posgresql server
>running Solaris 10. The load has smoked a P4 3.2 Ghz system I am using also
>with 2 gigs of ram running postgresql 8.0.3. I mean I started the sparc
>load after the P4 load. The sparc load has finished already rebuilding the
>database from a pg_dump file but the P4 system is still going. The p4 has
>1.3 Gigs of shared memory allocated to postgresql. How about them apples?
>
>
>Thanks,
>Juan
>
>On Wednesday 21 December 2005 18:57, William Yu wrote:
>
>
>>Juan Casero wrote:
>>
>>
>>>Can you elaborate on the reasons the opteron is better than the Xeon when
>>>it comes to disk io? I have a PostgreSQL 7.4.8 box running a DSS. One
>>>of our
>>>
>>>
>>Opterons have 64-bit IOMMU -- Xeons don't. That means in 64-bit mode,
>>transfers to > 4GB, the OS must allocated the memory < 4GB, DMA to that
>>block and then the CPU must do extra work in copying the memory to >
>>4GB. Versus on the Opteron, it's done by the IO adaptor using DMA in the
>>background.
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 6: explain analyze is your friend
>>
>>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>
>
>

Hi Juan,

Solaris 10 license is for free.. Infact I believe you do receive the
media with Sun Fire V20z. If you want support then there are various
"pay" plans depending on the level of support. If not your license
allows you Right to Use anyway for free.

That said I haven't done much testing with 32/64 bit differences.
However for long term purposes, 64-bit always seems to be the safe bet.
As for your load performance, lot of it depends on your file system
layout also.

Regards,
Jignesh



Juan Casero wrote:

>I just sent my boss an email asking him for a Sun v20z with dual 2.2 Ghz
>opterons, 2 Gigs of RAM and RAID 1. I would have liked a better server
>capable of RAID but that seems to be out of his budget right now. Ok so I
>assume I get this Sun box. Most likely I will go with Linux since it is a
>fair bet he doesn't want to pay for the Solaris 10 x86 license. Although I
>kind of like the idea of using Solaris 10 x86 for this. I will assume I
>need to install the x64 kernel that comes with say Fedora Core 4. Should I
>run the Postgresql 8.x binaries in 32 bit mode or 64 bit mode? My instinct
>tells me 64 bit mode is most efficient for our database size about 20 gigs
>right now but may grow to 100 gigs in a year or so. I just finished loading
>a 20 gig database on a dual 900 Mhz Ultrasparc III system with 2 gigs of ram
>and about 768 megs of shared memory available for the posgresql server
>running Solaris 10. The load has smoked a P4 3.2 Ghz system I am using also
>with 2 gigs of ram running postgresql 8.0.3. I mean I started the sparc
>load after the P4 load. The sparc load has finished already rebuilding the
>database from a pg_dump file but the P4 system is still going. The p4 has
>1.3 Gigs of shared memory allocated to postgresql. How about them apples?
>
>
>Thanks,
>Juan
>
>On Wednesday 21 December 2005 18:57, William Yu wrote:
>
>
>>Juan Casero wrote:
>>
>>
>>>Can you elaborate on the reasons the opteron is better than the Xeon when
>>>it comes to disk io? I have a PostgreSQL 7.4.8 box running a DSS. One
>>>of our
>>>
>>>
>>Opterons have 64-bit IOMMU -- Xeons don't. That means in 64-bit mode,
>>transfers to > 4GB, the OS must allocated the memory < 4GB, DMA to that
>>block and then the CPU must do extra work in copying the memory to >
>>4GB. Versus on the Opteron, it's done by the IO adaptor using DMA in the
>>background.
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 6: explain analyze is your friend
>>
>>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly