Re: [mailinglists] Re: MySQL - PostgreSQL - DB2 - What?
Hi, we are running several mysql servers and one is extremly busy performing nearly 1k queries per second average. mysql 4.0.18 is very stable, fast and as far as it comes to transactions the table type is important. mysqls myisam table type is not able to do transactions, but from mysql 4.x on innodb is included by default and you can do transactions, row-locking and pretty much other stuff. what is really missing in my view is the ability to do sub-queries (planned for 4.1), stored procedures (planned for 5.0) and a real solution (with native mysql) for clustering mysql dbs (no idea about plans). at the moment you can only do replication with a master (where writes go to) and serveral slaves (where you can read from), but i need a system with the ability to do synchronous replication between two or more masters to have a HA mysql cluster. but this is not offered by postgreSQL either. if you dont need replication then using mysql is a good choice. Best Regards, Philipp PS: concerning "mission critical": the next election of the european parliament will be supported by mysql and a java-application. have a look at this article: http://www.heise.de/newsticker/result.xhtml?url=/newsticker/meldung/45001 (german only, sorry) > > I too have had similar concerns. I always thought of postgresql as the > > "stable" database and mysql as the, well, "development" database or > > "non-mission-critical" database. Esp. since mysql didn't handle > > transactoins (which is why it was faster). Check out some of the articles > > on phpbuilder.com re: speed tests and things like that of postgres vs mysql. > > The author did lots of tests and found that mysql did not outperform > > postgres on some of his tests. > > I thought that 4.0.1+ was introducing transactions, or at least 4.1 is. We > have MySQL on our production sites for clients and have to say it is pretty > solid. We have some clients running 20K plus records. > > There was some issues with 4.0.12-13 though. As far as I know 4.0.17 is > solid as a rock so far. > -- > Thanks!! > David Thurman > List Only at Web Presence Group Net > > > > -- > To UNSUBSCRIBE, email to [EMAIL PROTECTED] > with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED] > >
Re: MySQL - PostgreSQL - DB2 - What? [SCANNED]
On 3/5/04 10:49 AM, "Ben Yau" wrote: > I too have had similar concerns. I always thought of postgresql as the > "stable" database and mysql as the, well, "development" database or > "non-mission-critical" database. Esp. since mysql didn't handle > transactoins (which is why it was faster). Check out some of the articles > on phpbuilder.com re: speed tests and things like that of postgres vs mysql. > The author did lots of tests and found that mysql did not outperform > postgres on some of his tests. I thought that 4.0.1+ was introducing transactions, or at least 4.1 is. We have MySQL on our production sites for clients and have to say it is pretty solid. We have some clients running 20K plus records. There was some issues with 4.0.12-13 though. As far as I know 4.0.17 is solid as a rock so far. -- Thanks!! David Thurman List Only at Web Presence Group Net
Re: [mailinglists] Re: MySQL - PostgreSQL - DB2 - What?
Hi, we are running several mysql servers and one is extremly busy performing nearly 1k queries per second average. mysql 4.0.18 is very stable, fast and as far as it comes to transactions the table type is important. mysqls myisam table type is not able to do transactions, but from mysql 4.x on innodb is included by default and you can do transactions, row-locking and pretty much other stuff. what is really missing in my view is the ability to do sub-queries (planned for 4.1), stored procedures (planned for 5.0) and a real solution (with native mysql) for clustering mysql dbs (no idea about plans). at the moment you can only do replication with a master (where writes go to) and serveral slaves (where you can read from), but i need a system with the ability to do synchronous replication between two or more masters to have a HA mysql cluster. but this is not offered by postgreSQL either. if you dont need replication then using mysql is a good choice. Best Regards, Philipp PS: concerning "mission critical": the next election of the european parliament will be supported by mysql and a java-application. have a look at this article: http://www.heise.de/newsticker/result.xhtml?url=/newsticker/meldung/45001 (german only, sorry) > > I too have had similar concerns. I always thought of postgresql as the > > "stable" database and mysql as the, well, "development" database or > > "non-mission-critical" database. Esp. since mysql didn't handle > > transactoins (which is why it was faster). Check out some of the articles > > on phpbuilder.com re: speed tests and things like that of postgres vs mysql. > > The author did lots of tests and found that mysql did not outperform > > postgres on some of his tests. > > I thought that 4.0.1+ was introducing transactions, or at least 4.1 is. We > have MySQL on our production sites for clients and have to say it is pretty > solid. We have some clients running 20K plus records. > > There was some issues with 4.0.12-13 though. As far as I know 4.0.17 is > solid as a rock so far. > -- > Thanks!! > David Thurman > List Only at Web Presence Group Net > > > > -- > To UNSUBSCRIBE, email to [EMAIL PROTECTED] > with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED] > > -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
Re: MySQL - PostgreSQL - DB2 - What? [SCANNED]
On 3/5/04 10:49 AM, "Ben Yau" wrote: > I too have had similar concerns. I always thought of postgresql as the > "stable" database and mysql as the, well, "development" database or > "non-mission-critical" database. Esp. since mysql didn't handle > transactoins (which is why it was faster). Check out some of the articles > on phpbuilder.com re: speed tests and things like that of postgres vs mysql. > The author did lots of tests and found that mysql did not outperform > postgres on some of his tests. I thought that 4.0.1+ was introducing transactions, or at least 4.1 is. We have MySQL on our production sites for clients and have to say it is pretty solid. We have some clients running 20K plus records. There was some issues with 4.0.12-13 though. As far as I know 4.0.17 is solid as a rock so far. -- Thanks!! David Thurman List Only at Web Presence Group Net -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
Re: MySQL - PostgreSQL - DB2 - What?
I have worked with Oracle, MS-SQL, Sybase, PostgreSQL and MySQL. For smaller applications like you are describing, MySQL wins hands down. I currently have one table that has over 5 million rows (distance between any two zip (postal) codes that are >= 50 miles apart in the US), and it works in real time with no problem. Most complex query in the app is done with a union of four queries, each reading from four tables (one of which is the zip codes), with probably a half dozen "where" clauses each and, as I said, the response is in real time. That is on a 1.7Mhz machine, 512M DRAM, 10,000rpm SCSI in a hardware RAID 5, though in tests it worked just fine on a 500Mhz machine with 128M and a single SCSI HDD. Only time I've been frustrated with it was when I created the zip code distance table by doing a cross join of a zipcode/lat/long table with itself, then using floating point math to calc the distance. That took about 2.5 hours on a 500Mhz computer with 128M RAM, which is reasonable as far as I'm concerned. PostgreSQL is fast also, though I haven't tested it with that load. I would assume it would comparable. However, I hate managing PostgreSQL. Don't know why, but I always seem to have problems when there is an upgrade and it drives me up a tree. (MySQL has very non-standard permissions, by the way). I would say that for under 10M rows in any table, they are both good. That may be conservative. There is also the fact that MySQL has been updated quite a bit, and getting the newer (4.???) version is well worth it. I chose PostgreSQL at one time because I love subselects, but now MySQL does them also, plus transactions and (if you are of an entirely different mindset from me) will support foreign keys Real Soon Now. And unions. Finally, unions! Basically, if you want full SQL-92 right now, I think you still have to go PostgreSQL. But, if you don't need the whole thing, administration of MySQL makes it the winner for me. Rod > Hi > We're planning a new website where we will use a DB with 500.000 to 1.000.000 > records. We are now deciding which database server we will use. We've read > that MySQL has big problems from 150.000 records and more. Also we've read > that PostgreSQL is very slow on such records. > But we don't have any experience, so we must rely on other people experience. > > I'm sure there are some stories about DB servers, like MySQL being the fastest > ever, or MySQL functionality being the most ridiculous ever (can't do certain > subselects, triggers...). > > What do you think of that stories? Which DB server would you use? > > > -- > To UNSUBSCRIBE, email to [EMAIL PROTECTED] > with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED] > > -- Latest survey shows that 3 out of 4 people make up 75% of the world's population.
Re: MySQL - PostgreSQL - DB2 - What?
I have worked with Oracle, MS-SQL, Sybase, PostgreSQL and MySQL. For smaller applications like you are describing, MySQL wins hands down. I currently have one table that has over 5 million rows (distance between any two zip (postal) codes that are >= 50 miles apart in the US), and it works in real time with no problem. Most complex query in the app is done with a union of four queries, each reading from four tables (one of which is the zip codes), with probably a half dozen "where" clauses each and, as I said, the response is in real time. That is on a 1.7Mhz machine, 512M DRAM, 10,000rpm SCSI in a hardware RAID 5, though in tests it worked just fine on a 500Mhz machine with 128M and a single SCSI HDD. Only time I've been frustrated with it was when I created the zip code distance table by doing a cross join of a zipcode/lat/long table with itself, then using floating point math to calc the distance. That took about 2.5 hours on a 500Mhz computer with 128M RAM, which is reasonable as far as I'm concerned. PostgreSQL is fast also, though I haven't tested it with that load. I would assume it would comparable. However, I hate managing PostgreSQL. Don't know why, but I always seem to have problems when there is an upgrade and it drives me up a tree. (MySQL has very non-standard permissions, by the way). I would say that for under 10M rows in any table, they are both good. That may be conservative. There is also the fact that MySQL has been updated quite a bit, and getting the newer (4.???) version is well worth it. I chose PostgreSQL at one time because I love subselects, but now MySQL does them also, plus transactions and (if you are of an entirely different mindset from me) will support foreign keys Real Soon Now. And unions. Finally, unions! Basically, if you want full SQL-92 right now, I think you still have to go PostgreSQL. But, if you don't need the whole thing, administration of MySQL makes it the winner for me. Rod > Hi > We're planning a new website where we will use a DB with 500.000 to 1.000.000 > records. We are now deciding which database server we will use. We've read > that MySQL has big problems from 150.000 records and more. Also we've read > that PostgreSQL is very slow on such records. > But we don't have any experience, so we must rely on other people experience. > > I'm sure there are some stories about DB servers, like MySQL being the fastest > ever, or MySQL functionality being the most ridiculous ever (can't do certain > subselects, triggers...). > > What do you think of that stories? Which DB server would you use? > > > -- > To UNSUBSCRIBE, email to [EMAIL PROTECTED] > with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED] > > -- Latest survey shows that 3 out of 4 people make up 75% of the world's population. -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
RE: MySQL - PostgreSQL - DB2 - What?
> > > Hi, > > I work as webmaster for SONY. > We use several db system (oracle, postgressql, mysql) > > We are very happy and satisfied with mySQL! > > > > We're planning a new website where we will use a DB with > > 500.000 to 1.000.000 records. We are now deciding which > > database server we will use. We've read that MySQL > > has big problems from 150.000 records and more. > > Not true. I administer several bigger websites/projects running on mySQL. > I have mySQL databases with over 10.000.000 records which runs fast > and are perfectly able to scope with high number of read and writes. > I too have had similar concerns. I always thought of postgresql as the "stable" database and mysql as the, well, "development" database or "non-mission-critical" database. Esp. since mysql didn't handle transactoins (which is why it was faster). Check out some of the articles on phpbuilder.com re: speed tests and things like that of postgres vs mysql. The author did lots of tests and found that mysql did not outperform postgres on some of his tests. That being said, during my research I had found that lots of companies are using mysql on large production dbs without problems. I did use it on a recent project of mine and had no problems with it. I think I inserted 10,000,000 records into a table and had minor problems with the index rebuilding (slow...) and was doing lots of inserts/deletes during my testing phase. DIdn't seem as quick to do this as postgres was. But if you're not going to be doing huge maounts of inserts/deletes at one time, it seemed fine (my laptop by the way i was testing on was a PIII with 128MB RAM on redhat 9) Ben >
RE: MySQL - PostgreSQL - DB2 - What?
Hi, I work as webmaster for SONY. We use several db system (oracle, postgressql, mysql) We are very happy and satisfied with mySQL! > We're planning a new website where we will use a DB with > 500.000 to 1.000.000 records. We are now deciding which > database server we will use. We've read that MySQL > has big problems from 150.000 records and more. Not true. I administer several bigger websites/projects running on mySQL. I have mySQL databases with over 10.000.000 records which runs fast and are perfectly able to scope with high number of read and writes. There is on thing you need to be aware of with mySQL databases: With MySQL you can choose which table format (handler) you want to store your data in. These different table formats have different abilities. e.g. myISAM -no transactions -very fast -excellent read performance -very good write performance -but bad performence for simultanious read and writes! innodb -transactions -fast but little bit slower than myISAM -can handle high volume simultaniously reads and writes. > Also we've read that PostgreSQL is very slow on such records. Sounds like old/wrong info to me. PostgreSQL is powerfull and actually quite fast. I have experience with Oracle, Postgress and mySQL. I can get Oracle basicly for free (company agreement). I would choose MySQL in 9 out of 10 times for my project. I hope that my comments will help you. Kind regards Gunnar
RE: MySQL - PostgreSQL - DB2 - What?
> > > Hi, > > I work as webmaster for SONY. > We use several db system (oracle, postgressql, mysql) > > We are very happy and satisfied with mySQL! > > > > We're planning a new website where we will use a DB with > > 500.000 to 1.000.000 records. We are now deciding which > > database server we will use. We've read that MySQL > > has big problems from 150.000 records and more. > > Not true. I administer several bigger websites/projects running on mySQL. > I have mySQL databases with over 10.000.000 records which runs fast > and are perfectly able to scope with high number of read and writes. > I too have had similar concerns. I always thought of postgresql as the "stable" database and mysql as the, well, "development" database or "non-mission-critical" database. Esp. since mysql didn't handle transactoins (which is why it was faster). Check out some of the articles on phpbuilder.com re: speed tests and things like that of postgres vs mysql. The author did lots of tests and found that mysql did not outperform postgres on some of his tests. That being said, during my research I had found that lots of companies are using mysql on large production dbs without problems. I did use it on a recent project of mine and had no problems with it. I think I inserted 10,000,000 records into a table and had minor problems with the index rebuilding (slow...) and was doing lots of inserts/deletes during my testing phase. DIdn't seem as quick to do this as postgres was. But if you're not going to be doing huge maounts of inserts/deletes at one time, it seemed fine (my laptop by the way i was testing on was a PIII with 128MB RAM on redhat 9) Ben > -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
RE: MySQL - PostgreSQL - DB2 - What?
Hi, I work as webmaster for SONY. We use several db system (oracle, postgressql, mysql) We are very happy and satisfied with mySQL! > We're planning a new website where we will use a DB with > 500.000 to 1.000.000 records. We are now deciding which > database server we will use. We've read that MySQL > has big problems from 150.000 records and more. Not true. I administer several bigger websites/projects running on mySQL. I have mySQL databases with over 10.000.000 records which runs fast and are perfectly able to scope with high number of read and writes. There is on thing you need to be aware of with mySQL databases: With MySQL you can choose which table format (handler) you want to store your data in. These different table formats have different abilities. e.g. myISAM -no transactions -very fast -excellent read performance -very good write performance -but bad performence for simultanious read and writes! innodb -transactions -fast but little bit slower than myISAM -can handle high volume simultaniously reads and writes. > Also we've read that PostgreSQL is very slow on such records. Sounds like old/wrong info to me. PostgreSQL is powerfull and actually quite fast. I have experience with Oracle, Postgress and mySQL. I can get Oracle basicly for free (company agreement). I would choose MySQL in 9 out of 10 times for my project. I hope that my comments will help you. Kind regards Gunnar -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
Re: MySQL - PostgreSQL - DB2 - What?
On Fri, 5 Mar 2004 22:14, Tomàs Núñez Lirola <[EMAIL PROTECTED]> wrote: > We're planning a new website where we will use a DB with 500.000 to > 1.000.000 records. We are now deciding which database server we will use. > We've read that MySQL has big problems from 150.000 records and more. Also > we've read that PostgreSQL is very slow on such records. > But we don't have any experience, so we must rely on other people > experience. How big are these records? Usually records are no more than 1K in size, so the entire database should fit into cache. I've run databases much slower than those on hardware that was OK by 1999 standards (but sucks badly by today's standards) and it was OK. Of course it really depends on what exactly you are doing, how many indexes, how many programs may be writing at the same time, whether you need transactions, etc. But given RAM prices etc I suggest first making sure that your RAM is about the same size as the database if at all possible. If you can do that then apart from 5-10 mins at startup IO performance is totally dependant on writes. Then get a battery-backed write-back disk cache for best write performance (maybe use data journalling and put an external journal on a device from http://www.umem.com ). Probably getting the performance you want is easy if you have the right budget and are able to be a little creative with the way you install things (EG the uMem device). The REAL issue will probably be redundancy. -- http://www.coker.com.au/selinux/ My NSA Security Enhanced Linux packages http://www.coker.com.au/bonnie++/ Bonnie++ hard drive benchmark http://www.coker.com.au/postal/Postal SMTP/POP benchmark http://www.coker.com.au/~russell/ My home page
Re: MySQL - PostgreSQL - DB2 - What?
On Fri, 2004-03-05 12:14:51 +0100, Tomàs Núñez Lirola <[EMAIL PROTECTED]> wrote in message <[EMAIL PROTECTED]>: > I'm sure there are some stories about DB servers, like MySQL being the > fastest > ever, or MySQL functionality being the most ridiculous ever (can't do certain > subselects, triggers...). MySQL seems to perform quite good on small amounts of data and limited selects. This is why it's used so much for web development. Personally, I'd use PostgreSQL. It's stable and can handle large tables. And, it's faster (most of the time) if you have more complex queries... MfG, JBG -- Jan-Benedict Glaw [EMAIL PROTECTED]. +49-172-7608481 "Eine Freie Meinung in einem Freien Kopf| Gegen Zensur | Gegen Krieg fuer einen Freien Staat voll Freier Bürger" | im Internet! | im Irak! ret = do_actions((curr | FREE_SPEECH) & ~(NEW_COPYRIGHT_LAW | DRM | TCPA)); signature.asc Description: Digital signature
Re: MySQL - PostgreSQL - DB2 - What?
On Fri, 5 Mar 2004 22:14, Tomàs Núñez Lirola <[EMAIL PROTECTED]> wrote: > We're planning a new website where we will use a DB with 500.000 to > 1.000.000 records. We are now deciding which database server we will use. > We've read that MySQL has big problems from 150.000 records and more. Also > we've read that PostgreSQL is very slow on such records. > But we don't have any experience, so we must rely on other people > experience. How big are these records? Usually records are no more than 1K in size, so the entire database should fit into cache. I've run databases much slower than those on hardware that was OK by 1999 standards (but sucks badly by today's standards) and it was OK. Of course it really depends on what exactly you are doing, how many indexes, how many programs may be writing at the same time, whether you need transactions, etc. But given RAM prices etc I suggest first making sure that your RAM is about the same size as the database if at all possible. If you can do that then apart from 5-10 mins at startup IO performance is totally dependant on writes. Then get a battery-backed write-back disk cache for best write performance (maybe use data journalling and put an external journal on a device from http://www.umem.com ). Probably getting the performance you want is easy if you have the right budget and are able to be a little creative with the way you install things (EG the uMem device). The REAL issue will probably be redundancy. -- http://www.coker.com.au/selinux/ My NSA Security Enhanced Linux packages http://www.coker.com.au/bonnie++/ Bonnie++ hard drive benchmark http://www.coker.com.au/postal/Postal SMTP/POP benchmark http://www.coker.com.au/~russell/ My home page -- To UNSUBSCRIBE, email to [EMAIL PROTECTED] with a subject of "unsubscribe". Trouble? Contact [EMAIL PROTECTED]
Re: MySQL - PostgreSQL - DB2 - What?
On Fri, 2004-03-05 12:14:51 +0100, Tomàs Núñez Lirola <[EMAIL PROTECTED]> wrote in message <[EMAIL PROTECTED]>: > I'm sure there are some stories about DB servers, like MySQL being the fastest > ever, or MySQL functionality being the most ridiculous ever (can't do certain > subselects, triggers...). MySQL seems to perform quite good on small amounts of data and limited selects. This is why it's used so much for web development. Personally, I'd use PostgreSQL. It's stable and can handle large tables. And, it's faster (most of the time) if you have more complex queries... MfG, JBG -- Jan-Benedict Glaw [EMAIL PROTECTED]. +49-172-7608481 "Eine Freie Meinung in einem Freien Kopf| Gegen Zensur | Gegen Krieg fuer einen Freien Staat voll Freier Bürger" | im Internet! | im Irak! ret = do_actions((curr | FREE_SPEECH) & ~(NEW_COPYRIGHT_LAW | DRM | TCPA)); signature.asc Description: Digital signature