Re: [PERFORM] Best suiting OS
Scott Marlowe wrote: Personally, I use Fedora, and my servers have been quite stable. One of our main web servers running Fedora: It's not that there can't be stable releases of FC, it's that it's not the focus of that project. So, if you get lucky, great! I can't imagine running a production DB on FC, with it's short supported life span and focus on development and not stability. I use Fedora, and it was a mistake. I am looking for a better solution. Fedora has been very stable (uptime of 430 days on one server), BUT... Realistically, the lifetime of a release is as low as SIX MONTHS. We bought servers just as a FC release was coming out, and thought we'd be safe by going with the older, tested release. But six months after that, the next FC release came out, and the version we'd installed fell off the support list. It takes almost no time with Fedora to run into big problems. Maybe there's a security release of ssh, you try to compile it, but it needs the latest gcc, but that's not available on your unsupported version of FC that you installed less than a year ago. Or maybe you need a new version of PHP to pass audit with your credit-card processor, but again, your FC release isn't supported so you have to uninstall the FC PHP, get the source, and compile PHP from scratch ... on and on it goes. Fedora is a very nice project, but it's not suitable for production database servers. This discussion has been very helpful indeed, and we appreciate everyone's contributions. I'm leaning towards a stable Debian release for our next upgrade, but there are several other well-reasoned suggestions here. Craig -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] updating a row in a table with only one row
Robert Haas wrote:
On Fri, Oct 2, 2009 at 9:54 AM, Merlin Moncure mmonc...@gmail.com wrote:
On Fri, Oct 2, 2009 at 4:18 AM, Michal Vitecek f...@mageo.cz wrote:
Hello everyone,
I'm using PostgreSQL 8.3.8 running on a server with 2 Xeon CPUs, 4GB
RAM, 4+2 disks in RAID 5 and CentOS 5.3. There's only one database
which dumped with pgdump takes ~0.5GB.
There are ~100 tables in the database and one of them (tableOne) always
contains only a single row. There's one index on it. However performing
update on the single row (which occurs every 60 secs) takes a
considerably long time -- around 200ms. The system is not loaded in any
way.
The table definition is:
CREATE TABLE tableOne (
value1 BIGINT NOT NULL,
value2 INTEGER NOT NULL,
value3 INTEGER NOT NULL,
value4 INTEGER NOT NULL,
value5 INTEGER NOT NULL,
);
CREATE INDEX tableOne_index1 ON tableOne (value5);
And the SQL query to update the _only_ row in the above table is:
('value5' can't be used to identify the row as I don't know it at the
time)
UPDATE tableOne SET value1 = newValue1, value2 = newValue2, value5 =
newValue5;
And this is what EXPLAIN says on the above SQL query:
DB= EXPLAIN UPDATE tableOne SET value1 = newValue1, value2 = newValue2,
value5 = newValue5;
LOG: duration: 235.948 ms statement: EXPLAIN UPDATE tableOne SET value1
= newValue1, value2 = newValue2, value5 = newValue5;
QUERY PLAN
Seq Scan on tableOne (cost=0.00..1.01 rows=1 width=14)
(1 row)
What takes PostgreSQL so long? I guess I could add a fake 'id' column,
create an index on it to identify the single row, but still -- the time
seems quite ridiculous to me.
it is ridiculous. your problem is almost definitely dead rows. I
can't recall (and I can't find the info anywhere) if the 'hot' feature
requires an index to be active -- I think it does. If so, creating a
dummy field and indexing it should resolve the problem. Can you
confirm the dead row issue by doing vacuum verbose and create the
index? please respond with your results, I'm curious. Also, is
autovacuum on? Have you measured iowait?
Autovacuum is on. I have dropped the superfluous index on value5.
The following is a result of running vacuum verbose analyze on the
table after the database has been running for 3 days (it was restored
from pgdump 3 days ago).
DB= vacuum verbose analyze tableOne;
INFO: vacuuming public.tableOne
INFO: tableOne: found 82 removable, 1 nonremovable row versions in 1 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 141 unused item pointers.
1 pages contain useful free space.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing public.tableOne
INFO: tableOne: scanned 1 of 1 pages, containing 1 live rows and 0 dead
rows; 1 rows in sample, 1 estimated total rows
LOG: duration: 23.833 ms statement: vacuum verbose analyze tableOne;
VACUUM
The problem occurs also on different tables but on tableOne this is
most striking as it is very simple. Also I should mention that the
problem doesn't occur every time -- but in ~1/6 cases.
Could the problem be the HW RAID card? There's ServerRAID 8k with 256MB
with write-back enabled. Could it be that its internal cache becomes
full and all disk I/O operations are delayed until it writes all
changes to hard drives?
Thanks,
--
Michal Vitecek (f...@mageo.cz)
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Best suiting OS
On Sun, 2009-10-04 at 15:51 -0400, Mark Mielke wrote: How do you provide effective support for a kernel that has 3000 back ported patches against it? This is again nonsense. Red Hat employs top kernel hackers. They do maintain vanilla kernel. It is not hard for Red Hat to maintain their own version ;) -- Devrim GÜNDÜZ, RHCE Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [PERFORM] Best suiting OS
On Thu, 2009-10-01 at 15:16 +0530, S Arvind wrote: What is the best Linux flavor for server which runs postgres alone. The postgres must handle greater number of database around 200 +. Performance on speed is the vital factor. Is it FreeBSD, CentOS, Fedora, Redhat xxx?? Go for Debian: * It is a free community, very active. * It is guaranteed to be upgradable. * Very easy to administrate via apt-get. Choose Debian SID or testing, which will provide the latest fixes. Kind regards, JMP -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Best suiting OS
On Sun, 2009-10-04 at 15:51 -0400, Mark Mielke wrote: If somebody were to come to you with a *new* deployment request, what would you recommend? Would you really recommend RHEL 5 *today*? Well, I would, and I do recommend people. RHEL5 is well-tested, and stable. Many hardware vendors support RHEL 5. The list goes on. If I would want to live with bleeding edge, I'd use Fedora in my servers. Otherwise, linux 2.6.31 is not *that much* better than Red Hat's 2.6.18. Actually the point is: Red Hat's 2.6.18 is not actually 2.6.18. I also want to state that Red Hat is adding new features to each point release, as you know. It is not that old. We have a customer that run ~ 1 hundred million transaction/hour , and they run RHEL. We also have another one that runs about that one, and guess which OS they are running? If I weren't using RHEL, I'd use Ubuntu. Nothing else. ...and disclaimer: I don't work for Red Hat. -- Devrim GÜNDÜZ, RHCE Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [PERFORM] updating a row in a table with only one row
On Mon, Oct 5, 2009 at 5:17 AM, Michal Vitecek f...@mageo.cz wrote: Could the problem be the HW RAID card? There's ServerRAID 8k with 256MB with write-back enabled. Could it be that its internal cache becomes full and all disk I/O operations are delayed until it writes all changes to hard drives? that's possible...the red flag is going to be iowait. if your server can't keep up with the sync demands for example, you will eventually outrun the write cache and you can start to see slow queries. With your server though it would take in the hundreds of (write) transactions per second to do that minimum. merlin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Bad performance of SELECT ... where id IN (...)
On Sun, Oct 4, 2009 at 9:58 PM, Omar Kilani omar.kil...@gmail.com wrote: Hi Xia, Try this patch: http://treehou.se/~omar/postgresql-8.4.1-array_sel_hack.patch It's a hack, but it works for us. I think you're probably spending most of your query time planning, and this patch helps speed things up 10x over here. Woof. I can see that helping in some situations, but what a foot-gun! ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Bad performance of SELECT ... where id IN (...)
Robert, On Mon, Oct 5, 2009 at 11:01 PM, Robert Haas robertmh...@gmail.com wrote: On Sun, Oct 4, 2009 at 9:58 PM, Omar Kilani omar.kil...@gmail.com wrote: Hi Xia, Try this patch: http://treehou.se/~omar/postgresql-8.4.1-array_sel_hack.patch It's a hack, but it works for us. I think you're probably spending most of your query time planning, and this patch helps speed things up 10x over here. Woof. I can see that helping in some situations, but what a foot-gun! We've run that patch for about 4 years (originally coded for us by Neil Conway for 8.2, I think), and have never seen any negatives from it. I'm not really sure what the alternatives are -- it never really makes sense to get the selectivity for thousands of items in the IN clause. I've never seen a different plan for the same query against a DB with that patch vs without -- it just takes a huge amount of time longer to run without it. :) But yeah, definitely a hack, and should only be used if needed -- hopefully there's some sort of official solution on the horizon. :) ...Robert Regards, Omar -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Best suiting OS
On Sun, 2009-10-04 at 10:05 -0400, Mark Mielke wrote: On 10/01/2009 03:44 PM, Denis Lussier wrote: I'm a BSD license fan, but, I don't know much about *BSD otherwise (except that many advocates say it runs PG very nicely). On the Linux side, unless your a dweeb, go with a newer, popular well supported release for Production. IMHO, that's RHEL 5.x or CentOS 5.x. Of course the latest SLES UBuntu schtuff are also fine. In other words, unless you've got a really good reason for it, stay away from Fedora OpenSuse for production usage. Lots of conflicting opinions and results in this thread. Also, a lot of hand waving and speculation. :-) RHEL and CentOS are particular bad *right now*. See here: http://en.wikipedia.org/wiki/RHEL http://en.wikipedia.org/wiki/CentOS Talk about hand waving and speculation - you are citing Wikipedia as a source?! For RHEL, look down to Release History and RHEL 5.3 based on Linux-2.6.18, released March, 2007. On the CentOS page you'll see it is dated April, 2007. CentOS is identical to RHEL on purpose, but always 1 to 6 months after the RHEL, since they take the RHEL source, re-build it, and then re-test it. Maybe that is the kernel version - but it isn't a vanilla kernel. Comparing kernel versions between distros is a dodgy business as they all have their own patch sets and backports of patches. Linux is up to Linux-2.6.31.1 right now: http://www.kernel.org/ And I very much doubt kernel version is a significant factor in performance unless you hit one of the lemon versions. Personally, I use Fedora, and my servers have been quite stable. One of our main web servers running Fedora: [m...@bambi]~% uptime 09:45:41 up 236 days, 10:07, 1 user, load average: 0.02, 0.04, 0.08 gourd-amber:~ # uptime 8:28am up 867 days 12:30, 1 user, load average: 0.24, 0.18, 0.10 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Bad performance of SELECT ... where id IN (...)
On Mon, Oct 5, 2009 at 1:24 PM, Omar Kilani omar.kil...@gmail.com wrote: I'm not really sure what the alternatives are -- it never really makes sense to get the selectivity for thousands of items in the IN clause. I've never seen a different plan for the same query against a DB with that patch vs without -- it just takes a huge amount of time longer to run without it. :) But yeah, definitely a hack, and should only be used if needed -- hopefully there's some sort of official solution on the horizon. :) start using temporary tables, transactions, and joins. Depending on source of the data (if the source is another query, than just combine it in one query with join), otherwise create temp table, fill out with data, and run query with join. If you do all that in transaction, it will be very fast. -- GJ
Re: Maybe OT, not sure Re: [PERFORM] Best suiting OS
Maybe - if the only thing the server is running is PostgreSQL. Show of hands - how many users who ONLY install PostgreSQL, and use a bare minimum OS install, choosing to not run any other software? Now, how many people ALSO run things like PHP, and require software more up-to-date than 3 years? Me. Not everyone is running LA?P stack applications. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Best suiting OS
On Mon, Oct 5, 2009 at 2:00 AM, Craig James craig_ja...@emolecules.com wrote: Fedora is a very nice project, but it's not suitable for production database servers. The trick is to write such a kick-ass application that before the Fedora support window ends, the load has increased enough that it's time to upgrade the hardware anyway. Also, I'd just like to mention that vi is a much better editor than emacs. ...Robert -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Speed / Server
But you should plan on partitioning to multiple db servers up front and save pain of conversion later on. A dual socket motherboard with 16 to 32 SAS drives and a fast RAID controller is WAY cheaper than a similar machine with 4 to 8 sockets is gonna be. And if you gotta go there anyway, might as well spend your money on other stuff. I agree. If you can partition that sensor data across multiple DBs and have your application do the knitting you might be better off. If I may be so bold, you might want to look at splaying the systems out across your backends. I'm just trying to think of a dimension that you won't want to aggregate across frequently. On the other hand, one of these 16 to 32 SAS drive systems with a raid card will likely get you a long way.
Re: [PERFORM] Best suiting OS
Robert Haas wrote (in part): Also, I'd just like to mention that vi is a much better editor than emacs. That is not my impression. I have used vi from when it first came out (I used ed before that) until about 1998 when I first installed Linux on one of my machines and started using emacs. I find that for some tasks involving global editing, that vi is a lot easier to use. But for most of the things I do on a regular basis, if find emacs better. So, for me, it is not which is the better editor, but which is the better editor for the task at hand. -- .~. Jean-David Beyer Registered Linux User 85642. /V\ PGP-Key: 9A2FC99A Registered Machine 241939. /( )\ Shrewsbury, New Jerseyhttp://counter.li.org ^^-^^ 09:30:01 up 4 days, 18:29, 3 users, load average: 4.09, 4.07, 4.09 -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Best suiting OS
On Mon, 2009-10-05 at 09:37 -0400, Jean-David Beyer wrote: Robert Haas wrote (in part): Also, I'd just like to mention that vi is a much better editor than emacs. That is not my impression. I have used vi from when it first came out (I used ed before that) until about 1998 when I first installed Linux on one of my machines and started using emacs. I find that for some tasks involving global editing, that vi is a lot easier to use. But for most of the things I do on a regular basis, if find emacs better. So, for me, it is not which is the better editor, but which is the better editor for the task at hand. Both vi and emacs are obsolete. Bow before the glory of gedit! -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM][OT] Best suiting OS
Hi Jean-David, On Mon, 2009-10-05 at 15:37 +0200, Jean-David Beyer wrote: Robert Haas wrote (in part): Also, I'd just like to mention that vi is a much better editor than emacs. That is not my impression. I have used vi from when it first came out (I used ed before that) until about 1998 when I first installed Linux on one of my machines and started using emacs. I find that for some tasks involving global editing, that vi is a lot easier to use. But for most of the things I do on a regular basis, if find emacs better. So, for me, it is not which is the better editor, but which is the better editor for the task at hand. You are probably absolutely right, but Robert only wanted to point out that this conversation gets in the flame-war direction, in his subtle way of doing this... Cheers, Csaba. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Query plan for NOT IN
mnw21-modmine-r13features-copy=# select count(*) from project; count --- 10 (1 row) mnw21-modmine-r13features-copy=# select count(*) from intermineobject; count -- 26344616 (1 row) mnw21-modmine-r13features-copy=# \d intermineobject; Table public.intermineobject Column | Type | Modifiers +-+--- object | text| id | integer | not null class | text| Indexes: intermineobject_pkey UNIQUE, btree (id) mnw21-modmine-r13features-copy=# explain select * from project where id NOT IN (SELECT id FROM intermineobject); QUERY PLAN Seq Scan on project (cost=1476573.93..1476575.05 rows=5 width=183) Filter: (NOT (hashed SubPlan 1)) SubPlan 1 - Seq Scan on intermineobject (cost=0.00..1410720.74 rows=26341274 width=4) (4 rows) This query plan seems to me to be a little slow. Surely it could iterate through the ten project rows and perform ten index lookups in the big table? Matthew -- Riker: Our memory pathways have become accustomed to your sensory input. Data: I understand - I'm fond of you too, Commander. And you too Counsellor -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Query plan for NOT IN
On Mon, Oct 5, 2009 at 2:52 PM, Matthew Wakeling matt...@flymine.orgwrote: mnw21-modmine-r13features-copy=# select count(*) from project; count --- 10 (1 row) mnw21-modmine-r13features-copy=# select count(*) from intermineobject; count -- 26344616 (1 row) mnw21-modmine-r13features-copy=# \d intermineobject; Table public.intermineobject Column | Type | Modifiers +-+--- object | text| id | integer | not null class | text| Indexes: intermineobject_pkey UNIQUE, btree (id) mnw21-modmine-r13features-copy=# explain select * from project where id NOT IN (SELECT id FROM intermineobject); QUERY PLAN Seq Scan on project (cost=1476573.93..1476575.05 rows=5 width=183) Filter: (NOT (hashed SubPlan 1)) SubPlan 1 - Seq Scan on intermineobject (cost=0.00..1410720.74 rows=26341274 width=4) (4 rows) This query plan seems to me to be a little slow. Surely it could iterate through the ten project rows and perform ten index lookups in the big table? try using join instead of 'not in'.. select p.* from project p left join intermineobject i on i.id=p.id where i.id is null; -- GJ
Re: [PERFORM] Query plan for NOT IN
2009/10/5 Matthew Wakeling matt...@flymine.org Yes, that does work, but only because id is NOT NULL. I thought Postgres 8.4 had had a load of these join types unified to make it less important how the query is written? well, as a rule of thumb - unless you can't think of a default value of column - don't use nulls. So using nulls as default 'idunno' - is a bad practice, but everybody's opinion on that differ. But back on a subject, postgresql is very very poor performance wise with [NOT] IN () type of constructs. So if you can, avoid them, and learn to use joins. -- GJ
Re: [PERFORM] Query plan for NOT IN
Matthew Wakeling matt...@flymine.org writes: Yes, that does work, but only because id is NOT NULL. I thought Postgres 8.4 had had a load of these join types unified to make it less important how the query is written? NOT IN is not easily optimizable because of its odd behavior in the presence of nulls. Use NOT EXISTS instead, or that left join hack. regards, tom lane -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Best suiting OS
Stefan Kaltenbrunner wrote: Devrim GÜNDÜZ wrote: On Mon, 2009-10-05 at 12:07 +0200, Jean-Michel Pouré wrote: Go for Debian: * It is a free community, very active. Well, we need to state that this is not a unique feature. * It is guaranteed to be upgradable. Depends. I had lots of issues with upgrade process in the past -- but yeah, it is much better than most distros. * Very easy to administrate via apt-get. Right. apt is better than yum (in terms of speed). Choose Debian SID or testing, which will provide the latest fixes. One thing that I don't like about Debian is their update policy. If upstream is releasing a security update, I'd like to be able to find new packages as upstream announces updated sets. Yes, I'm talking about PostgreSQL here. This is exactly what Debian does for a while now(at least for PostgreSQL).. Ie.: Debian Etch aka has 8.1.18 and Debian Lenny has 8.3.8... Debian Etch aka oldstable and Debian Lenny (the current release)... Stefan -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Best suiting OS
Scott Carey wrote: On 10/3/09 7:35 PM, Karl Denninger k...@denninger.net wrote: I am a particular fan of FreeBSD, and in some benchmarking I did between it and CentOS FreeBSD 7.x literally wiped the floor with the CentOS release I tried on IDENTICAL hardware. I also like the 3ware raid coprocessors - they work well, are fast, and I've had zero trouble with them. -- Karl With CentOS 5.x, I have to do quite a bit of tuning to get it to perform well. I often get almost 2x the performance after tuning. For I/O -- Deadline scheduler + reasonably large block device read-ahead + XFS configured with large 'allocsize' settings (8MB to 80MB) make a huge difference. Furthermore, the 3ware 35xx and 36xx (I think) I tried performed particularly badly out of the box without tuning on CentOS. So, Identical hardware or not, both have to be tuned well to really compare anyway. However, I have certainly seen some inefficiencies with Linux and large use of shared memory -- and I wouldn't be surprised if these problems don't exist on FreeBSD or OpenSolaris. I don't run the 3x series 3ware boards. If I recall correctly they're not true coprocessor boards and rely on the host CPU. Those are always going to be a lose compared to a true coprocessor with dedicated cache memory on the card. The 9xxx series boards are, and are extremely fast (make sure you install the battery backup or run on a UPS, set the appropriate flags, and take your chances - writeback caching makes a HUGE difference.) Other than pinning shared memory on FreeBSD (and increasing a couple of boot-time tunables to permit large enough shared segments and semaphore lists) little is required to get excellent performance. The LSI cards that DELL, Intel and a few others have used (these appear to be deprecated now as it looks like LSI bought 3ware) also work well but their user interface is somewhat of a pain in the butt compared to 3Ware's. -- Karl attachment: karl.vcf -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Best suiting OS
However, I have certainly seen some inefficiencies with Linux and large use of shared memory -- and I wouldn't be surprised if these problems don't exist on FreeBSD or OpenSolaris. This came on the freebsd-performance-list a few days ago. http://docs.freebsd.org/cgi/getmsg.cgi?fetch=13001+0+current/freebsd-performance -- regards Claus When lenity and cruelty play for a kingdom, the gentler gamester is the soonest winner. Shakespeare -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Best suiting OS
On 10/5/09 10:27 AM, Karl Denninger k...@denninger.net wrote: Scott Carey wrote: On 10/3/09 7:35 PM, Karl Denninger k...@denninger.net mailto:k...@denninger.net wrote: I am a particular fan of FreeBSD, and in some benchmarking I did between it and CentOS FreeBSD 7.x literally wiped the floor with the CentOS release I tried on IDENTICAL hardware. I also like the 3ware raid coprocessors - they work well, are fast, and I've had zero trouble with them. -- Karl With CentOS 5.x, I have to do quite a bit of tuning to get it to perform well. I often get almost 2x the performance after tuning. For I/O -- Deadline scheduler + reasonably large block device read-ahead + XFS configured with large 'allocsize' settings (8MB to 80MB) make a huge difference. Furthermore, the 3ware 35xx and 36xx (I think) I tried performed particularly badly out of the box without tuning on CentOS. So, Identical hardware or not, both have to be tuned well to really compare anyway. However, I have certainly seen some inefficiencies with Linux and large use of shared memory -- and I wouldn't be surprised if these problems don't exist on FreeBSD or OpenSolaris. I don't run the 3x series 3ware boards. If I recall correctly they're not true coprocessor boards and rely on the host CPU. Those are always going to be a lose compared to a true coprocessor with dedicated cache memory on the card. I screwed up, it was the 95xx and 96xx that stink for me. (Adaptec 2x as fast, PERC 6 25% faster) with 1TB SATA drives. Thought 96xx was a good chunk faster due to the faster interface. The 9xxx series boards are, and are extremely fast (make sure you install the battery backup or run on a UPS, set the appropriate flags, and take your chances - writeback caching makes a HUGE difference.) Not at all in my experience, 12 drives in raid 10, and 300MB/sec sequential trasfer rate = crap. Heavily tweaked, 450MB/sec. (Adaptec 5805 = 600MB/sec). Other than pinning shared memory on FreeBSD (and increasing a couple of boot-time tunables to permit large enough shared segments and semaphore lists) little is required to get excellent performance. The LSI cards that DELL, Intel and a few others have used (these appear to be deprecated now as it looks like LSI bought 3ware) also work well but their user interface is somewhat of a pain in the butt compared to 3Ware's. -- Karl -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Best suiting OS
Claus Guttesen wrote: However, I have certainly seen some inefficiencies with Linux and large use of shared memory -- and I wouldn't be surprised if these problems don't exist on FreeBSD or OpenSolaris. This came on the freebsd-performance-list a few days ago. http://docs.freebsd.org/cgi/getmsg.cgi?fetch=13001+0+current/freebsd-performance Geezus - that's a BIG improvement. I have not yet benchmarked FreeBSD 8.x - my production systems are all on FreeBSD 7.x at present. The improvement going there from 6.x was MASSIVE. 8.x is on my plate to start playing with in the next couple of months. 8.x, I will note, is NOT YET RELEASED, and you're playing with fire to run it in a production environment at the present time. I've been a strong proponent (and user) of FreeBSD for years, going back to when I ran my ISP on it. It has had its problems from time to time as do all operating systems, but when 8.X is released and is stable it will definitely be worth moving to - IF its stable. I have systems with two years of uptime on them running FreeBSD 6.x in production use, and haven't had an actual OS crash on a production FreeBSD machine in a very long time. One thing FreeBSD has focused more and more on is SMP efficiency and effective utilization of all the cores in the system. I have several systems running 8-way SMP (Quad Xeons) and a couple running the CoreQuadExtreme (4 physical cores w/2 threads each via HT) and get excellent performance out of all of them. The key is to make sure your I/O subsystem is up to the job and split storage across spindles and controllers as necessary so you don't run into bottlenecks there. -- Karl attachment: karl.vcf -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Best suiting OS
Axel Rau wrote: Am 05.10.2009 um 19:42 schrieb Karl Denninger: I have not yet benchmarked FreeBSD 8.x - my production systems are all on FreeBSD 7.x at present. The improvement going there from 6.x was MASSIVE. 8.x is on my plate to start playing with in the next couple of months. Did you ever try gjournal or zfs as tablespace? gjournal, no. ZFS has potential stability issues - I am VERY interested in it when those are resolved. It looks good on a test platform but I'm unwilling to run it in production; there are both reports of crashes and I have been able to crash it under some (admittedly rather extreme) synthetic loads. -- Karl attachment: karl.vcf -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Best suiting OS
Am 05.10.2009 um 19:42 schrieb Karl Denninger: I have not yet benchmarked FreeBSD 8.x - my production systems are all on FreeBSD 7.x at present. The improvement going there from 6.x was MASSIVE. 8.x is on my plate to start playing with in the next couple of months. Did you ever try gjournal or zfs as tablespace? Axel --- axel@chaos1.de PGP-Key:29E99DD6 +49 151 2300 9283 computing @ chaos claudius -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Best suiting OS
Scott Carey wrote: On 10/5/09 10:27 AM, Karl Denninger k...@denninger.net wrote: I don't run the 3x series 3ware boards. If I recall correctly they're not true coprocessor boards and rely on the host CPU. Those are always going to be a lose compared to a true coprocessor with dedicated cache memory on the card. I screwed up, it was the 95xx and 96xx that stink for me. (Adaptec 2x as fast, PERC 6 25% faster) with 1TB SATA drives. Thought 96xx was a good chunk faster due to the faster interface. I'm running the 9650s in most of my busier machines. Haven't tried a PERC card yet - its on my list. Most of my stuff is configured as RAID 1 although I have a couple of RAID 10 arrays in service; depending on the data set and how it splits up I prefer to have more control of how I/O is partitioned rather than let the controller pick through striping. I don't think I have any of the 95xx stuff out in the wild at present; it didn't do particularly well in my testing in terms of performance. -- Karl attachment: karl.vcf -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Best suiting OS
On Sun, 4 Oct 2009, Mark Mielke wrote: I can show you tickets where RedHat has specifically state they *will not* update the kernel to better support new hardware, for fear of breaking support for older hardware. There are two reasonable paths you'll find in the Open Source world, which mirror the larger industry at large: 1) Branch a stable release rarely. Sit on it for a while without changing anything before release. Backport only critical stuff (important bug fixes) into the stable version once it's out there. Support that version for years. Examples of this model include RedHat and PostgreSQL, albeit with the latter having a much more regular release schedule than most long-term release pieces of software. 2) Branch a stable release often. Push it out the door with fairly recent components. Backport little, because a new release is coming out the door soon enough anyway. It's impossible for this model to backport as much as (1), because they have so many more releases to handle, and there's no pressure to do so because upgrade to the latest release to fix is usually an options. Examples of this model include the Linux kernel proper and Ubuntu. My personal belief is that (2) never leads to stable software, and that for the complexity level of the projects I follow you're lucky you can get a stable version of one piece of software if you focus on it about every year. Once every two years would be better, because as you correctly note it takes about that long for many hardware drivers to go from cutting-edge to old, and that would give less disruption to admins. That is unfortunately both more aggressive than the long-term release stable versions provided by RedHat and less than the hyper-aggressive schedules you'll find in Ubuntu and Fedora. It does happen to be very close to the PostgreSQL stable release frequency though: 8.0 2005-01-19 8.1 2005-11-08 8.2 2006-12-05 8.3 2008-02-04 8.4 2009-07-01 RedHat does a commendable job of backporting way more stuff than anybody else I'm aware of. The SATA issues you mention are actually a worst-case for their development model. The big SATA switch-over with Parallel PATA merge happened in 2.6.19. My recollection is that this was such a mess at first is basically forced RedHat to release RHEL5 with 2.6.18, as there wasn't expected to be a stable ATA stack from the resulting chaos for a few releases they could use; anecdotally, I didn't find Linux re-stabilized until between 2.6.20 and 2.6.22, depending on your hardware. I contrast this with Ubuntu, which I can't accept as a server because nothing I run into *ever* gets backported. I know they backport something, because I see the changelogs, but never what I run into. I encounter a bug or two in ever new Ubuntu release that makes life difficult for me, and in every case so far the resolution was fixed in next letter. In two of those cases I recall I saw the same bug fix (from an upstream package) was backported into RHEL. All 7 of the machines I installed RHEL 5.3 on *failed* to detect the SATA controller, and the install process completed at 2 Mbyte/s. After the machines were up, I discovered the issue is a known issue, and that RedHat would not patch the problem, but instead suggested a change to grub.conf. Is this stable? With all due respect, this was operator error on your part. Buying the hardware and then guessing that everything will work out fine with the OS install isn't ever a path to stable either. I (and every other person who deals regularly with RHEL on increasingly new hardware) could have told you this was going to be a disaster, that you don't try to provision a server using native SATA with unknown compatibility on that OS. I don't have this problem (for the database servers at work at least--suffered through it plenty with random white boxes). I buy from a vendor who figures this out and old sells me stuff that works on RHEL. You have a larger process problem you can't blame on the software. They finally back-ported FUSE - but did you know their 2.6.18 kernel has something like 3000 patches that they maintain against it? Does this not sound insane? How do you provide effective support for a kernel that has 3000 back ported patches against it? How exactly is this any different from effective support for the kernel at large, which integrates way more patches than that between releases? I see RedHat as having a much smaller set of patches to manage, which is one reason their releases are more stable than pick a random kernel release. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Best suiting OS
On 10/5/09 11:15 AM, Karl Denninger k...@denninger.net wrote: Scott Carey wrote: On 10/5/09 10:27 AM, Karl Denninger k...@denninger.net wrote: I don't run the 3x series 3ware boards. If I recall correctly they're not true coprocessor boards and rely on the host CPU. Those are always going to be a lose compared to a true coprocessor with dedicated cache memory on the card. I screwed up, it was the 95xx and 96xx that stink for me. (Adaptec 2x as fast, PERC 6 25% faster) with 1TB SATA drives. Thought 96xx was a good chunk faster due to the faster interface. I'm running the 9650s in most of my busier machines. Haven't tried a PERC card yet - its on my list. Most of my stuff is configured as RAID 1 although I have a couple of RAID 10 arrays in service; depending on the data set and how it splits up I prefer to have more control of how I/O is partitioned rather than let the controller pick through striping. I don't think I have any of the 95xx stuff out in the wild at present; it didn't do particularly well in my testing in terms of performance. -- Karl Let me make sure I clarify here -- The 3ware 9[56]xx issues I have seen were with throughput on larger RAID array sizes -- 8+ disks total. On smaller arrays, I have not tested. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Speed while runnning large transactions.
On Fri, 2 Oct 2009, Tom Lane wrote: The people who hollered loudest about this seemed to often have long-running read-only transactions in parallel with lots of short read-write transactions. Which makes sense if you think about it. Long-running read-only reports are quite common in DBA land. I'm sure most people can think of an example in businesses they work with that you can't refactor away into smaller chunks, everybody seems to have their own variation on the big overnight report. Long-running read-write transactions are much less common, and a bit more likely to break into logical chunks if you architect the design right, using techniques like staging areas for bulk operations and write barriers for when they can be applied. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Distributed/Parallel Computing
Hi Team, This question may have asked many times previously also, but I could not find a solution for this in any post. any help on the following will be greatly appreciated. We have a PG DB with PostGIS functions. There are around 100 tables in the DB and almost all the tables contains 1 million records, around 5 table contains more than 20 million records. The total DB size is 40GB running on a 16GB, 2 x XEON 5420, RAID6, RHEL5 64bit machines, the questions is 1. The geometry calculations which we does are very complex and it is taking a very long time to complete. We have optimised PG config to the best, now we need a mechanism to distribute these queries to multiple boxes. What is best recommended way for this distributed/parallel deployment. We have tried PGPOOL II, but the performance is not satisfactory. Going for a try with GridSQL 2. How we can distribute/split these large tables to multiple disks of different nodes? Thanks in advance Viji
Re: [PERFORM] Query plan for NOT IN
Grzegorz Jaśkiewicz wrote: well, as a rule of thumb - unless you can't think of a default value of column - don't use nulls. So using nulls as default 'idunno' - is a bad practice, but everybody's opinion on that differ. I don't understand this point of view. The concept of null was introduced into the SQL vernacular by Codd and Date expressly to represent unknown values. -- Guy Rouillier -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Best suiting OS
Scott Carey wrote: On 10/5/09 11:15 AM, Karl Denninger k...@denninger.net wrote: I'm running the 9650s in most of my busier machines. Haven't tried a PERC card yet - its on my list. Most of my stuff is configured as RAID 1 although I have a couple of RAID 10 arrays in service; depending on the data set and how it splits up I prefer to have more control of how I/O is partitioned rather than let the controller pick through striping. I don't think I have any of the 95xx stuff out in the wild at present; it didn't do particularly well in my testing in terms of performance. -- Karl Let me make sure I clarify here -- The 3ware 9[56]xx issues I have seen were with throughput on larger RAID array sizes -- 8+ disks total. On smaller arrays, I have not tested. Interesting... I'm curious if that's why I haven't run into it - I get damn close to N x rotational on sequential I/O out of these boards; you can't really do better than the physics allow :) I'll have to play with some larger ( 8 unit) Raid 1 and Raid 10 arrays and compare to see if there's a knee point and whether its a function of the aggregation through the chipset or whether it's a card issue. I suspect it's related to the aggregation as otherwise I'd have seen it on some of my larger configurations, but I tend to run multiple adapters for anything more than 8 spindles, which precludes the situation you've seen. Of course if you NEED 12 spindles in one logical device for capacity reasons -- Karl attachment: karl.vcf -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Best suiting OS
Claus Guttesen kome...@gmail.com wrote: http://docs.freebsd.org/cgi/getmsg.cgi?fetch=13001+0+current/freebsd-performance Not being particularly passionate about any OS, I've been intrigued by the FreeBSD benchmarks. However, management is reluctant to use boxes which don't have heavily-advertised decals on the front. At the moment they're going with IBM X-series boxes, and FreeBSD isn't supported, so we'd be on our own. Has anyone had any experience with this combination? (In particular, our biggest machines are x3850 M2 boxes.) Oh, and of course I dispute the supremacy of vim as an editor -- why use that when you've got ed? ;-) -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Best suiting OS
Am 05.10.2009 um 20:06 schrieb Karl Denninger: gjournal, no. ZFS has potential stability issues - I am VERY interested in it when those are resolved. It looks good on a test platform but I'm unwilling to run it in production; there are both reports of crashes and I have been able to crash it under some (admittedly rather extreme) synthetic loads. How do you prevent from long running fsck with TB size ufs partitions? I had some hope for zfs13 and fbsd 8.0. Axel --- axel@chaos1.de PGP-Key:29E99DD6 +49 151 2300 9283 computing @ chaos claudius -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Best suiting OS
Claus Guttesen kome...@gmail.com wrote: http://docs.freebsd.org/cgi/getmsg.cgi?fetch=13001+0+current/freebsd-performance Not being particularly passionate about any OS, I've been intrigued by the FreeBSD benchmarks. However, management is reluctant to use boxes which don't have heavily-advertised decals on the front. At the moment they're going with IBM X-series boxes, and FreeBSD isn't supported, so we'd be on our own. Has anyone had any experience with this combination? (In particular, our biggest machines are x3850 M2 boxes.) You can download a live-cd and see if it recognizes disk-controller, nic etc. on HP bce and bge, em GB nics works fine. Oh, and of course I dispute the supremacy of vim as an editor -- why use that when you've got ed? ;-) I have tried edlin on dos 3 or something like that. But don't recall the commands! :-) -- regards Claus When lenity and cruelty play for a kingdom, the gentler gamester is the soonest winner. Shakespeare -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Speed / Server
On Mon, Oct 5, 2009 at 7:30 AM, Nikolas Everett nik9...@gmail.com wrote: But you should plan on partitioning to multiple db servers up front and save pain of conversion later on. A dual socket motherboard with 16 to 32 SAS drives and a fast RAID controller is WAY cheaper than a similar machine with 4 to 8 sockets is gonna be. And if you gotta go there anyway, might as well spend your money on other stuff. I agree. If you can partition that sensor data across multiple DBs and have your application do the knitting you might be better off. If I may be so bold, you might want to look at splaying the systems out across your backends. I'm just trying to think of a dimension that you won't want to aggregate across frequently. Agreed back. If there's a logical dimension to split data on, it becomes much easier to throw x machines at it than to try and build one ubermachine to handle it all. On the other hand, one of these 16 to 32 SAS drive systems with a raid card will likely get you a long way. Yes they can. We're about to have to add a third db server, cause this is the load on our main slave db: procs ---memory-- ---swap-- -io --system-- -cpu-- r b swpd free buff cache si sobibo in cs us sy id wa st 22 0220 633228 229556 2843297600 638 30400 21 3 73 3 0 19 1220 571980 229584 284351800096 7091 9796 90 6 4 0 0 20 0220 532208 229644 2844024400 140 3357 7110 9175 90 6 3 0 0 19 1220 568440 229664 2844368800 146 1527 7765 10481 90 7 3 0 0 9 1220 806668 229688 284452400099 326 6661 10326 89 6 5 0 0 9 0220 814016 229712 284461440054 1544 7456 10283 90 6 4 0 0 11 0220 782876 229744 284476280096 406 6619 9354 90 5 5 0 0 29 1220 632624 229784 2844996400 113 994 7109 9958 90 7 3 0 0 It's working fine. This has a 16 15k5 SAS disks. A 12 Disk RAID-10, a 2 disk mirror for pg_xlog / OS, and two spares. It has 8 opteron cores and 32Gig ram. We're completely CPU bound because of the type of app we're running. So time for slave number 2... -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Best suiting OS
Axel Rau wrote: Am 05.10.2009 um 20:06 schrieb Karl Denninger: gjournal, no. ZFS has potential stability issues - I am VERY interested in it when those are resolved. It looks good on a test platform but I'm unwilling to run it in production; there are both reports of crashes and I have been able to crash it under some (admittedly rather extreme) synthetic loads. How do you prevent from long running fsck with TB size ufs partitions? I had some hope for zfs13 and fbsd 8.0. Axel Turn on softupdates. Fsck is deferred and the system comes up almost instantly even with TB-sized partitions; the fsck then cleans up the cruft. -- Karl attachment: karl.vcf -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: Maybe OT, not sure Re: [PERFORM] Best suiting OS
On Mon, Oct 5, 2009 at 6:35 AM, Adam Tauno Williams awill...@opengroupware.us wrote: Maybe - if the only thing the server is running is PostgreSQL. Show of hands - how many users who ONLY install PostgreSQL, and use a bare minimum OS install, choosing to not run any other software? Now, how many people ALSO run things like PHP, and require software more up-to-date than 3 years? Me. Not everyone is running LA?P stack applications. Me too, even though we are running LAPP stack. Not all LAPP stacks are small intranet servers with a few hundred users. We service 1.5 Million users running 10k to 20k page views a minute. On a server farm that fills 2/3 of a cabinet. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Distributed/Parallel Computing
On Mon, Oct 5, 2009 at 12:11 PM, Viji V Nair v...@fedoraproject.org wrote: Hi Team, This question may have asked many times previously also, but I could not find a solution for this in any post. any help on the following will be greatly appreciated. We have a PG DB with PostGIS functions. There are around 100 tables in the DB and almost all the tables contains 1 million records, around 5 table contains more than 20 million records. The total DB size is 40GB running on a 16GB, 2 x XEON 5420, RAID6, RHEL5 64bit machines, the questions is 1. The geometry calculations which we does are very complex and it is taking a very long time to complete. We have optimised PG config to the best, now we need a mechanism to distribute these queries to multiple boxes. What is best recommended way for this distributed/parallel deployment. We have tried PGPOOL II, but the performance is not satisfactory. Going for a try with GridSQL What is the nature of the transactions being run? Are they primarily read-only other than bulk updates to the GIS data, are they OLTP in regards to the GIS data, or are they transactional with regards to other tables but read-only with respect to the GIS? Jeff -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
