On Tuesday, April 26, 2016 11:21:15 AM Michael Nolan wrote:
> On Tue, Apr 26, 2016 at 10:03 AM, Artem Tomyuk wrote:
> > Hi All.
> >
> > I've noticed that there is a huge (more than ~3x slower) performance
> > difference between KVM guest and host machine.
>
> Is this unique to KVM, or do similar
On Tuesday, March 25, 2014 03:48:07 PM Graeme B. Bell wrote:
> Postgresql rsync backups require the DB to be shutdown during the 'second'
> rsync.
>
> 1. rsync the DB onto the backup filesystem (produces e.g. 95-99.99%
> consistent DB on the backup filesystem) 2. shut down the DB
> 3. rsync the s
On Friday, December 06, 2013 11:06:58 PM chidamparam muthusamy wrote:
> hi,
> Registered with PostgreSQL Help Forum to identify and resolve the Postgres
> DB performance issue, received suggestions but could not improve the
> speed/response time. Please help.
>
> Details:
> Postgres Version 9.3.1
memory that would be allocated for a session (e.g. there is no
multiplying factor like the work_mem for a session)?
We are using Postgres 9.0.13
shared_buffers = 800MB
work_mem = 1MB
temp_buffers = 8MB (our applications do not use temp tables)
effective_cache_size = 1500MB
Thanks,
Alan
On Wednesday, May 29, 2013 06:25:21 PM Daniele Varrazzo wrote:
> My solution has been to become pg_repack maintainer. YMMV. Just don't
> expect vacuum to reduce the indexes size: it doesn't.
It's not supposed to. It is supposed to keep them from indefinitely growing,
though, which it does reasona
On Tuesday, January 08, 2013 03:48:38 PM Shaun Thomas wrote:
> On 01/08/2013 02:05 PM, AJ Weber wrote:
> > Is there an "easy" way to tell what scheduler my OS is using?
>
> Unfortunately not. I looked again, and it seems that CFS was merged into
> 2.6.23. Anything before that is probably safe, but
On Thursday, September 27, 2012 03:04:51 PM David Boreham wrote:
> On 9/27/2012 2:55 PM, Scott Marlowe wrote:
> > Whatever you do, go for the Intel ethernet adaptor option. We've had so
> > many>
> > >headaches with integrated broadcom NICs.:(
>
> Sound advice, but not a get out of jail card unfo
On Thursday, September 27, 2012 02:13:01 PM David Boreham wrote:
> The equivalent Supermicro box looks to be somewhat less expensive :
> http://www.newegg.com/Product/Product.aspx?Item=N82E16816101693
>
> When you consider downtime and the cost to ship equipment back to the
> supplier, a warranty
Thanks for the information guys. And Yes, I am only updating the Foreign Key
definitions to be deferrable. I am not modifying the Unique/Primary Key
definitions.
Thanks again,
Alan
-Original Message-
From: Craig Ringer [mailto:ring...@ringerc.id.au]
Sent: Sunday, September 16, 2012
Key/Unique Key is changed to deferred, due to assumptions the optimizer
can or cannot make regarding whether the associated index is unique. But I
have not found any negatives in regard to changing foreign key definitions to
be deferrable.
Thanks,
Alan
On November 16, 2011 02:53:17 PM Tory M Blue wrote:
> We now have about 180mill records in that table. The database size is
> about 580GB and the userstats table which is the biggest one and the
> one we query the most is 83GB.
>
> Just a basic query takes 4 minutes:
>
> For e.g. select count(dis
, and
notify the user once resuilts are available
} else {
run the query and wait for the results in real time.
}
Thanks,
Alan
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
ext(CURRENT_DATE - interval '1
day'),'-MM-DD HH24:MI:SS')";
$insert->execute($device,$groupid,$timestamp,$val1,$val2));
Alan
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
atum);
COMMIT;
But
1. it’s taking forever and
2. I’m seeing that my disk is filling up real fast.
Any suggestions?
Alan
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
On September 11, 2011 03:44:34 PM Anthony Presley wrote:
> First thing I noticed is that it takes the same amount of time to load the
> db (about 40 minutes) on the new hardware as the old hardware. I was
> really hoping with the faster, additional drives and a hardware RAID
> controller, that thi
On September 6, 2011 12:35:35 PM Richard Shaw wrote:
> Thanks for the advice, It's one under consideration at the moment. What
> are your thoughts on increasing RAM and shared_buffers?
>
If it's running OK after the startup rush, and it seems to be, I would leave
them alone. More RAM is always
On September 6, 2011 12:11:10 PM Richard Shaw wrote:
> 24 :)
>
> 4 x Intel Xeon-NehalemEX E7540-HexCore [2GHz]
>
Nice box.
Still I/O-bound, though. SSDs would help a lot, I would think.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subsc
On September 5, 2011 03:36:09 PM you wrote:
> After Restart
>
> procs ---memory-- ---swap-- -io --system--
> -cpu-- r b swpd free buff cache si sobibo in
> cs us sy id wa st 2 34 2332 5819012 75632 258553680089
> 420
On September 5, 2011, Richard Shaw wrote:
> Hi Andy,
>
> It's not a new issue no, It's a legacy system that is in no way ideal but
> is also not in a position to be overhauled. Indexes are correct, tables
> are up to 25 million rows.
>
> On startup, it hits CPU more than IO, I'll provide some a
On August 31, 2011 11:56:56 AM Andy Colson wrote:
> On 8/31/2011 1:51 PM, Alan Hodgson wrote:
> > On August 31, 2011 11:26:57 AM Andy Colson wrote:
> >> When you ran it, did it really feel like 30 seconds? Or did it come
> >> right back real quick?
> >
On August 31, 2011 11:26:57 AM Andy Colson wrote:
> When you ran it, did it really feel like 30 seconds? Or did it come
> right back real quick?
>
> Because your report says:
> > 35.833 ms
>
> Thats ms, or milliseconds, or 0.035 seconds.
>
I think the "." is a thousands separator in some loca
On August 22, 2011 09:55:33 PM Scott Marlowe wrote:
>
> If you're running linux and thus stuck with the command line on the
> LSI, I'd recommend anything else. MegaRAID is the hardest RAID
> control software to use I've ever seen. If you can spring for the
> money, get the Areca 1680:
> http://ww
more code on my client app (if ($cat_id =
get_cat_id($cat)) }else { $cat_id = insert_cat($cat)})
Can I write a BEFORE ROW trigger for the products table to runs on
INSERT or UPDATE to
1. insert a new category & return the new category_id OR
2. return the existing category_id fo
so cascaded to the
product table.
testdb=# UPDATE category SET category_id = 2 WHERE category_id = 1;
UPDATE 1
testdb=# SELECT * FROM products;
product_id | name | category
++--
1 | Postgresql for Dummies |2
Alan
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
7 | 2011-07-07 | hostb | 1.4286 |40.
Alan
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
us the sum of DayVal for the
previous 6 rows.
MonthAvg = current row's DayVal plus the sum of DayVal for the
previous 29 rows.
Should I place the logic in a Trigger or in a Function?
Does someone have an example or a link showing how I could set this
up?
Regards,
Alan
--
Sent via pgsql-perf
On May 3, 2011 12:43:13 pm you wrote:
> On May 3, 2011, at 8:41 PM, Alan Hodgson wrote:
> > I am also interested in tips for this. EBS seems to suck pretty bad.
>
> Alan, can you elaborate? Are you using PG on top of EBS?
>
Trying to, yes.
Let's see ...
EBS volumes seem
On May 3, 2011 11:48:35 am Joel Reymont wrote:
> What are the best practices for setting up PG 9.x on Amazon EC2 to get the
> best performance?
>
I am also interested in tips for this. EBS seems to suck pretty bad.
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
T
On April 17, 2011, Phoenix wrote:
> >> Surely this is not tenable for enterprise environments? I am on a
> >> 64bit RedHat server with dual CPU Intel Woodcrest or whatever that was
> >> called. Postgres is 8.2.9.
> >>
.. and you have essentially 1 disk drive. Your hardware is not sized for a
da
On January 27, 2011, Robert Schnabel wrote:
> So my questions are 1) am I'm crazy for doing this, 2) would you change
> anything and 3) is it acceptable to put the xlog & wal (and perhaps tmp
> filespace) on a different controller than everything else? Please keep
> in mind I'm a geneticist who h
On Wed, Dec 8, 2010 at 3:03 PM, Benjamin Krajmalnik wrote:
> I need to build a new high performance server to replace our current
> production database server.
We run FreeBSD 8.1 with PG 8.4 (soon to upgrade to PG 9). Hardware is:
Supermicro 2u 6026T-NTR+
2x Intel Xeon E5520 Nehalem 2.26GHz
On October 25, 2010 11:36:24 am Divakar Singh wrote:
> Above results show good INSERT performance of PG when using SQL procedures.
> But performance when I use C++ lib is very bad. I did that test some time
> back so I do not have data for that right now.
Wrap it in a transaction.
--
Sent via pg
On Thursday, August 05, 2010, Mark Kirkwood
wrote:
> Normally I'd agree with the others and recommend RAID10 - but you say
> you have an OLAP workload - if it is *heavily* read biased you may get
> better performance with RAID5 (more effective disks to read from).
> Having said that, your sequent
On Thursday, August 05, 2010, "Kenneth Cox" wrote:
> 1) Should I switch to RAID 10 for performance? I see things like "RAID 5
> is bad for a DB" and "RAID 5 is slow with <= 6 drives" but I see little
> on RAID 6. RAID 6 was the original choice for more usable space with
> good redundancy. My cu
On Tuesday 01 June 2010, Mark Kirkwood
wrote:
> I'm helping set up a Red Hat 5.5 system for Postgres. I was going to
> recommend xfs for the filesystem - however it seems that xfs is
> supported as a technology preview "layered product" for 5.5. This
> apparently means that the xfs tools are only
On Wednesday 07 April 2010, Craig James wrote:
> I thought so too, except that I can't login during the flood. If the
> CPUs were all doing iowaits, logging in should be easy.
Busying out the drives is about the most reliable way to make logging in
very slow (especially, but not only, if it's d
Ug, sorry! As soon as I hit "enter" I realised this was the wrong
list even for OT :-)
On Fri, Jan 15, 2010 at 12:16 PM, Alan McKay wrote:
> Hey folks,
>
> Sorry for the OT - we are most of the way through a Db2 --> PG
> migration that is some 18 months in the ma
xcellent pgbouncer tool we have implemented on PG
We are 100% CentOS based.
Anyone know of anything?
From my process list it looks like Db2 V8.1 - my DBA is away at the
moment so I cannot ask him :)
root 3370 1 0 2009 ?00:18:38 /opt/IBM/db2/V8.1/bin/db2fmcd
thanks,
-Alan
--
“D
On Monday 07 December 2009, Ben Brehmer wrote:
> Disk Setup: Using a single disk Amazon image for the destination
> (database). Source is coming from an EBS volume. I didn't think there
> were any disk options in Amazon?
I don't think any Amazon cloud service is particularly well suited to a
dat
On Tuesday 24 November 2009, Thom Brown wrote:
>
> It's a shame there isn't a LIMIT option on DELETE so this can be done in
> small batches.
delete from table where pk in (select pk from table where delete_condition
limit X);
--
"No animals were harmed in the recording of this episode. We tri
Hey folks,
CentOS / PostgreSQL shop over here.
I'm hitting 3 of my favorite lists with this, so here's hoping that
the BCC trick is the right way to do it :-)
We've just discovered thanks to a new Munin plugin
http://blogs.amd.co.at/robe/2008/12/graphing-linux-disk-io-statistics-with-munin.html
On Monday 21 September 2009, Scott Marlowe wrote:
> I'm looking at running session servers in ram.
> Does anybody any real world experience here or any words of sage
> advice before I go off and start testing this?
Use memcached for session data.
--
"No animals were harmed in the recording of t
On Tue, Sep 22, 2009 at 2:42 AM, Magnus Hagander wrote:
> That's not true at all.
>
> If you have many relations in your cluster that have at some point been
> touched, the starts collector can create a *significant* load on the I/o
> system. I've come across several cases where the only choice wa
> Best practice to avoid that, is to bump the work_mem temporarily
> before the query, and than lower it again, lowers the chance of memory
> exhaustion.
Interesting - I can do that dynamically?
--
“Don't eat anything you've ever seen advertised on TV”
- Michael Pollan, author of "In D
On Mon, Sep 21, 2009 at 4:08 PM, Robert Haas wrote:
> Setting work_mem too high is a frequent cause of problems of this sort, I
> think.
Too high? How high is too high?
--
“Don't eat anything you've ever seen advertised on TV”
- Michael Pollan, author of "In Defense of Food"
--
Se
>> And if so, where does that extra load go? Disk? CPU? RAM?
>
> As of 8.4.X the load isn't measurable.
Thanks Bruce. What about 8.3 since that is our current production DB?
--
“Don't eat anything you've ever seen advertised on TV”
- Michael Pollan, author of "In Defense of Food
Hey folks,
We are looking to optimize the query I was talking about last week
which is killing our system.
We have explain and analyze which tell us about the cost of a query
time-wise, but what does one use to determine (and trace / predict?)
memory consumption?
thanks,
-Alan
--
“Don'
Is there a rule of thumb for the extra load that will be put on a
system when statement stats are turned on?
And if so, where does that extra load go?Disk? CPU? RAM?
--
“Don't eat anything you've ever seen advertised on TV”
- Michael Pollan, author of "In Defense of Food"
--
Sen
> What's the scale on the bottom there? The label says "by week" but the way
> your message is written makes me think it's actually a much smaller time
> frame. If those valleys are around around five minutes apart, those are the
> checkpoints finishing; the shape of the graph is right for it to
> My guess is this is checkpoint related.
I'll assume "checkpoint" is a PG term that I'm not yet familiar with -
will query my DBA :-)
If this OS buffer cache, wouldn't that be cached an awfully long time?
i.e. we're in big trouble if we get a bad crash?
--
“Don't eat anything you've ever see
global
hot spare)
We aren't seeing any performance problems on this per-se. But that
just seems like a really odd graph to me. Can anyone explain it? In
particular, how regular it is?
cheers,
-Alan
--
“Don't eat anything you've ever seen advertised on TV”
- Michael Pol
On Tuesday 30 June 2009, Mike Ivanov wrote:
> Hi Scott,
>
> > Well, we can't be sure OP's only got one core.
>
> In fact, we can, Sean posted what top -b -n 1 says. There was only one
> CPU line.
>
Recent versions of top on Linux (on RedHat 5 anyway) may show only one
combined CPU line unless yo
Thanks Greg!
On Fri, Jun 26, 2009 at 11:27 PM, Greg Smith wrote:
--
“Don't eat anything you've ever seen advertised on TV”
- Michael Pollan, author of "In Defense of Food"
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscript
On Friday 26 June 2009, Brian Troutwine wrote:
> CREATE TABLE amazon_items (
> asin char(10) PRIMARY KEY,
> locale varchar(10) NOT NULL DEFAULT 'US',
> currency_code char(3) DEFAULT 'USD',
> isbn char(13),
> sales_rank integer,
>
Sorry, just in case anyone is filtering on that in the subject line ...
On Tue, Jun 23, 2009 at 4:41 PM, Alan McKay wrote:
> BTW, our designer got the nytprofile or whatever it is called for Perl
> and found out that it was a problem with the POE library that was
> being used as a stat
BTW, our designer got the nytprofile or whatever it is called for Perl
and found out that it was a problem with the POE library that was
being used as a state-machine to drive the whole load suite. It was
taking something like 95% of the CPU time!
On Fri, Jun 19, 2009 at 11:59 AM, Alan McKay
> Like the other poster said, we likely don't have enough to tell you
> what's going on, but from what you've said here it sounds like you're
> mostly just CPU bound. Assuming you're reading the output of vmstat
> and top and other tools like that.
Thanks. I used 'sadc' from the sysstat RPM (par
ion issues."
On my first test where the CPU is not pegged, context switching goes
from about 3700 to about 4900, maybe averaging 4100
On the pegged test, the values are maybe 10% higher than that, maybe 15%.
It is an IBM 3550 with 8 cores, 2660.134 MHz (from dmesg), 32Gigs RAM
thanks,
-Alan
ied this on a Linux box, and I do not see the
problem there.
1. Run the following in psql client to generate a 8M row data file.
copy (select generate_series(1,800), ('1 second'::interval *
generate_series(1,800) + '2007-01-01'::timestamp)) to
'/export/home/alan/wo
On Monday 15 June 2009, Nimesh Satam wrote:
> Hi,
>
> When we do a ps U postgres command, we find some connection in BIND
> status:
>
> 10088 ?Ss 0:00 postgres: chk production xxx.xx.x.xx(48672)
> BIND 10090 ?Ss 0:00 postgres: chk production
> xxx.xx.x.xx(48674) BIND
>
>
>
od reading material would be
greatly appreciated.
On Fri, Jun 12, 2009 at 4:40 PM, Rauan Maemirov wrote:
> Hi Alan. For simple needs you can use Staplr, it's very easy to configure.
> There's also one - zabbix, pretty much.
--
“Don't eat anything you've ever seen adverti
> I'm unfamiliar with Munin, but if you can turn off the graphing (so as to
> achieve your desired level of un-cluttered-ness) without disabling the capture
> of the data that was being graphed, you'll be better off. Others' opinions may
> certainly vary, but in my experience, provided you're not c
ing and tuning reading
that you can recommend?
thanks,
-Alan
--
“Mother Nature doesn’t do bailouts.”
- Glenn Prickett
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Disclaimer : I'm very much a newbie here!
But I am on the path in my new job to figure this stuff out as well,
and went to PG Con here in Ottawa 2 weeks ago and attended quite a few
lectures on this topic. Have a look at :
http://wiki.postgresql.org/wiki/PgCon_2009
And in particular "Database H
Hmmm. Anyone out there have the Continuent solution working with PostgreSQL?
If so, what release? We're at 8.3 right now.
thanks,
-Alan
p.s. I'm continuing the cross-post because that is the way I started
this thread. Future threads will not be cross-posted.
On Thu, May 28, 2009
t interested in is load
balancing to be able to scale up when required, and of course to be
able to determine ahead of time when that might be :-)
cheers,
-Alan
--
“Mother Nature doesn’t do bailouts.”
- Glenn Prickett
--
Sent via pgsql-performance mailing list (pgsql-performance@
? Terminology. I'm pretty new to the whole data-warehouse
thing. And once I do all the reading, I'll even be open to product
recommendations :-)
And in particular since I already have heard of this particular
product - are there any opinions on Continuent?
thanks,
-Alan
--
“Mother Nature
it? What combination of switches?
thanks,
-Alan
--
“Mother Nature doesn’t do bailouts.”
- Glenn Prickett
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
On Tuesday 28 April 2009, Whit Armstrong wrote:
> Additionally are there any clear choices w/ regard to filesystem
> types? Our choices would be xfs, ext3, or ext4.
xfs consistently delivers much higher sequential throughput than ext3 (up to
100%), at least on my hardware.
--
Even a sixth-gra
On Monday 16 March 2009, Joe Uhl wrote:
> Right now (not under peak load) this server is running at 68% CPU
> utilization and its SATA raid 10 is doing about 2MB/s writes and 11MB/
> s reads. When I run dd I can hit 200+MB/s writes and 230+ MB/s reads,
> so we are barely using the available IO.
Gregory Stark wrote:
A minute ago I said:
AFAIK Opensolaris doesn't implement posix_fadvise() so there's no benefit. It
would be great to hear if you could catch the ear of the right people to get
an implementation committed. Depending on how the i/o scheduler system is
written it might not
On Friday 20 February 2009, Sergio Lopez wrote:
> Hi,
>
> I've made a benchmark comparing PostgreSQL, MySQL and Oracle under three
> environments: GNU/Linux-x86, Solaris-x86 (same machine as GNU/Linux) and
> Solaris-SPARC. I think you might find it interesting:
>
> http://blogs.nologin.es/slopez/a
nything special about this table? Does it have like a hundred
indexes on it or something? Because deleting 8k rows from a normal table
should never take more than a couple of seconds.
--
Alan
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your sub
On Monday 12 January 2009, Bill Preston
wrote:
> I had a data load that I was doing with 8.1. It involved about 250k sql
> statements that were inserts into a table with just one index. The index
> has two fields.
> With the upgrade to 8.3 that process started taking all night and 1/2 a
> day. I
On Monday 12 January 2009, "Scott Marlowe" wrote:
> I've found that when you do frequently query on two or more columns, a
> multi-column index is faster than bitmap scans, especially for larger
> data sets.
Very much faster, especially if you're only looking for a few dozen or
hundred rows out
> >>> Mario Weilguni <[EMAIL PROTECTED]> wrote:
> > strange values. An individual drive is capable of delivering 91
> > MB/sec
>
> > sequential read performance, and we get values ~102MB/sec out of a
> > 8-drive RAID5, seems to be ridiculous slow.
What command are you using to test the reads?
S
ed_buffers on one server here. No problems.
--
Alan
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
On Friday 21 November 2008, "Andrus" <[EMAIL PROTECTED]> wrote:
> Those commands cause server probably to stop responding to other client
> like vacuum full pg_shdepend
> did.
>
> Should vacuum_cost_delay = 2000 allow other users to work when running
> those commands ?
Any vacuum full or cluster w
On Monday 17 November 2008, Dimi Paun <[EMAIL PROTECTED]> wrote:
>> It takes 0.091s (!):
> perpedes_db=# EXPLAIN ANALYZE SELECT * FROM triphistory WHERE ownerId =
> 10015 ORDER BY accessTS DESC LIMIT 5; QUERY PLAN
> -
>
The extra disk access caused by vacuum? That seems pretty obvious.
Use auto-vacuum. There's no reason to vacuum your entire database every hour
(doing so reads from disk the entirety of every table and index, and
generates some write activity).
--
Alan
--
Sent via pgsql-perform
On Thursday 25 September 2008, John Huttley <[EMAIL PROTECTED]> wrote:
>
> Comments anyone?
Don't do full table updates? This is not exactly a news flash.
--
Alan
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your s
fill up, etc) so I tend to go in the direction of a few large
> partitions.
I used to feel this way until LVM became usable. LVM plus online resizable
filesystems really makes multiple partitions manageable.
--
Alan
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.
Turning fsync off might help. You should also drop all indexes on the table
before the COPY and add them back after (which would eliminate a lot of
random I/O during the COPY).
--
Alan
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
47
different Slony clusters, for starters. The complications from adding and
dropping tables and sequences across 47 databases, and trying to keep Slony
up to date throughout, staggers the imagination, honestly.
--
Alan
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql
ils the
> master.
A heartbeat+DRBD solution might make more sense than database-level
replication to achieve this.
--
Alan
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
n key
> > does not have an index on it (a 330K row table).
>
Yeah you need to fix that. You're doing 80,000 sequential scans of that
table to do your delete. That's a whole lot of memory access ...
I don't let people here create foreign key relationships without match
t, you'll need to reboot.
--
Alan
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
On Tuesday 17 June 2008, Howard Cole <[EMAIL PROTECTED]> wrote:
> This misses out the random access of the email table, turning my 27
> second query into 6 seconds.
It took less time because it retrieved a lot less data - it still has to
look at the table.
--
Alan
--
Se
e
> email_fts_index is cached.
It's because everything is cached, in particular the relevant rows from
the "email" table (accessing which took 22 of the original 27 seconds).
The plan looks good for what it's doing.
I don't see that query getting much faster unle
notice any differences in execution time.
A primary key is a unique btree index, and it's as about as good as it gets
for a bigint.
--
Alan
signature.asc
Description: This is a digitally signed message part.
On Wednesday 26 March 2008, "p prince" <[EMAIL PROTECTED]> wrote:
> Is this a sign of disk contention?
Yes.
> How does CPU load come into play?
Processes waiting for disk I/O generally show up as load.
--
Alan
--
Sent via pgsql-performance mailing list (pgsql-performa
nly do maybe
150 seeks per second.
Oh, and updates in PostgreSQL are expensive. But mostly I'd say it's your
drive.
--
Alan
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your Subscription:
http://mail.postgresql.org/mj/mj_www
On Mon, Mar 3, 2008 at 5:11 PM, Greg Smith <[EMAIL PROTECTED]> wrote:
> On Mon, 3 Mar 2008, alan bryan wrote:
>
> >> pgbench -c 100 -t 1000 testdb
>
> > tps = 558.013714 (excluding connections establishing)
> >
> > Just for testing, I tried turning
benchmarks and make sure disk is near where it should be.
Should turning off fsync make things roughly 8x-10x faster? Or is
that indicative of something not being correct or tuned quite right in
the rest of the system? I'll have to run in production with fsync on
but was just testing to s
I've got a new server and am myself new to tuning postgres.
Server is an 8 core Xeon 2.33GHz, 8GB RAM, RAID 10 on a 3ware 9550SX-4LP w/ BBU.
It's serving as the DB for a fairly write intensive (maybe 25-30%) Web
application in PHP. We are not using persistent connections, thus the
high max conne
On Wednesday 09 January 2008, Adrian Moisey <[EMAIL PROTECTED]>
wrote:
>
> Also, we're running the db on ext3 with noatime. Should I look at
> changing or getting rid of journaling ?
No (unless you like really long fsck times). data=writeback is safe with
PostgreSQL, though.
On Wednesday 14 November 2007, Jeff Frost <[EMAIL PROTECTED]>
wrote:
> > OK, impressive RAID-6 performance (not so impressive RAID-10
> > performance, but that could be a filesystem issue). Note to self; try
> > an Areca controller in next storage server.
>
> I believe these were both on ext3. I
On Tuesday 13 November 2007, Jeff Frost <[EMAIL PROTECTED]> wrote:
> Ok, Areca ARC1261ML. Note that results were similar for an 8 drive RAID6
> vs 8 drive RAID10, but I don't have those bonnie results any longer.
>
> Version 1.03 --Sequential Output-- --Sequential Input-
> --Random-
On November 9, 2007, Vivek Khera <[EMAIL PROTECTED]> wrote:
> On Nov 8, 2007, at 3:56 PM, Alan Hodgson wrote:
> > You can't touch RAID 10 for performance or reliability. The only
> > reason to
> > use RAID 5 or RAID 6 is to get more capacity out of the same
> &
5 or RAID 6 is to get more capacity out of the same drives.
--
Alan
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
1 - 100 of 171 matches
Mail list logo
