8 ms·

At work we have an MS SQL Server instance managing ~1TB of data spread among various databases. No one database is > 200GB so it's not "web scale" by any means

At work we have an MS SQL Server instance managing ~1TB of data spread among various databases. No one database is > 200GB so it's not "web scale" by any means but sometimes you need to pull out tricks like indexed/materialised views. I use Transact-SQL directly via ADO in C++ and ADO .NET in C# and indirectly via EF and NHibernate. It's easy to manage schema with SQL Server Data Tools and diagnosing a poorly performing query is straightforward with graphical execution plans. Quite frankly, I don't care that there are alternative RDBMS' or alternatives to traditional RDBMS. But ... I keep hearing about problem after problem with MySQL. What's the trick to using it successfully? Is the barrier to entry too low, and problems are from legions of rank amateurs? i.e. is it as simple as constructing your schema thoughtfully, with tables that are "well-it's-almost-3NF" and making some educated guesses about which indexes might be needed in advance?

eksith 13y ago
Answer to your last 3 questions : - Replace MySQL with MariaDB (hopefully your tables are InnoDB and the transition will be "relatively" seamless). You can continue using the rest of your app as-is. - Yes. There are many by the simple fact that a lot of people are unaware of what they're really doing and what impact certain indexes, queries and even to some extent, compile-time options will introduce. - Schema is everything for an RDBMS. If people just take the time to calmly and carefully consider what it is they're trying to do and what options they'll truly need vs. just guessing what future "growth" would be like, they will have fewer actual (rather than imagined) growing pains later. Of course, most of these problems could be mitigated if new projects started off with Postgres or MariaDB, if they're taking the RDBMS route.
- Keyframe 13y ago
  I keep hearing people recommend MariaDB, which is fine. I'd add in Percona as well. I have/had a decent run with it, but MySQL limitations with querying make me look towards Postgres. SQL engines are hard to do right, no wonder most of MPPs are built on top of Postgres.
raverbashing 13y ago
MySQL is a good database for basing a CMS on. (for a loose definition of database) PostgreSQL is a good SQL database. And don't take me wrong, I'll take MySQL instead of PostgreSQL for simple and medium projects. "Is the barrier to entry too low, and problems are from legions of rank amateurs?" Maybe. Well, "low barrier to entry" is not a defect.
- cperciva 13y ago
  MySQL is a good database for basing a CMS on. (for a loose definition of database) It seems to me that the success of MySQL can be tied directly to people using "database" for things where what they really need is "filesystem".
  - fatalmind 13y ago
    Seems like. That's also what this comment on Reddit says: http://www.reddit.com/r/programming/comments/1niheb/mongodb_is_to_nosql_like_mysql_to_sql_in_the_most/ccje34x http://www.reddit.com/r/programming/comments/1niheb/mongodb_...
  - bad_user 13y ago
    How easy is it to put a lock on a file while writing on it? What happens in an environment where you have multiple frontend servers that need to write to the same file? Are you going to have like one server that does the reading/writing from a file and have like a protocol with which other servers can send commands? Great, you've just invented a half-assed DBMS.
    - lmm 13y ago
      > Great, you've just invented a half-assed DBMS. My first thought is "Yeah, we already have one of those, it's called MySQL"
  - ArbitraryLimits 13y ago
    Agree, except I'd amend it as The success of MySQL can be tied directly to people using "database" for things where what they really need is "network filesystem". The success of SQLite can be tied directly to people using "database" for things where what they really need is "filesystem". Interesting that it's the same dynamic at play.
mattmanser 13y ago
What are you actually trying to say? Not everything in your post is a full truth. MS's graphical query plan tool is actually pretty awful and hasn't been updated since forever, quite often hiding the most important information, like estimated rows vs. actual rows. It takes quite a while to learn how to actually read it unless someone shows you. Like months. And it's really just a wrapper around SHOW PLAN, which MySQL has with EXPLAIN. I'm not convinced that SQL Server Data Tools easily manages schemas, I've only been using it a few months but it seems a massive hack to me which relies on constant schema compares. MS also constantly push you to use the graphical tools, but if you play in the tools and then try to make something repeatable it's a right PITA. Your only option is to turn on the incredibly annoying 'Autogenerate change scripts' whereas in MySQL you can just look at the history tab in Workbench which shows every command you've run. Imagine that. Everything as SQL scripts you can modify and re-run! Finally, SQL Server Management studio is an absolute dog performance wise, I actually still miss Query Analyser which was much better IMO. And you can't actually use SQL Server with something like Ruby or Python without quite a massive amount of effort. I know because I've tried, there's no decent, easy to install, equivalent of ADO.Net without mucking around with ODBC connections. At least a year ago there wasn't. As for the problems with MySQL, he mentions in the post the problem with MySQL is the lack of certain types of operations. Also there's a bunch of gotchas, sub-selects perform terribly in MySQL, which makes the EF unusable on it. Still, I've used both and SQL Server is much better if you're developing with C#.
- mcdougle 13y ago
  I work with SQL Server and its parts (SSIS, SSAS, etc.) pretty much exclusively at my day job, and I occasionally find myself hacking up Python scripts to automate various tasks. I don't know about a year ago, but several months ago I found pyodbc [1] and it's been wonderful. Easy to set up, works a lot like the mysql functions in PHP. [1] https://code.google.com/p/pyodbc/ https://code.google.com/p/pyodbc/
  - raverbashing 13y ago
    Oh this looks like a modern solution But really, Python + MSSQL = Bag of hurt. It's a real pity, but I see the "market" for that being very small...
corresation 13y ago
It's easy to manage schema with SQL Server Data Tools and diagnosing a poorly performing query is straightforward with graphical execution plans. I wouldn't put too much stock in the graphical execution plans. Outside of the problem that it simply fails for complex queries, it lies about costings (costings are always based upon estimates, which means that when you have super-fast storage, as many new installs do, the query analyzer becomes a giant bag of lies), it can't "see into" table-defined functions (they sit there innocuously with negligible cost when often they sit in front of a curse of poor performance). SQL Server is a great database, but it is very far from perfect. I mentioned above about the graphical execution plans lying about storage costs, and the same goes for the query planner -- it will choose horrific query plans under the assumption that you have one spinning disc of rust, falling apart when you're really on some FusionIO SLC. This can manifest in queries that run excellently one day falling completely to shit the next because it passed a threshold and has decided to switch join patterns. One would think that it would actively actually monitor storage/memory and CPU to make all of these weights, but it doesn't whatsoever. The other major issue being lock escalation that can destroy SQL Server databases under heavy contention.
ris 13y ago
"I keep hearing about problem after problem with MySQL. What's the trick to using it successfully?" Switching to PostgreSQL.
lmm 13y ago
MySQL is pretty good in practice, I think many complaints are indeed just user error. That said: - Much like MongoDB, the defaults sacrifice safety for performance, e.g. MyISAM tables (which everyone everywhere will tell you never to use, but are IIRC still the default) ignore things like foreign key constraints - Lots of "error" conditions silently complete as best as they can, rather than failing with an error. E.g. trying to store the wrong type of value, or trying to make a table using a disabled storage engine, or trying to store a string in an encoding that can't represent all its characters. A lot of this is legacy that is slowly being improved on, but reputation naturally lags slightly behind - The query planner is a bit simplistic, so if you want performance you do need to put a little more thought into tables so that you're not doing 5-way joins all the time - Some more advanced "features" of other databases like triggers just don't exist. IMO they are bad features and shouldn't be used, but their absence provides an easy thing to criticize mysql for if you're that way inclined It's perfectly possible to make a good product using MySQL, quite possibly better than you would with other databases. But you do need to be very scrupulous about checking for warnings after every statement.
- dkoch 13y ago
  MySQL has strict mode flags that can turn those silent "best guess" inserts into errors. Triggers have existed since 5.0.2 in MySQL. The query planner has been greatly improved with MySQL 5.6 and the MariaDB fork.
- AaronBBrown 13y ago
  InnoDB, not MyISAM, has been the default storage engine since 5.5 which was released nearly 3 years ago.
darklajid 13y ago
Except that I have coworkers that have no clue whatsoever and ran into deadlocks. Hmmm.. Deadlocks? That's probably a transaction problem. MS SQL is buggy, right? We just don't use transations anymore and pass on the wisdom that transactions are slow and don't work. The _transactions_ cause deadlocks. Evil transactions.. SQL works well, if you invest time understanding the theory and the limitations. If you care about the implementation you're going to use (whether it's MySQL, MS SQL, PostgreSQL or whatnot). Unfortunately my rather sad experience is that people give a damn about it and will use the trial&error approach, until they cannot reproduce deadlocks/critical errors in their local 'runs on my Thinkpad' VM. (I really need to quit)
- fatalmind 13y ago
  The MsSQL locking problems is just because snapshot isolation is disabled per default: http://use-the-index-luke.com/blog/2011-06-24/dear-database-vendors http://use-the-index-luke.com/blog/2011-06-24/dear-database-...

eksith 13y ago

Answer to your last 3 questions : - Replace MySQL with MariaDB (hopefully your tables are InnoDB and the transition will be "relatively" seamless). You can continue using the rest of your app as-is. - Yes. There are many by the simple fact that a lot of people are unaware of what they're really doing and what impact certain indexes, queries and even to some extent, compile-time options will introduce. - Schema is everything for an RDBMS. If people just take the time to calmly and carefully consider what it is they're trying to do and what options they'll truly need vs. just guessing what future "growth" would be like, they will have fewer actual (rather than imagined) growing pains later. Of course, most of these problems could be mitigated if new projects started off with Postgres or MariaDB, if they're taking the RDBMS route.

Keyframe 13y ago
I keep hearing people recommend MariaDB, which is fine. I'd add in Percona as well. I have/had a decent run with it, but MySQL limitations with querying make me look towards Postgres. SQL engines are hard to do right, no wonder most of MPPs are built on top of Postgres.

raverbashing 13y ago

MySQL is a good database for basing a CMS on. (for a loose definition of database) PostgreSQL is a good SQL database. And don't take me wrong, I'll take MySQL instead of PostgreSQL for simple and medium projects. "Is the barrier to entry too low, and problems are from legions of rank amateurs?" Maybe. Well, "low barrier to entry" is not a defect.

cperciva 13y ago
MySQL is a good database for basing a CMS on. (for a loose definition of database) It seems to me that the success of MySQL can be tied directly to people using "database" for things where what they really need is "filesystem".
- fatalmind 13y ago
  Seems like. That's also what this comment on Reddit says: http://www.reddit.com/r/programming/comments/1niheb/mongodb_is_to_nosql_like_mysql_to_sql_in_the_most/ccje34x http://www.reddit.com/r/programming/comments/1niheb/mongodb_...
- bad_user 13y ago
  How easy is it to put a lock on a file while writing on it? What happens in an environment where you have multiple frontend servers that need to write to the same file? Are you going to have like one server that does the reading/writing from a file and have like a protocol with which other servers can send commands? Great, you've just invented a half-assed DBMS.
  - lmm 13y ago
    > Great, you've just invented a half-assed DBMS. My first thought is "Yeah, we already have one of those, it's called MySQL"
- ArbitraryLimits 13y ago
  Agree, except I'd amend it as The success of MySQL can be tied directly to people using "database" for things where what they really need is "network filesystem". The success of SQLite can be tied directly to people using "database" for things where what they really need is "filesystem". Interesting that it's the same dynamic at play.

mattmanser 13y ago

What are you actually trying to say? Not everything in your post is a full truth. MS's graphical query plan tool is actually pretty awful and hasn't been updated since forever, quite often hiding the most important information, like estimated rows vs. actual rows. It takes quite a while to learn how to actually read it unless someone shows you. Like months. And it's really just a wrapper around SHOW PLAN, which MySQL has with EXPLAIN. I'm not convinced that SQL Server Data Tools easily manages schemas, I've only been using it a few months but it seems a massive hack to me which relies on constant schema compares. MS also constantly push you to use the graphical tools, but if you play in the tools and then try to make something repeatable it's a right PITA. Your only option is to turn on the incredibly annoying 'Autogenerate change scripts' whereas in MySQL you can just look at the history tab in Workbench which shows every command you've run. Imagine that. Everything as SQL scripts you can modify and re-run! Finally, SQL Server Management studio is an absolute dog performance wise, I actually still miss Query Analyser which was much better IMO. And you can't actually use SQL Server with something like Ruby or Python without quite a massive amount of effort. I know because I've tried, there's no decent, easy to install, equivalent of ADO.Net without mucking around with ODBC connections. At least a year ago there wasn't. As for the problems with MySQL, he mentions in the post the problem with MySQL is the lack of certain types of operations. Also there's a bunch of gotchas, sub-selects perform terribly in MySQL, which makes the EF unusable on it. Still, I've used both and SQL Server is much better if you're developing with C#.

mcdougle 13y ago
I work with SQL Server and its parts (SSIS, SSAS, etc.) pretty much exclusively at my day job, and I occasionally find myself hacking up Python scripts to automate various tasks. I don't know about a year ago, but several months ago I found pyodbc [1] and it's been wonderful. Easy to set up, works a lot like the mysql functions in PHP. [1] https://code.google.com/p/pyodbc/ https://code.google.com/p/pyodbc/
- raverbashing 13y ago
  Oh this looks like a modern solution But really, Python + MSSQL = Bag of hurt. It's a real pity, but I see the "market" for that being very small...

corresation 13y ago

It's easy to manage schema with SQL Server Data Tools and diagnosing a poorly performing query is straightforward with graphical execution plans. I wouldn't put too much stock in the graphical execution plans. Outside of the problem that it simply fails for complex queries, it lies about costings (costings are always based upon estimates, which means that when you have super-fast storage, as many new installs do, the query analyzer becomes a giant bag of lies), it can't "see into" table-defined functions (they sit there innocuously with negligible cost when often they sit in front of a curse of poor performance). SQL Server is a great database, but it is very far from perfect. I mentioned above about the graphical execution plans lying about storage costs, and the same goes for the query planner -- it will choose horrific query plans under the assumption that you have one spinning disc of rust, falling apart when you're really on some FusionIO SLC. This can manifest in queries that run excellently one day falling completely to shit the next because it passed a threshold and has decided to switch join patterns. One would think that it would actively actually monitor storage/memory and CPU to make all of these weights, but it doesn't whatsoever. The other major issue being lock escalation that can destroy SQL Server databases under heavy contention.

ris 13y ago

"I keep hearing about problem after problem with MySQL. What's the trick to using it successfully?" Switching to PostgreSQL.

lmm 13y ago

MySQL is pretty good in practice, I think many complaints are indeed just user error. That said: - Much like MongoDB, the defaults sacrifice safety for performance, e.g. MyISAM tables (which everyone everywhere will tell you never to use, but are IIRC still the default) ignore things like foreign key constraints - Lots of "error" conditions silently complete as best as they can, rather than failing with an error. E.g. trying to store the wrong type of value, or trying to make a table using a disabled storage engine, or trying to store a string in an encoding that can't represent all its characters. A lot of this is legacy that is slowly being improved on, but reputation naturally lags slightly behind - The query planner is a bit simplistic, so if you want performance you do need to put a little more thought into tables so that you're not doing 5-way joins all the time - Some more advanced "features" of other databases like triggers just don't exist. IMO they are bad features and shouldn't be used, but their absence provides an easy thing to criticize mysql for if you're that way inclined It's perfectly possible to make a good product using MySQL, quite possibly better than you would with other databases. But you do need to be very scrupulous about checking for warnings after every statement.

dkoch 13y ago
MySQL has strict mode flags that can turn those silent "best guess" inserts into errors. Triggers have existed since 5.0.2 in MySQL. The query planner has been greatly improved with MySQL 5.6 and the MariaDB fork.
AaronBBrown 13y ago
InnoDB, not MyISAM, has been the default storage engine since 5.5 which was released nearly 3 years ago.

darklajid 13y ago

Except that I have coworkers that have no clue whatsoever and ran into deadlocks. Hmmm.. Deadlocks? That's probably a transaction problem. MS SQL is buggy, right? We just don't use transations anymore and pass on the wisdom that transactions are slow and don't work. The _transactions_ cause deadlocks. Evil transactions.. SQL works well, if you invest time understanding the theory and the limitations. If you care about the implementation you're going to use (whether it's MySQL, MS SQL, PostgreSQL or whatnot). Unfortunately my rather sad experience is that people give a damn about it and will use the trial&error approach, until they cannot reproduce deadlocks/critical errors in their local 'runs on my Thinkpad' VM. (I really need to quit)

fatalmind 13y ago
The MsSQL locking problems is just because snapshot isolation is disabled per default: http://use-the-index-luke.com/blog/2011-06-24/dear-database-vendors http://use-the-index-luke.com/blog/2011-06-24/dear-database-...