[RESOLVED] mysql and these limitations, how about other open source database servers?

blackhorse

Somehow I experienced "too many mysql connections", "too many open files" etc. recently on mysql.

I just have a question, are these limitations MySQL's own issues or other database system has them too?

I used Interbase/Firebird before I switched to MySQL. Interbase/Firebird early versions missed something very important, such as AUTO INCRESEMENT VALUE. They didn't have it. (I don't know why they missed this simple but very important feature.) Or in older version, they didn't have the "select limit" function.

But years ago, they also had a lot of database features MySQL doesn't have. Plus, I had quit a few sites running on InterBase/Firebird before, I never experienced the database problems such as too many connections, or too many open files. InterBase/Firebird were running very stable. I don't know if it is because that Interbase/Firebird is stable or if my database traffic is smaller than today's my mysql database traffic.

But after Cobalt servers faded out the market, it is getting a little harder to find big hosting company with great hosting packages still preset support interbase/firebird. So I have to switch to mysql, mostly for the host platform reasons just in case my current clients want to hosting somewhere else.

Piranha

Let's get one thing corrected from your post. Those things you mention are not limits in MySQL, it is settings that is possible to change. With a database server that is bought with the traffic in mind and set up properly this is not an issue.

Now about these "limitations" in other databases. I don't know other databases that good so I can't really answer that question. But I guess that other databases have to be correctly set up on servers that can handle the traffic, otherwise you will get errors there as well. Maybe a thought to solve your problem could be to first make sure that you have a dedicated database server, and if that is not enough maybe it is possible to use more than one database server. And of course, check your queries so they don't use more resources that needed.

MarkR

blackhorse wrote:
Somehow I experienced "too many mysql connections", "too many open files" etc. recently on mysql.

I just have a question, are these limitations MySQL's own issues or other database system has them too?

Whatever the problem is, you can probably fix it by optimising your installation. In most cases this can be done by tuning either MySQL or the OS.

In the case of needing a very large amount of RAM, you will need to switch to 64-bit if you're currently on 32-bit. This needs an OS reinstall hence will take down time - I recommend that you install one or more readonly replication slaves in the meantime, and switch to one of those while you're upgrading (You can mix 32 and 64 bit databases in replication).

Note that on 32 bit systems, if you're using InnoDB, the maximum practical connection limit is about 900. But you may hit other limits first (e.g. address space for buffer size)

If you have a database server which NEEDS to serve more than 900 connections, you pretty must MUST switch it to 64-bit (which you probably want to do for performance anyway).

I used Interbase/Firebird before I switched to MySQL. Interbase/Firebird early versions missed something very important, such as AUTO INCRESEMENT VALUE. They didn't have it.

Firebird has no auto increment feature; this is not because they "forgot" to put it in, it was a design decision - they don't need it (they have generators instead).

(I don't know why they missed this simple but very important feature.) Or in older version, they didn't have the "select limit" function.

I believe it exists now.

In any case, Firebird is very un-userfriendly and featureless compared with MySQL (but has a very nice storage engine).

But years ago, they also had a lot of database features MySQL doesn't have.

No, they have very few features that MySQL doesn't have. Also, Firebird only has an experimental 64bit port, which makes it pretty much useless for large data.

Plus, I had quit a few sites running on InterBase/Firebird before, I never experienced the database problems such as too many connections, or too many open files. InterBase/Firebird were running very stable. I don't know if it is because that Interbase/Firebird is stable or if my database traffic is smaller than today's my mysql database traffic.

That is no basis for a comparison 🙂

It sounds like you have a performance problem which needs to be addressed by your DBA tuning your server and/or additional hardware, 64bit etc.

It may also be that you've got some architectural problem, e.g. web server keeping too many open connections - you will want to fix that first.

Mark

blackhorse

Thanks Mark. My sites are not with high traffic. They are usually for local small to middle size businesses. And the hosting company reset up mysql and apache. I was concerned because the system was fine without big changes (a few small traffic sites added and that is it.) but suddenly giving me these problems.

Also thanks for explain Interbase/Firebird to me. By the way what kind decision for a company open its system's source codes and then close it in the new versions? Like offering free products to attract clients and then sell them the (new) products later.

MarkR

blackhorse wrote:
Also thanks for explain Interbase/Firebird to me. By the way what kind decision for a company open its system's source codes and then close it in the new versions?

Open source is NOT the same as "free as in beer".

Once you've open sourced something, you can't then take it away again. A company COULD make a closed-source fork of an existing open source product and sell it under a commercial licence, but that wouldn't affect existing users.

As far as I'm aware, Borland are doing nothing of the sort with Firebird, it is wholly open source, free to use, and the only chargeable things are extras and services which are completely optional.