Re: [PERFORM] Benchmark: Dell/Perc 6, 8 disk RAID 10
On Thu, Mar 13, 2008 at 4:53 PM, justin [EMAIL PROTECTED] wrote: I'm ran pgbench from my laptop to the new server My laptop is dual core with 2 gigs of ram and 1 gig enthernet connection to server. so i don't think the network is going to be a problem in the test. When i look at the server memory its only consuming 463 megs. I have the effective cache set at 12 gigs and sharebuffer at 100megs and work mem set to 50megs You do know that effective_cache_size is the size of the OS level cache. i.e. it won't show up in postgresql's memory usage. On a machine with (I assume) 12 or more gigs or memory, you should have your shared_buffers set to a much higher number than 100Meg. (unless you're still running 7.4 but that's another story.) pgbench will never use 50 megs of work_mem, as it's transactional and hitting single rows at a time, not sorting huge lists of rows. Having PostgreSQL use up all the memory is NOT necessarily your best bet. Letting the OS cache your data is quite likely a good choice here, so I'd keep your shared_buffers in the 500M to 2G range. transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 1 number of transactions per client: 10 number of transactions actually processed: 10/10 tps = 20.618557 (including connections establishing) tps = 20.618557 (excluding connections establishing) transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 10 number of transactions per client: 10 number of transactions actually processed: 100/100 tps = 18.231541 (including connections establishing) tps = 18.231541 (excluding connections establishing) transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 10 number of transactions per client: 100 number of transactions actually processed: 1000/1000 tps = 19.116073 (including connections establishing) tps = 19.116073 (excluding connections establishing) transaction type: TPC-B (sort of) scaling factor: 100 number of clients: 40 number of transactions per client: 1000 number of transactions actually processed: 4/4 tps = 20.368217 (including connections establishing) tps = 20.368217 (excluding connections establishing) Those numbers are abysmal. I had a P-III-750 5 years ago that ran well into the hundreds on a large scaling factor (1000 or so) pgbench db with 100 or more concurrent connections all the way down to 10 threads. I.e. it never dropped below 200 or so during the testing. this was with a Perc3 series LSI controller with LSI firmware and the megaraid 2.0.x driver, which I believe is the basis for the current LSI drivers today. A few points. 10 or 100 total transactions is far too few transactions to really get a good number. 1000 is about the minimum to run to get a good average, and running 1 or so is about the minimum I shoot for. So your later tests are likely to be less noisy. They're all way too slow for a modern server, and point ot non-optimal hardware. An untuned pgsql database should be able to get to or over 100 tps. I had a sparc-20 that could do 80 or so. Do you know if you're I/O bound or CPU bound? -- 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] Benchmark: Dell/Perc 6, 8 disk RAID 10
On Thu, Mar 13, 2008 at 3:09 PM, justin [EMAIL PROTECTED] wrote: I chose to use ext3 on these partition You should really consider another file system. ext3 has two flaws that mean I can't really use it properly. A 2TB file system size limit (at least on the servers I've tested) and it locks the whole file system while deleting large files, which can take several seconds and stop ANYTHING from happening during that time. This means that dropping or truncating large tables in the middle of the day could halt your database for seconds at a time. This one misfeature means that ext2/3 are unsuitable for running under a database. -- 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] Benchmark: Dell/Perc 6, 8 disk RAID 10
On Fri, Mar 14, 2008 at 12:17 AM, Jesper Krogh [EMAIL PROTECTED] wrote: Scott Marlowe wrote: On Thu, Mar 13, 2008 at 3:09 PM, justin [EMAIL PROTECTED] wrote: I chose to use ext3 on these partition You should really consider another file system. ext3 has two flaws that mean I can't really use it properly. A 2TB file system size limit (at least on the servers I've tested) and it locks the whole file system while deleting large files, which can take several seconds and stop ANYTHING from happening during that time. This means that dropping or truncating large tables in the middle of the day could halt your database for seconds at a time. This one misfeature means that ext2/3 are unsuitable for running under a database. I cannot acknowledge or deny the last one, but the first one is not true. I have several volumes in the 4TB+ range on ext3 performing nicely. I can test the large file stuff, but how large? .. several GB is not a problem here. Is this on a 64 bit or 32 bit machine? We had the problem with a 32 bit linux box (not sure what flavor) just a few months ago. I would not create a filesystem on a partition of 2+TB -- 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] Benchmark: Dell/Perc 6, 8 disk RAID 10
On Fri, Mar 14, 2008 at 12:19 AM, Scott Marlowe [EMAIL PROTECTED] wrote: On Fri, Mar 14, 2008 at 12:17 AM, Jesper Krogh [EMAIL PROTECTED] wrote: Scott Marlowe wrote: On Thu, Mar 13, 2008 at 3:09 PM, justin [EMAIL PROTECTED] wrote: I chose to use ext3 on these partition You should really consider another file system. ext3 has two flaws that mean I can't really use it properly. A 2TB file system size limit (at least on the servers I've tested) and it locks the whole file system while deleting large files, which can take several seconds and stop ANYTHING from happening during that time. This means that dropping or truncating large tables in the middle of the day could halt your database for seconds at a time. This one misfeature means that ext2/3 are unsuitable for running under a database. I cannot acknowledge or deny the last one, but the first one is not true. I have several volumes in the 4TB+ range on ext3 performing nicely. I can test the large file stuff, but how large? .. several GB is not a problem here. Is this on a 64 bit or 32 bit machine? We had the problem with a 32 bit linux box (not sure what flavor) just a few months ago. I would not create a filesystem on a partition of 2+TB OK, according to this it's 16TiB: http://en.wikipedia.org/wiki/Ext2 so I'm not sure what problem we were having. It was a friend setting up the RAID and I'd already told him to use xfs but he really wanted to use ext3 because he was more familiar with it. -- 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] Benchmark: Dell/Perc 6, 8 disk RAID 10
Scott Marlowe wrote: On Fri, Mar 14, 2008 at 12:17 AM, Jesper Krogh [EMAIL PROTECTED] wrote: Scott Marlowe wrote: On Thu, Mar 13, 2008 at 3:09 PM, justin [EMAIL PROTECTED] wrote: I chose to use ext3 on these partition You should really consider another file system. ext3 has two flaws that mean I can't really use it properly. A 2TB file system size limit (at least on the servers I've tested) and it locks the whole file system while deleting large files, which can take several seconds and stop ANYTHING from happening during that time. This means that dropping or truncating large tables in the middle of the day could halt your database for seconds at a time. This one misfeature means that ext2/3 are unsuitable for running under a database. I cannot acknowledge or deny the last one, but the first one is not true. I have several volumes in the 4TB+ range on ext3 performing nicely. I can test the large file stuff, but how large? .. several GB is not a problem here. Is this on a 64 bit or 32 bit machine? We had the problem with a 32 bit linux box (not sure what flavor) just a few months ago. I would not create a filesystem on a partition of 2+TB It is on a 64 bit machine.. but ext3 doesnt have anything specifik in it as far as I know.. I have mountet filesystems created on 32 bit on 64 bit and the other way around. The filesystems are around years old. http://en.wikipedia.org/wiki/Ext3 = Limit seems to be 16TB currently (It might get down to something lower if you choose a small blocksize). -- Jesper -- 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] Benchmark: Dell/Perc 6, 8 disk RAID 10
Scott Marlowe wrote: On Thu, Mar 13, 2008 at 3:09 PM, justin [EMAIL PROTECTED] wrote: I chose to use ext3 on these partition You should really consider another file system. ext3 has two flaws that mean I can't really use it properly. A 2TB file system size limit (at least on the servers I've tested) and it locks the whole file system while deleting large files, which can take several seconds and stop ANYTHING from happening during that time. This means that dropping or truncating large tables in the middle of the day could halt your database for seconds at a time. This one misfeature means that ext2/3 are unsuitable for running under a database. I cannot acknowledge or deny the last one, but the first one is not true. I have several volumes in the 4TB+ range on ext3 performing nicely. I can test the large file stuff, but how large? .. several GB is not a problem here. Jesper -- Jesper -- 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] Benchmark: Dell/Perc 6, 8 disk RAID 10
On Fri, 14 Mar 2008, Justin wrote: I played with shared_buffer and never saw much of an improvement from 100 all the way up to 800 megs moved the checkpoints from 3 to 30 and still never saw no movement in the numbers. Increasing shared_buffers normally improves performance as the size of the database goes up, but since the pgbench workload is so simple the operating system will cache it pretty well even if you don't give the memory directly to PostgreSQL. Also, on Windows large settings for shared_buffers don't work very well, you might as well keep it in the 100MB range. wal_sync_method=fsync You might get a decent boost in resuls that write data (not the SELECT ones) by changing wal_sync_method = open_datasync which is the default on Windows. The way you've got your RAID controller setup, this is no more or less safe than using fsync. i agree with you, those numbers are terrible i realized after posting i had the option -C turned on if i read the option -C correctly it is disconnecting and reconnecting between transactions. The way read -C option creates the worst case. In addition to being an odd testing mode, there's an outstanding bug in how -C results are computed that someone submitted a fix for, but it hasn't been applied yet. I would suggest forgetting you ever ran that test. number of clients: 10 number of transactions per client: 1 number of transactions actually processed: 10/10 tps = 1768.940935 (including connections establishing) number of clients: 40 number of transactions per client: 1 number of transactions actually processed: 40/40 tps = 567.149831 (including connections establishing) tps = 568.648692 (excluding connections establishing) Note how the total number of transactions goes up here, because it's actually doing clients x requested transcations in total. The 40 client case is actually doing 4X as many total operations. That also means you can expect 4X as many checkpoints during that run. It's a longer run like this second one that you might see some impact by increasing checkpoint_segments. To keep comparisons like this more fair, I like to keep the total transactions constant and just divide that number by the number of clients to figure out what to set the -t parameter to. 40 is a good medium length test, so for that case you'd get -c 10 -t 4 -c 40 -t 1 as the two to compare. -- * Greg Smith [EMAIL PROTECTED] 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] ER diagram tool
14:31 rtfm_please For information about erd 14:31 rtfm_please see http://druid.sf.net/ 14:31 rtfm_please or http://schemaspy.sourceforge.net/ A very great Thanks. SchemaSpy drawn ER diagram by referring my database... it done a very good job Thanks a lot GUY... http://schemaspy.sourceforge.net/ 14:31 rtfm_please or http://uml.sourceforge.net/index.php
[PERFORM] Lots of semop calls under load
On a database (PostgreSQL 8.2.4 on 64-bit Linux 2.6.18 on 8 AMD Opterons)
that is under high load, I observe the following:
- About 200 database sessions concurrently issue queries, most of them small,
but I have found one that touches 38000 table and index blocks.
- vmstat shows that CPU time is divided between idle and iowait,
with user and sys time practically zero.
- the run queue is short, but the blocked queue (uninterruptible sleep) is
around 10.
- Many context switches are reported (over hundred per second).
- sar says that the disk with the database is on 100% of its capacity.
Storage is on a SAN box.
Queries that normally take seconds at most require up to an hour to finish.
I ran lsof -p on a backend running the big query mentioned above, and
it does not use any temp files (work_mem = 20MB).
The query accesses only one table and its index.
What puzzles me is the strace -tt output from that backend:
13:44:58.263598 semop(393227, 0x7fff482f6050, 1) = 0
13:44:58.313448 semop(229382, 0x7fff482f6070, 1) = 0
13:44:58.313567 semop(393227, 0x7fff482f6050, 1) = 0
13:44:58.442917 semop(229382, 0x7fff482f6070, 1) = 0
13:44:58.443074 semop(393227, 0x7fff482f6050, 1) = 0
13:44:58.565313 semop(393227, 0x7fff482f6050, 1) = 0
13:44:58.682178 semop(229382, 0x7fff482f6070, 1) = 0
13:44:58.682333 semop(393227, 0x7fff482f6480, 1) = 0
13:44:58.807452 semop(393227, 0x7fff482f6050, 1) = 0
13:44:58.924425 semop(393227, 0x7fff482f6480, 1) = 0
13:44:58.924727 semop(393227, 0x7fff482f6050, 1) = 0
13:44:59.045456 semop(393227, 0x7fff482f6050, 1) = 0
13:44:59.169011 semop(393227, 0x7fff482f6480, 1) = 0
13:44:59.169226 semop(327689, 0x7fff482f64a0, 1) = 0
[many more semops]
13:44:59.602532 semop(327689, 0x7fff482f6070, 1) = 0
13:44:59.602648 lseek(32, 120176640, SEEK_SET) = 120176640
13:44:59.602742 read(32, {\0\0\0xwv\227\1\0\0\0\320\0\350\0\0 \3 [EMAIL
PROTECTED]..., 8192) = 8192
13:44:59.602825 semop(327689, 0x7fff482f64d0, 1) = 0
13:44:59.602872 semop(393227, 0x7fff482f6080, 1) = 0
13:44:59.602929 semop(393227, 0x7fff482f6050, 1) = 0
13:44:59.614559 semop(360458, 0x7fff482f6070, 1) = 0
[many more semops]
13:44:59.742103 semop(229382, 0x7fff482f64a0, 1) = 0
13:44:59.742172 semop(393227, 0x7fff482f6050, 1) = 0
13:44:59.756526 select(0, NULL, NULL, NULL, {0, 1000}) = 0 (Timeout)
13:44:59.758096 semop(393227, 0x7fff482f6480, 1) = 0
13:44:59.771655 semop(393227, 0x7fff482f6050, 1) = 0
[hundreds of semops]
13:45:14.339905 semop(393227, 0x7fff482f6050, 1) = 0
13:45:14.466992 semop(360458, 0x7fff482f6070, 1) = 0
13:45:14.467102 lseek(33, 332693504, SEEK_SET) = 332693504
13:45:14.467138 read(33, {\0\0\0\210\235\351\331\1\0\0\0\204\0010\32\360\37\3
\340\237 \0\320\237 \0\300\237 \0..., 8192) = 8192
13:45:14.599815 semop(163844, 0x7fff482f60a0, 1) = 0
13:45:14.66 lseek(32, 125034496, SEEK_SET) = 125034496
13:45:14.600305 read(32, {\0\0\0\230\257\270\227\1\0\0\0\330\0\340\0\0 \3
[EMAIL PROTECTED]..., 8192) = 8192
13:45:14.600391 semop(163844, 0x7fff482f64d0, 1) = 0
13:45:14.600519 semop(393227, 0x7fff482f6480, 1) = 0
and so on. File 32 is the table, file 33 is the index.
Many of the table and index blocks are probably already in shared memory
(shared_buffers = 6GB) and don't have to be read from disk.
My questions:
Is the long duration of the query caused by something else than I/O overload?
What are the semops? Lightweight locks waiting for shared buffer?
Are the lseek and read operations really that fast although the disk is on 100%?
Is this normal behavior under overload or is something ill tuned?
Yours,
Laurenz Albe
--
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] Lots of semop calls under load
Albe Laurenz [EMAIL PROTECTED] writes: On a database (PostgreSQL 8.2.4 on 64-bit Linux 2.6.18 on 8 AMD Opterons) that is under high load, I observe the following: ... - vmstat shows that CPU time is divided between idle and iowait, with user and sys time practically zero. - sar says that the disk with the database is on 100% of its capacity. It sounds like you've simply saturated the disk's I/O bandwidth. (I've noticed that Linux isn't all that good about distinguishing idle from iowait --- more than likely you're really looking at 100% iowait.) Storage is on a SAN box. What kind of SAN box? You're going to need something pretty beefy to keep all those CPUs busy. What puzzles me is the strace -tt output from that backend: Some low level of contention and consequent semops/context switches is to be expected. I don't think you need to worry if it's only 100/sec. The sort of context swap storm behavior we've seen in the past is in the tens of thousands of swaps/sec on hardware much weaker than what you have here --- if you were seeing one of those I bet you'd be well above 10 swaps/sec. Are the lseek and read operations really that fast although the disk is on 100%? lseek is (should be) cheap ... it doesn't do any actual I/O. The read()s you're showing here were probably satisfied from kernel disk cache. If you look at a larger sample you'll find slower ones, I think. Another thing to look for is slow writes. 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] Adaptec 5805 SAS Raid
Any of you chaps used this controller? ___ Rise to the challenge for Sport Relief with Yahoo! For Good http://uk.promotions.yahoo.com/forgood/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] The many nulls problem
It often happens that a particular pieces of information is non-null for a small minority of cases. A superficially different manifestation of this is when two pieces of information are identical in all but a small minority of cases. This can be easily mapped to the previous description by defining a null in one column to mean that its contents should be obtained from those of another column. A further variant of this is when one piece of information is a simple function of another one in all but a small minority of cases. (BTW, I vaguely recall that RDb theorists have a technical term for this particular design issue, but I don't remember it.) In all these cases, the design choice, at least according to RDb's 101, is between including a column in the table that will be NULL most of the time, or defining a second auxiliary column that references the first one and holds the non-redundant information for the minority of cases for which this is necessary (and maybe define a VIEW that includes all the columns). But for me it is a frequent occurrence that my quaint and simple RDb's 101 reasoning doesn't really apply for PostgreSQL. Basically, Pg is too smart for it! For example, does a large proportion of NULLs really imply a lot of wasted space? Maybe this is true for fixed-length data types, but what about for type TEXT or VARCHAR? Just to be concrete, consider the case of a customers database for some home shopping website. Suppose that, as it happens, for the majority of this site's customers, the shipping and billing addresses are identical. Or consider the scenario of a company in which, for most employees, the email address can be readily computed from the first and last name using the rule First M. Last = [EMAIL PROTECTED], but the company allows some flexibility for special cases (e.g. for people like Yasuhiro Tanaka who's known to everyone by his nickname, Yaz, the email is [EMAIL PROTECTED] hardly anyone remembers or even knows his full name.) What's your schema design approach for such situations? How would you go about deciding whether the number of exceptional cases is small enough to warrant a second table? Of course, one could do a systematic profiling of various possible scenarios, but as a first approximation what's your rule-of-thumb? TIA! Kynn
Re: [PERFORM] The many nulls problem
Kynn, have you seen contrib/hstore ? You can have one table with common attributes and hide others in hstore Oleg On Fri, 14 Mar 2008, Kynn Jones wrote: It often happens that a particular pieces of information is non-null for a small minority of cases. A superficially different manifestation of this is when two pieces of information are identical in all but a small minority of cases. This can be easily mapped to the previous description by defining a null in one column to mean that its contents should be obtained from those of another column. A further variant of this is when one piece of information is a simple function of another one in all but a small minority of cases. (BTW, I vaguely recall that RDb theorists have a technical term for this particular design issue, but I don't remember it.) In all these cases, the design choice, at least according to RDb's 101, is between including a column in the table that will be NULL most of the time, or defining a second auxiliary column that references the first one and holds the non-redundant information for the minority of cases for which this is necessary (and maybe define a VIEW that includes all the columns). But for me it is a frequent occurrence that my quaint and simple RDb's 101 reasoning doesn't really apply for PostgreSQL. Basically, Pg is too smart for it! For example, does a large proportion of NULLs really imply a lot of wasted space? Maybe this is true for fixed-length data types, but what about for type TEXT or VARCHAR? Just to be concrete, consider the case of a customers database for some home shopping website. Suppose that, as it happens, for the majority of this site's customers, the shipping and billing addresses are identical. Or consider the scenario of a company in which, for most employees, the email address can be readily computed from the first and last name using the rule First M. Last = [EMAIL PROTECTED], but the company allows some flexibility for special cases (e.g. for people like Yasuhiro Tanaka who's known to everyone by his nickname, Yaz, the email is [EMAIL PROTECTED] hardly anyone remembers or even knows his full name.) What's your schema design approach for such situations? How would you go about deciding whether the number of exceptional cases is small enough to warrant a second table? Of course, one could do a systematic profiling of various possible scenarios, but as a first approximation what's your rule-of-thumb? TIA! Kynn Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- 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] Hardware question for a DB server
Greg Smith wrote: On Wed, 12 Mar 2008, Mark Lewis wrote: One question that's likely going to be important depending on your answers above is whether or not you're getting a battery-backed write cache for that ServeRAID-8K. Apparently there's a 8k-l and an regular 8-k; the l doesn't have the cache, so if this one is a regular 8-k it will have 256MB and a battery. See http://www.redbooks.ibm.com/abstracts/TIPS0054.html?Open#ServeRAID-8k It is the solution with RAM and battery. From Pascal's description of the application this system sounds like overkill whether or not there's a cache. For scaling to lots of small requests, using things like using connection pooling may end up being more important than worring about the disk system (the database isn't big enough relative to RAM for that to be too important). I agree with what you are saying. We are using Java with a pool of connections to access the DB. Today our database is really small compared to the RAM but it may evolve and even will probably grow (hope so which would be a good situation). Thanks for your advices/remarks. -- 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] Hardware question for a DB server
On Fri, Mar 14, 2008 at 1:24 PM, Pascal Cohen [EMAIL PROTECTED] wrote: I agree with what you are saying. We are using Java with a pool of connections to access the DB. Today our database is really small compared to the RAM but it may evolve and even will probably grow (hope so which would be a good situation). Keep in mind that differential cost between a mediocre and a good RAID controller is often only a few hundred dollars. If that means you can scale to 10 or 100 times as many users, it's an investment worth making up front rather than later on. -- 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] The many nulls problem
Kynn Jones wrote: In all these cases, the design choice, at least according to RDb's 101, is between including a column in the table that will be NULL most of the time, or defining a second auxiliary column that references the first one and holds the non-redundant information for the minority of cases for which this is necessary (and maybe define a VIEW that includes all the columns). But for me it is a frequent occurrence that my quaint and simple RDb's 101 reasoning doesn't really apply for PostgreSQL. Basically, Pg is too smart for it! For example, does a large proportion of NULLs really imply a lot of wasted space? It depends. If there's *any* NULLs on a row, a bitmap of the NULLs is stored in the tuple header. Without NULL bitmap, the tuple header is 23 bytes, and due to memory alignment, it's always rounded up to 24 bytes. That one padding byte is free for use as NULL bitmap, so it happens that if your table has eight columns or less, NULLs will take no space at all. If you have more columns than that, if there's *any* NULLs on a row you'll waste a whole 4 or 8 bytes (or more if you have a very wide table and go beyond the next 4/8 byte boundary), depending on whether you're on a 32-bit or 64-bit platform, regardless of how many NULLs there is. That's on 8.3. 8.2 and earlier versions are similar, but the tuple header used to be 27 bytes instead of 23, so you have either one or five free bytes, depending on architecture. In any case, that's pretty good compared to many other RDBMSs. Maybe this is true for fixed-length data types, but what about for type TEXT or VARCHAR? Datatype doesn't make any difference. Neither does fixed vs variable length. What's your schema design approach for such situations? How would you go about deciding whether the number of exceptional cases is small enough to warrant a second table? Of course, one could do a systematic profiling of various possible scenarios, but as a first approximation what's your rule-of-thumb? From performance point of view, I would go with a single table with NULL fields on PostgreSQL. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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] The many nulls problem
On Fri, Mar 14, 2008 at 3:46 PM, Heikki Linnakangas [EMAIL PROTECTED] wrote: tons of useful info snipped From performance point of view, I would go with a single table with NULL fields on PostgreSQL. Wow. I'm so glad I asked! Thank you very much! Kynn
Re: [PERFORM] The many nulls problem
On Fri, Mar 14, 2008 at 2:59 PM, Oleg Bartunov [EMAIL PROTECTED] wrote: have you seen contrib/hstore ? You can have one table with common attributes and hide others in hstore That's interesting. I'll check it out. Thanks! Kynn
Re: [PERFORM] The many nulls problem
On Fri, 14 Mar 2008, Kynn Jones wrote: On Fri, Mar 14, 2008 at 2:59 PM, Oleg Bartunov [EMAIL PROTECTED] wrote: have you seen contrib/hstore ? You can have one table with common attributes and hide others in hstore That's interesting. I'll check it out. Thanks! actually, hstore was designed specially for this kind of problems. Kynn Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- 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] Anyone using a SAN?
Hi all, I had a few meetings with SAN vendors and I thought I'd give you some follow-up on points of potential interest. - Dell/EMC The representative was like the Dell dude grown up. The sales pitch mentioned price point about twenty times (to the point where it was annoying), and the pitch ultimately boiled down to Dude, you're getting a SAN. My apologies in advance to bringing back repressed memories of the Dell dude. As far as technical stuff goes, it's about what you'd expect from a low-level SAN. The cost for a SAN was in the $2-3 per GB range if you went with the cheap option...not terrible, but not great either, especially since you'd have to buy lots of GB. Performance numbers weren't bad, but they weren't great either. - 3par The sales pitch was more focused on technical aspects and only mentioned price point twice...which is a win in my books, at least compared to Dell. Their real place to shine was in the technical aspect. Whereas Dell just wanted to sell you a storage system that you put on a network, 3par wanted to sell you a storage system specifically designed for a network, and change the very way you think about storage. They had a bunch of cool management concepts, and very advanced failover, power outage, and backup techniques and tools. Performance wasn't shabby, either, for instance a RAID 5 set could get about 90% the IOPS and transfer rate that a RAID 10 set could. How exactly this compares to DAS they didn't say. The main stumbling block with 3par is price. While they didn't give any specific numbers, best estimates put a SAN in the $5-7 per GB range. The extra features just might be worth it though. - Lefthand This is going to be an upcoming meeting, so I don't have as good of an opinion. Looking at their website, they seem more to the Dell end in terms of price and functionality. I'll keep you in touch as I have more info. They seem good for entry-level SANs, though. Luckily, almost everything here works with Linux (at least the major distros), including the management tools, in case people were worried about that. One of the key points to consider going forward is that the competition of iSCSI and Fibre Channel techs will likely bring price down in the future. While SANs are certainly more expensive than their DAS counterparts, the gap appears to be closing. However, to paraphrase a discussion between a few of my co-workers, you can buy toilet paper or kitty litter in huge quantities because you know you'll eventually use it...and it doesn't change in performance or basic functionality. Storage is just something that you don't always want to buy a lot of in one go. It will get bigger, and cheaper, and probably faster in a relatively short amount of time. The other thing is that you can't really get a small SAN. The minimum is usually in the multiple TB range (and usually 10 TB). I'd love to be able to put together a proof of concept and a test using 3par's technology and commodity 80GB slow disks, but I really can't. You're stuck with going all-in right away, and enough people have had problems being married to specific techs or vendors that it's really hard to break that uneasiness. Thanks for reading, hopefully you found it slightly informative. Peter -- 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] Adaptec 5805 SAS Raid
Glyn Astill wrote: Any of you chaps used this controller? It looks very similar to the rebadged Adaptec that Sun shipped in the X4150 I ordered a few weeks ago, though the Sun model had only 256MB of cache RAM. I was wary of going Adaptec after my experiences with the PERC/3i, which couldn't even seem to manage a single disk's worth of read performance from a RAID-1 array, but I was pleasantly surprised by this card. I'm only running a RAID-1 array on it, with 2 146GB 10krpm SAS drives, but I was impressed with the read performance -- it seems quite happy to split sequential reads across the two disks. Here are the bonnie++ numbers I took during my run-in testing: Version 1.03 --Sequential Output-- --Sequential Input- --Random- -Per Chr- --Block-- -Rewrite- -Per Chr- --Block-- --Seeks-- MachineSize K/sec %CP K/sec %CP K/sec %CP K/sec %CP K/sec %CP /sec %CP membrane12G 54417 89 86808 15 41489 6 59517 96 125266 10 629.6 0 --Sequential Create-- Random Create -Create-- --Read--- -Delete-- -Create-- --Read--- -Delete-- files:max:min/sec %CP /sec %CP /sec %CP /sec %CP /sec %CP /sec %CP membrane 16 19496 97 + +++ 14220 68 7673 40 + +++ 5246 26 I'm not sure if I'd yet be comfortable running a larger array for a database on an Adaptec card, but it's definitely a great improvement on the earlier Adaptec hardware I've used. Thanks Leigh ___ Rise to the challenge for Sport Relief with Yahoo! For Good http://uk.promotions.yahoo.com/forgood/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] best way to run maintenance script
Hi all, I have been searching for the best way to run maintenance scripts which does a vacuum, analyze and deletes some old data. Whenever the maintenance script runs - mainly the pg_maintenance --analyze script - it slows down postgresql inserts and I want to avoid that. The system is under constant load and I am not interested in the time taken to vacuum. Is there a utility or mechanism in postgresql which helps in reducing priority of maintenance queries? Is writing a postgresql C function and setting the priority of process the only way to change the priority of the maintenance script or is there a better way. http://weblog.bignerdranch.com/?p=11 I tried using the nice command (Linux system) on the maintenance script - it did not have any effect - guess it does not change the niceness of the postgresql vacuum process. (I am running Postgresql 8.0 on a Linux) -- Vinu -- 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 way to run maintenance script
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Fri, 14 Mar 2008 17:00:21 -0800 Vinubalaji Gopal [EMAIL PROTECTED] wrote: Hi all, I have been searching for the best way to run maintenance scripts which does a vacuum, analyze and deletes some old data. Whenever the maintenance script runs - mainly the pg_maintenance --analyze script - it slows down postgresql inserts and I want to avoid that. The system is under constant load and I am not interested in the time taken to vacuum. Is there a utility or mechanism in postgresql which helps in reducing priority of maintenance queries? You can use parameters such as vacuum_cost_delay to help this... see the docs: http://www.postgresql.org/docs/8.3/static/runtime-config-autovacuum.html Is writing a postgresql C function and setting the priority of process the only way to change the priority of the maintenance script or is there a better way. http://weblog.bignerdranch.com/?p=11 I tried using the nice command (Linux system) on the maintenance script - it did not have any effect - guess it does not change the niceness of the postgresql vacuum process. (I am running Postgresql 8.0 on a Linux) If you are truly running 8.0 and not something like 8.0.15 vacuum is the least of your worries. Sincerely, Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL political pundit | Mocker of Dolphins -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFH2xkkATb/zqfZUUQRAsFxAJ422xFUGNwJZZVS47SwM9HJEYrb/gCePESL YZFM27b93ylhy5TuE2MCcww= =2Zpp -END PGP SIGNATURE- -- 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 way to run maintenance script
Hi Joshua, You can use parameters such as vacuum_cost_delay to help this... see the docs: http://www.postgresql.org/docs/8.3/static/runtime-config-autovacuum.html I am checking it out. Seems to be a nice option for vacuum - but wish there was a way to change the delete priority or I will try to use the C based priority hack. If you are truly running 8.0 and not something like 8.0.15 vacuum is the least of your worries. Its 8.0.4. Thanks. -- Vinu -- 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 way to run maintenance script
Vinubalaji Gopal [EMAIL PROTECTED] writes: If you are truly running 8.0 and not something like 8.0.15 vacuum is the least of your worries. Its 8.0.4. That's only a little bit better. Read about all the bug fixes you're missing at http://www.postgresql.org/docs/8.0/static/release.html and then consider updating ... 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 way to run maintenance script
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Fri, 14 Mar 2008 17:51:52 -0800 Vinubalaji Gopal [EMAIL PROTECTED] wrote: Hi Joshua, You can use parameters such as vacuum_cost_delay to help this... see the docs: http://www.postgresql.org/docs/8.3/static/runtime-config-autovacuum.html I am checking it out. Seems to be a nice option for vacuum - but wish there was a way to change the delete priority or I will try to use the C based priority hack. I think you will find if you do it the right way, which is to say the way that it is meant to be done with the configurable options, your life will be a great deal more pleasant than some one off hack. Sincerely, Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL political pundit | Mocker of Dolphins -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFH2zG0ATb/zqfZUUQRAtmeAKCpKUbZP63qmiAPI6x4i9sLaf3LfwCfTPwb mdS3L7JzlwarEjuu3WGFdaE= =V7wn -END PGP SIGNATURE- -- 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 way to run maintenance script
On Fri, 2008-03-14 at 18:37 -0700, Tom Lane wrote: That's only a little bit better. Read about all the bug fixes you're Sure - will eventually upgrade it sometime - but it has to wait for now :( -- Vinu -- 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 way to run maintenance script
I think you will find if you do it the right way, which is to say the way that it is meant to be done with the configurable options, your life will be a great deal more pleasant than some one off hack. yeah I agree. The pg_maintanence script which calls vacuum and analyze is the one of the thing that is causing more problems. I am trying out various vacuum options (vacuum_cost_limit, vacuum_cost_delay) and finding it hard to understand the implications of the variables. What are the optimal values for the vacuum_* parameters - for a really active database (writes at the rate of ~ 50 rows/seconds). I started with vacuum_cost_delay = 200 vacuum_cost_limit = 400 and that did not help much. -- Vinu -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
