Re: [PERFORM] Request for feedback on hardware for a new database server
On Thu, Mar 17, 2011 at 7:51 PM, Oliver Charles postgresql-p...@ocharles.org.uk wrote: Hello, At MusicBrainz we're looking to get a new database server, and are hoping to buy this in the next couple of days. I'm mostly a software guy, but I'm posting this on behalf of Rob, who's actually going to be buying the hardware. Here's a quote of what we're looking to get: I'm working to spec out a bad-ass 1U database server with loads of cores (12), RAM (24GB) and drives (4 SAS) in a hardware RAID-1,0 configuration: 1 * SuperMicro 2016R-URF, 1U, redundant power supply, 4 SATA/SAS drive bays 2 2 * Intel Xeon X5650 Westmere 2.66GHz 12MB L3 Cache LGA 1366 95W Six-Core Server Processor 2 2 * Crucial 24GB (3 x 4GB) DDR3 SDRAM ECC Registered DDR3 1333, CT3KIT51272BV1339 1 1 * LSI MegaRAID SATA/SAS 9260-4i ($379) (linux support [1]) or 1 * HighPoint RocketRAID 4320 PCI-Express x8 ($429) or 1 * Adaptec RAID 3405 controller ($354) 4 * Fujitsu MBA3147RC 147GB 15000 RPM SuperMicro machines have treated us really well over time (better than Dell or Sun boxes), so I am really happy to throw more money in their direction. Redundant power supplies seem like a good idea for a database server. For $400 more we can get hexa core processors as opposed to quad core processors at 2.66Ghz. This seems like a really good deal -- any thoughts on this? Crucial memory has also served us really well, so that is a no-brainer. The RAID controller cards are where I need to most feedback! Of the LSI, Highpoint or Adaptec cards, which one is likely to have native linux support that does not require custom drivers to be installed? The LSI card has great specs at a great price point with Linux support, but installing the custom driver sounds like a pain. Does anyone have any experience with these cards? We've opted to not go for SSD drives in the server just yet -- it doesn't seem clear how well SSDs do in a driver environment. That's it -- anyone have any feedback? Just a quick bit more information. Our database is certainly weighted towards being read heavy, rather than write heavy (with a read-only web service accounting for ~90% of our traffic). Our tables vary in size, with the upperbound being around 10mil rows. It doesn't sound like SSD are a good fit for you -- you have small enough data that you can easily buffer in RAM and not enough writing to bottleneck you on the I/O side. The #1 server building mistake is focusing too much on cpu and not enough on i/o, but as noted by others you should be ok with a decent raid controller with a bbu on it. A bbu will make a tremendous difference in server responsiveness to sudden write bursts (like vacuum), which is particularly critical with your whole setup being on a single physical volume. Keeping your o/s and the db on the same LUN is a dangerous btw because it can limit your ability to log in and deal with certain classes of emergency situations. It's possible to do a hybrid type setup where you keep your o/s mounted on a CF or even a thumb drive(s) (most 1U servers now have internal usb ports for exactly this purpose) but this takes a certain bit of preparation and understanding what is sane to do with flash.. My other concern with your setup is you might not have room for expansion unless you have an unallocated pci-e slot in the back (some 1U have 1, some have 2). With an extra slot, you can pop a sas hba in the future attached to an enclosure if your storage requirements go up significantly. Option '2' is to go all out on the raid controller right now, so that you have both internal and external sas ports, although these tend to be much more expensive. Option '3' is to just 2U now, leaving yourself room for backplane expansion. Putting it all together, I am not a fan of 1U database boxes unless you are breaking the storage out -- there are ways you can get burned so that you have to redo all your storage volumes (assuming you are not using LVM, which I have very mixed feelings about) or even buy a completely new server -- both scenarios can be expensive in terms of downtime. 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] Help: massive parallel update to the same table
Hi, I have found the bug in my code that made the update to the same row in the table instead of two different row. Now I have all cores up and running 100%. Thank you for all your help. On Fri, Mar 18, 2011 at 3:21 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Red Maple redmaplel...@gmail.com wrote: Here is my function. If I comment out the update then it would run all the cores, if not then only one core will run CREATE OR REPLACE FUNCTION [...] select sysuptime into this_sysuptime from ap_sysuptime where ap_id = this_id for update; -- == -- if I comment out the next update --then all cores will be running, --else only one core will be running -- == update ap_sysuptime set sysuptime = this_sysuptime, last_contacted = now() where ap_id = this_id; This proves that you're not showing us the important part. The update locks the same row previously locked by the SELECT FOR UPDATE, so any effect at the row level would be a serialization failure based on a write conflict, which doesn't sound like your problem. They get different locks at the table level, though: http://www.postgresql.org/docs/9.0/interactive/explicit-locking.html#LOCKING-TABLES Somewhere in code you're not showing us you're acquiring a lock on the ap_sysuptime table which conflicts with a ROW EXCLUSIVE lock but not with a ROW SHARE lock. The lock types which could do that are SHARE and SHARE ROW EXCLUSIVE. CREATE INDEX (without CONCURRENTLY) could do that; otherwise it seems that you would need to be explicitly issuing a LOCK statement at one of these levels somewhere in your transaction. That is what is causing the transactions to run one at a time. -Kevin
[PERFORM] Analyze on temp table taking very long
I have a function where in In a cursor loop I 1. create a temp table (on commit drop) 2. insert data into it 3. Run Analyze on the table Select/update outside the loop. This has been running fine for a while on multiple setups, large and small volumes. The setups all have the same hardware configuration. On one particular setup with about 200k records and this analyze runs for 45min and then times out(statement timeout is set to 45 min). typically this takes a few seconds at best. But when I move the analyze outside the loop everything runs fine. An section of the code for reference. CREATE TEMP TABLE tmp_hierarchy_sorted ( sort_id serial, aqu_document_id integer,parent_id integer, ancestor_id integer, object_hierarchy character varying(255), object_hierarchy_array text[], levels integer) ON COMMIT DROP TABLESPACE tblspc_tmp ; CREATE UNIQUE INDEX tmp_hierarchy_sorted_aqu_document_id_idx ON tmp_hierarchy_sorted USING btree( aqu_document_id ) TABLESPACE tblspc_index;'; execute vSQL; --get min doc number for that collection based on existing promoted collections in the matter select coalesce(max(doc_number_max),0) into iMin_Doc_number FROM doc_Collection c WHERE exists (SELECT 1 FROM doc_collection c1 WHERE c1.id = iCollectionId and c1.matter_id = c.matter_id and c1.doc_number_prefix = c.doc_number_prefix) AND status = 'PROMOTED'; --go ancestor by ancestor for ones that are not loose files open curAncestor for select distinct id FROM aqu_document_hierarchy h where collection_Id = iCollectionId and ancestor_id =-1 and parent_id = -1 AND EXISTS (select 1 from aqu_document_hierarchy h1 where h1.ancestor_id = h.id ) order by id ; LOOP FETCH curAncestor into iAncestor_id; EXIT WHEN NOT FOUND; --insert each ancestor into the table as this is not part in the bulk insert vSQL := 'INSERT INTO tmp_hierarchy_sorted( aqu_document_id, parent_id , ancestor_id , object_hierarchy, object_hierarchy_array,levels) (select id, -1, -1, object_hierarchy, regexp_split_to_array(object_hierarchy, ''/'') ,0 from aqu_document_hierarchy where collection_Id =' || iCollectionId || ' AND id = ' || iAncestor_id || ')'; execute vSQL; -- insert filtered documents for that ancestor vSQL := 'INSERT INTO tmp_hierarchy_sorted (aqu_document_id, parent_id , ancestor_id , object_hierarchy, object_hierarchy_array, levels) ( SELECT id, parent_id, ancestor_id, object_hierarchy, regexp_split_to_array(object_hierarchy, ''/'') as object_hierarchy_array, array_length(regexp_split_to_array(object_hierarchy, ''/'') ,1) as levels FROM aqu_document_hierarchy h WHERE EXISTS (SELECT 1 FROM aqu_document_error_details e where e.aqu_document_id = h.id and e.exit_status in (2,3,4,5) ) AND ancestor_id = ' || iAncestor_id || ' ORDER BY regexp_split_to_array(object_hierarchy, ''/'') );'; execute vSQL; ANALYZE tmp_hierarchy_sorted; END LOOP; Thanks for the help -mridula The information contained in this email message and its attachments is intended only for the private and confidential use of the recipient(s) named above, unless the sender expressly agrees otherwise. Transmission of email over the Internet is not a secure communications medium. If you are requesting or have requested the transmittal of personal data, as defined in applicable privacy laws by means of email or in an attachment to email, you must select a more secure alternate means of transmittal that supports your obligations to protect such personal data. If the reader of this message is not the intended recipient and/or you have received this email in error, you must take no action based on the information in this email and you are hereby notified that any dissemination, misuse or copying or disclosure of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by email and delete the original message.
Re: [PERFORM] Performance on AIX
Phillippe, At Bull company, we want to answer a call for tender from a large company. And we are asked for information about PostgreSQL performance under AIX on Power 7 servers. Afilias runs PostgreSQL on AIX. I don't know the architecture, though. Or what they think of it as a platform. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- 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] Analyze on temp table taking very long
Mahadevan, Mridula mridula.mahade...@ironmountain.com writes: This has been running fine for a while on multiple setups, large and small volumes. The setups all have the same hardware configuration. On one particular setup with about 200k records and this analyze runs for 45min and then times out(statement timeout is set to 45 min). typically this takes a few seconds at best. But when I move the analyze outside the loop everything runs fine. Is it actually *running*, as in doing something, or is it just blocked? I can't immediately think of any reason for some other process to have a lock on a temp table that belongs to your process; but it seems unlikely that ANALYZE would randomly take much longer than expected unless something was preventing it from making progress. Look into pg_locks and/or watch the backend with strace next time this happens. 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
[PERFORM] good old VACUUM FULL
I posted many weeks ago about a severe problem with a table that was obviously bloated and was stunningly slow. Up to 70 seconds just to get a row count on 300k rows. I removed the text column, so it really was just a few columns of fixed data. Still very bloated. Table size was 450M The advice I was given was to do CLUSTER, but this did not reduce the table size in the least. Nor performance. Also to resize my free space map (which still does need to be done). Since that involves tweaking the kernel settings, taking the site down and rebooting postgres and exposing the system to all kinds of risks and unknowns and expensive experimentations I was unable to do it and have had to hobble along with a slow table in my backend holding up jobs. Much swearing that nobody should ever do VACUUM FULL. Manual advises against it. Only crazy people do that. Finally I decide to stop taking advice. ns= explain analyze select count(*) from fastadder_fastadderstatus; - Aggregate (cost=62602.08..62602.09 rows=1 width=0) (actual time=25320.000..25320.000 rows=1 loops=1) - Seq Scan on fastadder_fastadderstatus (cost=0.00..61815.86 rows=314486 width=0) (actual time=180.000..25140.000 rows=314493 loops=1) Total runtime: *25320.000* ms ns= vacuum full fastadder_fastadderstatus; took about 20 minutes ns= explain analyze select count(*) from fastadder_fastadderstatus; Aggregate (cost=7478.03..7478.04 rows=1 width=0) (actual time=940.000..940.000 rows=1 loops=1) - Seq Scan on fastadder_fastadderstatus (cost=0.00..6691.82 rows=314482 width=0) (actual time=0.000..530.000 rows=314493 loops=1) Total runtime: *940.000 ms* moral of the story: if your table is really bloated, just do VACUUM FULL CLUSTER will not reduce table bloat in and identical fashion
Re: [PERFORM] good old VACUUM FULL
On 23/03/11 11:52, felix wrote: I posted many weeks ago about a severe problem with a table that was obviously bloated and was stunningly slow. Up to 70 seconds just to get a row count on 300k rows. I removed the text column, so it really was just a few columns of fixed data. Still very bloated. Table size was 450M The advice I was given was to do CLUSTER, but this did not reduce the table size in the least. Nor performance. Also to resize my free space map (which still does need to be done). Since that involves tweaking the kernel settings, taking the site down and rebooting postgres and exposing the system to all kinds of risks and unknowns and expensive experimentations I was unable to do it and have had to hobble along with a slow table in my backend holding up jobs. Much swearing that nobody should ever do VACUUM FULL. Manual advises against it. Only crazy people do that. snip moral of the story: if your table is really bloated, just do VACUUM FULL You'll need to reindex that table now - vacuum full can bloat your indexes which will affect your other queries. reindex table fastadder_fastadderstatus; -- Postgresql php tutorials http://www.designmagick.com/ -- 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] ANTI-JOIN needs table, index scan not possible?
On Fri, Mar 11, 2011 at 10:32 AM, hans wulf lo...@gmx.net wrote: I need an ANTI-JOIN (not exists SELECT something from table.../ left join table WHERE table.id IS NULL) on the same table. Acutally I have an index to serve the not exists question, but the query planner chooses to to a bitmap heap scan. The table has 100 Mio rows, so doing a heap scan is messed up... It would be really fast if Postgres could compare the to indicies. Does Postgres have to visit the table for this ANTI-JOIN? A bitmap heap scan implies that a bitmap index scan is also being done, so it IS using the indexes. Now that leaves open the question of why it's not fast... but it's hard to guess the answer to that question without seeing at least the EXPLAIN output, preferably EXPLAIN ANALYZE. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
