Scaling database. The easiest one!?

Rails
#1

Hi,

I got a application actualy running on mysql, but, due a contract with a
client, the application can get some complex queries and lots of users
(like 10x more users).

Its a academic application, and the company we are negociating have 7k+
students.

Im worry about the database, its better stick with de mysql? migrate to
postgresql?

Which one can scale better with quality?

Thanks!

#2

Diego B. wrote:

Hi,

I got a application actualy running on mysql, but, due a contract with a
client, the application can get some complex queries and lots of users
(like 10x more users).

Its a academic application, and the company we are negociating have 7k+
students.

Im worry about the database, its better stick with de mysql? migrate to
postgresql?

Which one can scale better with quality?

PostgreSQL is my default choice for all projects. It’s a better
database overall than mySQL, and does not force you to choose between
data integrity and speed the way mySQL does. I’m told it doesn’t handle
clustering quite as well as mySQL, but that’s its only real
disadvantage.

However, it is likely that either database will scale suitably. The
more immediate problems are likely to come from inefficient queries.

Thanks!

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

#3

Scaling read queries with mysql is drop dead simple. Just add replicas.
But you only have one server to do inputs on.Real simple. But a Quad
core
machine with a little memory, 16 GB, you can serve hundred thousand
people
easily. We do hundreds of thousands of heavy queries every minute on
one
box. Just create indexes, ‘explain’ is your friend.

Trausti

On Tue, Oct 6, 2009 at 10:44 PM, Diego B. <

#4

On Tue, Oct 6, 2009 at 4:10 PM, Marnen Laibow-Koser
[email protected] wrote:

PostgreSQL is my default choice for all projects.

I usually think about the specs and requirements, then make a choice.

–
Greg D.
http://destiney.com/

#5

On Tue, Oct 6, 2009 at 10:44 PM, Diego B.
[email protected] wrote:

Im worry about the database, its better stick with de mysql? migrate to
postgresql?

Which one can scale better with quality?

+1 to PostgreSQL. It is my default too. I don’t know about clustering,
though.
I’ve had many bad experiences with MySQL in the past, specially with
large amounts of data, tables just got broken.

–
Leonardo M…
There’s no place like ~

#6

Greg D. wrote:

On Tue, Oct 6, 2009 at 4:10 PM, Marnen Laibow-Koser
[email protected] wrote:

PostgreSQL is my default choice for all projects.

I usually think about the specs and requirements, then make a choice.

So do I. But the type of projects I do are generally ones that are well
suited to PostgreSQL. Anyway, mySQL and SQLite are problematic, and MS
SQL and Oracle are too expensive to be justifiable for the sort of stuff
I work on. So that doesn’t leave me much choice. :slight_smile:

–
Greg D.
http://destiney.com/

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

#7

I too choose PostgreSQL by default.

Here are some of my reasons.

  1. It adheres more reliably to SQL standard than MySQL.
  2. PostgreSQL has a better query planner than MySQL.
  3. The number value zero in MySQL is treated NULL.
  4. The default settings for MySQL make it case IN-sensitive.
  5. Until version 5.0, MySQL did not support referential integrity, or
    advanced features like views, transactions, or triggers.

Just my thoughts.

Andrew

#8

Andrew P. wrote:

I too choose PostgreSQL by default.

Here are some of my reasons.

  1. It adheres more reliably to SQL standard than MySQL.
  2. PostgreSQL has a better query planner than MySQL.
  3. The number value zero in MySQL is treated NULL.
  4. The default settings for MySQL make it case IN-sensitive.
  5. Until version 5.0, MySQL did not support referential integrity, or
    advanced features like views, transactions, or triggers.

What he said. Also:

  • mySQL’s procedural language sucks (less of a problem than it used to
    be, since I don’t use stored procedures as much).
  • I use PostGIS a lot. There is nothing comparable for mySQL.
  • MySQL makes you choose between speed and data integrity. PostgreSQL
    does not.
  • I have yet to see anything in mySQL that Postgres cannot do better.

Just my thoughts.

Andrew

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

#9

On Wed, Oct 7, 2009 at 5:50 AM, Marnen Laibow-Koser
[email protected] wrote:

Anyway, mySQL and SQLite are problematic

I’ve used both much success over the years. What sort of problems are
you having?

–
Greg D.
http://destiney.com/

#10

Here are some of my reasons.

  1. It adheres more reliably to SQL standard than MySQL.
  2. PostgreSQL has a better query planner than MySQL.
  3. The number value zero in MySQL is treated NULL.
  4. The default settings for MySQL make it case IN-sensitive.
  5. Until version 5.0, MySQL did not support referential integrity, or
    advanced features like views, transactions, or triggers.

Actually, the only true reason to choose PostgreSQL over MySQL is this:
you know PostgresSQL better.
As for transaction support in MySQL it is not even funny anymore. But
just in case:
MySQL supports transactions since version 3.23.34a

Regards,
Rimantas

http://rimantas.com/

#11

On Wed, Oct 7, 2009 at 10:02 AM, Andrew P. [email protected]
wrote:

I too choose PostgreSQL by default.

Here are some of my reasons.

  1. It adheres more reliably to SQL standard than MySQL.

No it doesn’t, they are both standards compliant.

  1. PostgreSQL has a better query planner than MySQL.

MySQL’s explain works great for me.

http://dev.mysql.com/doc/refman/5.0/en/using-explain.html

Looks nearly identical to the PostgreSQL offering:

http://www.postgresql.org/docs/8.4/static/using-explain.html

  1. The number value zero in MySQL is treated NULL.

That’s a lie.

mysql> select * from test;
±—±-------+
| id | number |
±—±-------+
| 1 | 0 |
±—±-------+
1 row in set (0.00 sec)

mysql> select * from test where number is null;
Empty set (0.00 sec)

  1. The default settings for MySQL make it case IN-sensitive.

So what? You can easily make an individual table case sensitive by
creating it with proper binary collation.

mysql> create table test3 (name varchar(255)) character set latin1
collate latin1_bin;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into test3 values
(‘Frank’),(‘Google’),(‘froogle’),(‘flickr’),(‘FlicKr’);
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM test3 WHERE name LIKE ‘F%’;
±-------+
| name |
±-------+
| Frank |
| FlicKr |
±-------+
2 rows in set (0.00 sec)

mysql> SELECT * FROM test3 WHERE name LIKE ‘f%’;
±--------+
| name |
±--------+
| froogle |
| flickr |
±--------+
2 rows in set (0.00 sec)

  1. Until version 5.0, MySQL did not support referential integrity, or
    advanced features like views, transactions, or triggers.

This argument is useless. There’s a ton of things PostgreSQL used to
not do when it was < 5.0. Replication, binary field storage, etc.

–
Greg D.
http://destiney.com/

#12

On Wed, Oct 7, 2009 at 10:11 AM, Marnen Laibow-Koser
[email protected] wrote:

  • I use PostGIS a lot. There is nothing comparable for mySQL.

This is a lie.

http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html

  • MySQL makes you choose between speed and data integrity. PostgreSQL
    does not.

PostgreSQL has one table type. MySQL gives you 7 different table
types to choose from.

Sounds like you’re either not smart enough to choose the right one or
you didn’t know you could make a choice.

I’ve never has any speed problems with MySQL, even using it on
mega-sites like ezsweeps.com back in the late 90’s.

–
Greg D.
http://destiney.com/

#13

On Wed, Oct 7, 2009 at 10:27 AM, Rimantas L. [email protected]
wrote:

MySQL supports transactions since version 3.23.34a

MySQL had replication before PostgreSQL too, I know I worked for a
startup that helped fund it.

MySQL supports 7 different table types so you choose the right type
for the data at hand. PostgreSQL support one table type.

PostgreSQL doesn’t even store user/host permissions in a database,
talk about not eating your own dog food.

–
Greg D.
http://destiney.com/

#14

Rimantas L. wrote:
[…]

Actually, the only true reason to choose PostgreSQL over MySQL is this:
you know PostgresSQL better.

Quite wrong. I know mySQL, if anything, a bit better than PostgreSQL.
I know it well enough that I have run into its limitations, discovered
that there’s no way around many of them, and given up on it as a result.

I might even go so far as to say that the only reason to use mySQL is
that you’re unaware of superior alternatives. :slight_smile:

As for transaction support in MySQL it is not even funny anymore. But
just in case:
MySQL supports transactions since version 3.23.34a

Sure – in InnoDB. If you need transactions and full-text search on the
same table, or if you need the speed of MyISAM, you’re out of luck.

Regards,
Rimantas

http://rimantas.com/

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

#15

Greg D. wrote:

On Wed, Oct 7, 2009 at 10:11 AM, Marnen Laibow-Koser
[email protected] wrote:

  • I use PostGIS a lot. There is nothing comparable for mySQL.

This is a lie.

Surely you can tell me you think I’m wrong without resorting to calling
me a liar. Let’s keep this debate as civil as possible. :slight_smile:

http://dev.mysql.com/doc/refman/5.0/en/spatial-extensions.html

Srsly? You’re claiming that these are comparable to PostGIS? As
someone who has worked with both, let me assure you that they are not.
In fact, my first PostGIS project was originally going to use mySQL and
these very features. It quickly became clear to me that although I
wasn’t doing anything fancy, mySQL would not suit my needs. So I found
PostGIS, which got me into PostgreSQL (which, at the time, I knew
nothing about). I can’t imagine going back.

  • MySQL makes you choose between speed and data integrity. �PostgreSQL
    does not.

PostgreSQL has one table type. MySQL gives you 7 different table
types to choose from.

Sounds like you’re either not smart enough to choose the right one or
you didn’t know you could make a choice.

Again, I’m sure you could have said this without insulting my
intelligence. Anyway, I do know about mySQL’s plethora of storage
engines. So tell me: which one (no, BLACKHOLE doesn’t count :slight_smile: ) will
get me transactions, foreign-key checking, and full-text search in the
same table? As far as I know, the answer is none.

I’ve never has any speed problems with MySQL, even using it on
mega-sites like ezsweeps.com back in the late 90’s.

Fair enough. I have had speed problems with mySQL, but I’m not certain
whether the cause was mySQL itself, poor DB design, or poor setup (and
at the time, I really couldn’t tell).

–
Greg D.
http://destiney.com/

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]

#16
  1. PostgreSQL has a better query planner than MySQL.

MySQL’s explain works great for me.

http://dev.mysql.com/doc/refman/5.0/en/using-explain.html

Looks nearly identical to the PostgreSQL offering:

http://www.postgresql.org/docs/8.4/static/using-explain.html

I think what the GP was trying to say is that PostgreSQL picks a
better execution plan for the query than MySQL does. Not all the
time, but certainly some of the time. Several months ago I had both
servers setup with the defaults, same schema, same indexes, same
dataset, same query. The query was a three table join. Two smaller
tables, one with 30,000 rows. PostgreSQL finished almost
immediately. MySQL took 3 seconds. It kind of shocked me. I ran
explain on both and MySQL simply would not use some of the indexes
provided. This was with a 5.0-something release of MySQL. I’d heard
that before, but it’s still an issue in certain situations.

  1. The number value zero in MySQL is treated NULL.

That’s a lie.

Glad to know that’s fixed! It was true awhile back. So where the
issues of inserting data that is too long and having it be silently
truncated – yes, I’m ignoring the warnings MySQL provides. It should
raise an error…

I notice that as of 5.0.51a-24 it still turns invalid dates into
0000-00-00 (with the warning, but no error).

  1. Until version 5.0, MySQL did not support referential integrity, or
    advanced features like views, transactions, or triggers.

This argument is useless. There’s a ton of things PostgreSQL used to
not do when it was < 5.0. Replication, binary field storage, etc.

That’s not a very fair comparison. Version numbers are meaningless…
Compare the versions at the same time… I will happily agree that
prior to 7.x postgres had some issues with these things.

Replication is still not as simple as MySQL’s, but there are solutions
out there.

I’d suggest we let the original poster do some research on his own and
figure out what works best. MySQL vs PostgreSQL is like VI vs Emacs
vs TextMate :slight_smile:

I don’t know how current these pages are, but they are interesting…

http://sql-info.de/mysql/gotchas.html
http://sql-info.de/postgresql/postgres-gotchas.html

-philip

#17

On Wed, Oct 7, 2009 at 11:51 AM, Marnen Laibow-Koser
[email protected] wrote:

Surely you can tell me you think I’m wrong without resorting to calling
me a liar. Let’s keep this debate as civil as possible. :slight_smile:

You lied. Stop telling lies and your problem will solve itself.

Srsly? You’re claiming that these are comparable to PostGIS?

They have served me well. What problems are you having?

–
Greg D.
http://destiney.com/

#18

On Wed, Oct 7, 2009 at 11:21 AM, Marnen Laibow-Koser
[email protected] wrote:

Sure – in InnoDB. If you need transactions and full-text search on the
same table, or if you need the speed of MyISAM, you’re out of luck.

You can easily replicate a table to another table of a different type.

What’s the PostgreSQL analogue for taking away table features you
don’t need? Oh, right, there is none. You’re stuck with that one
“everything but the kitchen sink” table type.

–
Greg D.
http://destiney.com/

#19

On Wed, Oct 7, 2009 at 11:21 AM, Philip H. [email protected]
wrote:

So where the
issues of inserting data that is too long and having it be silently
truncated – yes, I’m ignoring the warnings MySQL provides. It should
raise an error…

It does raise an error if you’re knowledgeable enough to know about
the various strict modes you can set:

mysql> create table test4 (name char(3));
Query OK, 0 rows affected (0.03 sec)

mysql> insert into test4 values (‘four’);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> SELECT * FROM test4;
±-----+
| name |
±-----+
| fou |
±-----+
1 row in set (0.00 sec)

mysql> SET SQL_MODE = ‘STRICT_ALL_TABLES’;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into test4 values (‘four’);
ERROR 1406 (22001): Data too long for column ‘name’ at row 1

I notice that as of 5.0.51a-24 it still turns invalid dates into
0000-00-00 (with the warning, but no error).

Similarly there is NO_ZERO_DATE strict mode for use with dates.

–
Greg D.
http://destiney.com/

#20

Greg D. wrote:

On Wed, Oct 7, 2009 at 11:51 AM, Marnen Laibow-Koser
[email protected] wrote:

Surely you can tell me you think I’m wrong without resorting to calling
me a liar. �Let’s keep this debate as civil as possible. :slight_smile:

You lied. Stop telling lies and your problem will solve itself.

I did not lie. I stand by my original statement and see no reason to
retract or revise it.

I’m not “telling lies” as you claim – I’m stating what I believe is
true. You may disagree, but that doesn’t make either one of us a liar.

Srsly? �You’re claiming that these are comparable to PostGIS?

They have served me well. What problems are you having?

Oh, just that they were too underpowered to be of any use at all in
implementing a simple geocoded system (terrestrial coordinates,
spheroidal distance calculation) – which PostGIS does beautifully.
MySQL’s spatial features might be OK for some abstract geometry use
cases, but that’s about it.

–
Greg D.
http://destiney.com/

Best,

Marnen Laibow-Koser
http://www.marnen.org
[email protected]