On 18/08/2011 6:40 AM, hyelluas wrote:
Hello,
I have an old application that was written on Postgres 8.1.
There are a few hundreds tables, 30-40 columns per table, hundreds of
views, and all the sql is inside java code.
We are moving it to 8.4, it seems to be VERY slow.
There are 20-30 tables
On 18/08/2011 9:03 AM, Anish Kejariwal wrote:
Thanks for the help Pavel and Craig. I really appreciate it. I'm
going to try a couple of these different options (write a c function,
use a sql function with case statements, and use plperl), so I can see
which gives me the realtime performance t
Hello,
I have an old application that was written on Postgres 8.1.
There are a few hundreds tables, 30-40 columns per table, hundreds of
views, and all the sql is inside java code.
We are moving it to 8.4, it seems to be VERY slow.
There are 20-30 tables transactions - the objects are spread ac
Igor,
Thank you for the hint, I read about the planner, added "vacuum analyze " to
my procedures.
There is no join in my query but GROUP BY that is taking all the time and I
don't know how to tune it.
It gets executed by the procedure, the execution time requirement is < 4
sec,
but it takes 8-11
On 18/08/2011 11:48 AM, Ogden wrote:
Isn't this very dangerous? I have the Dell PERC H700 card - I see that it has
512Mb Cache. Is this the same thing and good enough to switch to nobarrier?
Just worried if a sudden power shut down, then data can be lost on this option.
Yeah, I'm confused by
On Jul 30, 2011, at 3:02 PM, Filippos wrote:
> thx a lot for your answer.
> i will provide some stats, so if you could help me figure out the source of
> the problem that would be great
>
> -*top -c*
> Tasks: 1220 total, 49 running, 1171 sleeping, 0 stopped, 0 zombie
> Cpu(s): *84.1%us*, 2
On Aug 17, 2011, at 4:16 PM, Greg Smith wrote:
> On 08/17/2011 02:26 PM, Ogden wrote:
>> I am using bonnie++ to benchmark our current Postgres system (on RAID 5)
>> with the new one we have, which I have configured with RAID 10. The drives
>> are the same (SAS 15K). I tried the new system with
On 08/17/2011 08:35 PM, mark wrote:
With XFS the mount option is nobarrier. With ext4 I think it is barrier=0
http://www.mjmwired.net/kernel/Documentation/filesystems/ext4.txt
ext4 supports both; "nobarrier" and "barrier=0" mean the same thing. I
tend to use "nobarrier" just because I'm used
Thanks for the help Pavel and Craig. I really appreciate it. I'm going to
try a couple of these different options (write a c function, use a sql
function with case statements, and use plperl), so I can see which gives me
the realtime performance that I need, and works best for clean code in my
pa
> -Original Message-
> From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-
> ow...@postgresql.org] On Behalf Of Greg Smith
> Sent: Wednesday, August 17, 2011 3:18 PM
> To: pgsql-performance@postgresql.org
> Subject: Re: [PERFORM] Raid 5 vs Raid 10 Benchmarks Using bonn
On 18/08/2011 3:00 AM, Anish Kejariwal wrote:
Thanks Pavel! that definitely solved it.
Unfortunately, the function I gave you was a simple/short version of
what the actual function is going to be. The actual function is going
to get parameters passed to it, and based on the parameters will go
On 08/17/2011 02:26 PM, Ogden wrote:
I am using bonnie++ to benchmark our current Postgres system (on RAID
5) with the new one we have, which I have configured with RAID 10. The
drives are the same (SAS 15K). I tried the new system with ext3 and
then XFS but the results seem really outrageous a
On Aug 4, 2011, at 10:07 AM, Scott Marlowe wrote:
> On Thu, Aug 4, 2011 at 7:57 AM, Kevin Grittner
> wrote:
>>> RAM : 16 GB
>>
>>> effective_cache_size = 4096MB
>>
>> That should probably be more like 12GB to 15GB. It probably won't
>> affect the load time here, but could affect other queries.
Aidan Van Dyk writes:
> On Wed, Aug 17, 2011 at 4:12 PM, Tom Lane wrote:
>> It sounds to me like bobbyw might have two separate installations of
>> postgres (or at least two copies of psql), one compiled with /tmp as the
>> default socket location and one compiled with /var/run/postgresql as the
On Aug 17, 2011, at 3:56 PM, k...@rice.edu wrote:
> On Wed, Aug 17, 2011 at 03:40:03PM -0500, Ogden wrote:
>>
>> On Aug 17, 2011, at 1:35 PM, k...@rice.edu wrote:
>>
>>> On Wed, Aug 17, 2011 at 01:32:41PM -0500, Ogden wrote:
On Aug 17, 2011, at 1:31 PM, k...@rice.edu wrote:
>>>
On Wed, Aug 17, 2011 at 03:40:03PM -0500, Ogden wrote:
>
> On Aug 17, 2011, at 1:35 PM, k...@rice.edu wrote:
>
> > On Wed, Aug 17, 2011 at 01:32:41PM -0500, Ogden wrote:
> >>
> >> On Aug 17, 2011, at 1:31 PM, k...@rice.edu wrote:
> >>
> >>> On Wed, Aug 17, 2011 at 01:26:56PM -0500, Ogden wrote:
On Aug 17, 2011, at 1:49 AM, Eyal Wilde wrote:
> 1. is there any more elegant solution?
Very possibly, but I'm having a heck of a time trying to figure out what your
current code is actually doing.
What's the actual problem you're trying to solve here?
--
Jim C. Nasby, Database Architect
On Wed, Aug 17, 2011 at 4:12 PM, Tom Lane wrote:
> It sounds to me like bobbyw might have two separate installations of
> postgres (or at least two copies of psql), one compiled with /tmp as the
> default socket location and one compiled with /var/run/postgresql as the
> default. /tmp is the out
On Aug 17, 2011, at 1:35 PM, k...@rice.edu wrote:
> On Wed, Aug 17, 2011 at 01:32:41PM -0500, Ogden wrote:
>>
>> On Aug 17, 2011, at 1:31 PM, k...@rice.edu wrote:
>>
>>> On Wed, Aug 17, 2011 at 01:26:56PM -0500, Ogden wrote:
I am using bonnie++ to benchmark our current Postgres system (on
"k...@rice.edu" writes:
> On Wed, Aug 17, 2011 at 10:59:12AM -0700, bobbyw wrote:
>> Why is psql looking in /tmp?
> Because that is the default location. If you want to change it, you need
> to use the -h commandline option.
It sounds to me like bobbyw might have two separate installations of
po
On 17 Srpen 2011, 21:22, Ogden wrote:
>> This is a very important point. I've found on most machines with
>> hardware caching RAID and 8 or fewer 15k SCSI drives it's just as
>> fast to put it all on one big RAID-10 and if necessary partition it to
>> put the pg_xlog on its own file system. Afte
On Aug 17, 2011, at 2:14 PM, Scott Marlowe wrote:
> On Wed, Aug 17, 2011 at 12:56 PM, Tomas Vondra wrote:
>>
>> I think you've mentioned the database is on 6 drives, while the other
>> volume is on 2 drives, right? That makes the OS drive about 3x slower
>> (just a rough estimate). But if the d
On Wed, Aug 17, 2011 at 1:55 PM, Ogden wrote:
>
>
> What about the OS itself? I put the Debian linux sysem also on XFS but
> haven't played around with it too much. Is it better to put the OS itself on
> ext4 and the /var/lib/pgsql partition on XFS?
>
>
We've always put the OS on whatever default
On Wed, Aug 17, 2011 at 12:56 PM, Tomas Vondra wrote:
>
> I think you've mentioned the database is on 6 drives, while the other
> volume is on 2 drives, right? That makes the OS drive about 3x slower
> (just a rough estimate). But if the database drive is used heavily, it
> might help to move the
On 8/17/2011 1:55 PM, Ogden wrote:
On Aug 17, 2011, at 1:48 PM, Andy Colson wrote:
On 8/17/2011 1:35 PM, k...@rice.edu wrote:
On Wed, Aug 17, 2011 at 01:32:41PM -0500, Ogden wrote:
On Aug 17, 2011, at 1:31 PM, k...@rice.edu wrote:
On Wed, Aug 17, 2011 at 01:26:56PM -0500, Ogden wrote:
I
On Aug 17, 2011, at 1:56 PM, Tomas Vondra wrote:
> On 17 Srpen 2011, 18:39, Ogden wrote:
>>> Yes, but it greatly depends on the amount of WAL and your workload. If
>>> you
>>> need to write a lot of WAL data (e.g. during bulk loading), this may
>>> significantly improve performance. It may also h
2011/8/17 Anish Kejariwal :
> Thanks Pavel! that definitely solved it.
> Unfortunately, the function I gave you was a simple/short version of what
> the actual function is going to be. The actual function is going to get
> parameters passed to it, and based on the parameters will go through some
>
On 17/08/2011 7:56 PM, Ogden wrote:
On Aug 17, 2011, at 1:33 PM, Gary Doades wrote:
On 17/08/2011 7:26 PM, Ogden wrote:
I am using bonnie++ to benchmark our current Postgres system (on RAID 5) with
the new one we have, which I have configured with RAID 10. The drives are the
same (SAS 15K).
Thanks Pavel! that definitely solved it.
Unfortunately, the function I gave you was a simple/short version of what
the actual function is going to be. The actual function is going to get
parameters passed to it, and based on the parameters will go through some
if...else conditions, and maybe even
On 17 Srpen 2011, 18:39, Ogden wrote:
>> Yes, but it greatly depends on the amount of WAL and your workload. If
>> you
>> need to write a lot of WAL data (e.g. during bulk loading), this may
>> significantly improve performance. It may also help when you have a
>> write-heavy workload (a lot of cli
On Aug 17, 2011, at 1:33 PM, Gary Doades wrote:
> On 17/08/2011 7:26 PM, Ogden wrote:
>> I am using bonnie++ to benchmark our current Postgres system (on RAID 5)
>> with the new one we have, which I have configured with RAID 10. The drives
>> are the same (SAS 15K). I tried the new system with
On Aug 17, 2011, at 1:48 PM, Andy Colson wrote:
> On 8/17/2011 1:35 PM, k...@rice.edu wrote:
>> On Wed, Aug 17, 2011 at 01:32:41PM -0500, Ogden wrote:
>>>
>>> On Aug 17, 2011, at 1:31 PM, k...@rice.edu wrote:
>>>
On Wed, Aug 17, 2011 at 01:26:56PM -0500, Ogden wrote:
> I am using bonni
On 8/17/2011 1:35 PM, k...@rice.edu wrote:
On Wed, Aug 17, 2011 at 01:32:41PM -0500, Ogden wrote:
On Aug 17, 2011, at 1:31 PM, k...@rice.edu wrote:
On Wed, Aug 17, 2011 at 01:26:56PM -0500, Ogden wrote:
I am using bonnie++ to benchmark our current Postgres system (on RAID 5) with
the new on
On 17/08/2011 7:26 PM, Ogden wrote:
I am using bonnie++ to benchmark our current Postgres system (on RAID
5) with the new one we have, which I have configured with RAID 10. The
drives are the same (SAS 15K). I tried the new system with ext3 and
then XFS but the results seem really outrageous as
On Wed, Aug 17, 2011 at 01:32:41PM -0500, Ogden wrote:
>
> On Aug 17, 2011, at 1:31 PM, k...@rice.edu wrote:
>
> > On Wed, Aug 17, 2011 at 01:26:56PM -0500, Ogden wrote:
> >> I am using bonnie++ to benchmark our current Postgres system (on RAID 5)
> >> with the new one we have, which I have conf
On Aug 17, 2011, at 1:31 PM, k...@rice.edu wrote:
> On Wed, Aug 17, 2011 at 01:26:56PM -0500, Ogden wrote:
>> I am using bonnie++ to benchmark our current Postgres system (on RAID 5)
>> with the new one we have, which I have configured with RAID 10. The drives
>> are the same (SAS 15K). I tried
On Wed, Aug 17, 2011 at 01:26:56PM -0500, Ogden wrote:
> I am using bonnie++ to benchmark our current Postgres system (on RAID 5) with
> the new one we have, which I have configured with RAID 10. The drives are the
> same (SAS 15K). I tried the new system with ext3 and then XFS but the results
>
Hello
2011/8/17 Anish Kejariwal :
> Hi everyone,
> I'm using postgres 9.0.3, and here's the OS I'm running this on:
> Linux 2.6.18-238.12.1.el5xen #1 SMP Tue May 31 14:02:29 EDT 2011 x86_64
> x86_64 x86_64 GNU/Linux
> I have a fairly straight forward query. I'm doing a group by on an ID, and
> th
I am using bonnie++ to benchmark our current Postgres system (on RAID 5) with
the new one we have, which I have configured with RAID 10. The drives are the
same (SAS 15K). I tried the new system with ext3 and then XFS but the results
seem really outrageous as compared to the current system, or a
Hi everyone,
I'm using postgres 9.0.3, and here's the OS I'm running this on:
Linux 2.6.18-238.12.1.el5xen #1 SMP Tue May 31 14:02:29 EDT 2011 x86_64
x86_64 x86_64 GNU/Linux
I have a fairly straight forward query. I'm doing a group by on an ID, and
then calculating some a statistic on the result
On Wed, Aug 17, 2011 at 10:59:12AM -0700, bobbyw wrote:
> Awesome.. that did it! It was actually not set at all in postgresql.conf,
> although it was commented out as:
>
> # unix_socket_directory = ''
>
> Presumably it was using the default of '/tmp'?
>
> Anyway, after making that change dbt5
Awesome.. that did it! It was actually not set at all in postgresql.conf,
although it was commented out as:
# unix_socket_directory = ''
Presumably it was using the default of '/tmp'?
Anyway, after making that change dbt5 runs fine, but now when I try to
connect via "psql" I get:
psql.bin: co
On 8/17/2011 10:29 AM, bobbyw wrote:
Hi, I know this is an old thread, but I wanted to chime in since I am having
problems with this as well.
I too am trying to run dbt5 against Postgres. Specifically I am trying to
run it against Postgres 9.1beta3.
After jumping through many hoops I ultimatel
On Aug 17, 2011, at 9:44 AM, Tomas Vondra wrote:
> On 17 Srpen 2011, 3:35, Ogden wrote:
>> Hope all is well. I have received tremendous help from this list prior and
>> therefore wanted some more advice.
>>
>> I bought some new servers and instead of RAID 5 (which I think greatly
>> hindered our
Hi, I know this is an old thread, but I wanted to chime in since I am having
problems with this as well.
I too am trying to run dbt5 against Postgres. Specifically I am trying to
run it against Postgres 9.1beta3.
After jumping through many hoops I ultimately was able to build dbt5 on my
debian e
> -Original Message-
> From: hyelluas [mailto:helen_yell...@mcafee.com]
> Sent: Monday, August 15, 2011 2:33 PM
> To: pgsql-performance@postgresql.org
> Subject: Re: How to see memory usage using explain analyze ?
>
> Igor,
>
> thank you , my tests showed better performance against the l
On 17 Srpen 2011, 16:28, Ogden wrote:
> I was wondering what the best parameters would be with my new setup. The
> work_mem obviously will increase as will everything else as it's a 64Gb
> machine as opposed to a 16Gb machine. The configuration I posted was for
> a 16Gb machine but this new one is
On 17 Srpen 2011, 3:35, Ogden wrote:
> Hope all is well. I have received tremendous help from this list prior and
> therefore wanted some more advice.
>
> I bought some new servers and instead of RAID 5 (which I think greatly
> hindered our writing performance), I configured 6 SCSI 15K drives with
On Aug 17, 2011, at 8:41 AM, Andy Colson wrote:
> On 8/16/2011 8:35 PM, Ogden wrote:
>> Hope all is well. I have received tremendous help from this list prior and
>> therefore wanted some more advice.
>>
>> I bought some new servers and instead of RAID 5 (which I think greatly
>> hindered our
On 8/16/2011 8:35 PM, Ogden wrote:
Hope all is well. I have received tremendous help from this list prior and
therefore wanted some more advice.
I bought some new servers and instead of RAID 5 (which I think greatly hindered
our writing performance), I configured 6 SCSI 15K drives with RAID 10
Thanks for the reply.
(i'm sorry for that i didn't really know how to reply to a certain
message...)
well, i used LIKE, but i actually wanted just "starts with".
the solution i found without using LIKE is this:
CREATE OR REPLACE FUNCTION test_func(STR text)
RETURNS integer AS
$BODY$
declare
ST
51 matches
Mail list logo
