Re: [Discuss] Rob Conery's critique of MySQL?
On 07/31/2012 01:34 PM, Rich Braun wrote: Mark Woodward ma...@mohawksoft.com wrote: Well, with MySQL, create index and drop index LOCK the tables as they are operating. LOCK THE TABLES. Think about that. In PostgreSQL, Oracle, and any real database, create index and drop index only impact performance in as much as any other transaction. True of older versions, less true of 5.5. NDBCLUSTER storage engine works around this by propagating the update to cluster members one at a time, taking each offline. InnoDB does a table copy. Provided you have a cluster of course, but it still raises the issue of effectively removing a machine from the cluster. You're right that MySQL's rivals had a better design for this operation; future versions of MySQL could replace this logic. But as far as it being a showstopper for production, that depends. It hardly depends. It is indicative of a bad design. For any active site, this kind of behavior problematic, you have to be able to admit that. Here is where I have a problem with these types technical debates. This is not a subjective point. With another real database this is not a problem. Side by side, PostgreSQL and MySQL are both free and comparable in many ways. This is absolutely one clear reason to choose PostgreSQL over MySQL. Is it a show stopper for you, perhaps not, but I tell you this one little gem has caused me a great deal of actual grief. If there are design deficiencies in a tool and a better designed tool, at the same price, is available why would you not choose the better designed tool? It make no sense. I am not pro PostgreSQL so much as anti bad databases. It is only the fact that PostgreSQL embodies so much of what a good database does that it is a defacto example. I would use Oracle in this discussion, maybe even DB2, but those are not open source and very expensive, the obvious counter argument would be that MySQL is free. In my case, I'll be having to deal with large table sizes, but there will rarely be changes to indices and the nature of the business permits taking the DB offline for maintenance (unlike a public site). So this is only one of many criteria for choosing a system. (Note also that even with a public site like at my last employer, we had some solid workarounds using read-only slaves which enabled us to update indices easily enough without major production impact.) OK, if you are going to have large tables, forget MySQL. A number of years ago I contracted at Yahoo. I wrote a data collection system that would query snmp info from every system they had, as well as dmesg and other performance and configuration information. The idea was that they could analyze infrastructure to calculate the proper compute requirements for each business unit. The premise was that eight 5 year old computers could be replaced with two or three new ones and save space and power usage, or that a business unit with 6 servers, even at peak, only needed 4, and so on. On the first run, we isolated enough dead-wood that the estimated savings would over a million dollars in power. At the start of the project, I said MySQL would not work and that I would need either Oracle or PostgreSQL. I offered that I could go with PostgreSQL while they were working on the Oracle license. As I was transferring the project to their IT, a new IT director came on. Dictated MySQL, his words were We have invested a lot of money into MySQL and we have MySQL experts here, it can work on MySQL. Sigh, people who don't understand databases should not choose databases. This edict had nothing to do with any understanding of what we were doing. Well, working with a MySQL contributor and a well regarded yahoo internal expert. A query that took a painful minute and a half on PostgreSQL could not be made any faster than 20 minutes on MySQL. To compound the problem, the indexing issue made life painful. To try a new index, we would have to turn off the system and let it re-index before we could do ANYTHING with it. It was a terrible experience. This kind of technical comparison is exactly what I'm looking for. If I had a list of the top-10 things that PostgreSQL does better than MySQL then I'd probably have a case. One or two won't be enough. Well, I strongly suggest watching the video. The stuff about MySQL is at the beginning so you don't have to watch it all. Also, seriously, start with PostgreSQL and post any questions on BLU. I bet you could get some great advice. I can only say that if you are used to MySQL, you will find yourself initially saying PostgreSQL does that?!?! Really?! The light will shine and you'll never go back. -rich ___ Discuss mailing list Discuss@blu.org http://lists.blu.org/mailman/listinfo/discuss ___ Discuss mailing list Discuss@blu.org http://lists.blu.org/mailman/listinfo/discuss
Re: [Discuss] Rob Conery's critique of MySQL?
Mark, In my opinion, the problem with MySQL is not that it locks tables. It's that it has tables. There is no such thing as a good RDBMS. They're all bad. They all suck. They're all designed around a data storage and retrieval philosophy that was obsolete 30 years ago. Tables are slow and they don't scale. There are faster, more robust, more flexible and more scalable ways of storing and retrieving data than hyper-thyroidal spreadsheets. Which makes me wonder why you're such a strong advocate of PostgreSQL over MySQL. You wrote, [i]f there are design deficiencies in a tool and a better designed tool, at the same price, is available why would you not choose the better designed tool? Why, in light of this, do you bother with relational databases? PostgreSQL may suck less than MySQL but it still sucks. Why are you not advocating a tool that doesn't suck? -- Rich P. ___ Discuss mailing list Discuss@blu.org http://lists.blu.org/mailman/listinfo/discuss
Re: [Discuss] Rob Conery's critique of MySQL?
On 8/1/2012 8:45 PM, Daniel C. wrote: Like what? Objects. -- Rich P. ___ Discuss mailing list Discuss@blu.org http://lists.blu.org/mailman/listinfo/discuss
Re: [Discuss] Rob Conery's critique of MySQL?
On 08/01/2012 08:36 PM, Richard Pieri wrote: Mark, In my opinion, the problem with MySQL is not that it locks tables. It's that it has tables. Oh no! Don't buy in to the No-SQL nonsense. A table is nothing more than a naming convention of the technique of storing related data in the same logical unit of storage. There is no such thing as a good RDBMS. They're all bad. They all suck. I disagree and I would LOVE to have a civil debate about this subject on this group. They're all designed around a data storage and retrieval philosophy that was obsolete 30 years ago. This is basically FUD, of course. You say it is obsolete, but you give no examples as to why. Electricity is far older and it is not obsolete. Tables are slow and they don't scale. Like I said, table is a naming convention. It is useful for expressing data relationships in a canonically understandable language, but a table doesn't really describe anything technically. Why don't they scale? A name/value pair is nothing more than a two entry row in a column in a table. Perhaps even the key is a virtual value within the index, but, none the less, one can express it as a table. There are faster, more robust, more flexible and more scalable ways of storing and retrieving data than hyper-thyroidal spreadsheets. Why? Why do you think that this is true? SQL is nothing more than a language and an algebra around data storage. The underlying storage is what scales. SQL is just a language for accessing it. The big No-SQL storage systems are all getting SQL front-ends because ad-hoc APIs are bad. You say there are more robust, flexible, and scalable ways of storing data. Like what? How is it *not* a table? Which makes me wonder why you're such a strong advocate of PostgreSQL over MySQL. You wrote, [i]f there are design deficiencies in a tool and a better designed tool, at the same price, is available why would you not choose the better designed tool? Why, in light of this, do you bother with relational databases? PostgreSQL may suck less than MySQL but it still sucks. Why are you not advocating a tool that doesn't suck? Well, like I said, lets have that debate. SQL is a language NOT a database. MongoDB is webscale! http://www.youtube.com/watch?v=b2F-DItXtZs While I don't agree with MySQL POV, but the video is funny and sounds like your arguments. ___ Discuss mailing list Discuss@blu.org http://lists.blu.org/mailman/listinfo/discuss
Re: [Discuss] Rob Conery's critique of MySQL?
On 8/1/2012 9:12 PM, Mark Woodward wrote: Oh no! Don't buy in to the No-SQL nonsense. I'm not. NoSQL is a buzzword for a class of high-performance non-relational database designs. It's not the only non-relational philosophy out there and it's certainly not one that I consider best but I'm biased against data loss. Why don't they scale? The time taken to perform a query against a table increases roughly linearly with the number of rows in the query and the complexity of the query. This degradation continues until the limits of the hardware are reached. At that point the whole thing slows to an unusable crawl and maybe the system crashes. Even Oracle's Exadata frames will crash and burn under sufficient load. You say there are more robust, flexible, and scalable ways of storing data. Like what? How is it *not* a table? There are plenty of examples of object-oriented and distributed databases that I could use as examples. The most common of the former are things like medical records and financial histories. Robust: In addition to ACID, object databases can provide versioning of objects within data stores. Flexible: Object databases provide for arbitrarily complex data structures. Objects can be nested and they can be arranged hierarchically. Classes provide a variety of object types within a database. Objects can even be ordered as rows and columns if you so desire. Scalable: Objects can be addressed directly. Searches and joins aren't needed. This means that object retrieval never takes more than a given amount of time regardless of the data store size. Caveat: there are exceptions to this but they are rare. DNS is the most widely-used of the latter. -- Rich P. ___ Discuss mailing list Discuss@blu.org http://lists.blu.org/mailman/listinfo/discuss
