Re: PostgresQL database on raw partition (and something about Access conversion)
There was a question about business perspective of MySQL vs. PostgreSQL... As far as I can tell , MySQL is not free for commercial distribution. It's only free if you use it yourself or as part of some Open Source distribution. Correct me if I'm wrong (am I reading MySQL license wrong?) -Alex
Re: PostgresQL database on raw partition (and something about Access conversion)
On Sunday 25 February 2007 16:09, Alex Dover wrote: There was a question about business perspective of MySQL vs. PostgreSQL... As far as I can tell , MySQL is not free for commercial distribution. It's only free if you use it yourself or as part of some Open Source distribution. Correct me if I'm wrong (am I reading MySQL license wrong?) And, PostgreSQL is free in that regard since it has a BSD license which allows it to be incorporated into any commercial distribution or embedded in any closed sourced project. -Alex -- Regards, Tzahi. -- Tzahi Fadida Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info WARNING TO SPAMMERS: see at http://members.lycos.co.uk/my2nis/spamwarning.html To unsubscribe, send mail to [EMAIL PROTECTED] with the word unsubscribe in the message body, e.g., run the command echo unsubscribe | mail [EMAIL PROTECTED]
Re: PostgresQL database on raw partition (and something about Access conversion)
Quoting Tzahi Fadida, from the post of Sun, 25 Feb: On Sunday 25 February 2007 16:09, Alex Dover wrote: There was a question about business perspective of MySQL vs. PostgreSQL... As far as I can tell , MySQL is not free for commercial distribution. It's only free if you use it yourself or as part of some Open Source distribution. Correct me if I'm wrong (am I reading MySQL license wrong?) And, PostgreSQL is free in that regard since it has a BSD license which allows it to be incorporated into any commercial distribution or embedded in any closed sourced project. Didn't MySQL used to be plublished under dual licenses? either GPL or embedded? -- One big joke Ira Abramov http://ira.abramov.org/email/ = To unsubscribe, send mail to [EMAIL PROTECTED] with the word unsubscribe in the message body, e.g., run the command echo unsubscribe | mail [EMAIL PROTECTED]
MySQL License (was: Re: PostgresQL database on raw partition (and something about Access conversion)
On Sun, 25 Feb 2007, Ira Abramov wrote: Quoting Tzahi Fadida, from the post of Sun, 25 Feb: On Sunday 25 February 2007 16:09, Alex Dover wrote: There was a question about business perspective of MySQL vs. PostgreSQL... As far as I can tell , MySQL is not free for commercial distribution. It's only free if you use it yourself or as part of some Open Source distribution. Correct me if I'm wrong (am I reading MySQL license wrong?) And, PostgreSQL is free in that regard since it has a BSD license which allows it to be incorporated into any commercial distribution or embedded in any closed sourced project. Didn't MySQL used to be plublished under dual licenses? either GPL or embedded? it still is. however, the other license is commercial - i.e. you need to pay $$$ for that. -- guy For world domination - press 1, or dial 0, and please hold, for the creator. -- nob o. dy = To unsubscribe, send mail to [EMAIL PROTECTED] with the word unsubscribe in the message body, e.g., run the command echo unsubscribe | mail [EMAIL PROTECTED]
Re: PostgresQL database on raw partition (and something about Access conversion)
On Sunday, 25 בFebruary 2007 16:39, Tzahi Fadida wrote: On Sunday 25 February 2007 16:09, Alex Dover wrote: As far as I can tell , MySQL is not free for commercial distribution. Correct me if I'm wrong (am I reading MySQL license wrong?) And, PostgreSQL is free in that regard since it has a BSD license which allows it to be incorporated into any commercial distribution or embedded in any closed sourced project. Let's not mix commercial with proprietary. MySQL is covered by GPL (in the latest revisions also the client libraries). This means anybody can use it for any purpose (commercial or not) as long as they abide by the license terms. If your commercial venture includes *distributing* the program, than you have to distribute the source as well. Tzahi is correct that proprietary programs that link against MySQL libraries probably cannot be distributed (gratis or for a fee). However, the GPL does permit such a use internally. -- Oron Peled Voice/Fax: +972-4-8228492 [EMAIL PROTECTED] http://www.actcom.co.il/~oron ICQ UIN: 16527398 Microsoft is NOT the answer. Microsoft is the Question. The answer is: NO! = To unsubscribe, send mail to [EMAIL PROTECTED] with the word unsubscribe in the message body, e.g., run the command echo unsubscribe | mail [EMAIL PROTECTED]
Re: MySQL License (was: Re: PostgresQL database on raw partition (and something about Access conversion)
Quoting guy keren, from the post of Sun, 25 Feb: Didn't MySQL used to be plublished under dual licenses? either GPL or embedded? it still is. however, the other license is commercial - i.e. you need to pay $$$ for that. Choo, You've been in this business a long time, I expect more of you... A dual license means you can choose which license you want to use the product under. If you choose GPL, it's GPL all the way, you can redistribute it with source in any way you want along with any product you want, propriatery or open. dynamicly linking with GPL libraries is also fine, only static linking isn't. However, if you want to stick it inside a product (like VersionCue that is built into all of Adobe's products AFAIK) and you want to patch it, hide its original name or hard-link it with other code without redistributing the source, that's when you need to use the other license (and pay for it), which gives you rights to embed MySQL in your product, get support for it and so on. -- Networking washing machines since 1999 Ira Abramov http://ira.abramov.org/email/ = To unsubscribe, send mail to [EMAIL PROTECTED] with the word unsubscribe in the message body, e.g., run the command echo unsubscribe | mail [EMAIL PROTECTED]
Re: PostgresQL database on raw partition (and something about Access conversion)
Yes and no. It will be as slow as any common harddrive for write operations, but it will be extremely fast for read operations. Now, what is your expected usage profile? Ez. Amos Shapira wrote: On 21/02/07, *Ira Abramov* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Quoting guy keren, from the post of Wed, 21 Feb: what? what a mirror is as _slow_ as the _slower_ disk. an I/O request to the mirror, gets a response only after its clones were written into both legs of the mirror - not as soon as one was written. I once did some benchmarks for a client and showed that even a mirror of three disks (yes, every sector written 3 times) the write time penelty was extremely small, however reading speed jumped practically in a Yes but these were all practically identical disks - Guy's response was about my idea to mirror a RAM disk with a regular magnetic media disk, which would mean that that this volume will be as slow as the magnetic media, so loosing the advantage of investing in a RAM disk. Cheers, --Amos
Re: PostgresQL database on raw partition (and something about Access conversion)
Regarding a multi-master clustering solution that someone asked for. Such a product was advertised in the pgsql-announce mailing list. Since the product does not seem to be free i will not advertise it here but rather redirect the readers to the mailing list archives which can be accessed at PostgreSQL main site. On Monday 19 February 2007 05:26, Amos Shapira wrote: -- Regards, Tzahi. -- Tzahi Fadida Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info WARNING TO SPAMMERS: see at http://members.lycos.co.uk/my2nis/spamwarning.html To unsubscribe, send mail to [EMAIL PROTECTED] with the word unsubscribe in the message body, e.g., run the command echo unsubscribe | mail [EMAIL PROTECTED]
Re: PostgresQL database on raw partition (and something about Access conversion)
On 23/02/07, Ez-Aton [EMAIL PROTECTED] wrote: Yes and no. It will be as slow as any common harddrive for write operations, but it will be extremely fast for read operations. Now, what is your expected usage profile? It's in the context of the thread - this RAM disk was suggested in order to speed-up auxiliary database data such as transaction logs and index files, therefore write speed is as important as, if not more than, read speed (expecting that data will be written many more times than read). --Amos
Re: PostgresQL database on raw partition (and something about Access conversion)
Quoting Amos Shapira, from the post of Thu, 22 Feb: Yes but these were all practically identical disks - Guy's response was about my idea to mirror a RAM disk with a regular magnetic media disk, which would mean that that this volume will be as slow as the magnetic media, so loosing the advantage of investing in a RAM disk. an OS-level RAID like that would certainly lose all benefits of the speed of writing (transactions would not be final until the disk informs you it fnished writing), but your reading will be nice and fast. however, that's less usefull than any DBMS caching tables and queries in memory... the one mechanism that does what you are describing is the MySQL NDB backend, also known as MySQL cluster, where all the DBs' tables are in the RAM of the participating nodes, and commiting to disks is done rarely and not even by all the nodes if you don't want it to. the collection of RAM of all the machines is your RAID in a way, and the speed of transactions is said to be phenomenal in theory because you never use the disks, you just trust your UPS very very very much. In practice however, I understand it's not a major speed boost, some say, but I never tested it myself, and I have no idea why. -- Japanese Seizure Robot Ira Abramov http://ira.abramov.org/email/ = To unsubscribe, send mail to [EMAIL PROTECTED] with the word unsubscribe in the message body, e.g., run the command echo unsubscribe | mail [EMAIL PROTECTED]
Re: PostgresQL database on raw partition (and something about Access conversion)
Quoting guy keren, from the post of Wed, 21 Feb: what? what a mirror is as _slow_ as the _slower_ disk. an I/O request to the mirror, gets a response only after its clones were written into both legs of the mirror - not as soon as one was written. I once did some benchmarks for a client and showed that even a mirror of three disks (yes, every sector written 3 times) the write time penelty was extremely small, however reading speed jumped practically in a linear fashion (i.e. a 3-disk RAID1 under linux reads 3 times as fast than a single disk). we are talking of course about three SATA drives... This is one very simple feature is oddly lacking from Micro$oft's software RAID, where you write to all disks in parallel but read from only one of them. I don't need to tell you how moronic I find this... not only the user loses performance, but that disk is going to be the first to blow a gasket, it's as problemtic as RAID4, almost. -- Tired TV producer Ira Abramov http://ira.abramov.org/email/ = To unsubscribe, send mail to [EMAIL PROTECTED] with the word unsubscribe in the message body, e.g., run the command echo unsubscribe | mail [EMAIL PROTECTED]
Re: PostgresQL database on raw partition (and something about Access conversion)
On Tuesday 20 February 2007 01:53, Amos Shapira wrote: On 19/02/07, Tzahi Fadida [EMAIL PROTECTED] wrote: On Monday 19 February 2007 05:26, Amos Shapira wrote: Hello, Is it possible to configure PostgresQL to use raw disk partition, like Oracle does? If not - is there any recommendation for favourite filesystem type to use? Most certainly not. PostgreSQL relies on the OS and FileSystems it inhabits for all I/O operations. Raw partition are not worth the extra effort anycase on an OS such as linux which is already efficient with files and extents of those files. If you want you can use XFS to increase efficiency at the expense of Well I didn't expect Postgres to do the actual SCSI calls to the disk, but I sort of guess that since all the database needs from the OS is a bunch of disk blocks and the database file can be usually pre-allocated, it might be possible to do away from the complexity (and time penalty) of a FS which assumes that files have to be created/resized/removed all the time. That's the way Oracle databases at least used to be configured for many years (taking the update from Ira into account). In PostgreSQL they don't even do the caching (more or less), they let the OS to do it. This is a common question and the claim of the PostgreSQL dev community is that the added benefit of a raw fs will be negligable. I for one believe it because many of them uses oracle also. Not to mention PostgreSQL is more or less compatible in syntax with oracle. I also can tell you that with recent stable versions 8+ and recent kernels you can probably achieve almost raw fs capabilities by using certain file options (which they do). They also use techniques as back writings etc... This thing is very advanced. more chance for data loss in case of a crash since it has a large caching mechanisms. You can also use raid to increase performance. Additionally you may try to put the transactions log, which is the bottleneck in databases, on a ram memory that is backed by a battery. I think i saw a battery PCI card at asus for 50$. Add 128mb ram and you are good to go. That (battey-backed RAM) sounds like a good tip. Will keep it around. Thanks. If you have more specific details to help Google for such a product I'll appreciate to hear them. Check out these sites, i think these are the real thing: http://www.anandtech.com/tradeshows/showdoc.aspx?i=2431p=5 http://www.cenatek.com/ However, also remember to periodically back up your stuff to the HD. I trust batteries only so far. :) Cheers, --Amos -- Regards, Tzahi. -- Tzahi Fadida Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info WARNING TO SPAMMERS: see at http://members.lycos.co.uk/my2nis/spamwarning.html To unsubscribe, send mail to [EMAIL PROTECTED] with the word unsubscribe in the message body, e.g., run the command echo unsubscribe | mail [EMAIL PROTECTED]
Re: PostgresQL database on raw partition (and something about Access conversion)
On Tuesday 20 February 2007 01:48, Amos Shapira wrote: I'm still digging postgresql.org and last time I went to a (large) book shop I saw an entire section (about 6-7 shelves) about MySQL but not a single book about PostgresQL. Did you also compare this to Oracle books? The problem is that PostgreSQL is an overkill for the average joe developer. MySQL is simpler, faster, for the common made-at-home web app. I used PostgreSQL for 3 years, mainly for development and i can tell you it is a magnificent peace of software. I suggest you use the site book, which is free, as i did. It is constantly updated and is very very comprehensive. Cheers, --Amos -- Regards, Tzahi. -- Tzahi Fadida Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info WARNING TO SPAMMERS: see at http://members.lycos.co.uk/my2nis/spamwarning.html To unsubscribe, send mail to [EMAIL PROTECTED] with the word unsubscribe in the message body, e.g., run the command echo unsubscribe | mail [EMAIL PROTECTED]
Re: PostgresQL database on raw partition (and something about Access conversion)
On 2/19/07, Tzahi Fadida [EMAIL PROTECTED] wrote: On Monday 19 February 2007 11:58, Israel Shikler wrote: Hi , I see your post here concerning PostgresQL. We are using RedHat Mysql. Is there any advantages using PostgresQL over Mysql? There are advantages both ways. You will need PostgreSQL when: 1) You need a database that can handle a very large load of queries per a small unit of time (secs,minutes). 2) You need a database that can handle transactions in an efficient manner, plus, you require a transactional system that has many features like save points in a middle of a transaction and you can role back to a savepoint instead of the whole transaction. 3) You need a very wide and extendable syntax and operations. 4) You need an RDBMS that can handle itself in catastrophies such as point in time recovery. i.e. using the transactions log to reconstract the last queries, etc... All of the above requires investment of resources which are wasted if you don't need those advantages. MySQL will serve you better for a small load of queries. It has a less extensive tranactional support. It's syntax is suited for smaller applications. Finally, PostgreSQL exists in various forms for 20 year so it is a beast of a thing that is very mature. MySQL has only matured in features in the last years. This race to features left much to be desired. There is no reason to use PostgreSQL for small apps or small load apps, in fact perhaps even MySQL is an overkill and there are even more efficient databases. Regards, Israel Shikler Softkol Ltd -- Regards, Tzahi. -- Tzahi Fadida Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info WARNING TO SPAMMERS: see at http://members.lycos.co.uk/my2nis/spamwarning.html What about redundancy. I need an active-active cluster for databases to get the 5*'9's up time euphoria. Is there an open source database that can do that? Planing to? Tried to? -- Cheers, Maxim Veksler Free as in Freedom - Do u GNU ? = To unsubscribe, send mail to [EMAIL PROTECTED] with the word unsubscribe in the message body, e.g., run the command echo unsubscribe | mail [EMAIL PROTECTED]
Re: PostgresQL database on raw partition (and something about Access conversion)
On Tuesday 20 February 2007 14:37, Maxim Veksler wrote: What about redundancy. I need an active-active cluster for databases to get the 5*'9's up time euphoria. Is there an open source database that can do that? Planing to? Tried to? PostgreSQL can do that and i am betting MySQL can too. Though not alone. You need an additional product that works with them. Check out Slony-l and friends. Usually we are talking about Master-Slave replication service but i think both can be active. I.e. one point of change, multiple points of read. For peer to peer products, you'd perhaps have to check out other products but i am not keeping current with new versions. -- Regards, Tzahi. -- Tzahi Fadida Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info WARNING TO SPAMMERS: see at http://members.lycos.co.uk/my2nis/spamwarning.html To unsubscribe, send mail to [EMAIL PROTECTED] with the word unsubscribe in the message body, e.g., run the command echo unsubscribe | mail [EMAIL PROTECTED]
Re: PostgresQL database on raw partition (and something about Access conversion)
On 20/02/07, Ira Abramov [EMAIL PROTECTED] wrote: Quoting Amos Shapira, from the post of Tue, 20 Feb: that was only a temporary solution for RAC untill OCFS came along and temporary? Back in '99 Oracle wouldn't have had it any other way. When we yes, temporary the way that punched cards were the best thing till magnetic media and interactive terminal were perfected. Get with the times. Ah, that kind of temporary... Anyway, now that OCFS is out and about - would it be recommended for other databases besides Oracle or is it too Oracle-specific? supposedly it's not Oracle specific, but it has really low I/O for anything else you try to do with it. it's more like raw device you can look at and back up via the VFS, and it's useless if you are not running a cluster. OK, thanks for the explanation. It still sounds like Oracle are trying to minimize the penalty for going through the file system layer, tough. does PostgreSQL even support running two instances of the DB on two separate nodes over GFS?! I don't think it does. that's available only from the big guns like OracleDB and DB2 probably. So far my searches came to a conclusion that PostgresQL doesn't support shared disk, at least not out of the box. I'm still digging postgresql.org and last time I went to a (large) book shop I saw an entire section (about 6-7 shelves) about MySQL but not a single book about PostgresQL. it's a bad bad bad statistics indicator, but I think you may find that on amazon.com (not co.au) the situation will be very similar. and then again, maybe MySQL is enough for the task? Maybe. But I generally like PostgresQL's completeness and robustness, especially when compared to MySQL. Also from talking to people who use PostgresQL to run a Very Important Database (TM) it looks like although they were a bit apologetic about speed compared to MySQL when I asked what should I use, their recommendation was to use MySQL for more transient data with not many inter-record relations and PostgresQL for data which requires many relations and has to be relied on for a long term. --Amos
Re: PostgresQL database on raw partition (and something about Access conversion)
On 20/02/07, Tzahi Fadida [EMAIL PROTECTED] wrote: Did you also compare this to Oracle books? The problem is that PostgreSQL is an overkill for the average joe developer. MySQL is simpler, faster, for the Why is PostgresQL an overkill? It looks just as easy to setup as mysql - apt-get instal... :) common made-at-home web app. I used PostgreSQL for 3 years, mainly for development and i can tell you it is a magnificent peace of software. I I agree - it just feel much more professional and robust than MySQL's multi-backend way of doing things. suggest you use the site book, which is free, as i did. It is constantly updated and is very very comprehensive. Thanks. Postgresql.org is inded a great web site, I was hoping to hear about more pointers. Cheers, --Amos
Re: PostgresQL database on raw partition (and something about Access conversion)
On 20/02/07, Tzahi Fadida [EMAIL PROTECTED] wrote: In PostgreSQL they don't even do the caching (more or less), they let the OS to do it. This is a common question and the claim of the PostgreSQL dev community is that the added benefit of a raw fs will be negligable. I for one believe it because many of them uses oracle also. Not to mention PostgreSQL is more or less compatible in syntax with oracle. I also can tell you that with recent stable versions 8+ and recent kernels you can probably achieve almost raw fs capabilities by using certain file options (which they do). They also use techniques as back writings etc... This thing is very advanced. Thanks for the clarification. That (battey-backed RAM) sounds like a good tip. Will keep it around. Thanks. If you have more specific details to help Google for such a product I'll appreciate to hear them. Check out these sites, i think these are the real thing: http://www.anandtech.com/tradeshows/showdoc.aspx?i=2431p=5 http://www.cenatek.com/ However, also remember to periodically back up your stuff to the HD. I trust batteries only so far. :) Well - if I use it only for transaction logging then hopefully the data there will be useful only for fractions of seconds, wouldn't it? Thanks, --Amos
Re: PostgresQL database on raw partition (and something about Access conversion)
On Wednesday 21 February 2007 01:43, Amos Shapira wrote: On 20/02/07, Tzahi Fadida [EMAIL PROTECTED] wrote: In PostgreSQL they don't even do the caching (more or less), they let the OS to do it. This is a common question and the claim of the PostgreSQL dev community is that the added benefit of a raw fs will be negligable. I for one believe it because many of them uses oracle also. Not to mention PostgreSQL is more or less compatible in syntax with oracle. I also can tell you that with recent stable versions 8+ and recent kernels you can probably achieve almost raw fs capabilities by using certain file options (which they do). They also use techniques as back writings etc... This thing is very advanced. Thanks for the clarification. That (battey-backed RAM) sounds like a good tip. Will keep it around. Thanks. If you have more specific details to help Google for such a product I'll appreciate to hear them. Check out these sites, i think these are the real thing: http://www.anandtech.com/tradeshows/showdoc.aspx?i=2431p=5 http://www.cenatek.com/ However, also remember to periodically back up your stuff to the HD. I trust batteries only so far. :) Well - if I use it only for transaction logging then hopefully the data there will be useful only for fractions of seconds, wouldn't it? Depends. Sometimes queries takes longer. However it is not the point. The reason for backupping once in a while is for PITR - point in time recovery which is a new facility in PostgreSQL 8+. I am not an expert on this subject but IIRC, imagine you backupped the database at 8am. At 9am the database crashed. The data at the database is too corrupt due to various insane reasons you can conjure in your mind. However, the transaction log survived. It turns out, it kept all the transactions back to 8am when you backed up your database. YAY!, you can basically rerun all the transactions from that time using the log and reconstruct the database. Thanks, --Amos -- Regards, Tzahi. -- Tzahi Fadida Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info WARNING TO SPAMMERS: see at http://members.lycos.co.uk/my2nis/spamwarning.html To unsubscribe, send mail to [EMAIL PROTECTED] with the word unsubscribe in the message body, e.g., run the command echo unsubscribe | mail [EMAIL PROTECTED]
Re: PostgresQL database on raw partition (and something about Access conversion)
On 21/02/07, Tzahi Fadida [EMAIL PROTECTED] wrote: Depends. Sometimes queries takes longer. However it is not the point. The reason for backupping once in a while is for PITR - point in time recovery which is a new facility in PostgreSQL 8+. I am not an expert on this subject but IIRC, imagine you backupped the database at 8am. At 9am the database crashed. The data at the database is too corrupt due to various insane reasons you can conjure in your mind. However, the transaction log survived. It turns out, it kept all the transactions back to 8am when you backed up your database. YAY!, you can basically rerun all the transactions from that time using the log and reconstruct the database. Hmm, I see your point. How about creating a RAID 1 (mirror) volume with that disk as one of its sides? That way you get automatic instant backup. I assume the write to the RAID will be as fast as the fastest side. --Amos
Re: PostgresQL database on raw partition (and something about Access conversion)
On Wednesday 21 February 2007 01:39, Amos Shapira wrote: On 20/02/07, Ira Abramov [EMAIL PROTECTED] wrote: Quoting Amos Shapira, from the post of Tue, 20 Feb: that was only a temporary solution for RAC untill OCFS came along and temporary? Back in '99 Oracle wouldn't have had it any other way. When we yes, temporary the way that punched cards were the best thing till magnetic media and interactive terminal were perfected. Get with the times. Ah, that kind of temporary... Anyway, now that OCFS is out and about - would it be recommended for other databases besides Oracle or is it too Oracle-specific? supposedly it's not Oracle specific, but it has really low I/O for anything else you try to do with it. it's more like raw device you can look at and back up via the VFS, and it's useless if you are not running a cluster. OK, thanks for the explanation. It still sounds like Oracle are trying to minimize the penalty for going through the file system layer, tough. does PostgreSQL even support running two instances of the DB on two separate nodes over GFS?! I don't think it does. that's available only from the big guns like OracleDB and DB2 probably. So far my searches came to a conclusion that PostgresQL doesn't support shared disk, at least not out of the box. What do you mean by shared disk? Maybe this is what looking for: http://www.postgresql.org/docs/8.2/interactive/manage-ag-tablespaces.html I'm still digging postgresql.org and last time I went to a (large) book shop I saw an entire section (about 6-7 shelves) about MySQL but not a single book about PostgresQL. it's a bad bad bad statistics indicator, but I think you may find that on amazon.com (not co.au) the situation will be very similar. and then again, maybe MySQL is enough for the task? Maybe. But I generally like PostgresQL's completeness and robustness, especially when compared to MySQL. Also from talking to people who use PostgresQL to run a Very Important Database (TM) it looks like although they were a bit apologetic about speed compared to MySQL when I asked what should I use, their recommendation was to use MySQL for more transient data with not many inter-record relations and PostgresQL for data which requires many relations and has to be relied on for a long term. --Amos -- Regards, Tzahi. -- Tzahi Fadida Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info WARNING TO SPAMMERS: see at http://members.lycos.co.uk/my2nis/spamwarning.html To unsubscribe, send mail to [EMAIL PROTECTED] with the word unsubscribe in the message body, e.g., run the command echo unsubscribe | mail [EMAIL PROTECTED]
Re: PostgresQL database on raw partition (and something about Access conversion)
On 21/02/07, Tzahi Fadida [EMAIL PROTECTED] wrote: On Wednesday 21 February 2007 01:39, Amos Shapira wrote: So far my searches came to a conclusion that PostgresQL doesn't support shared disk, at least not out of the box. What do you mean by shared disk? I meant sharing the same disk and database instance by two active Postgres servers. In http://www.postgresql.org/docs/8.2/interactive/shared-disk-failover.htmlyou can see that they mention sharing of disk between and active and a passive instance. Maybe this is what looking for: http://www.postgresql.org/docs/8.2/interactive/manage-ag-tablespaces.html Sounds like yet another place to visit when I learn about tuning postgres. Cheers, --Amos
Re: PostgresQL database on raw partition (and something about Access conversion)
On 20/02/07, Tzahi Fadida [EMAIL PROTECTED] wrote: On Tuesday 20 February 2007 14:37, Maxim Veksler wrote: What about redundancy. I need an active-active cluster for databases to get the 5*'9's up time euphoria. Is there an open source database that can do that? Planing to? Tried to? PostgreSQL can do that and i am betting MySQL can too. Though not alone. You need an additional product that works with them. Are you sure you need multi-master (aka active-active) in order to achieve 5 9's? From what I gather multi-master is important for scalability (in terms of being able to serve a large number of writing clients). master-slave replication with auto fail-over sounds almost trivial both with MySQL and PostgresQL. According to http://www.postgresql.org/docs/8.2/interactive/failover.htmlthere are commercial solutions to do multi-master that but I couldn't find a pointer to a specific one. There is PGcluster which is supposed to be an open-source solution for multi-master and load balancing, and maybe some sites use it, but it feels a bit like a hack (it's sort of an SQL reverse proxy and/or software mirror RAID, sitting between the clients and the multiple masters, synchronizing changes among the multiple servers), but that's just a feel from reading about it. Check out Slony-l and friends. Usually we are talking about Master-Slave replication service but i think both can be active. I.e. one point of change, As far as I read about Slony-I so far - it's the recommended way to do master-slave replication but for master-master support you'll have to wait for Slony-II ( http://www.slony2.org/) which seems to be years away and I'm not sure how active is its development at all (its main Wiki page was last touched almost a year ago). According to the thread on http://gborg.postgresql.org/pipermail/pgreplication-general/2006-May/thread.html#1433 it sounds not so good - some talk about the initial approach found to be unscalable and generally it starts to look like a frozen project. --Amos
Re: PostgresQL database on raw partition (and something about Access conversion)
Amos Shapira wrote: On 21/02/07, *Tzahi Fadida* [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: Depends. Sometimes queries takes longer. However it is not the point. The reason for backupping once in a while is for PITR - point in time recovery which is a new facility in PostgreSQL 8+. I am not an expert on this subject but IIRC, imagine you backupped the database at 8am. At 9am the database crashed. The data at the database is too corrupt due to various insane reasons you can conjure in your mind. However, the transaction log survived. It turns out, it kept all the transactions back to 8am when you backed up your database. YAY!, you can basically rerun all the transactions from that time using the log and reconstruct the database. Hmm, I see your point. How about creating a RAID 1 (mirror) volume with that disk as one of its sides? That way you get automatic instant backup. I assume the write to the RAID will be as fast as the fastest side. --Amos what? what a mirror is as _slow_ as the _slower_ disk. an I/O request to the mirror, gets a response only after its clones were written into both legs of the mirror - not as soon as one was written. --guy = To unsubscribe, send mail to [EMAIL PROTECTED] with the word unsubscribe in the message body, e.g., run the command echo unsubscribe | mail [EMAIL PROTECTED]
Re: PostgresQL database on raw partition (and something about Access conversion)
Tzahi Fadida wrote: On Tuesday 20 February 2007 14:37, Maxim Veksler wrote: What about redundancy. I need an active-active cluster for databases to get the 5*'9's up time euphoria. Is there an open source database that can do that? Planing to? Tried to? PostgreSQL can do that and i am betting MySQL can too. Though not alone. You need an additional product that works with them. Check out Slony-l and friends. Usually we are talking about Master-Slave replication service but i think both can be active. I.e. one point of change, multiple points of read. For peer to peer products, you'd perhaps have to check out other products but i am not keeping current with new versions. this is not 'active-active'. this is a a single writer with multiple readers. 'active-active' means what amos wrote later on - clients may access either of the two (or more) active nodes for any operation, and they both can peform reads and writes at the same time, to the same database. no replication - just (instantneous) synchronization. --guy = To unsubscribe, send mail to [EMAIL PROTECTED] with the word unsubscribe in the message body, e.g., run the command echo unsubscribe | mail [EMAIL PROTECTED]
Re: PostgresQL database on raw partition (and something about Access conversion)
On 21/02/07, guy keren [EMAIL PROTECTED] wrote: what? what a mirror is as _slow_ as the _slower_ disk. an I/O request to the mirror, gets a response only after its clones were written into both legs of the mirror - not as soon as one was written. Yes I see it now (after some more digging about RAID 1). Looks like PostgresQL.org comes to the rescue again and explains: http://www.postgresql.org/docs/8.2/interactive/continuous-archiving.html So it looks like it should be a simple matter to configure this setup to archive WAL from the RAM disk to more permanent storage. Cheers, --Amos
Re: PostgresQL database on raw partition (and something about Access conversion)
Quoting Amos Shapira, from the post of Mon, 19 Feb: Hello, Is it possible to configure PostgresQL to use raw disk partition, like Oracle does? that was only a temporary solution for RAC untill OCFS came along and now it has become the recommended way of doing things. I see no point in mucking about with RAW devices, expecially if they are not going to be supported, as some LKML threads suggested. -- Supersize me! Ira Abramov http://ira.abramov.org/email/ = To unsubscribe, send mail to [EMAIL PROTECTED] with the word unsubscribe in the message body, e.g., run the command echo unsubscribe | mail [EMAIL PROTECTED]
Re: PostgresQL database on raw partition (and something about Access conversion)
On Monday 19 February 2007 05:26, Amos Shapira wrote: Hello, Is it possible to configure PostgresQL to use raw disk partition, like Oracle does? If not - is there any recommendation for favourite filesystem type to use? Most certainly not. PostgreSQL relies on the OS and FileSystems it inhabits for all I/O operations. Raw partition are not worth the extra effort anycase on an OS such as linux which is already efficient with files and extents of those files. If you want you can use XFS to increase efficiency at the expense of more chance for data loss in case of a crash since it has a large caching mechanisms. You can also use raid to increase performance. Additionally you may try to put the transactions log, which is the bottleneck in databases, on a ram memory that is backed by a battery. I think i saw a battery PCI card at asus for 50$. Add 128mb ram and you are good to go. So far google'ing around haven't revealed anything about the subject. And BTW - I found the following link which also mentions procedures to convert MS Access databases to Psql: http://www.postgresql.org/docs/techdocs.3 Cheers, --Amos -- Regards, Tzahi. -- Tzahi Fadida Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info WARNING TO SPAMMERS: see at http://members.lycos.co.uk/my2nis/spamwarning.html To unsubscribe, send mail to [EMAIL PROTECTED] with the word unsubscribe in the message body, e.g., run the command echo unsubscribe | mail [EMAIL PROTECTED]
RE: PostgresQL database on raw partition (and something about Access conversion)
Hi , I see your post here concerning PostgresQL. We are using RedHat Mysql. Is there any advantages using PostgresQL over Mysql? Regards, Israel Shikler Softkol Ltd -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Tzahi Fadida Sent: Monday, February 19, 2007 11:44 AM To: Amos Shapira Cc: Linux-IL Subject: Re: PostgresQL database on raw partition (and something about Access conversion) On Monday 19 February 2007 05:26, Amos Shapira wrote: Hello, Is it possible to configure PostgresQL to use raw disk partition, like Oracle does? If not - is there any recommendation for favourite filesystem type to use? Most certainly not. PostgreSQL relies on the OS and FileSystems it inhabits for all I/O operations. Raw partition are not worth the extra effort anycase on an OS such as linux which is already efficient with files and extents of those files. If you want you can use XFS to increase efficiency at the expense of more chance for data loss in case of a crash since it has a large caching mechanisms. You can also use raid to increase performance. Additionally you may try to put the transactions log, which is the bottleneck in databases, on a ram memory that is backed by a battery. I think i saw a battery PCI card at asus for 50$. Add 128mb ram and you are good to go. So far google'ing around haven't revealed anything about the subject. And BTW - I found the following link which also mentions procedures to convert MS Access databases to Psql: http://www.postgresql.org/docs/techdocs.3 Cheers, --Amos -- Regards, Tzahi. -- Tzahi Fadida Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info WARNING TO SPAMMERS: see at http://members.lycos.co.uk/my2nis/spamwarning.html To unsubscribe, send mail to [EMAIL PROTECTED] with the word unsubscribe in the message body, e.g., run the command echo unsubscribe | mail [EMAIL PROTECTED] = To unsubscribe, send mail to [EMAIL PROTECTED] with the word unsubscribe in the message body, e.g., run the command echo unsubscribe | mail [EMAIL PROTECTED]
Re: PostgresQL database on raw partition (and something about Access conversion)
On Monday 19 February 2007 11:58, Israel Shikler wrote: Hi , I see your post here concerning PostgresQL. We are using RedHat Mysql. Is there any advantages using PostgresQL over Mysql? There are advantages both ways. You will need PostgreSQL when: 1) You need a database that can handle a very large load of queries per a small unit of time (secs,minutes). 2) You need a database that can handle transactions in an efficient manner, plus, you require a transactional system that has many features like save points in a middle of a transaction and you can role back to a savepoint instead of the whole transaction. 3) You need a very wide and extendable syntax and operations. 4) You need an RDBMS that can handle itself in catastrophies such as point in time recovery. i.e. using the transactions log to reconstract the last queries, etc... All of the above requires investment of resources which are wasted if you don't need those advantages. MySQL will serve you better for a small load of queries. It has a less extensive tranactional support. It's syntax is suited for smaller applications. Finally, PostgreSQL exists in various forms for 20 year so it is a beast of a thing that is very mature. MySQL has only matured in features in the last years. This race to features left much to be desired. There is no reason to use PostgreSQL for small apps or small load apps, in fact perhaps even MySQL is an overkill and there are even more efficient databases. Regards, Israel Shikler Softkol Ltd -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Tzahi Fadida Sent: Monday, February 19, 2007 11:44 AM To: Amos Shapira Cc: Linux-IL Subject: Re: PostgresQL database on raw partition (and something about Access conversion) On Monday 19 February 2007 05:26, Amos Shapira wrote: Hello, Is it possible to configure PostgresQL to use raw disk partition, like Oracle does? If not - is there any recommendation for favourite filesystem type to use? Most certainly not. PostgreSQL relies on the OS and FileSystems it inhabits for all I/O operations. Raw partition are not worth the extra effort anycase on an OS such as linux which is already efficient with files and extents of those files. If you want you can use XFS to increase efficiency at the expense of more chance for data loss in case of a crash since it has a large caching mechanisms. You can also use raid to increase performance. Additionally you may try to put the transactions log, which is the bottleneck in databases, on a ram memory that is backed by a battery. I think i saw a battery PCI card at asus for 50$. Add 128mb ram and you are good to go. So far google'ing around haven't revealed anything about the subject. And BTW - I found the following link which also mentions procedures to convert MS Access databases to Psql: http://www.postgresql.org/docs/techdocs.3 Cheers, --Amos -- Regards, Tzahi. -- Tzahi Fadida Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info WARNING TO SPAMMERS: see at http://members.lycos.co.uk/my2nis/spamwarning.html To unsubscribe, send mail to [EMAIL PROTECTED] with the word unsubscribe in the message body, e.g., run the command echo unsubscribe | mail [EMAIL PROTECTED] -- Regards, Tzahi. -- Tzahi Fadida Blog: http://tzahi.blogsite.org | Home Site: http://tzahi.webhop.info WARNING TO SPAMMERS: see at http://members.lycos.co.uk/my2nis/spamwarning.html To unsubscribe, send mail to [EMAIL PROTECTED] with the word unsubscribe in the message body, e.g., run the command echo unsubscribe | mail [EMAIL PROTECTED]
Re: PostgresQL database on raw partition (and something about Access conversion)
On 19/02/07, Ira Abramov [EMAIL PROTECTED] wrote: Quoting Amos Shapira, from the post of Mon, 19 Feb: Hello, Is it possible to configure PostgresQL to use raw disk partition, like Oracle does? that was only a temporary solution for RAC untill OCFS came along and now it has become the recommended way of doing things. I see no point in mucking about with RAW devices, expecially if they are not going to be supported, as some LKML threads suggested. temporary? Back in '99 Oracle wouldn't have had it any other way. When we (Sun MDE) did benchmarks of E10k for Amdocs it was all about how to shift RAID volumes and Oracle database/index/log partitions around to maximize performance. Anyway, now that OCFS is out and about - would it be recommended for other databases besides Oracle or is it too Oracle-specific? (side note - I'm reading linux/Documentation/vfs.txt to see how I can implement a tiny addition to procfs and had this brilliant idea for yet another db-specific filesystem but you seem to trump all over my hopes...:-). My original question stemmed from a wider perspective - I expect to have to put a business + technical case for converting from MS-SQL to PostgresQL soon. Do people with actual dirt on their hands point to a good source for Postgres performance tuning? I'm still digging postgresql.org and last time I went to a (large) book shop I saw an entire section (about 6-7 shelves) about MySQL but not a single book about PostgresQL. Cheers, --Amos
Re: PostgresQL database on raw partition (and something about Access conversion)
On 19/02/07, Tzahi Fadida [EMAIL PROTECTED] wrote: On Monday 19 February 2007 05:26, Amos Shapira wrote: Hello, Is it possible to configure PostgresQL to use raw disk partition, like Oracle does? If not - is there any recommendation for favourite filesystem type to use? Most certainly not. PostgreSQL relies on the OS and FileSystems it inhabits for all I/O operations. Raw partition are not worth the extra effort anycase on an OS such as linux which is already efficient with files and extents of those files. If you want you can use XFS to increase efficiency at the expense of Well I didn't expect Postgres to do the actual SCSI calls to the disk, but I sort of guess that since all the database needs from the OS is a bunch of disk blocks and the database file can be usually pre-allocated, it might be possible to do away from the complexity (and time penalty) of a FS which assumes that files have to be created/resized/removed all the time. That's the way Oracle databases at least used to be configured for many years (taking the update from Ira into account). more chance for data loss in case of a crash since it has a large caching mechanisms. You can also use raid to increase performance. Additionally you may try to put the transactions log, which is the bottleneck in databases, on a ram memory that is backed by a battery. I think i saw a battery PCI card at asus for 50$. Add 128mb ram and you are good to go. That (battey-backed RAM) sounds like a good tip. Will keep it around. Thanks. If you have more specific details to help Google for such a product I'll appreciate to hear them. Cheers, --Amos
Re: PostgresQL database on raw partition (and something about Access conversion)
Quoting Amos Shapira, from the post of Tue, 20 Feb: that was only a temporary solution for RAC untill OCFS came along and temporary? Back in '99 Oracle wouldn't have had it any other way. When we yes, temporary the way that punched cards were the best thing till magnetic media and interactive terminal were perfected. Get with the times. Anyway, now that OCFS is out and about - would it be recommended for other databases besides Oracle or is it too Oracle-specific? supposedly it's not Oracle specific, but it has really low I/O for anything else you try to do with it. it's more like raw device you can look at and back up via the VFS, and it's useless if you are not running a cluster. does PostgreSQL even support running two instances of the DB on two separate nodes over GFS?! I don't think it does. that's available only from the big guns like OracleDB and DB2 probably. I'm still digging postgresql.org and last time I went to a (large) book shop I saw an entire section (about 6-7 shelves) about MySQL but not a single book about PostgresQL. it's a bad bad bad statistics indicator, but I think you may find that on amazon.com (not co.au) the situation will be very similar. and then again, maybe MySQL is enough for the task? -- The bad boy of the .sig world Ira Abramov http://ira.abramov.org/email/ = To unsubscribe, send mail to [EMAIL PROTECTED] with the word unsubscribe in the message body, e.g., run the command echo unsubscribe | mail [EMAIL PROTECTED]