X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 58BF93A5425 for ; Mon, 24 Jan 2005 15:46:13 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 47293-09 for ; Mon, 24 Jan 2005 15:45:59 +0000 (GMT) Received: from outputservices.com (outputt1130.customer.frii.net [216.17.159.130]) by svr1.postgresql.org (Postfix) with ESMTP id 590173A550C for ; Mon, 24 Jan 2005 15:46:01 +0000 (GMT) Received: from outputservices.com (outputservices.com [137.106.76.15]) by outputservices.com (8.10.2+Sun/8.11.3) with ESMTP id j0OFjvJ21972; Mon, 24 Jan 2005 08:45:57 -0700 (MST) Message-ID: <41F51835.3040202@outputservices.com> Date: Mon, 24 Jan 2005 08:45:57 -0700 From: Marty Scholes User-Agent: Mozilla/5.0 (X11; U; SunOS sun4u; en-US; rv:0.9.9) Gecko/20020517 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Tatsuo Ishii Cc: herve@elma.fr, pgsql-performance@postgresql.org Subject: Re: PostgreSQL clustering VS MySQL clustering References: <41F144FE.30902@outputservices.com> <41F14758.4030602@outputservices.com> <20050122.121300.41631131.t-ishii@sra.co.jp> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.05 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200501/441 X-Sequence-Number: 10131 Tatsuo, I agree completely that vacuum falls apart on huge tables. We could=20 probably do the math and figure out what the ratio of updated rows per=20 total rows is each day, but on a constantly growing table, that ratio=20 gets smaller and smaller, making the impact of dead tuples in the table=20 proportionately less and less. If multi-version indexes are handled the same way as table rows, then=20 the indexes will also suffer the same fate, if not worse. For huge=20 tables, the b-tree depth can get fairly large. When a b-tree is of=20 depth X and the machine holds the first Y levels of the b-tree in=20 memory, then each table row selected requires a MINIMUM of (X-Y) disk=20 access *before* the table row is accessed. Substitute any numbers you=20 want for X and Y, but you will find that huge tables require many index=20 reads. Index updates are even worse. A table row update requires only a copy=20 of the row. An index update requires at least a copy of the leaf node,=20 and possibly more nodes if nodes must be split or collapsed. These=20 splits and collapses can cascade, causing many nodes to be affected. This whole process takes place for each and every index affected by the=20 change, which is every index on the table when a row is added or=20 deleted. All of this monkeying around takes place above and beyond the=20 simple change of the row data. Further, each and every affected index=20 page is dumped to WAL. Assuming the indexes have the same MVCC proprties of row data, then the=20 indexes would get dead tuples at a rate far higher than that of the=20 table data. So yes, vacuuming is a problem on large tables. It is a bigger problem=20 for indexes. On large tables, index I/O comprises most of the I/O mix. Don't take my word for it. Run a benchmark on Pg. Then, soft-link the=20 index files and the WAL directories to a RAM disk. Rerun the benchmark=20 and you will find that Pg far faster, much faster than if only the data=20 were on the RAM disk. Marty Tatsuo Ishii wrote: > IMO the bottle neck is not WAL but table/index bloat. Lots of updates > on large tables will produce lots of dead tuples. Problem is, There' > is no effective way to reuse these dead tuples since VACUUM on huge > tables takes longer time. 8.0 adds new vacuum delay > paramters. Unfortunately this does not help. It just make the > execution time of VACUUM longer, that means more and more dead tuples > are being made while updating. >=20 > Probably VACUUM works well for small to medium size tables, but not > for huge ones. I'm considering about to implement "on the spot > salvaging dead tuples". > -- > Tatsuo Ishii >=20 >=20 >>This is probably a lot easier than you would think. You say that your = >>DB will have lots of data, lots of updates and lots of reads. >> >>Very likely the disk bottleneck is mostly index reads and writes, with = >>some critical WAL fsync() calls. In the grand scheme of things, the=20 >>actual data is likely not accessed very often. >> >>The indexes can be put on a RAM disk tablespace and that's the end of=20 >>index problems -- just make sure you have enough memory available. Als= o=20 >>make sure that the machine can restart correctly after a crash: the=20 >>tablespace is dropped and recreated, along with the indexes. This will= =20 >>cause a machine restart to take some time. >> >>After that, if the WAL fsync() calls are becoming a problem, put the WA= L=20 >>files on a fast RAID array, etiher a card or external enclosure, that=20 >>has a good amount of battery-backed write cache. This way, the WAL=20 >>fsync() calls will flush quickly to the RAM and Pg can move on while th= e=20 >>RAID controller worries about putting the data to disk. With WAL, low = >>access time is usually more important than total throughput. >> >>The truth is that you could have this running for not much money. >> >>Good Luck, >>Marty >> >> >>>Le Jeudi 20 Janvier 2005 19:09, Bruno Almeida do Lago a =E9crit : >>> > Could you explain us what do you have in mind for that solution? I = mean, >>> > forget the PostgreSQL (or any other database) restrictions and=20 >>>explain us >>> > how this hardware would be. Where the data would be stored? >>> > >>> > I've something in mind for you, but first I need to understand your= =20 >>>needs! >>> >>>I just want to make a big database as explained in my first mail ... A= t the >>>beginning we will have aprox. 150 000 000 records ... each month we wi= ll=20 >>>add >>>about 4/8 millions new rows in constant flow during the day ... and in= same >>>time web users will access to the database in order to read those data= =2E >>>Stored data are quite close to data stored by google ... (we are not=20 >>>making a >>>google clone ... just a lot of data many small values and some big one= s ... >>>that's why I'm comparing with google for data storage). >>>Then we will have a search engine searching into those data ... >>> >>>Dealing about the hardware, for the moment we have only a bi-pentium X= eon >>>2.8Ghz with 4 Gb of RAM ... and we saw we had bad performance results = >>>... so >>>we are thinking about a new solution with maybe several servers (serve= r >>>design may vary from one to other) ... to get a kind of cluster to get= =20 >>>better >>>performance ... >>> >>>Am I clear ? >>> >>>Regards, >> >> >> >> >>---------------------------(end of broadcast)--------------------------= - >>TIP 2: you can get off all lists at once with the unregister command >> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org= ) >> >=20