Hmmm... what if you forgot the password? I've a USB stick with an encrypted volume for storing passwords and other private data, and the password is nowhere recorded. Should I forget it and come in conflict with the law I'm going to end in jail forever for having bad memory?
Seems like encryption without any precautions is really dangerous.
I did not say MySQL is falling like a drunken guy, but for sure it's not stable for mission critical stuff. If you want to use it for mission-critical data, feel free to do so, but be prepared to loose your data (keep backing up). And you should read http://drbrain.livejournal.com/61705.html and check your settings.
I'm not against using MySQL for several purposes, but dont use Google as an example for stability. It isn't for the reasons already mentioned. And I cannot call a database stable that allows incorrect data, especially MySQLs handling of NULL values is still broken in 5.x, even in strict mode. Stability is more than 99.99999% uptime.
Maybe you missed your profession? Reading the docs, I immidiatly figured out how to create something you call unsigned int: CREATE DOMAIN UINT numeric check (VALUE >= 0 and VALUE 4294967296);
You should re-read my post. Yes, google-ads is an important source of income, but suppose a cluster of thousands of google-ads machines. Who cares if it's not stable, another machine will take-over the job. That does still not mean mysql is stable.
Google is no real example. What are they doing with MySQL? Serving ADS. It does not matter for Google if a Database crashes, it can be easily taken offline and re-initialized (in fact, the use a lot of hosts for their adserver-stuff). If an add fails, who cares.
1. "MySQL runs as a native Windows application (a service on NT/Win2000/WinXP), while PostgreSQL is run under the cygwin emulation." Get your facts. If this review ist really 15 months old, the 8.x series was available more than 6 months, and it's running native on Windows.
2. under section "SQL standard COMPLIANCE": MySQL uses SQL92 as its foundation. Runs on countless platforms. Wow. It's completly new to me that the number of platforms is related to SQL standard compliance. Beside this, standard conformance is far better in postgresql, there are numerous comparision tables available.
3. "STABILITY": mentioning 6.x releases clearly states how clueless the authors of the review are. Last update for 6.5 was in early 1999.
4. "LARGE OBJECTS": "In Postgres, Large Objects are very special beasties.". Historically seen - right. But why not use the bytea datatype as available in Postgresql for several years now?
5. "ALTER TABLE": 15 month ago postgresql of course had full support for all those functions.
MySQL data has never been atomic - and never will be - if you're using MyISAM. Try a big update and while it's running kill -9 all mysqld processes. Your data is now half updated, congratulations.
Which version are you using? I've done this (restoring to different database versions, with other tools that made the dump) quite often (for sure 50 times or more for testing purposes). I never had such a problem, I'm pretty sure you're making a mistake in your restore process.
A little bit offtopic here: 3 months ago I had troubles restoring a MySQL 5.0.18 dump to another MySQL 5.0.18 database. Now that's what I call unreliable.
In fact you seem to be talking from very old releases, maybe 7.2?
From a 7.4 database: Renaming columns: ALTER TABLE [ ONLY ] name [ * ]
RENAME [ COLUMN ] column TO new_column
> Want to add a not null column with a default value? Thats 3 statements. Plus one to update the existing rows to the default value. How often do you add columns? Pretty seldom I guess, so this is not a real problem. Or if you want, create a plsql function that does this for you.
In fact, the shared_buffer setting IS EXACTLY for this. In Postgres you have: effective_cache_size => a hint for the planner how much memory is remaining. Postgres does not allocate this memory, it's just used in the calculations of the planer. If you give postgres enough buffers here to cache your database in memory, it will happily do so. However, UPDATES/DELETES will still require to access the disks.
shared_buffers => Shared Memory Segment where commonly used page tables are stored for later reuse.
work_mem => Memory Postgres will allocate PER database backend for sorting and other purposes. This is not shared between backends.
In fact, this data is from a postgres database with 3,8GB of used space, 300 relations and 600 indices: * shared_buffers is set to 200MB * work_mem is set to 4MB * effective_cache_size is set to 1GB
This way, the system gets a cache hit ratio of 99.6% The DB is up around 120 days on this system, 16901 GB (~17TB) where fetched from disk, and around 1700TB was fetched from RAM. So I guess this is a pretty good hit rate.
And stating that InnoDB has to do exactly the same here is for a good reason, InnoDB uses MVVC too. And MVVC requires to have full table scans. BTW, oracle uses full table scans too, at least it did until 3 years when I stopped using Oracle.
Why don't you write: insert into tablename ( field) values (42); This is a good practise anyway, never ever write queries like insert into tablename values (42); -- You will be in trouble sooner or later.
"Forever broken COUNT(). Although MIN & MAX were fixed in the latest release, COUNT() is still broken and there is no fixing in sight. Yes, I beieve 10 seconds execution time for count() on a table with just a few million records qualifies it as a broken feature."
You know that count() does full table scans in MySQL too when you use InnoDB? And for MyISAM the result is simply wrong while updating.
"No memory management. For example, here is 1GB database on a dedicated host with 2GB of RAM. PG should suck the while DB into RAM and run selects from there, right? Wrong. PG is extremely frugal about memory management. It caches the last few results, but otherwise goes to disk for data even if there is anough RAM to cache the whole DB. The PG developers keep saying that it's the job for the OS. Now, which OS should we use then? FreeBSD, Linux, Windows? Which one?"
That's right. Postgres requires that you specify how much memory it should use. As for every complex program, it requires some skill.
Wiki is a bad example for several reasons: * Stability: we all know the wiki-crash a few months ago with corrupted inno-db tables. InnoDB released the info it was the fault of the hardware - but unproven. I've heard similar reports from others making me think that InnoDB is not quite stable.
* Performance: the MySQL performance here is not so important, most of Wiki's speed comes from their extensive use of Memcached/tugela and their Squid proxies.
And the most interesting part is, whenever MySQL is benched, they use MyISAM tables, and when someone says "what about transactions?" they refer to InnoDB tables, but without benchmarks, and that for a good reason
I've tested RC1 with some applications, and was really impressed by the bitmap-indices. I hope to see soon a multi-master replication now that 2PC is available. I've heard Slony-2 will have it.
Please don't mention Oracle, MySQL and Enterprise in the same sentence. It's not true, no matter how often this myth is told. MySQL ist NOT enterprise ready. At least not now.
What's wrong with copy? I use it daily, and it works very fine. Even creating a prepared statement helps a lot if you don't want to use copy: declare add_it(int, int, int) as insert into tabname (f1, f2, f3) values ($1, $2, $3); execute add_it(1, 2, 3); execute add_it(4, 5, 6);
>I used to work on a site that got millions of hits per month. Most of the content was published static, but there was a fair amount of dynamic content. The same MySQL database was used for their content management system. It ran flawlessly.
A friend of mine used to drive without safety belt. It work flawlessly for 50000km. Now he's dead.
Wrong. The database MUST throw an error when the users does something unsupported, and not silently ignore what the user really wanted and doing something completly different. This is broken by design.
Slashdot Mirror
Hmmm... what if you forgot the password? I've a USB stick with an encrypted volume for storing passwords and other private data, and the password is nowhere recorded. Should I forget it and come in conflict with the law I'm going to end in jail forever for having bad memory?
Seems like encryption without any precautions is really dangerous.
Whenever some of my employees produces code like "i=0; i=i++;" he will get fired anyway. Good code is readable, and not a riddle or puzzle.
So the correct answer is: does that really matter?
I did not say MySQL is falling like a drunken guy, but for sure it's not stable for mission critical stuff. If you want to use it for mission-critical data, feel free to do so, but be prepared to loose your data (keep backing up). And you should read http://drbrain.livejournal.com/61705.html and check your settings.
I'm not against using MySQL for several purposes, but dont use Google as an example for stability. It isn't for the reasons already mentioned. And I cannot call a database stable that allows incorrect data, especially MySQLs handling of NULL values is still broken in 5.x, even in strict mode. Stability is more than 99.99999% uptime.
>Can't figure anything out.
Maybe you missed your profession? Reading the docs, I immidiatly figured out how to create something you call unsigned int:
CREATE DOMAIN UINT numeric check (VALUE >= 0 and VALUE 4294967296);
You should re-read my post. Yes, google-ads is an important source of income, but suppose a cluster of thousands of google-ads machines. Who cares if it's not stable, another machine will take-over the job. That does still not mean mysql is stable.
Google is no real example. What are they doing with MySQL? Serving ADS. It does not matter for Google if a Database crashes, it can be easily taken offline and re-initialized (in fact, the use a lot of hosts for their adserver-stuff). If an add fails, who cares.
That does not mean MySQL is stable.
KDE: 4712273/(1554+25+65) => 1 fault per 2866 LOC
GNOME: 430809/(357+5+214) => 1 fault per 747 LOC
Wow. I did not expect GNOME to have 4 times more possible faults, maybe it's because of their preferred language C instead of C++?
What exactly do you mean with case insensitive collations? Could you please give an example?
1. "MySQL runs as a native Windows application (a service on NT/Win2000/WinXP), while PostgreSQL is run under the cygwin emulation."
Get your facts. If this review ist really 15 months old, the 8.x series was available more than 6 months, and it's running native on Windows.
2. under section "SQL standard COMPLIANCE": MySQL uses SQL92 as its foundation. Runs on countless platforms.
Wow. It's completly new to me that the number of platforms is related to SQL standard compliance. Beside this, standard conformance is far better in postgresql, there are numerous comparision tables available.
3. "STABILITY": mentioning 6.x releases clearly states how clueless the authors of the review are. Last update for 6.5 was in early 1999.
4. "LARGE OBJECTS": "In Postgres, Large Objects are very special beasties.". Historically seen - right. But why not use the bytea datatype as available in Postgresql for several years now?
5. "ALTER TABLE": 15 month ago postgresql of course had full support for all those functions.
Biased, clueless and just stupid review.
MySQL data has never been atomic - and never will be - if you're using MyISAM. Try a big update and while it's running kill -9 all mysqld processes. Your data is now half updated, congratulations.
Which version are you using? I've done this (restoring to different database versions, with other tools that made the dump) quite often (for sure 50 times or more for testing purposes). I never had such a problem, I'm pretty sure you're making a mistake in your restore process.
A little bit offtopic here: 3 months ago I had troubles restoring a MySQL 5.0.18 dump to another MySQL 5.0.18 database. Now that's what I call unreliable.
In fact you seem to be talking from very old releases, maybe 7.2?
From a 7.4 database:
Renaming columns:
ALTER TABLE [ ONLY ] name [ * ]
RENAME [ COLUMN ] column TO new_column
> Want to add a not null column with a default value? Thats 3 statements. Plus one to update the existing rows to the default value.
How often do you add columns? Pretty seldom I guess, so this is not a real problem. Or if you want, create a plsql function that does this for you.
In fact, the shared_buffer setting IS EXACTLY for this. In Postgres you have:
effective_cache_size => a hint for the planner how much memory is remaining. Postgres does not allocate this memory, it's just used in the calculations of the planer. If you give postgres enough buffers here to cache your database in memory, it will happily do so. However, UPDATES/DELETES will still require to access the disks.
shared_buffers => Shared Memory Segment where commonly used page tables are stored for later reuse.
work_mem => Memory Postgres will allocate PER database backend for sorting and other purposes. This is not shared between backends.
In fact, this data is from a postgres database with 3,8GB of used space, 300 relations and 600 indices:
* shared_buffers is set to 200MB
* work_mem is set to 4MB
* effective_cache_size is set to 1GB
This way, the system gets a cache hit ratio of 99.6%
The DB is up around 120 days on this system, 16901 GB (~17TB) where fetched from disk, and around 1700TB was fetched from RAM. So I guess this is a pretty good hit rate.
And stating that InnoDB has to do exactly the same here is for a good reason, InnoDB uses MVVC too. And MVVC requires to have full table scans. BTW, oracle uses full table scans too, at least it did until 3 years when I stopped using Oracle.
Why don't you write:
insert into tablename ( field) values (42);
This is a good practise anyway, never ever write queries like
insert into tablename values (42); -- You will be in trouble sooner or later.
"Forever broken COUNT(). Although MIN & MAX were fixed in the latest release, COUNT() is still broken and there is no fixing in sight. Yes, I beieve 10 seconds execution time for count() on a table with just a few million records qualifies it as a broken feature."
You know that count() does full table scans in MySQL too when you use InnoDB? And for MyISAM the result is simply wrong while updating.
"No memory management. For example, here is 1GB database on a dedicated host with 2GB of RAM. PG should suck the while DB into RAM and run selects from there, right? Wrong. PG is extremely frugal about memory management. It caches the last few results, but otherwise goes to disk for data even if there is anough RAM to cache the whole DB. The PG developers keep saying that it's the job for the OS. Now, which OS should we use then? FreeBSD, Linux, Windows? Which one?"
That's right. Postgres requires that you specify how much memory it should use. As for every complex program, it requires some skill.
Wiki is a bad example for several reasons:
* Stability: we all know the wiki-crash a few months ago with corrupted inno-db tables. InnoDB released the info it was the fault of the hardware - but unproven. I've heard similar reports from others making me think that InnoDB is not quite stable.
* Performance: the MySQL performance here is not so important, most of Wiki's speed comes from their extensive use of Memcached/tugela and their Squid proxies.
And the most interesting part is, whenever MySQL is benched, they use MyISAM tables, and when someone says "what about transactions?" they refer to InnoDB tables, but without benchmarks, and that for a good reason
If you're using pg_restore it will exaktly do this, at least if you're dumping like this: .... dbname -Fc -b -f file.dmp
... -d dbname -v file.dmp
pg_dump -U
Restore:
pg_restore -U
Should exactly do what you want.
At least on Linux KEXEC might be the answer to this problem. Booting a kernel without really doing a real boot.
Seems quite unrelated to my posting. Where's the quote from?
I've tested RC1 with some applications, and was really impressed by the bitmap-indices. I hope to see soon a multi-master replication now that 2PC is available. I've heard Slony-2 will have it.
In fact this is true, and was fixed in 7.2 I think, maybe 2-3 years ago.
Please don't mention Oracle, MySQL and Enterprise in the same sentence. It's not true, no matter how often this myth is told. MySQL ist NOT enterprise ready. At least not now.
What's wrong with copy? I use it daily, and it works very fine. Even creating a prepared statement helps a lot if you don't want to use copy:
declare add_it(int, int, int) as insert into tabname (f1, f2, f3) values ($1, $2, $3);
execute add_it(1, 2, 3);
execute add_it(4, 5, 6);
Did you try it?
>I used to work on a site that got millions of hits per month. Most of the content was published static, but there was a fair amount of dynamic content. The same MySQL database was used for their content management system. It ran flawlessly.
A friend of mine used to drive without safety belt. It work flawlessly for 50000km. Now he's dead.
Rethink your arguments.
Wrong. The database MUST throw an error when the users does something unsupported, and not silently ignore what the user really wanted and doing something completly different. This is broken by design.