5 ms·

I'd be very interested to hear from people using other databases on whether their DB of choice is much better. I've been using MySQL for a while, have been burn

by matt2000 14y ago

I'd be very interested to hear from people using other databases on whether their DB of choice is much better. I've been using MySQL for a while, have been burned by a few things, but figured it was mainly my fault. If indeed there are better options I'd love to hear the details. (Just to be clear, I know about other databases, just aren't sure if any are that much better in real world use).

saurik 14y ago
Many of the complaints I hear people make about the entire concept of an "RDBMS" (often then to motivate why the NoSQL solution they decided to start using is better) are actually MySQL-specific issues that do not affect PostgreSQL (or Oracle, or usually SQL Server; I only mention PostgreSQL, as you wanted a concrete experience); one key example is "if you want to change your schema, it requires locking the entire system and rewriting the table" <- no, as the schema is just metadata; you should be able to do these things under first-class transactions, and PostgreSQL supports this just fine.
- rosser 14y ago
  To be fair, there are cases where schema changes in PostgreSQL require re-writing the table, too. Like, for example, when you change the data type of an existing column. Otherwise, you're good, though.
  - kconor 14y ago
    To be fair, if you want to change the datatype of data in a NoSQL system, you have to rewrite it as well.
  - saurik 14y ago
    Yes, but that case comes up less often and isn't what I see NoSQL people complaining about ;P. (Also, when I've listed that as an explicit caveat recently, I often get corrected that they changed the ramifications of that recently, and it only sometimes has to happen, so I figured I'd just explicitly list the situations where you clearly should never have needed to rewrite the table.) Generally, though, this is related to be able to do table changes under transaction locks: I have often enjoyed being able to, under an atomic transaction, replace a table with a view over that table, or make modifications to indexes and columns that I then rollback if there's a mistake.
  - jeltz 14y ago
    Actually that depends on which change you do. varchar(2) -> varchar(4) does not require a rewrite while text -> int and varchar(4) -> varchar(2) does.
- jacques_chester 14y ago
  Or "joins are too slow". Well, yes, they can be. But some database systems have smarter plan builders than others. Take, for example, this gem: > Joining and ordering by rows from multiple tables often forces MySQL to dump the whole join to a temporary table, then sort it -- awful, especially if you then use LIMIT BY to paginate the results. This describes just about every standard page-with-comments schema ever devised. It struck me with particular force because perhaps I've been unfairly blaming Wordpress for the failings of MySQL, vis-a-vis the atrocious performance of the Recent Comments Widget. I see horrible join performance on tables with about half a million rows, which is chicken feed. The query cache "solves" my problem, but it's amazing that it should be necessary for very trivial joins between 2 or 3 tables.
  - toast0 14y ago
    Is it MySQLs fault that wordpress asks it to do something that's hard? LIMIT OFFSET queries are always going to be hard because you have to generate lots of rows only to throw them away, but there are ways to organize the data, and write the queries so that you avoid temporary tables. I don't think Wordpress is MySQL specific; and maybe they use an ORM layer anyway, but if you want to get the most performance out of MySQL, you need to know how it works and write your queries accordingly. Sometimes that means get all the data unsorted from MySQL and sort it yourself, which is unfortunate, but frontend cpu + memory is cheaper than database I/O to make a temp table and sort it
    - knightni 14y ago
      > LIMIT OFFSET queries are always going to be hard because you have to generate lots of rows only to throw them away In fairness, that often depends upon the intelligence of the planner. A lot of sort-limit-offset queries can be reasonably easy and not generate too many rows if the planner works correctly and you have the right indexes. edit: Assuming you're mostly picking up early pages as opposed to getting late ones, anyway - which is usually the case for, say, a comment system.
    - sergiosgc 14y ago
      I know WordPress quite well. Most of these queries can be very easily executed from indexes, pulling from the tables only the data that will be output. It's just that mysql chooses to do it the dumb way.
sadmysqluser 14y ago
I've never recommended MySQL for anything where the data is important, but I've had to help several businesses deal with existing installations. Here's a few teachable moments: One customer who ran MySQL on windows experienced data loss after a historic MySQL crash left them with a corrupted MyISAM table, which caused mysqldump to SILENTLY exit before the dump was complete. Their dumps only had the tables up to the corrupted one. They had been saving those dumps for months, but never testing them. When the server eventually gave up the ghost they found that none of their dumps were good. Lesson: 1. ALWAYS test your dumps. 2. NEVER trust MyISAM. Another customer, smart enough to use InnoDB but not smart enough to change the MySQL defaults let one database get too large and found they couldn't shrink any of their databases because unless you use innodb_file_per_table InnoDB won't ever relinquish space even if you delete all the rows in your tables even after "optimize table". Lesson: 1. ALWAYS use innodb_file_per_table if you plan to keep the database for a long time. 2. Periodically run mysqlcheck optimize or optimize table to actually reclaim deleted space. I've also helped customers rewrite their applications to avoid the "Total number of locks exceeds lock table size" problem. What is that? Well, even if you use database level locks the number of rows a transaction can alter is still limited by the server's memory buffer pool size. Lesson: 1. Know your working set. Make sure your application checks the server's buffer pool size via SHOW VARIABLES and is smart enough to avoid large transaction updates. 2. Use a large buffer pool size whenever you can. Coming from a PostgreSQL background, these came as total surprises to me. I've never been completely happy with PostgreSQL's transaction id wraparound issues but I have yet to experience anything like the problems I've observed with MySQL.

saurik 14y ago

Many of the complaints I hear people make about the entire concept of an "RDBMS" (often then to motivate why the NoSQL solution they decided to start using is better) are actually MySQL-specific issues that do not affect PostgreSQL (or Oracle, or usually SQL Server; I only mention PostgreSQL, as you wanted a concrete experience); one key example is "if you want to change your schema, it requires locking the entire system and rewriting the table" <- no, as the schema is just metadata; you should be able to do these things under first-class transactions, and PostgreSQL supports this just fine.

rosser 14y ago
To be fair, there are cases where schema changes in PostgreSQL require re-writing the table, too. Like, for example, when you change the data type of an existing column. Otherwise, you're good, though.
- kconor 14y ago
  To be fair, if you want to change the datatype of data in a NoSQL system, you have to rewrite it as well.
- saurik 14y ago
  Yes, but that case comes up less often and isn't what I see NoSQL people complaining about ;P. (Also, when I've listed that as an explicit caveat recently, I often get corrected that they changed the ramifications of that recently, and it only sometimes has to happen, so I figured I'd just explicitly list the situations where you clearly should never have needed to rewrite the table.) Generally, though, this is related to be able to do table changes under transaction locks: I have often enjoyed being able to, under an atomic transaction, replace a table with a view over that table, or make modifications to indexes and columns that I then rollback if there's a mistake.
- jeltz 14y ago
  Actually that depends on which change you do. varchar(2) -> varchar(4) does not require a rewrite while text -> int and varchar(4) -> varchar(2) does.
jacques_chester 14y ago
Or "joins are too slow". Well, yes, they can be. But some database systems have smarter plan builders than others. Take, for example, this gem: > Joining and ordering by rows from multiple tables often forces MySQL to dump the whole join to a temporary table, then sort it -- awful, especially if you then use LIMIT BY to paginate the results. This describes just about every standard page-with-comments schema ever devised. It struck me with particular force because perhaps I've been unfairly blaming Wordpress for the failings of MySQL, vis-a-vis the atrocious performance of the Recent Comments Widget. I see horrible join performance on tables with about half a million rows, which is chicken feed. The query cache "solves" my problem, but it's amazing that it should be necessary for very trivial joins between 2 or 3 tables.
- toast0 14y ago
  Is it MySQLs fault that wordpress asks it to do something that's hard? LIMIT OFFSET queries are always going to be hard because you have to generate lots of rows only to throw them away, but there are ways to organize the data, and write the queries so that you avoid temporary tables. I don't think Wordpress is MySQL specific; and maybe they use an ORM layer anyway, but if you want to get the most performance out of MySQL, you need to know how it works and write your queries accordingly. Sometimes that means get all the data unsorted from MySQL and sort it yourself, which is unfortunate, but frontend cpu + memory is cheaper than database I/O to make a temp table and sort it
  - knightni 14y ago
    > LIMIT OFFSET queries are always going to be hard because you have to generate lots of rows only to throw them away In fairness, that often depends upon the intelligence of the planner. A lot of sort-limit-offset queries can be reasonably easy and not generate too many rows if the planner works correctly and you have the right indexes. edit: Assuming you're mostly picking up early pages as opposed to getting late ones, anyway - which is usually the case for, say, a comment system.
  - sergiosgc 14y ago
    I know WordPress quite well. Most of these queries can be very easily executed from indexes, pulling from the tables only the data that will be output. It's just that mysql chooses to do it the dumb way.

sadmysqluser 14y ago

I've never recommended MySQL for anything where the data is important, but I've had to help several businesses deal with existing installations. Here's a few teachable moments: One customer who ran MySQL on windows experienced data loss after a historic MySQL crash left them with a corrupted MyISAM table, which caused mysqldump to SILENTLY exit before the dump was complete. Their dumps only had the tables up to the corrupted one. They had been saving those dumps for months, but never testing them. When the server eventually gave up the ghost they found that none of their dumps were good. Lesson: 1. ALWAYS test your dumps. 2. NEVER trust MyISAM. Another customer, smart enough to use InnoDB but not smart enough to change the MySQL defaults let one database get too large and found they couldn't shrink any of their databases because unless you use innodb_file_per_table InnoDB won't ever relinquish space even if you delete all the rows in your tables even after "optimize table". Lesson: 1. ALWAYS use innodb_file_per_table if you plan to keep the database for a long time. 2. Periodically run mysqlcheck optimize or optimize table to actually reclaim deleted space. I've also helped customers rewrite their applications to avoid the "Total number of locks exceeds lock table size" problem. What is that? Well, even if you use database level locks the number of rows a transaction can alter is still limited by the server's memory buffer pool size. Lesson: 1. Know your working set. Make sure your application checks the server's buffer pool size via SHOW VARIABLES and is smart enough to avoid large transaction updates. 2. Use a large buffer pool size whenever you can. Coming from a PostgreSQL background, these came as total surprises to me. I've never been completely happy with PostgreSQL's transaction id wraparound issues but I have yet to experience anything like the problems I've observed with MySQL.