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
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
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
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
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 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
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
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 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
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,
>
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
>
>
>
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.
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
On Monday 12 January 2009, Bill Preston
wrote:
> As to the second example with the delete. There are no foreign keys.
> For the index. If the table has fields a,b,c and d.
> We have a btree index (a,b,c,d)
> and we are saying DELETE FROM table_messed_up WHERE a=x.
>
Is there anything special abo
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
On Wednesday 26 November 2008, "Ryan Hansen"
<[EMAIL PROTECTED]> wrote:
> This may be more of a Linux question than a PG question, but I'm
> wondering if any of you have successfully allocated more than 8 GB of
> memory to PG before.
>
CentOS 5, 24GB shared_buffers on one server here. No problems
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
> -
>
On Thursday 30 October 2008, "Oliver Johnson" <[EMAIL PROTECTED]>
wrote:
> Another thing to note, we have VACUUM ANALYZE running on an hourly
> interval and the switch from CPU to IO wait appears to always coincide
> with a vacuum.
>
> What might cause this shift?
The extra disk access caused by
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 subscription:
http://www.pos
On Thursday 11 September 2008, [EMAIL PROTECTED] wrote:
> while I agree with you in theory, in practice I've seen multiple
> partitions cause far more problems than they have prevented (due to the
> partitions ending up not being large enough and having to be resized
> after they fill up, etc) so I
On Wednesday 10 September 2008, Ryan Hansen <[EMAIL PROTECTED]>
wrote:
>Currently it's taking about 12 hours to complete on a 64 bit
> server with 3 GB memory allocated (shared_buffer), single SATA 320 GB
> drive. I don't seem to get any improvement running the same operation
> on a dual opteron
On Thursday 21 August 2008, salman <[EMAIL PROTECTED]> wrote:
> Mathias Stjernström wrote:
> > Yes thats true. It does support DDL changes but not in a automatic way.
> > You have to execute all DDL changes with a separate script.
>
> That's true, but it's quite simple to do with the provided perl
On Thursday 21 August 2008, Dan Harris <[EMAIL PROTECTED]> wrote:
> Especially since we make frequent use of sequences in our databases. If
> MM is too difficult, I'm willing to accept a hot-standby read-only
> system that will handle queries until we can fix whatever ails the
> master.
A heartbe
On Friday 04 July 2008, [EMAIL PROTECTED] wrote:
> > My next question is: what is the difference between "select" and
> > "delete"? There is another table that has one foreign key to reference
> > the test (parent) table that I am deleting from and this foreign key
> > does not have an index on it
On Tuesday 17 June 2008, Howard Cole <[EMAIL PROTECTED]> wrote:
> Incidentally, how can I clear the cache in between queries?
Stop PostgreSQL, unmount the filesystem it's on, remount it, restart
PostgreSQL. Works under Linux.
If it's on a filesystem you can't unmount hot, you'll need to reboot.
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
--
Sent via pgsql-performanc
On Monday 16 June 2008, Howard Cole <[EMAIL PROTECTED]> wrote:
> Hi, I am looking to improve the initial query speed for the following
> query:
>
> select email_id from email, to_tsquery('default','example') as q where
> q@@fts;
>
> This is running on 8.2.4 on Windows Server 2K3.
>
> The initial ou
On Thursday 08 May 2008, Rauan Maemirov <[EMAIL PROTECTED]> wrote:
> Hi, all. I want to ask what type of index is better to create for
> bigint types. I have table with bigint (bigserial) primary key. What
> type is better to use for it? I tried btree and hash, but didn't
> notice any differences i
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-performance@postgresql.org)
To m
On Tuesday 04 March 2008, dforums <[EMAIL PROTECTED]> wrote:
> Hello
>
>
> We hace a Quad Xeon server, with 8GO of ram, sata II 750Go
>
>
> I suppose the main problem is from database server settings.
No, the problem is your hard drive is too slow. One drive can only do maybe
150 seeks per sec
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
> &
On Thursday 08 November 2007, Dimitri Fontaine <[EMAIL PROTECTED]>
> Is raid6 better than raid10 in term of overall performances, or a better
> cut when you need capacity more than throughput?
You can't touch RAID 10 for performance or reliability. The only reason to
use RAID 5 or RAID 6 is to g
On Friday 12 October 2007, henk de wit <[EMAIL PROTECTED]> wrote:
> > select payment_id from transactions order by payment_id desc limit 1;
>
> This one is indeed instant! Less than 50ms. In my case I can't use it for
> max though because of the fact that payment_id can be null (which is an
> unf
On Friday 14 September 2007, "Harsh Azad" <[EMAIL PROTECTED]> wrote:
> Great, creating new tablespace for indexes worked! Now the question is
> whether existing tables/index can be moved to the new tablespace using an
> alter command or the only way possible is to drop and recreate them?
>
ALTER T
On Friday 07 September 2007 10:56, "Bryan Murphy"
<[EMAIL PROTECTED]> wrote:
> Our database server connects to the san via iSCSI over Gig/E using
> jumbo frames. File system is XFS (noatime).
>
> Throughput, however, kinda sucks. I just can't get the kind of
> throughput to it I was hoping to ge
On Wednesday 29 August 2007, Steve Atkins <[EMAIL PROTECTED]> wrote:
> There are higher end ones that do have ECC RAM (and backup drives and
> stuff) but they're spectacularly more expensive than the cheapo
> consumer ones.
>
Yeah the good ones look more like http://ramsan.com/ .
--
"Pulling tog
On Tuesday 07 August 2007 05:58, Mark Makarowsky
<[EMAIL PROTECTED]> wrote:
> I have a table with 4,889,820 records in it. The
> table also has 47 fields. I'm having problems with
> update performance. Just as a test, I issued the
> following update:
>
> update valley set test='this is a test'
On Thursday 02 August 2007 09:02, "Bryan Murphy" <[EMAIL PROTECTED]>
wrote:
> My question: Is there a way I can decrease the priority of a specific
> query, or determine the PID of the process it is running in? I'd like
> to throw together a quick shell script if at all possible, as right
> now
On Wednesday 25 July 2007 13:27, Pallav Kalva <[EMAIL PROTECTED]>
wrote:
> I am hoping "SELECT 1 FROM ONLY "provisioning"."account" x WHERE
> "accountid" = $1 FOR UPDATE OF x" is causing the problem. If that is the
> case why doesnt it show in the pg_stat_activity view ? or am I missing
> somethin
On Wednesday 11 July 2007 08:36, Andrew Sullivan <[EMAIL PROTECTED]>
wrote:
> Put the WAL where the battery is. Even if it's slower (and I don't
> know whether it will be), I assume that having the right data more
> slowly is better than maybe not having the data at all, quickly.
>
Presumably he
On Friday 18 May 2007 11:51, "Joshua D. Drake" <[EMAIL PROTECTED]> wrote:
> > The update query that started this all I had to kill after 17hours. It
> > should have updated all 121+ million records. That brought my select
> > count down to 19 minutes, but still a far cry from acceptable.
You're
On Tuesday 03 April 2007 12:47, "A.M." <[EMAIL PROTECTED]> wrote:
> On Apr 3, 2007, at 15:39 , C. Bergström wrote:
> I would like to use transactional semantics over tables that can
> disappear whenever the server fails. memcached does not offer that.
How would temporary tables?
--
Ginsberg's Th
On Tuesday 13 February 2007 10:36, "Campbell, Lance" <[EMAIL PROTECTED]>
wrote:
> We have 12+ schemas in 1 database. When I do a unix "top" command I
> notice one postmaster process has 100% CPU usage. This process just
> stays at 100% to 99% CPU usage. There are other postmaster processes
> tha
On Wednesday 27 September 2006 09:08, Edoardo Ceccarelli <[EMAIL PROTECTED]>
wrote:
>
> How can I configure the vacuum to run after the daily batch
> insert/update?
>
If you really only want it to run then, you should disable autovacuum and
continue to run the vacuum manually.
You might also in
On Monday 18 September 2006 13:56, "Merlin Moncure" <[EMAIL PROTECTED]>
wrote:
> just another fyi, if you have a really big database, you can forget
> about doing pg_dump for backups (unless you really don't care about
> being x day or days behind)...you simply have to due some type of
> replicati
On Thursday 14 September 2006 11:05, "Craig A. James"
<[EMAIL PROTECTED]> wrote:
> I'm experiment with RAID, looking for an inexpensive way to boost
> performance. I bought 4 Seagate 7200.9 120 GB SATA drives and two SIIG
> dual-port SATA cards. (NB: I don't plan to run RAID 0 in production,
> p
On Wednesday 30 August 2006 03:48, Willo van der Merwe
<[EMAIL PROTECTED]> wrote:
> Hi Rusty,
>
> Good ideas and I've implemented some of them, and gained about 10%. I'm
> still sitting on a load avg of about 60.
>
> Any ideas on optimizations on my postgresql.conf, that might have an
> effect?
I
On May 25, 2006 01:31 pm, "Merlin Moncure" <[EMAIL PROTECTED]> wrote:
> > SELECT DISTINCT ON (a, b) a, b, c FROM t ORDER BY a DESC, b DESC, c
> > DESC;
>
> that is actually slower than group by in my case...am i missing
> something? (both essentially resolved to seq_scan)
Try it with an index on a
On May 9, 2006 02:45 am, [EMAIL PROTECTED] wrote:
> What I am worry about is "93.5% wa" ...
>
> Could someone explain me what is the VACUUM process waiting for ?
>
Disk I/O.
--
In a truly free society, "Alcohol, Tobacco and Firearms" would be a
convenience store chain.
---
On April 28, 2006 10:31 am, "Bealach-na Bo" <[EMAIL PROTECTED]>
wrote:
> The exclusive lock is going to cause problems for me since the table is
> very active. Is there a way of getting around that or do I need to
> schedule the application that accesses this table?
If you don't need access to th
On April 28, 2006 04:41 am, "Bealach-na Bo" <[EMAIL PROTECTED]>
wrote:
> INFO: index "job_log_id_pkey" now contains 10496152 row versions in
> 59665 pages
See the 10496152 above? That means you have 10496152 rows of data in your
table. If those, only 365000 are alive. That means you have ba
78 matches
Mail list logo
