Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
2009/3/19 Shane Ambler pg...@sheeky.biz: Thomas Kellerer wrote: Harald Armin Massa, 17.03.2009 15:00: That is: what table size would you or anybody consider really, really large actually? I recently attended and Oracle training by Tom Kyte and he said (partially joking though) that a database is only large when the size is measured in terrabytes :) So really, really large would mean something like 100 petabytes My personal opinion is that a large database has more than ~10 million rows in more than ~10 tables. Thomas I would say that as far as GPS data goes the street maps of the world would be pretty big. openstreetmap.org is still a work in progress but their current db dumps gzip down to 6.4GB. It was a while back that I noseyed around with it but I do recall that it compressed well and was very large uncompressed. Don't recall how many rows it contained. I wonder what an almost complete world street map like google maps comes in at? Hmm Interestingly OSM have just switched from MySQL to PostgreSQL. I think this is a big pat on the back for PostgreSQL and a sign that PostgreSQL is now gaining the level of users that it always should have had The 6.4Gb is BZipped XML, its over 150G of XML and is not actually the total size of the OSM database, as that has extra historical and who done it data as well, plus index etc. I would want to have at least 1/2TB minimum to put it on a machine probably more. Peter. Peter. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
On Tue, Apr 21, 2009 at 1:15 PM, Peter Childs peterachi...@gmail.com wrote: Hmm Interestingly OSM have just switched from MySQL to PostgreSQL. On the news blog page it mentioned switching to MonetDB. I saw nothing about pgsql there. Do they store it in pgsql for manipulation then export to MonetDB? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
* Scott Marlowe (scott.marl...@gmail.com) wrote: On Tue, Apr 21, 2009 at 1:15 PM, Peter Childs peterachi...@gmail.com wrote: Hmm Interestingly OSM have just switched from MySQL to PostgreSQL. On the news blog page it mentioned switching to MonetDB. I saw nothing about pgsql there. Do they store it in pgsql for manipulation then export to MonetDB? I thought they had always used PG for some piece of what they're doing, and just used MySQL for some other piece of it. I'm not sure which is which though. Thanks, Stephen signature.asc Description: Digital signature
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
* Scott Marlowe (scott.marl...@gmail.com) wrote: On Tue, Apr 21, 2009 at 1:15 PM, Peter Childs peterachi...@gmail.com wrote: Hmm Interestingly OSM have just switched from MySQL to PostgreSQL. On the news blog page it mentioned switching to MonetDB. I saw nothing about pgsql there. Do they store it in pgsql for manipulation then export to MonetDB? Err, why do I get the feeling that the date on the post wrt MonetDB and cherokee might play some role? Based on the wiki, they're using PG 8.3 now (as of April 2009, which does seem rather recent) and it replaced MySQL. http://wiki.openstreetmap.org/wiki/Servers/smaug http://wiki.openstreetmap.org/wiki/Servers/db Thanks, Stephen signature.asc Description: Digital signature
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
Scott Marlowe escribió: On Tue, Apr 21, 2009 at 1:15 PM, Peter Childs peterachi...@gmail.com wrote: Hmm Interestingly OSM have just switched from MySQL to PostgreSQL. On the news blog page it mentioned switching to MonetDB. I saw nothing about pgsql there. Do they store it in pgsql for manipulation then export to MonetDB? That's the April 1st news though ... the real news is here http://wiki.openstreetmap.org/wiki/OSM_Protocol_Version_0.6#Database_improvements -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
On Tue, Apr 21, 2009 at 08:15:00PM +0100, Peter Childs wrote: Hmm Interestingly OSM have just switched from MySQL to PostgreSQL. Can we get somebody from OSM to talk about this on the record? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [pgsql-advocacy] [GENERAL] PostgreSQL versus MySQL for GPS Data
On Tue, 21 Apr 2009, David Fetter wrote: On Tue, Apr 21, 2009 at 08:15:00PM +0100, Peter Childs wrote: Hmm Interestingly OSM have just switched from MySQL to PostgreSQL. Can we get somebody from OSM to talk about this on the record? I've forwarded this request the to the OSM talk list. Hopefully someone who can talk 'on the record' will step forward. The master OSM database used for editing used to by MySQL but most of the map rendering was done from Postgis hosted data. Over the weekend they switched ,as part of an API upgrade, the main editing database to Postgresql (but still not using complex geometry types). I think the reasoning had to do with them wanting transactions and the switch to InnoDB brought has some downsides, but I don't know which of the innodb downsides motivated the switch. I think the reference to MonetDB was part of an April fools joke. Steve Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- Sent via pgsql-advocacy mailing list (pgsql-advoc...@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-advocacy -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [pgsql-advocacy] [GENERAL] PostgreSQL versus MySQL for GPS Data
On Tue, Apr 21, 2009 at 9:19 PM, Steve Singer ssinger...@sympatico.ca wrote: On Tue, 21 Apr 2009, David Fetter wrote: On Tue, Apr 21, 2009 at 08:15:00PM +0100, Peter Childs wrote: Hmm Interestingly OSM have just switched from MySQL to PostgreSQL. Can we get somebody from OSM to talk about this on the record? I've forwarded this request the to the OSM talk list. Hopefully someone who can talk 'on the record' will step forward. The master OSM database used for editing used to by MySQL but most of the map rendering was done from Postgis hosted data. Over the weekend they switched ,as part of an API upgrade, the main editing database to Postgresql (but still not using complex geometry types). I think the reasoning had to do with them wanting transactions and the switch to InnoDB brought has some downsides, but I don't know which of the innodb downsides motivated the switch. I believe it was the loss of full text indexing with innodb that drove the switch. That's what the wiki entry on postgres says I think the reference to MonetDB was part of an April fools joke. Sounds like it. Still kinda freaked me out at first. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
On March 20, I asked for help in the Newbie MySQL forum, got no answers. Then the forum administrator moved the post to the PostgreSQL MySQL forum -a forum that deals with PostgreSQL migration issues-, and again no answers. http://forums.mysql.com/read.php?83,253709,253709#msg-253709 Regards Juan Karlos 2009/3/20 Pavel Stehule pavel.steh...@gmail.com Hello it isn't correct comparation. MySQL people use mainly web forum regards Pavel Stehule 2009/3/20 Juan Pereira juankarlos.open...@gmail.com: John Cheng wrote: This is question for Juan, have you asked the MySQL mailing list? I'm afraid MySQL general list isn't as dynamic as PostgreSQL general list. http://lists.mysql.com/mysql/216795 MySQL general list: 4 answers in about 48 hours PostgreSQL general list: 27 answers in about 72 hours Thanks again to everybody for the amount of knowledge you have shared in this thread. Juan Karlos 2009/3/17 John Cheng chonger.ch...@gmail.com This is question for Juan, have you asked the MySQL mailing list? What do they say about this? On Tue, Mar 17, 2009 at 9:05 AM, Erik Jones ejo...@engineyard.com wrote: On Mar 17, 2009, at 4:47 AM, Craig Ringer wrote: The question is: Which DBMS do you think is the best for this kind of application? PostgreSQL or MySQL? As you can imagine, PostgreSQL. My main reasons are that in a proper transactional environment (ie you're not using scary MyISAM tables) Pg is *much* better about handling concurrent load, particularly concurrent activity by readers and writers. Actually, following this comment it should be noted that if you were to choose MySQL you'd pretty much be making a decision to *not* be using transactions at all. The reason for this is that while InnoDB does support MySQL's geometry data types it does *not* support indexes on geometry columns, only MyISAM does which does not support transactions. Call me old fashioned if you like, but I like my data to have integrity ;) Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- - John L Cheng
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
On Monday 23. March 2009, Juan Pereira wrote: On March 20, I asked for help in the Newbie MySQL forum, got no answers. Then the forum administrator moved the post to the PostgreSQL MySQL forum -a forum that deals with PostgreSQL migration issues-, and again no answers. This kind of supports my suspicion that people who use MySQL either haven't heard of PostgreSQL or are too dumb to understand the difference. /troll -- Leif Biberg Kristensen | Registered Linux User #338009 Me And My Database: http://solumslekt.org/blog/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
Just to add to this list, I have been using Postgresql to store data for multiple GPS applications handling more than 150-200 vehicles. Some of the tables that I have are running into 20 - 25 million rows at the max, and on average 10 million rows. I am yet to see a problem from the database side, although must admit that I receive data every 10 seconds from the devices. I am sure that optimizing the postgresql.conf files, and using postgis would be of great help down the road. With regards Amitabh -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
John Cheng wrote: This is question for Juan, have you asked the MySQL mailing list? I'm afraid MySQL general list isn't as dynamic as PostgreSQL general list. http://lists.mysql.com/mysql/216795 MySQL general list: 4 answers in about 48 hours PostgreSQL general list: 27 answers in about 72 hours Thanks again to everybody for the amount of knowledge you have shared in this thread. Juan Karlos 2009/3/17 John Cheng chonger.ch...@gmail.com This is question for Juan, have you asked the MySQL mailing list? What do they say about this? On Tue, Mar 17, 2009 at 9:05 AM, Erik Jones ejo...@engineyard.com wrote: On Mar 17, 2009, at 4:47 AM, Craig Ringer wrote: The question is: Which DBMS do you think is the best for this kind of application? PostgreSQL or MySQL? As you can imagine, PostgreSQL. My main reasons are that in a proper transactional environment (ie you're not using scary MyISAM tables) Pg is *much* better about handling concurrent load, particularly concurrent activity by readers and writers. Actually, following this comment it should be noted that if you were to choose MySQL you'd pretty much be making a decision to *not* be using transactions at all. The reason for this is that while InnoDB does support MySQL's geometry data types it does *not* support indexes on geometry columns, only MyISAM does which does not support transactions. Call me old fashioned if you like, but I like my data to have integrity ;) Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- - John L Cheng
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
Hello it isn't correct comparation. MySQL people use mainly web forum regards Pavel Stehule 2009/3/20 Juan Pereira juankarlos.open...@gmail.com: John Cheng wrote: This is question for Juan, have you asked the MySQL mailing list? I'm afraid MySQL general list isn't as dynamic as PostgreSQL general list. http://lists.mysql.com/mysql/216795 MySQL general list: 4 answers in about 48 hours PostgreSQL general list: 27 answers in about 72 hours Thanks again to everybody for the amount of knowledge you have shared in this thread. Juan Karlos 2009/3/17 John Cheng chonger.ch...@gmail.com This is question for Juan, have you asked the MySQL mailing list? What do they say about this? On Tue, Mar 17, 2009 at 9:05 AM, Erik Jones ejo...@engineyard.com wrote: On Mar 17, 2009, at 4:47 AM, Craig Ringer wrote: The question is: Which DBMS do you think is the best for this kind of application? PostgreSQL or MySQL? As you can imagine, PostgreSQL. My main reasons are that in a proper transactional environment (ie you're not using scary MyISAM tables) Pg is *much* better about handling concurrent load, particularly concurrent activity by readers and writers. Actually, following this comment it should be noted that if you were to choose MySQL you'd pretty much be making a decision to *not* be using transactions at all. The reason for this is that while InnoDB does support MySQL's geometry data types it does *not* support indexes on geometry columns, only MyISAM does which does not support transactions. Call me old fashioned if you like, but I like my data to have integrity ;) Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- - John L Cheng -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
You would get better results if you posted in mysql forums. http://forums.mysql.com/ Amitabh -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
Thomas Kellerer wrote: Harald Armin Massa, 17.03.2009 15:00: That is: what table size would you or anybody consider really, really large actually? I recently attended and Oracle training by Tom Kyte and he said (partially joking though) that a database is only large when the size is measured in terrabytes :) So really, really large would mean something like 100 petabytes My personal opinion is that a large database has more than ~10 million rows in more than ~10 tables. Thomas I would say that as far as GPS data goes the street maps of the world would be pretty big. openstreetmap.org is still a work in progress but their current db dumps gzip down to 6.4GB. It was a while back that I noseyed around with it but I do recall that it compressed well and was very large uncompressed. Don't recall how many rows it contained. I wonder what an almost complete world street map like google maps comes in at? -- Shane Ambler pgSQL (at) Sheeky (dot) Biz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
On Tue, Mar 17, 2009 at 5:25 AM, Juan Pereira juankarlos.open...@gmail.com wrote: Hello, The question is: Which DBMS do you think is the best for this kind of application? PostgreSQL or MySQL? Another advantage pgsql has is that many ddl operations on tables do NOT require exclusive locks on those tables. Creating indexes, adding / dropping columns in mysql will lock the whole table and adding dropping columns will rewrite the whole table. In pgsql adding and dropping columns is almost immediate, and you can create indexes concurrently so that the table you're creating the index on is not locked. This is a big deal on a large production system where index creation could take anywhere from several minutes to several hours. Note that almost all ddl is transactable as well, so testing big schema changes is much safer in pgsql, where you can rollback just about anything except create / drop database / tablespace. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
On Thu, Mar 19, 2009 at 11:50 AM, Scott Marlowe scott.marl...@gmail.com wrote: On Tue, Mar 17, 2009 at 5:25 AM, Juan Pereira juankarlos.open...@gmail.com wrote: Hello, The question is: Which DBMS do you think is the best for this kind of application? PostgreSQL or MySQL? Another advantage pgsql has is that many ddl operations on tables do NOT require exclusive locks on those tables. Creating indexes, adding / dropping columns in mysql will lock the whole table and adding dropping columns will rewrite the whole table. In pgsql adding and dropping columns is almost immediate, and you can create indexes concurrently so that the table you're creating the index on is not locked. This is a big deal on a large production system where index creation could take anywhere from several minutes to several hours. Note that almost all ddl is transactable as well, so testing big schema changes is much safer in pgsql, where you can rollback just about anything except create / drop database / tablespace. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general This is the nicest feature about postgresql by far. It almost compensates the lack of in place upgrade. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
At 10:00 PM 3/17/2009, Harald Armin Massa wrote: Merlin, I agree though that a single table approach is best unless 1) the table has to scale to really, really large sizes or 2) there is a lot of churn on the data (lots of bulk inserts and deletes). while agreeing, an additional question: could you please pronounce really, really large in other units, like Gigabytes or Number of rows (with average rowlength in bytes, of course) That is: what table size would you or anybody consider really, really large actually? Tiny: fits in CPU cache Small: fits in RAM Big: multiples of RAM. Large: (size / storage bandwidth ) is measured in minutes. Huge: (size / storage bandwidth ) is measured in hours. Humungous: (size / storage bandwidth ) in days or larger units. That said, the active working set might be a lot smaller than the table, in which case you might prefer to use the size of the working set (except when you are doing stuff like full backups or restores). Link. - Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
At 12:05 AM 3/18/2009, Erik Jones wrote: On Mar 17, 2009, at 4:47 AM, Craig Ringer wrote: The question is: Which DBMS do you think is the best for this kind of application? PostgreSQL or MySQL? As you can imagine, PostgreSQL. My main reasons are that in a proper transactional environment (ie you're not using scary MyISAM tables) Pg is *much* better about handling concurrent load, particularly concurrent activity by readers and writers. Actually, following this comment it should be noted that if you were to choose MySQL you'd pretty much be making a decision to *not* be using transactions at all. The reason for this is that while InnoDB does support MySQL's geometry data types it does *not* support indexes on geometry columns, only MyISAM does which does not support transactions. Call me old fashioned if you like, but I like my data to have integrity ;) Interesting, didn't know that. But that's what I don't like about MySQL. On the brochure they've got all the ticks on the feature checkboxes. So the Bosses and CxOs think it's great. But then you find out (often the hard way) that many star features are mutually incompatible. Link. - Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
John Cheng wrote: This is question for Juan, have you asked the MySQL mailing list? Not yet. Admitting my ignorance in databases, I'm trying to understand all the concepts discussed in this thread . Be sure today I will ask the MySQL list. Thanks 2009/3/17 John Cheng chonger.ch...@gmail.com This is question for Juan, have you asked the MySQL mailing list? What do they say about this? On Tue, Mar 17, 2009 at 9:05 AM, Erik Jones ejo...@engineyard.com wrote: On Mar 17, 2009, at 4:47 AM, Craig Ringer wrote: The question is: Which DBMS do you think is the best for this kind of application? PostgreSQL or MySQL? As you can imagine, PostgreSQL. My main reasons are that in a proper transactional environment (ie you're not using scary MyISAM tables) Pg is *much* better about handling concurrent load, particularly concurrent activity by readers and writers. Actually, following this comment it should be noted that if you were to choose MySQL you'd pretty much be making a decision to *not* be using transactions at all. The reason for this is that while InnoDB does support MySQL's geometry data types it does *not* support indexes on geometry columns, only MyISAM does which does not support transactions. Call me old fashioned if you like, but I like my data to have integrity ;) Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- - John L Cheng
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
Merlin Moncure mmonc...@gmail.com writes: A good rule of thumb for large is table size working ram. Huge (really large) is 10x ram. Or better yet, large is data working ram. Very large is data directly attached drives... That means that without fairly expensive hardware you start talking about very large at about 4-10 TB. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! - Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
juankarlos.open...@gmail.com (Juan Pereira) writes: Quite interesting! The main reason why we thought using a table per truck was because concurrent load: if there are 100 trucks trying to write in the same table, maybe the performance is worse than having 100 tables, due to the fact that the table is blocked for other queries while the writing process is running, isn't it? You're assuming something that is distinctly Not True of PostgreSQL. You do NOT require an exclusive lock on a table in order to write to it. For writes to tables to acquire an exclusive lock on the table happens to be a specific feature of the MySQL(tm) storage engine called MyISAM. -- let name=cbbrowne and tld=linuxdatabases.info in name ^ @ ^ tld;; http://linuxfinances.info/info/spreadsheets.html Change is inevitable, except from a vending machine. - Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] PostgreSQL versus MySQL for GPS Data
Hello, I'm currently developing a program for centralizing the vehicle fleet GPS information -http://openggd.sourceforge.net-, written in C++. The database should have these requirements: - The schema for this kind of data consists of several arguments -latitude, longitude, time, speed. etc-, none of them is a text field. - The database also should create a table for every truck -around 100 trucks-. - There won't be more than 86400 * 365 rows per table -one GPS position every second along one year-. - There won't be more than 10 simultaneously read-only queries. The question is: Which DBMS do you think is the best for this kind of application? PostgreSQL or MySQL? Thanks in advance Juan Karlos.
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
On Tue, Mar 17, 2009 at 12:25:08PM +0100, Juan Pereira wrote: I'm currently developing a program for centralizing the vehicle fleet GPS information -http://openggd.sourceforge.net-, written in C++. The database should have these requirements: ... - The database also should create a table for every truck -around 100 trucks-. Why ? This smells like a design problem. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
Hi Juan, First of all congratulations on you project :) We, at MADEIRA GPS, use Postgresql and PostGIS as the corner stone of our fleet management solution and have tens of *millions* of records in a single vehicles history table without any visible performance problem (we do however clean it every year). A thought, however, regarding your plans for gps data acquisition/storage: every second... isn't that a bit too much? We, for most of our customers, offer minute-by-minute tracking and, this is important, *optimize* the vehicles' history table when writing data into it by means of comparing the data from the last record - i.e. if the info is the same *don't* write it! This will surely save you space ;-) About simultaneous queries: Last we checked we had ~200 of them with PGSQL still pumping at full speed... ;-) As a final note, IMHO, PGSQL/PostGIS is better than MySQL for a number of reasons: - proven robustness - tight integration with PostGIS - large user base (an always friendly bunch willing to help out each other ;-) ) - ... Regards, Pedro Doria Meunier GSM: +351961720188 Skype: pdoriam On Tuesday 17 March 2009 11:25:08 am Juan Pereira wrote: Hello, I'm currently developing a program for centralizing the vehicle fleet GPS information -http://openggd.sourceforge.net-, written in C++. The database should have these requirements: - The schema for this kind of data consists of several arguments -latitude, longitude, time, speed. etc-, none of them is a text field. - The database also should create a table for every truck -around 100 trucks-. - There won't be more than 86400 * 365 rows per table -one GPS position every second along one year-. - There won't be more than 10 simultaneously read-only queries. The question is: Which DBMS do you think is the best for this kind of application? PostgreSQL or MySQL? Thanks in advance Juan Karlos. signature.asc Description: This is a digitally signed message part.
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
Juan Pereira wrote: - The database also should create a table for every truck -around 100 trucks-. Why? That's a rather clumsy design that makes it really hard to get aggregate data across the fleet or do many interesting queries. You're almost always better off using a single table with a composite primary key like (truckid, datapointid) or whatever. If you'll be doing lots of queries that focus on individual vehicles and expect performance issues then you could partition the table by truckid, so you actually do land up with one table per truck, but transparently accessible via table inheritance so you can still query them all together. Read up on PostgreSQL's table partitioning features. The question is: Which DBMS do you think is the best for this kind of application? PostgreSQL or MySQL? As you can imagine, PostgreSQL. My main reasons are that in a proper transactional environment (ie you're not using scary MyISAM tables) Pg is *much* better about handling concurrent load, particularly concurrent activity by readers and writers. Pg's table partitioning support is also an ideal fit for your application. -- Craig Ringe -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
On Tue, Mar 17, 2009 at 7:47 AM, Craig Ringer cr...@postnewspapers.com.au wrote: Juan Pereira wrote: - The database also should create a table for every truck -around 100 trucks-. Why? That's a rather clumsy design that makes it really hard to get aggregate data across the fleet or do many interesting queries. You're almost always better off using a single table with a composite primary key like (truckid, datapointid) or whatever. If you'll be doing lots of queries that focus on individual vehicles and expect performance issues then you could partition the table by truckid, so you actually do land up with one table per truck, but transparently accessible via table inheritance so you can still query them all together. Read up on PostgreSQL's table partitioning features. If there is little/no reason to span queries over various trucks, then the OP's approach is ok, better than standard TP even. I agree though that a single table approach is best unless 1) the table has to scale to really, really large sizes or 2) there is a lot of churn on the data (lots of bulk inserts and deletes). merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
Juan, * Juan Pereira (juankarlos.open...@gmail.com) wrote: - The schema for this kind of data consists of several arguments -latitude, longitude, time, speed. etc-, none of them is a text field. I would think you might want *some* text fields, for vehicle identification, as a seperate table about trucks. - The database also should create a table for every truck -around 100 trucks-. As mentioned elsewhere, you're probably fine with 1 table, but if it becomes a problem you can always partition it up and have one view across all of them (make sure to set up your constraints correctly and enable constraint_exclusion if you go with this route). You could then have, say, 10 tables, with 10 trucks in each. - There won't be more than 86400 * 365 rows per table -one GPS position every second along one year-. As mentioned, you might want to eliminate duplicate entries; no sense storing information that can be trivially derived. - There won't be more than 10 simultaneously read-only queries. While this is good to know, I kind of doubt it's accurate, and more important is the number of simultaneous writers. I'm assuming 100, but is that correct? The question is: Which DBMS do you think is the best for this kind of application? PostgreSQL or MySQL? Given the list you posted to, I would say you're likely to get alot of PostgreSQL recommendations. Assuming you posted something similar to a MySQL list, I would recommend that you not pick a solution based on the number of responses you get but rather what you're most comfortable with and understand best. If there is a learning curve either way, I think PostgreSQL would be the best solution. If you're thinking about what to have your application support, you might consider trying to support both. Doing that from the beginning is usually best since you'll develop your system at a high enough level to mitigate the problems (syntax differences, performance differences, etc) between the databases. As an aside, and I don't know where the MySQL community is on this, but we have the US Census TIGER Shapefile data set loaded into PostgreSQL with PostGIS, with a geocoder that works with it. We should have a complete packaged solution for loading it, indexing, etc, soon. That's a fairly large, free, data set of all streets, addresses, etc, in the US with lat/long information. Thanks, Stephen signature.asc Description: Digital signature
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
Craig Ringer wrote: You're almost always better off using a single table with a composite primary key like (truckid, datapointid) or whatever. If you'll be doing lots of queries that focus on individual vehicles and expect performance issues then you could partition the table by truckid, so you actually do land up with one table per truck, but transparently accessible via table inheritance so you can still query them all together. Quite interesting! The main reason why we thought using a table per truck was because concurrent load: if there are 100 trucks trying to write in the same table, maybe the performance is worse than having 100 tables, due to the fact that the table is blocked for other queries while the writing process is running, isn't it? My main reasons are that in a proper transactional environment (ie you're not using scary MyISAM tables) Pg is *much* better about handling concurrent load, particularly concurrent activity by readers and writers. 2009/3/17 Craig Ringer cr...@postnewspapers.com.au Quite interesting again. Thank you for your answers Juan Karlos
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
Juan Pereira wrote: Craig Ringer wrote: You're almost always better off using a single table with a composite primary key like (truckid, datapointid) or whatever. If you'll be doing lots of queries that focus on individual vehicles and expect performance issues then you could partition the table by truckid, so you actually do land up with one table per truck, but transparently accessible via table inheritance so you can still query them all together. Quite interesting! The main reason why we thought using a table per truck was because concurrent load: if there are 100 trucks trying to write in the same table, maybe the performance is worse than having 100 tables, due to the fact that the table is blocked for other queries while the writing process is running, isn't it? Wow, you are carrying around a lot of MySQL baggage with you. ;-) You should probably read this: http://www.postgresql.org/docs/8.3/static/mvcc-intro.html -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
On Tue, Mar 17, 2009 at 8:25 AM, Juan Pereira juankarlos.open...@gmail.com wrote: Craig Ringer wrote: You're almost always better off using a single table with a composite primary key like (truckid, datapointid) or whatever. If you'll be doing lots of queries that focus on individual vehicles and expect performance issues then you could partition the table by truckid, so you actually do land up with one table per truck, but transparently accessible via table inheritance so you can still query them all together. Quite interesting! The main reason why we thought using a table per truck was because concurrent load: if there are 100 trucks trying to write in the same table, maybe the performance is worse than having 100 tables, due to the fact that the table is blocked for other queries while the writing process is running, isn't it? Using MySQL has a tendency to teach people bad habits, and this assumption would be one of them. :) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
Stephen Frost wrote: As mentioned, you might want to eliminate duplicate entries; no sense storing information that can be trivially derived. It's pretty easy to do that with a trigger - and you can add a degree of noise correction too, so that wobble in GPS position doesn't get recorded - you only log changes of more than a certain distance. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
Juan, * Juan Pereira (juankarlos.open...@gmail.com) wrote: The main reason why we thought using a table per truck was because concurrent load: if there are 100 trucks trying to write in the same table, maybe the performance is worse than having 100 tables, due to the fact that the table is blocked for other queries while the writing process is running, isn't it? That assumption is incorrect with regard to PostgreSQL, as you'll find if you go through the other links suggested. Writing to a table does not require a table-level write lock in PostgreSQL. Thanks, Stephen signature.asc Description: Digital signature
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
On Mar 17, 2009, at 4:47 AM, Craig Ringer wrote: The question is: Which DBMS do you think is the best for this kind of application? PostgreSQL or MySQL? As you can imagine, PostgreSQL. My main reasons are that in a proper transactional environment (ie you're not using scary MyISAM tables) Pg is *much* better about handling concurrent load, particularly concurrent activity by readers and writers. Actually, following this comment it should be noted that if you were to choose MySQL you'd pretty much be making a decision to *not* be using transactions at all. The reason for this is that while InnoDB does support MySQL's geometry data types it does *not* support indexes on geometry columns, only MyISAM does which does not support transactions. Call me old fashioned if you like, but I like my data to have integrity ;) Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
Merlin, I agree though that a single table approach is best unless 1) the table has to scale to really, really large sizes or 2) there is a lot of churn on the data (lots of bulk inserts and deletes). while agreeing, an additional question: could you please pronounce really, really large in other units, like Gigabytes or Number of rows (with average rowlength in bytes, of course) That is: what table size would you or anybody consider really, really large actually? Harakd -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - LASIK good, steroids bad? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
Harald Armin Massa, 17.03.2009 15:00: That is: what table size would you or anybody consider really, really large actually? I recently attended and Oracle training by Tom Kyte and he said (partially joking though) that a database is only large when the size is measured in terrabytes :) So really, really large would mean something like 100 petabytes My personal opinion is that a large database has more than ~10 million rows in more than ~10 tables. Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
On Tue, 2009-03-17 at 17:44 +0100, Thomas Kellerer wrote: Harald Armin Massa, 17.03.2009 15:00: That is: what table size would you or anybody consider really, really large actually? I recently attended and Oracle training by Tom Kyte and he said (partially joking though) that a database is only large when the size is measured in terrabytes :) So really, really large would mean something like 100 petabytes My personal opinion is that a large database has more than ~10 million rows in more than ~10 tables. It entirely depends on workload and hardware. Joshua D. Drake Thomas -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
On Tue, Mar 17, 2009 at 10:00 AM, Harald Armin Massa c...@ghum.de wrote: Merlin, I agree though that a single table approach is best unless 1) the table has to scale to really, really large sizes or 2) there is a lot of churn on the data (lots of bulk inserts and deletes). while agreeing, an additional question: could you please pronounce really, really large in other units, like Gigabytes or Number of rows (with average rowlength in bytes, of course) That is: what table size would you or anybody consider really, really large actually? A good rule of thumb for large is table size working ram. Huge (really large) is 10x ram. merlin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
On Tue, Mar 17, 2009 at 05:44:48PM +0100, Thomas Kellerer wrote: So really, really large would mean something like 100 petabytes My personal opinion is that a large database has more than ~10 million rows in more than ~10 tables. Surely anything like large or small is a relative measure that depends on personal experience. Because this mailing list is such a diverse group I'm not sure if they'd ever be particularly useful descriptions. If you're talking with a more cohesive group or you've already defined what you're talking about then maybe--i.e. this database is larger than that one, and so on. I'd suggest we try and not describe things as small or large and just use simple and unambiguous numeric descriptions; i.e. I'm expecting to have a couple of tables with 10 to 100 million rows and the remaining 10 to 20 supporting tables having a few hundred rows. I wouldn't expect row counts to be more accurate than a decimal log and table counts to be more accurate than a ratio of two. That's my two cents anyway! -- Sam http://samason.me.uk/ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] PostgreSQL versus MySQL for GPS Data
This is question for Juan, have you asked the MySQL mailing list? What do they say about this? On Tue, Mar 17, 2009 at 9:05 AM, Erik Jones ejo...@engineyard.com wrote: On Mar 17, 2009, at 4:47 AM, Craig Ringer wrote: The question is: Which DBMS do you think is the best for this kind of application? PostgreSQL or MySQL? As you can imagine, PostgreSQL. My main reasons are that in a proper transactional environment (ie you're not using scary MyISAM tables) Pg is *much* better about handling concurrent load, particularly concurrent activity by readers and writers. Actually, following this comment it should be noted that if you were to choose MySQL you'd pretty much be making a decision to *not* be using transactions at all. The reason for this is that while InnoDB does support MySQL's geometry data types it does *not* support indexes on geometry columns, only MyISAM does which does not support transactions. Call me old fashioned if you like, but I like my data to have integrity ;) Erik Jones, Database Administrator Engine Yard Support, Scalability, Reliability 866.518.9273 x 260 Location: US/Pacific IRC: mage2k -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general -- - John L Cheng
