Revisiting FreeBSD vs. Linux for MySQL

Dan writes "Jeremy Zawodny, who looks after all of Yahoo!'s MySQL servers says MySQL now runs very well on FreeBSD. He is no longer steering people toward Linux. There are two important things you should do to make the FreeBSD/MySQL combo work well: (1) build MySQL with LinuxThreads rather than FreeBSD's native threads, and (2) use MySQL 4.x or newer."

Comments

or (3) pkg-add -r postgresql7

[DrSkwid]

cos i thinks that's newer than mysql will ever be 8)

Re:or (3) pkg-add -r postgresql7

[Pathwalker]

Personally, I like using "cd /usr/ports/databases/postgresql7 && make install" rather than using a binary package, as that way you have the source code on your system, and you can use all of the wonderful things in the contrib directory.

tsearch is a very nice GIST indexed full text search that does word stemming.
reindexdb is a handy way to regenerate all of the indices in a database without interrupting anything.
earthdistance is far faster than writing the same thing as a SQL function.
ltree is wonderful when you have to deal with a hierarchy.

And those are just the ones I remember offhand...

Re:or (3) pkg-add -r postgresql7

[TheRaven64]

Up to a point this is true. However MySQL does win on one point, namely that if you do not need most of the features of a real RDBMS then it is faster than Postgres. If you are just using it as an information store for a web site, for example, then it may be a better choice. As always, right tool for the right job.

Re:or (3) pkg-add -r postgresql7

[lowmagnet]

And PostgreSQL is easier to work with. And setting up PgSQL is a breeze. And..

I could go on all day about why I don't use MySQL. Just because a great percentage of a particlar market is using something, it doesn't mean they are right. (viz: Microsoft products, on the whole, are a pain in the ass.)

Re:or (3) pkg-add -r postgresql7

[Corydon76]

Speak for yourself. I find that setting up and working with MySQL is easier than PostgreSQL.

Consider how you find the names of all the tables in a database:

On MySQL: SHOW TABLES

On Informix: SELECT tabname FROM systables

On PostgreSQL: SELECT relname FROM pg_class WHERE reltype='r'

Now if you hadn't known about the structure of pg_class beforehand and done considerable digging into the documentation, you'd be lost. A newbie even more so. Shouldn't something so simple be easy to do?

Re:or (3) pkg-add -r postgresql7

[nsayer]

# On PostgreSQL: SELECT relname FROM pg_class WHERE reltype='r'

Or just \d (if you're using psql).

Re:or (3) pkg-add -r postgresql7

[MattRog]

The problem is that, since people don't know the fundamentals of relational theory they don't know they don't need the 'features' (which in my mind, are a requirement).

This is why those of us that do know get flapped when those that don't tell us it is not necessary. See the problem?

Re:or (3) pkg-add -r postgresql7

[ViVeLaMe]

and let's not forget VACUUMDB.
Want to claim back all this space lost when you deleted something? guess what, you'll have to lock your database. greeeaaat.

And it's so sloooooooow that now i have to dump/restore the DB every week.

And don't even mention database replication: you can do that on mySQL (with the logs), while the postgreSQL team is still struggling with this one.

Re:or (3) pkg-add -r postgresql7

[lowmagnet]

On IFX, to show tables you would use the dbaccess. You also use it to write and store queries, debug, etc. It's very easy to use, and even a novice could grok that.

By far, the easiest database to use is SQL Server. This is half because 'database' classes train in Access, and MSFT modeled SQL Server partially around the Access design. This means a left hand pand showing all your tables. No typing needed.

Please note that this is not an endorsement for SQLServer, but merely a statement of opinion. The server is a pig, it only runs in certain configurations well, etc. It is not as flexible as PostgreSQL or MySQL by any stretch.

Re:or (3) pkg-add -r postgresql7

[Ragica]

Or select * from pg_tables; if you really want to. \d and the variations thereof are the quickets choice though. Use \? from psql to see what's available.

A lot of mysql people seem to complain about the lack of the nonstandard "show tables" type commands in postgresql. But "show tables" being non-standard means you had to read the documenation to find that. If you read the postgresql documentation also instead of expecting postgresql to have the mysql non-standard extensions you'd find that in fact postgresql is much more flexible and powerful when it comes to finding system information.

Re:or (3) pkg-add -r postgresql7

[Ragica]

Only "vacuum full" currently locks tables. Normal vacuums do not lock tables. If you need to reclaim space on your HD that often you have other problems, and postgresql probably isn't suited to your limited resources.

As to your speed problems, you must have other problems. We run billing databases on postgresql and they are in no way slow.

Re:or (3) pkg-add -r postgresql7

[ViVeLaMe]

the database in itself isn't slow. That is, unless a vacuum full isn't done regularly, in which case it comes to a crawl. Vacuum Full is quite slow too, and does lock the whole database, which is quite infortunate when it powers a website which is supposed to be updated 24/24... So i can definitely see why a website operator would choose mySQL over PG (if he don't need transactions, for exemple.)

Re:or (3) pkg-add -r postgresql7

[MattRog]

Oops, I got too mixed up in the verbiage and messed it up:

The problem is that, since people don't know the fundamentals of relational theory, they don't know what the 'features' are (which in my mind are requirements) - so how can they effectively decide whether or not they are important?

Re:or (3) pkg-add -r postgresql7

[Christian Smith]

(if he don't need transactions, for exemple.)

Although I think MySQL 4.0 has added transactions anyway (I may be wrong,) you may as well just have written:
(if he doesn't care if his data is correct.)

I don't understand why people be-little transaction support. It is possibly the single biggest reason to use PG over MySQL. You can throw more hardware at a slow database, but you can do nothing for corrupt data (not that PG is slow!)

Re:or (3) pkg-add -r postgresql7

[Corydon76]

But the documentation on MySQL is also better; SHOW TABLES can found with little to no trouble. On the other hand, finding the pg_class information was a pain in the neck. Not only was the information (that I found anyway) not on the PostgreSQL site, it was buried several levels deep in a forum posting.

While this isn't making any points on how good a database is, if I can't find the documentation without Googling all over the place, it isn't perfect for me.

cd ... wouldn't fit in the subject 8)

[DrSkwid]

thanks for theinfo though, tsearch looks pretty useful for my stuff & ltree sounds intreaguing (if you'll excuse the pun)

linux threads on freebsd -release or -stable

[JDizzy]

Given that FreeBSD -current 1:1 threads are not 100% yet, the native FreeBSD threads will not grant the optimal performance compared to the linux threads. Yahoo probably uses a RELEASE version, or a -STABLE version of FreeBSD on their production systems, so the KSE probably isn't an option anyways. Once KSE is finished, I'm sure we will reviste the "FreeBSD V. Linux" threads war again. No granted I'm a bit biased toward FreeBSD, but I think that since the same folks control the LIBC taht are also creating the KSE (kernel scheduled Entities, aka kernel aware threads) that FreeBSD will have a bit tighter integration when all things are said and done.

OpenBSD, too

[chrysalis]

Please note that on 3.3-STABLE and -current, MySQL is also finally extremely stable on OpenBSD, with native threads.

A lot of threading-related work has been made during the 3.3 development cycle and there are no more unexpected crashes with this sort of apps. For instance the new threading code solved all issues I had with the Oops proxy, that is now very stable on production servers.

Time to make world

[xA40D]

The article mentions that some things have been fixed in 5.0. Which could make some feel that upgrading to 5.0 would be a smart idea. However, FreeBSD hackers, being a conservative bunch, would disagree.

Thankfully, any worthwhile fixes in CURRENT will usually be backported to the STABLE. For example:

From the article:
The problem of MySQL occasionally thinking that all databases had vanished resulted from FreeBSD's non-threadsafe implementation of realpath().

CVS log for src/lib/libc/stdlib/realpath.c
MFC: make realpath thread-safe.

So personally me thinks a make world would be an idea sometime soon.

Re:or portupgrade -RN postgresql7

[GodOfNothing]

Of course you first have to install portupgrade itself. Also despite the name it'll do ports, packages, upgrades, installs and of course (most importantly) will upgrade ports where make fails because the port you're upgrading is listed as a dependancy for other ports.

Anticipatory scheduler

[placiBo]

I'd like to see similar comments running the anticipatory scheduler for Linux and a quad processor SMP box vs FreeBSD. Thats where the differences in SMP performance and the threading implementation will show.

Elegy for *BSD

[Anonymous Coward]

An Elegy For *BSD

I am a *BSD user
and I try hard to be brave
That is a tall order
*BSD's foot is in the grave.

I tap at my toy keyboard
and whistle a happy tune
but keeping happy's so hard,
*BSD died so soon.

Each day I wake and softly sob
Nightfall finds me crying
Not only am I a zit faced slob
but *BSD is dying.