Re: [PERFORM] long transaction
long running transactions can be evil. is there a reason why this has to run in a single transaction? This single transaction is used to import new information in a database. I need it because the database cannot be disconected from the users, and the whole new data has to be consistently. There are different constraints that are checked during the import. -- 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] Using PK value as a String
Valentin Bogdanov schrieb: --- On Mon, 11/8/08, Gregory Stark [EMAIL PROTECTED] wrote: From: Gregory Stark [EMAIL PROTECTED] Subject: Re: [PERFORM] Using PK value as a String To: Jay [EMAIL PROTECTED] Cc: pgsql-performance@postgresql.org Date: Monday, 11 August, 2008, 10:30 AM Jay [EMAIL PROTECTED] writes: I have a table named table_Users: CREATE TABLE table_Users ( UserID character(40) NOT NULL default '', Username varchar(256) NOT NULL default '', Email varchar(256) NOT NULL default '' etc... ); The UserID is a character(40) and is generated using UUID function. We started making making other tables and ended up not really using UserID, but instead using Username as the unique identifier for the other tables. Now, we pass and insert the Username to for discussions, wikis, etc, for all the modules we have developed. I was wondering if it would be a performance improvement to use the 40 Character UserID instead of Username when querying the other tables, or if we should change the UserID to a serial value and use that to query the other tables. Or just keep the way things are because it doesn't really make much a difference. Username would not be any slower than UserID unless you have a lot of usernames longer than 40 characters. However making UserID an integer would be quite a bit more efficient. It would take 4 bytes instead of as the length of the Username which adds up when it's in all your other tables... Also internationalized text collations are quite a bit more expensive than a simple integer comparison. But the real question here is what's the better design. If you use Username you'll be cursing if you ever want to provide a facility to allow people to change their usernames. You may not want such a facility now but one day... I don't understand Gregory's suggestion about the design. I thought using natural primary keys as opposed to surrogate ones is a better design strategy, even when it comes to performance considerations and even more so if there are complex relationships within the database. Regards, Valentin UUID is already a surrogate key not a natural key, in no aspect better than a numeric key, just taking a lot more space. So why not use int4/int8? -- 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] Using PK value as a String
You guys totally rock! I guess, bottom line, we should take that extra day to convert our PK and FK to a numerical value, using BIG INT to be on the save side. (Even though Wikipedia's UserID uses just an integer as data type) To Gregory: Thank you for you valuable statement. But the real question here is what's the better design. If you use Username you'll be cursing if you ever want to provide a facility to allow people to change their usernames. You may not want such a facility now but one day I think you hit the nail on the head with this comment. If a member really wants to change their username, IE: Choose to go with IloveUSara, only to be dumped on the alter, who am I to say no. To Valentin: I wish someone would prove us both wrong or right. I still thought it wasn't a bad idea to use username a varchar(256) to interact with all the modules... Well thats what I thought when I first started writing the tables... To Jay: Thanks for keeping it short and simple. I'd like sequence, which is unique and just use 8 bytes(bigint) When it querying other tables, it will faster , and disk space smaller than UUID(40 bytes). I'm taking your advice on this^^ Although wikipedia's postgresql database schema still stands. To Craig: Yes, I agree. Please see my comment on IloveUSara. To Mario: Let's go! I'm Mario... Sorry, I love Mario Kart. Especially on the old super famacon. Going with int8, thank you for the advice. On Tue, Aug 12, 2008 at 6:58 PM, Mario Weilguni [EMAIL PROTECTED] wrote: Valentin Bogdanov schrieb: --- On Mon, 11/8/08, Gregory Stark [EMAIL PROTECTED] wrote: From: Gregory Stark [EMAIL PROTECTED] Subject: Re: [PERFORM] Using PK value as a String To: Jay [EMAIL PROTECTED] Cc: pgsql-performance@postgresql.org Date: Monday, 11 August, 2008, 10:30 AM Jay [EMAIL PROTECTED] writes: I have a table named table_Users: CREATE TABLE table_Users ( UserID character(40) NOT NULL default '', Username varchar(256) NOT NULL default '', Email varchar(256) NOT NULL default '' etc... ); The UserID is a character(40) and is generated using UUID function. We started making making other tables and ended up not really using UserID, but instead using Username as the unique identifier for the other tables. Now, we pass and insert the Username to for discussions, wikis, etc, for all the modules we have developed. I was wondering if it would be a performance improvement to use the 40 Character UserID instead of Username when querying the other tables, or if we should change the UserID to a serial value and use that to query the other tables. Or just keep the way things are because it doesn't really make much a difference. Username would not be any slower than UserID unless you have a lot of usernames longer than 40 characters. However making UserID an integer would be quite a bit more efficient. It would take 4 bytes instead of as the length of the Username which adds up when it's in all your other tables... Also internationalized text collations are quite a bit more expensive than a simple integer comparison. But the real question here is what's the better design. If you use Username you'll be cursing if you ever want to provide a facility to allow people to change their usernames. You may not want such a facility now but one day... I don't understand Gregory's suggestion about the design. I thought using natural primary keys as opposed to surrogate ones is a better design strategy, even when it comes to performance considerations and even more so if there are complex relationships within the database. Regards, Valentin UUID is already a surrogate key not a natural key, in no aspect better than a numeric key, just taking a lot more space. So why not use int4/int8? -- Regards, Jay Kang This e-mail is intended only for the proper person to whom it is addressed and may contain legally privileged and/or confidential information. If you received this communication erroneously, please notify me by reply e-mail, delete this e-mail and all your copies of this e-mail and do not review, disseminate, redistribute, make other use of, rely upon, or copy this communication. Thank you.
Re: [PERFORM] Using PK value as a String
Mario Weilguni [EMAIL PROTECTED] writes: UUID is already a surrogate key not a natural key, in no aspect better than a numeric key, just taking a lot more space. So why not use int4/int8? The main reason to use UUID instead of sequences is if you want to be able to generate unique values across multiple systems. So, for example, if you want to be able to send these userids to another system which is taking registrations from lots of places. Of course that only works if that other system is already using UUIDs and you're all using good generators. You only need int8 if you might someday have more than 2 *billion* users... Probably not an urgent issue. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! -- 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] Using PK value as a String
In response to Gregory Stark [EMAIL PROTECTED]: Mario Weilguni [EMAIL PROTECTED] writes: UUID is already a surrogate key not a natural key, in no aspect better than a numeric key, just taking a lot more space. So why not use int4/int8? The main reason to use UUID instead of sequences is if you want to be able to generate unique values across multiple systems. So, for example, if you want to be able to send these userids to another system which is taking registrations from lots of places. Of course that only works if that other system is already using UUIDs and you're all using good generators. Note that in many circumstances, there are other options than UUIDs. If you have control over all the systems generating values, you can prefix each generated value with a system ID (i.e. make the high 8 bits the system ID and the remaining bits come from a sequence) This allows you to still use int4 or int8. UUID is designed to be a universal solution. But universal solutions are frequently less efficient than custom-tailored solutions. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 -- 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] Using PK value as a String
Bill Moran wrote: The main reason to use UUID instead of sequences is if you want to be able to generate unique values across multiple systems. So, for example, if you want to be able to send these userids to another system which is taking registrations from lots of places. Of course that only works if that other system is already using UUIDs and you're all using good generators. Note that in many circumstances, there are other options than UUIDs. If you have control over all the systems generating values, you can prefix each generated value with a system ID (i.e. make the high 8 bits the system ID and the remaining bits come from a sequence) This allows you to still use int4 or int8. UUID is designed to be a universal solution. But universal solutions are frequently less efficient than custom-tailored solutions. Other benefits include: - Reduced management cost. As described above, one would have to allocate keyspace in each system. By using a UUID, one can skip this step. - Increased keyspace. Even if keyspace allocation is performed, an int4 only has 32-bit of keyspace to allocate. The IPv4 address space is already over 85% allocated as an example of how this can happen. 128-bits has a LOT more keyspace than 32-bits or 64-bits. - Reduced sequence predictability. Certain forms of exploits when the surrogate key is exposed to the public, are rendered ineffective as guessing the next or previous generated key is far more difficult. - Used as the key into a cache or other lookup table. Multiple types of records can be cached to the same storage as the sequence is intended to be universally unique. - Flexibility to merge systems later, even if unplanned. For example, System A and System B are run independently for some time. Then, it is determined that they should be merged. If unique keys are specific to the system, this becomes far more difficult to implement than if the unique keys are universal. That said, most uses of UUID do not require any of the above. It's a just in case measure, that suffers the performance cost, just in case. Cheers, mark -- Mark Mielke [EMAIL PROTECTED]
Re: [PERFORM] Using PK value as a String
Mark Mielke [EMAIL PROTECTED] writes: - Increased keyspace. Even if keyspace allocation is performed, an int4 only has 32-bit of keyspace to allocate. The IPv4 address space is already over 85% allocated as an example of how this can happen. 128-bits has a LOT more keyspace than 32-bits or 64-bits. The rest of your points are valid (though not particularly convincing to me for most applications) but this example is bogus. The IPv4 address space is congested because of the hierarchic nature of allocations. Not because there is an actual shortage of IPv4 addresses themselves. There would be enough IPv4 for every ethernet device on the planet for decades to come if we could allocate them individually -- but we can't. That is, when allocating an organization 100 addresses if they want to be able to treat them as a contiguous network they must be allocated 128 addresses. And if they might ever grow to 129 they're better off just justifying 256 addresses today. That's not an issue for a sequence generated primary key. Arguably it *is* a problem for UUID which partitions up that 128-bits much the way the original pre-CIDR IPv4 addressing scheme partitioned up the address. But 128-bits is so much bigger it avoids running into the issue. The flip side is that sequence generated keys have to deal with gaps if record is deleted later. So the relevant question is not whether you plan to have 2 billion users at any single point in the future but rather whether you plan to ever have had 2 billion users total over your history. I suspect large networks like Yahoo or Google might be nearing or past that point now even though they probably only have a few hundred million current users. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! -- 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] Using PK value as a String
Hi! We use normal sequences to generate id's across multiple nodes. We use the increment parameter for the sequence and we specify each node to increment its sequence with for example 10 and the the first node to start the sequence at 1 and the second at 2 and so on. In that way you get an unique ID across each nodes thats an INT. Not in chronological order but it's unique ;) The only issue with this is that the value you chose for increment value is your node limit. Cheers! Mathias On 12 aug 2008, at 14.51, Gregory Stark wrote: Mario Weilguni [EMAIL PROTECTED] writes: UUID is already a surrogate key not a natural key, in no aspect better than a numeric key, just taking a lot more space. So why not use int4/int8? The main reason to use UUID instead of sequences is if you want to be able to generate unique values across multiple systems. So, for example, if you want to be able to send these userids to another system which is taking registrations from lots of places. Of course that only works if that other system is already using UUIDs and you're all using good generators. You only need int8 if you might someday have more than 2 *billion* users... Probably not an urgent issue. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support! -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org ) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance PGP.sig Description: This is a digitally signed message part
Re: [PERFORM] Using PK value as a String
Gregory Stark wrote: Mark Mielke [EMAIL PROTECTED] writes: - Increased keyspace. Even if keyspace allocation is performed, an int4 only has 32-bit of keyspace to allocate. The IPv4 address space is already over 85% allocated as an example of how this can happen. 128-bits has a LOT more keyspace than 32-bits or 64-bits. The rest of your points are valid (though not particularly convincing to me for most applications) but this example is bogus. The IPv4 address space is congested because of the hierarchic nature of allocations. Not because there is an actual shortage of IPv4 addresses themselves. There would be enough IPv4 for every ethernet device on the planet for decades to come if we could allocate them individually -- but we can't. I don't disagree. Obviously, most systems people work with do not require 2**32 records. You trimmed my bottom statement where most systems don't require any of these benefits - it's only a just in case. :-) The point is valid - 128-bits has more keyspace than 32-bits or 64-bits. The relevance of this point to a particular application other than Facebook, Google, or Yahoo, is probably low or non-existent. Cheers, mark That is, when allocating an organization 100 addresses if they want to be able to treat them as a contiguous network they must be allocated 128 addresses. And if they might ever grow to 129 they're better off just justifying 256 addresses today. That's not an issue for a sequence generated primary key. Arguably it *is* a problem for UUID which partitions up that 128-bits much the way the original pre-CIDR IPv4 addressing scheme partitioned up the address. But 128-bits is so much bigger it avoids running into the issue. The flip side is that sequence generated keys have to deal with gaps if record is deleted later. So the relevant question is not whether you plan to have 2 billion users at any single point in the future but rather whether you plan to ever have had 2 billion users total over your history. I suspect large networks like Yahoo or Google might be nearing or past that point now even though they probably only have a few hundred million current users. -- Mark Mielke [EMAIL PROTECTED]
Re: [PERFORM] Using PK value as a String
On Tue, Aug 12, 2008 at 9:46 AM, Gregory Stark [EMAIL PROTECTED] wrote: Mark Mielke [EMAIL PROTECTED] writes: - Increased keyspace. Even if keyspace allocation is performed, an int4 only has 32-bit of keyspace to allocate. The IPv4 address space is already over 85% allocated as an example of how this can happen. 128-bits has a LOT more keyspace than 32-bits or 64-bits. The rest of your points are valid (though not particularly convincing to me for most applications) but this example is bogus. The IPv4 address space is congested because of the hierarchic nature of allocations. Not because there is an actual shortage of IPv4 addresses themselves. There would be enough IPv4 for every ethernet device on the planet for decades to come if we could allocate them individually -- but we can't. Only because of NAT. There are a _lot_ of IP devices out there maybe not billions, but maybe so, and 'enough for decades' is quite a stretch. 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] Using PK value as a String
We chose UUID as PK because there is still some information in an integer key. You can see if a user has registered before someone else (user1.id user2.id) or you can see how many new users registered in a specific period of time (compare the id of the newest user to the id a week ago). This is information which is in some cases critical. moritz -- 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] Using PK value as a String
In response to Moritz Onken [EMAIL PROTECTED]: We chose UUID as PK because there is still some information in an integer key. You can see if a user has registered before someone else (user1.id user2.id) or you can see how many new users registered in a specific period of time (compare the id of the newest user to the id a week ago). This is information which is in some cases critical. So you're accidentally storing critical information in magic values instead of storing it explicitly? Good luck with that. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 -- 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] Using PK value as a String
Am 12.08.2008 um 17:04 schrieb Bill Moran: In response to Moritz Onken [EMAIL PROTECTED]: We chose UUID as PK because there is still some information in an integer key. You can see if a user has registered before someone else (user1.id user2.id) or you can see how many new users registered in a specific period of time (compare the id of the newest user to the id a week ago). This is information which is in some cases critical. So you're accidentally storing critical information in magic values instead of storing it explicitly? Good luck with that. How do I store critical information? I was just saying that it easy to get some information out of a primary key which is an incrementing integer. And it makes sense, in some rare cases, to have a PK which is some kind of random like UUIDs where you cannot guess the next value. moritz -- 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] Distant mirroring
Tx to all. I reach the same reflection on partitionning the data to those tables. And postgresql is giving very good tools for that with the rules features. I got the SAS server for immediate fix. But I'm looking for buying a machine that will handle my needs for more long time. Regarding partitionning it seems that I could just use a daily tables for daily treatment and keeping a another one for mass reading. I even things to partition per years or half years. One question is on table that have FK constraint, I don't know how to maintain it ? Could I use rules for it too ? Tx for helps Regards David Scott Marlowe a écrit : On Mon, Aug 11, 2008 at 8:26 AM, dforums [EMAIL PROTECTED] wrote: Houlala I got headache !!! So please help...;; Assuming they all happen from 9 to 5 and during business days only, that's about 86 transactions per second. Well within the realm of a single mirror set to keep up if you don't make your db work real fat. OK i like, But my reality is that to make an insert of a table that have 27 millions of entrance it took 200 ms. so it took between 2 minutes and 10 minutes to treat 3000 records and dispatch/agregate in other tables. And I have for now 2 records every 3 minutes. Can you partition your data on some logical barrier like a timestamp or something? that would probably help a lot. also, are you doing all 3000 records in one transaction or individual transactions? If one at a time, can you batch them together for better performance or are you stuck doing them one at a time? At the moment I have a I have a Linux 2.6.24.2--std-ipv4-64 #3 SMP Tue Feb 12 12:27:47 CET 2008 x86_64 Intel(R) Xeon(R) CPU X5355 @ 2.66GHz GenuineIntel GNU/Linux with 8Gb of memory. Using sata II disk in RAID 1 (I known that is bad, but it would change has quickly I can). Yeah, you're gonna be I/O bound as long as you've only got a single mirror set. A machine with 8 or 12 SAS 15K drives should make it much more likely you can handle the load. I got 1-2 GO per week Definitely let's look at partitioning then if we can do it. I can change to 2 kinds of server, using 8.3.3 postgresql server, and even taking more sever if need. But it is the biggest computer that I can rent for now. Intel 2x Xeon X5355 2x 4x 2.66 GHz L2: 8Mo, FSB: 1333MHz Double Quadruple Coeur 64 bits 12 Go FBDIMM DDR2 2x 147 Go SAS 15 000 tr/min RAID 1 HARD All that memory and CPU power will be wasted on a db with just two drives. Do you at least have a decent RAID controller in that setup? I can add 500 Go under sataII OR Intel 2x Xeon X5355 2x 4x 2.66 GHz L2: 8Mo, FSB: 1333MHz Double Quadruple Coeur 64 bits 12 Go FBDIMM DDR2 5x 750 Go (2.8 To **) SATA2 RAID HARD 5 I can add 500 Go under sataII RAID5 is generally a poor choice for a write limited DB. I'd guess that the dual SAS drives above would work better than the 5 SATA drives in RAID 5 here. After several tunings, reading, ect... The low speed seems to be definetly linked to the SATA II in RAID 1. Going to 15k SAS RAID 1 will just about double your write rate (assuming it's a commits/second issue and it likely is). going to a 4 disk SAS RAID10 will double that, and so on. So I need a solution to be able to 1st supporting more transaction, secondly I need to secure the data, and being able to load balancing the charge. Look at slony for read only slaves and the master db as write only. If you can handle the slight delay in updates from master to slave. Otherwise you'll need sync replication, and that is generally not as fast. Take a look at something like this server: http://www.aberdeeninc.com/abcatg/Stirling-229.htm With 8 15k SAS 146G drives it runs around $5k or so. Right now all the servers your hosting provider is likely to provide you with are gonna be big on CPU and memory and light on I/O, and that's the opposite of what you need for databases. -- http://www.1st-affiliation.fr *David Bigand *Président Directeur Générale* *51 chemin des moulins 73000 CHAMBERY - FRANCE Web : htttp://www.1st-affiliation.fr Email : [EMAIL PROTECTED] Tel. : +33 479 696 685 Mob. : +33 666 583 836 Skype : firstaffiliation_support -- 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] Using PK value as a String
In response to Moritz Onken [EMAIL PROTECTED]: Am 12.08.2008 um 17:04 schrieb Bill Moran: In response to Moritz Onken [EMAIL PROTECTED]: We chose UUID as PK because there is still some information in an integer key. You can see if a user has registered before someone else (user1.id user2.id) or you can see how many new users registered in a specific period of time (compare the id of the newest user to the id a week ago). This is information which is in some cases critical. So you're accidentally storing critical information in magic values instead of storing it explicitly? Good luck with that. How do I store critical information? I was just saying that it easy to get some information out of a primary key which is an incrementing integer. And it makes sense, in some rare cases, to have a PK which is some kind of random like UUIDs where you cannot guess the next value. I just repeated your words. Read above this is information which is in some cases critical. If I misunderstood, then I misunderstood. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 -- 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] Using PK value as a String
Am 12.08.2008 um 17:21 schrieb Bill Moran: In response to Moritz Onken [EMAIL PROTECTED]: Am 12.08.2008 um 17:04 schrieb Bill Moran: In response to Moritz Onken [EMAIL PROTECTED]: We chose UUID as PK because there is still some information in an integer key. You can see if a user has registered before someone else (user1.id user2.id) or you can see how many new users registered in a specific period of time (compare the id of the newest user to the id a week ago). This is information which is in some cases critical. So you're accidentally storing critical information in magic values instead of storing it explicitly? Good luck with that. How do I store critical information? I was just saying that it easy to get some information out of a primary key which is an incrementing integer. And it makes sense, in some rare cases, to have a PK which is some kind of random like UUIDs where you cannot guess the next value. I just repeated your words. Read above this is information which is in some cases critical. If I misunderstood, then I misunderstood. If you are using incrementing integers as pk then you are storing this data implicitly with your primary key. Using UUIDs is a way to avoid that. -- 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] Using PK value as a String
In response to Steve Atkins [EMAIL PROTECTED]: On Aug 12, 2008, at 8:21 AM, Bill Moran wrote: In response to Moritz Onken [EMAIL PROTECTED]: Am 12.08.2008 um 17:04 schrieb Bill Moran: In response to Moritz Onken [EMAIL PROTECTED]: We chose UUID as PK because there is still some information in an integer key. You can see if a user has registered before someone else (user1.id user2.id) or you can see how many new users registered in a specific period of time (compare the id of the newest user to the id a week ago). This is information which is in some cases critical. So you're accidentally storing critical information in magic values instead of storing it explicitly? Good luck with that. How do I store critical information? I was just saying that it easy to get some information out of a primary key which is an incrementing integer. And it makes sense, in some rare cases, to have a PK which is some kind of random like UUIDs where you cannot guess the next value. I just repeated your words. Read above this is information which is in some cases critical. If I misunderstood, then I misunderstood. I think Moritz is more concerned about leakage of critical information, rather than intentional storage of it. When a simple incrementing integer is used as an identifier in publicly visible places (webapps, ticketing systems) then that may leak more information than intended. Then I did misunderstand. -- Bill Moran Collaborative Fusion Inc. http://people.collaborativefusion.com/~wmoran/ [EMAIL PROTECTED] Phone: 412-422-3463x4023 -- 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] Using PK value as a String
Bill Moran wrote: We chose UUID as PK because there is still some information in an integer key. You can see if a user has registered before someone else (user1.id user2.id) or you can see how many new users registered in a specific period of time (compare the id of the newest user to the id a week ago). This is information which is in some cases critical. I think Moritz is more concerned about leakage of critical information, rather than intentional storage of it. When a simple incrementing integer is used as an identifier in publicly visible places (webapps, ticketing systems) then that may leak more information than intended. While we are on this distraction - UUID will sometimes encode critical information such as: 1) The timestamp (allowing users to be compared), and 2) The MAC address of the computer that generated it. So, I wouldn't say that UUID truly protects you here unless you are sure to use one of the UUID formats that is not timestamp or MAC address based. The main benefit of UUID here is the increased keyspace, so predicting sequence becomes more difficult. (Note that an all-random UUID is not better than two pairs of all-random 64-bit integers with a good random number source. :-) ) Cheers, mark -- Mark Mielke [EMAIL PROTECTED] -- 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] long transaction
On Tue, Aug 12, 2008 at 4:17 AM, Sabin Coanda [EMAIL PROTECTED] wrote: long running transactions can be evil. is there a reason why this has to run in a single transaction? This single transaction is used to import new information in a database. I need it because the database cannot be disconected from the users, and the whole new data has to be consistently. There are different constraints that are checked during the import. have you considered importing to a temporary 'holding' table with copy, then doing 'big' sql statements on it to check constraints, etc? 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] Filesystem benchmarking for pg 8.3.3 server
Hi again all, Just wanted to give you an update. Talked to Dell tech support and they recommended using write- through(!) caching in RAID10 configuration. Well, it didn't work and got even worse performance. Anyone have an estimated what a RAID10 on 4 15k SAS disks should generate in random writes? I'm really keen on trying Scotts suggestion on using the PERC/6 with mirror sets only and then make the stripe with Linux SW raid. Thanks for all the input! Much appreciated. Cheers, Henke 11 aug 2008 kl. 17.56 skrev Greg Smith: On Sun, 10 Aug 2008, Henrik wrote: Normally, when a SATA implementation is running significantly faster than a SAS one, it's because there's some write cache in the SATA disks turned on (which they usually are unless you go out of your way to disable them). Lucky for my I have BBU on all my controllers cards and I'm also not using the SATA drives for database. From how you responded I don't think I made myself clear. In addition to the cache on the controller itself, each of the disks has its own cache, probably 8-32MB in size. Your controllers may have an option to enable or disable the caches on the individual disks, which would be a separate configuration setting from turning the main controller cache on or off. Your results look like what I'd expect if the individual disks caches on the SATA drives were on, while those on the SAS controller were off (which matches the defaults you'll find on some products in both categories). Just something to double-check. By the way: getting useful results out of iozone is fairly difficult if you're unfamiliar with it, there are lots of ways you can set that up to run tests that aren't completely fair or that you don't run them for long enough to give useful results. I'd suggest doing a round of comparisons with bonnie++, which isn't as flexible but will usually give fair results without needing to specify any parameters. The seeks number that comes out of bonnie++ is a combined read/write one and would be good for double-checking whether the unexpected results you're seeing are independant of the benchmark used. -- * 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 -- 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] Filesystem benchmarking for pg 8.3.3 server
On Tue, Aug 12, 2008 at 1:40 PM, Henrik [EMAIL PROTECTED] wrote: Hi again all, Just wanted to give you an update. Talked to Dell tech support and they recommended using write-through(!) caching in RAID10 configuration. Well, it didn't work and got even worse performance. Someone at Dell doesn't understand the difference between write back and write through. Anyone have an estimated what a RAID10 on 4 15k SAS disks should generate in random writes? Using sw RAID or a non-caching RAID controller, you should be able to get close to 2xmax write based on rpms. On 7200 RPM drives that's 2*150 or ~300 small transactions per second. On 15k drives that's about 2*250 or around 500 tps. The bigger the data you're writing, the fewer you're gonna be able to write each second of course. I'm really keen on trying Scotts suggestion on using the PERC/6 with mirror sets only and then make the stripe with Linux SW raid. Definitely worth the try. Even full on sw RAID may be faster. It's worth testing. On our new servers at work, we have Areca controllers with 512M bbc and they were about 10% faster mixing sw and hw raid, but honestly, it wasn't worth the extra trouble of the hw/sw combo to go with. -- 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] Filesystem benchmarking for pg 8.3.3 server
Greg Smith wrote: some write cache in the SATA disks...Since all non-battery backed caches need to get turned off for reliable database use, you might want to double-check that on the controller that's driving the SATA disks. Is this really true? Doesn't the ATA FLUSH CACHE command (say, ATA command 0xE7) guarantee that writes are on the media? http://www.t13.org/Documents/UploadedDocuments/technical/e01126r0.pdf A non-error completion of the command indicates that all cached data since the last FLUSH CACHE command completion was successfully written to media, including any cached data that may have been written prior to receipt of FLUSH CACHE command. (I still can't find any $0 SATA specs; but I imagine the final wording for the command is similar to the wording in the proposal for the command which can be found on the ATA Technical Committee's web site at the link above.) Really old software (notably 2.4 linux kernels) didn't send cache synchronizing commands for SCSI nor either ATA; but it seems well thought through in the 2.6 kernels as described in the Linux kernel documentation. http://www.mjmwired.net/kernel/Documentation/block/barrier.txt If you do have a disk where you need to disable write caches, I'd love to know the name of the disk and see the output of of hdparm -I /dev/sd*** to see if it claims to support such cache flushes. I'm almost tempted to say that if you find yourself having to disable caches on modern (this century) hardware and software, you're probably covering up a more serious issue with your system. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Incorrect estimates on correlated filters
Hello All, Ran into a re-occuring performance problem with some report queries again today. In a nutshell, we have filters on either multiple joined tables, or multiple columns on a single table that are highly correlated. So, the estimates come out grossly incorrect (the planner has no way to know they are correlated). 2000:1 for one I'm looking at right now. Generally this doesn't matter, except in complex reporting queries like these when this is the first join of 40 other joins. Because the estimate is wrong at the lowest level, it snowballs up through the rest of the joins causing the query to run very, very slowly. In many of these cases, forcing nested loops off for the duration of the query fixes the problem. But I have a couple that still are painfully slow and shouldn't be. I've been reading through the archives with others having similar problems (including myself a year ago). Am I right in assuming that at this point there is still little we can do in postgres to speed up this kind of query? Right now the planner has no way to know the correlation between different columns in the same table, let alone columns in different tables. So, it just assumes no correlation and returns incorrectly low estimates in cases like these. The only solution I've come up with so far is to materialize portions of the larger query into subqueries with these correlated filters which are indexed and analyzed before joining into the larger query. This would keep the incorrect estimates from snowballing up through the chain of joins. Are there any other solutions to this problem? Thanks, -Chris
Re: [PERFORM] Filesystem benchmarking for pg 8.3.3 server
Some SATA drives were known to not flush their cache when told to. Some file systems don't know about this (UFS, older linux kernels, etc). So yes, if your OS / File System / Controller card combo properly sends the write cache flush command, and the drive is not a flawed one, all is well. Most should, not all do. Any one of those bits along the chain can potentially be disk write cache unsafe. On Tue, Aug 12, 2008 at 2:47 PM, Ron Mayer [EMAIL PROTECTED]wrote: Greg Smith wrote: some write cache in the SATA disks...Since all non-battery backed caches need to get turned off for reliable database use, you might want to double-check that on the controller that's driving the SATA disks. Is this really true? Doesn't the ATA FLUSH CACHE command (say, ATA command 0xE7) guarantee that writes are on the media? http://www.t13.org/Documents/UploadedDocuments/technical/e01126r0.pdf A non-error completion of the command indicates that all cached data since the last FLUSH CACHE command completion was successfully written to media, including any cached data that may have been written prior to receipt of FLUSH CACHE command. (I still can't find any $0 SATA specs; but I imagine the final wording for the command is similar to the wording in the proposal for the command which can be found on the ATA Technical Committee's web site at the link above.) Really old software (notably 2.4 linux kernels) didn't send cache synchronizing commands for SCSI nor either ATA; but it seems well thought through in the 2.6 kernels as described in the Linux kernel documentation. http://www.mjmwired.net/kernel/Documentation/block/barrier.txt If you do have a disk where you need to disable write caches, I'd love to know the name of the disk and see the output of of hdparm -I /dev/sd*** to see if it claims to support such cache flushes. I'm almost tempted to say that if you find yourself having to disable caches on modern (this century) hardware and software, you're probably covering up a more serious issue with your system. -- 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] Filesystem benchmarking for pg 8.3.3 server
Scott Carey wrote: Some SATA drives were known to not flush their cache when told to. Can you name one? The ATA commands seem pretty clear on the matter, and ISTM most of the reports of these issues came from before Linux had write-barrier support. I've yet to hear of a drive with the problem; though no doubt there are some cheap RAID controllers somewhere that expect you to disable the drive caches. -- 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] Filesystem benchmarking for pg 8.3.3 server
On Tue, Aug 12, 2008 at 6:23 PM, Scott Carey [EMAIL PROTECTED] wrote: Some SATA drives were known to not flush their cache when told to. Some file systems don't know about this (UFS, older linux kernels, etc). So yes, if your OS / File System / Controller card combo properly sends the write cache flush command, and the drive is not a flawed one, all is well. Most should, not all do. Any one of those bits along the chain can potentially be disk write cache unsafe. I can attest to the 2.4 kernel not being able to guarantee fsync on IDE drives. And to the LSI megaraid SCSI controllers of the era surviving numerous power off tests. -- 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] Filesystem benchmarking for pg 8.3.3 server
On Tue, 12 Aug 2008, Ron Mayer wrote: Scott Carey wrote: Some SATA drives were known to not flush their cache when told to. Can you name one? The ATA commands seem pretty clear on the matter, and ISTM most of the reports of these issues came from before Linux had write-barrier support. I can't name one, but I've seen it mentioned in the discussions on linux-kernel several times by the folks who are writing the write-barrier support. David Lang -- 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] Filesystem benchmarking for pg 8.3.3 server
I'm not an expert on which and where -- its been a while since I was exposed to the issue. From what I've read in a few places over time ( storagereview.com, linux and windows patches or knowledge base articles), it happens from time to time. Drives usually get firmware updates quickly. Drivers / Controller cards often take longer to fix. Anyway, my anecdotal recollection was with a few instances of this occuring about 4 years ago and manefesting itself with complaints on message boards then going away. And in general some searching around google indicates this is a problem more often drivers and controllers than drives themselves. I recall some cheap raid cards and controller cards being an issue, like the below: http://www.fixya.com/support/t163682-hard_drive_corrupt_every_reboot And here is an example of an HP Fiber Channel Disk firmware bug: HS02969 28SEP07http://h50025.www5.hp.com/ENP5/Admin/UserFileAdmin/EV/23560/File/Hotstuffs.pdf • Title : OPN FIBRE CHANNEL DISK FIRMWARE • Platform : S-Series NS-Series only with FCDMs • Summary : HP recently discovered a firmware flaw in some versions of 72, 146, and 300 Gigabyte fibre channel disk devices that shipped in late 2006 and early 2007. The flaw enabled the affected disk devices to inadvertently cache write data. In very rare instances, this caching operation presents an opportunity for disk write operations to be lost. Even ext3 doesn't default to using write barriers at this time due to performance concerns: http://lwn.net/Articles/283161/ On Tue, Aug 12, 2008 at 6:33 PM, [EMAIL PROTECTED] wrote: On Tue, 12 Aug 2008, Ron Mayer wrote: Scott Carey wrote: Some SATA drives were known to not flush their cache when told to. Can you name one? The ATA commands seem pretty clear on the matter, and ISTM most of the reports of these issues came from before Linux had write-barrier support. I can't name one, but I've seen it mentioned in the discussions on linux-kernel several times by the folks who are writing the write-barrier support. David Lang -- 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] Filesystem benchmarking for pg 8.3.3 server
Scott Marlowe wrote: I can attest to the 2.4 kernel not being able to guarantee fsync on IDE drives. Sure. But note that it won't for SCSI either; since AFAICT the write barrier support was implemented at the same time for both. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
