Remove me from your email traffic.
> Date: Thu, 24 Jun 2010 23:05:06 -0400
> Subject: Re: [PERFORM] requested shared memory size overflows size_t
> From: robertmh...@gmail.com
> To: alvhe...@commandprompt.com
> CC: craig_ja...@emolecules.com; pgsql-performance@postgresql.org
>
> On Thu, Jun 24,
Craig James writes:
> So what is it that will cause every single Postgres backend to come to life
> at the same moment, when there's no real load on the server? Maybe if a
> backend crashes? Some other problem?
sinval queue overflow comes to mind ... although that really shouldn't
happen if t
On Thu, Jun 24, 2010 at 7:19 PM, Alvaro Herrera
wrote:
> Excerpts from Craig James's message of jue jun 24 19:03:00 -0400 2010:
>
>> select relname, pg_relation_size(relname) from pg_class
>> where pg_get_userbyid(relowner) = 'emol_warehouse_1'
>> and relname not like 'pg_%'
>>
Craig James wrote:
Now the question has narrowed down to this: what could trigger EVERY
postgres backend to do something at the same time? See the attached
output from "top -b", which shows what is happening during one of the
CPU spikes.
By the way: you probably want "top -b -c", which will
On Thu, 2010-06-24 at 17:50 -0700, Craig James wrote:
> I'm reviving this question because I never figured it out. To summarize: At
> random intervals anywhere from a few times per hour to once or twice a day,
> we see a huge spike in CPU load that essentially brings the system to a halt
> for
I'm reviving this question because I never figured it out. To summarize: At random
intervals anywhere from a few times per hour to once or twice a day, we see a huge spike
in CPU load that essentially brings the system to a halt for up to a minute or two.
Previous answers focused on "what is
Excerpts from Craig James's message of jue jun 24 19:24:44 -0400 2010:
> On 6/24/10 4:19 PM, Alvaro Herrera wrote:
> > Excerpts from Craig James's message of jue jun 24 19:03:00 -0400 2010:
> >
> >> select relname, pg_relation_size(relname) from pg_class
> >> where pg_get_userbyid(relowne
On 6/24/10 4:19 PM, Alvaro Herrera wrote:
Excerpts from Craig James's message of jue jun 24 19:03:00 -0400 2010:
select relname, pg_relation_size(relname) from pg_class
where pg_get_userbyid(relowner) = 'emol_warehouse_1'
and relname not like 'pg_%'
order by pg_rel
Excerpts from Craig James's message of jue jun 24 19:03:00 -0400 2010:
> select relname, pg_relation_size(relname) from pg_class
> where pg_get_userbyid(relowner) = 'emol_warehouse_1'
> and relname not like 'pg_%'
> order by pg_relation_size(relname) desc;
> ERROR: rela
Can anyone tell me what's going on here? I hope this doesn't mean my system
tables are corrupt...
Thanks,
Craig
select relname, pg_relation_size(relname) from pg_class
where pg_get_userbyid(relowner) = 'emol_warehouse_1'
and relname not like 'pg_%'
order by pg_relation
2010/6/24 Josh Berkus :
>
>> this is similar MySQL's memory tables. Personally, I don't see any
>> practical sense do same work on PostgreSQL now, when memcached exists.
>
> Thing is, if you only have one table (say, a sessions table) which you
> don't want logged, you don't necessarily want to fir
> this is similar MySQL's memory tables. Personally, I don't see any
> practical sense do same work on PostgreSQL now, when memcached exists.
Thing is, if you only have one table (say, a sessions table) which you
don't want logged, you don't necessarily want to fire up a 2nd software
application
2010/6/24 A.M. :
>
> On Jun 24, 2010, at 4:01 PM, Pavel Stehule wrote:
>
>> 2010/6/24 Joshua D. Drake :
>>> On Thu, 2010-06-24 at 21:14 +0200, Pavel Stehule wrote:
2010/6/24 Josh Berkus :
>
>> And I'm also planning to implement unlogged tables, which have the
>> same contents for a
On Jun 24, 2010, at 4:01 PM, Pavel Stehule wrote:
> 2010/6/24 Joshua D. Drake :
>> On Thu, 2010-06-24 at 21:14 +0200, Pavel Stehule wrote:
>>> 2010/6/24 Josh Berkus :
> And I'm also planning to implement unlogged tables, which have the
> same contents for all sessions but are not WA
2010/6/24 Joshua D. Drake :
> On Thu, 2010-06-24 at 21:14 +0200, Pavel Stehule wrote:
>> 2010/6/24 Josh Berkus :
>> >
>> >> And I'm also planning to implement unlogged tables, which have the
>> >> same contents for all sessions but are not WAL-logged (and are
>> >> truncated on startup).
>>
>> this
On Thu, 2010-06-24 at 21:14 +0200, Pavel Stehule wrote:
> 2010/6/24 Josh Berkus :
> >
> >> And I'm also planning to implement unlogged tables, which have the
> >> same contents for all sessions but are not WAL-logged (and are
> >> truncated on startup).
>
> this is similar MySQL's memory tables. P
2010/6/24 Josh Berkus :
>
>> And I'm also planning to implement unlogged tables, which have the
>> same contents for all sessions but are not WAL-logged (and are
>> truncated on startup).
this is similar MySQL's memory tables. Personally, I don't see any
practical sense do same work on PostgreSQL
Rajesh Kumar Mallah wrote:
> Kevin Grittner wrote:
>>> max_connections = 300
>>
>> As I've previously mentioned, I would use a connection pool, in
>> which case this wouldn't need to be that high.
>
> We do use connection pooling provided to mod_perl server
> via Apache::DBI::Cache. If i reduc
Excerpts from Rajesh Kumar Mallah's message of jue jun 24 13:25:32 -0400 2010:
> What prompted me to post to list is that the server transitioned from
> being IO bound to CPU bound and 90% of syscalls being
> lseek(XXX, 0, SEEK_END) = YYY
It could be useful to find out what file is being seek
> And I'm also planning to implement unlogged tables, which have the
> same contents for all sessions but are not WAL-logged (and are
> truncated on startup).
Yep. And it's quite possible that this will be adequate for most users.
And it's also possible that the extra CPU which Robert isn't get
Rajesh,
I had a similar situation a few weeks ago whereby performance all of a
sudden decreased.
The one tunable which resolved the problem in my case was increasing the
number of checkpoint segments.
After increasing them, everything went back to its normal state.
> -Original Message-
>
>i do not remember well but there is a system view that (i think)
>guides at what stage the marginal returns of increasing it
>starts disappearing , i had set it a few years back.
Sorry the above comment was regarding setting shared_buffers
not effective_cache_size.
On Thu, Jun 24, 2010 at 10:5
On Thu, Jun 24, 2010 at 8:57 PM, Kevin Grittner
wrote:
> I'm not clear whether you still have a problem, or whether the
> changes you mention solved your issues. I'll comment on potential
> issues that leap out at me.
It shall require more observation to know if the "problem" is solved.
my "pro
On 2010-06-24 15:45, Janning Vygen wrote:
On Thursday 24 June 2010 15:16:05 Janning wrote:
On Thursday 24 June 2010 14:53:57 Matthew Wakeling wrote:
On Thu, 24 Jun 2010, Janning wrote:
We have a 12 GB RAM machine with intel i7-975 and using
3 disks "Seagate Barracuda 7200.11,
On Thursday 24 June 2010 15:16:05 Janning wrote:
> On Thursday 24 June 2010 14:53:57 Matthew Wakeling wrote:
> > On Thu, 24 Jun 2010, Janning wrote:
> > > We have a 12 GB RAM machine with intel i7-975 and using
> > > 3 disks "Seagate Barracuda 7200.11, ST31500341AS (1.5 TB)"
> > >
> > For each driv
I'm not clear whether you still have a problem, or whether the
changes you mention solved your issues. I'll comment on potential
issues that leap out at me.
Rajesh Kumar Mallah wrote:
> 3. we use xfs and our controller has BBU , we changed barriers=1
> to barriers=0 as i learnt that having b
Scott Carey wrote:
> v. 8.4.3
>
> I have a table that has several indexes, one of which the table is
> clustered on. If I do an ALTER TABLE Foo ADD COLUMN bar integer not
> null default -1;
>
> It re-writes the whole table.
All good questions:
> * Does it adhere to the CLUSTER property of the
Dear List,
1. It was found that too many stray queries were getting generated
from rouge users and bots
we controlled using some manual methods.
2. We have made application changes and some significant changes have been done.
3. we use xfs and our controller has BBU , we changed barriers=1
What would you recommend to do a quick test for this? (i.e WAL on
internal disk vs WALon the 12 disk raid array )?
On Thu, Jun 24, 2010 at 6:31 AM, Scott Marlowe wrote:
> On Thu, Jun 24, 2010 at 5:14 AM, Matthew Wakeling wrote:
>> On Wed, 23 Jun 2010, Scott Marlowe wrote:
We have a 12
As others have already pointed out, your disk performance here is
completely typical of a single pair of drives doing random read/write
activity. So the question you should be asking is how to reduce the
amount of reading and writing needed to run your application. The
suggestions at
http://
On Thu, Jun 24, 2010 at 5:14 AM, Matthew Wakeling wrote:
> On Wed, 23 Jun 2010, Scott Marlowe wrote:
>>>
>>> We have a 12 x 600G hot swappable disk system (raid 10)
>>> and 2 internal disk ( 2x 146G)
>>>
>>> Does it make sense to put the WAL and OS on the internal disks
>>
>> So for us, the WAL a
thanks for your quick response, kenneth
On Thursday 24 June 2010 14:47:34 you wrote:
> On Thu, Jun 24, 2010 at 02:43:33PM +0200, Janning wrote:
> > Hi,
> >
> > at the moment we encounter some performance problems with our database
> > server.
> >
> > We have a 12 GB RAM machine with intel i7-975 a
On Thursday 24 June 2010 14:53:57 Matthew Wakeling wrote:
> On Thu, 24 Jun 2010, Janning wrote:
> > We have a 12 GB RAM machine with intel i7-975 and using
> > 3 disks "Seagate Barracuda 7200.11, ST31500341AS (1.5 GB)"
>
> Those discs are 1.5TB, not 1.5GB.
sorry, my fault.
> > One disk for the sy
On Thu, 24 Jun 2010, Janning wrote:
We have a 12 GB RAM machine with intel i7-975 and using
3 disks "Seagate Barracuda 7200.11, ST31500341AS (1.5 GB)"
Those discs are 1.5TB, not 1.5GB.
One disk for the system and WAL etc. and one SW RAID-0 with two disks for
postgresql data. Our database is a
On Thu, Jun 24, 2010 at 02:43:33PM +0200, Janning wrote:
> Hi,
>
> at the moment we encounter some performance problems with our database server.
>
> We have a 12 GB RAM machine with intel i7-975 and using
> 3 disks "Seagate Barracuda 7200.11, ST31500341AS (1.5 GB)"
> One disk for the system and
Hi,
at the moment we encounter some performance problems with our database server.
We have a 12 GB RAM machine with intel i7-975 and using
3 disks "Seagate Barracuda 7200.11, ST31500341AS (1.5 GB)"
One disk for the system and WAL etc. and one SW RAID-0 with two disks for
postgresql data. Our d
On Thu, Jun 24, 2010 at 4:40 AM, Rob Wultsch wrote:
> On Fri, Jun 18, 2010 at 1:55 PM, Josh Berkus wrote:
>>
>>> It must be a setting, not a version.
>>>
>>> For instance suppose you have a session table for your website and a
>>> users table.
>>>
>>> - Having ACID on the users table is of course
> Any suggestions on what I can do to speed things up? I presume if I turn
> off
> Sequential Scan then it might default to Index Scan.. Is there anything
> else?
>
> Cheers,
> Tom
Well, I doubt turning off the sequential scan will improve the performance
in this case - actually the first case (ru
Hi again!
I have finally got my Ubuntu VirtualBox VM running PostgreSQL with PL/Python
and am now looking at performance.
So here's the scenario:
We have a great big table:
cse=# \d nlpg.match_data
Table "nlpg.match_data"
Column | Type |
On Wed, 23 Jun 2010, Scott Marlowe wrote:
We have a 12 x 600G hot swappable disk system (raid 10)
and 2 internal disk ( 2x 146G)
Does it make sense to put the WAL and OS on the internal disks
So for us, the WAL and OS and logging on the same data set works well.
Generally, it is recommended
On Fri, Jun 18, 2010 at 1:55 PM, Josh Berkus wrote:
>
>> It must be a setting, not a version.
>>
>> For instance suppose you have a session table for your website and a
>> users table.
>>
>> - Having ACID on the users table is of course a must ;
>> - for the sessions table you can drop the "D"
>
>
Tom Lane writes:
> The problem with a system-wide no-WAL setting is it means you can't
> trust the system catalogs after a crash. Which means you are forced to
> use initdb to recover from any crash, in return for not a lot of savings
> (for typical usages where there's not really much churn in t
42 matches
Mail list logo
