Open and close Database

The_Referee

We are running a server with a lot of question to the database "all the time".

We start files with opendatabase and end them with closedatabase.

Our system is not working as smooth as we want, we think it looks like the database is "locked" for queries and dont allow others to access it. This means that with 50+ users surfing the site, it slows down considerable.

We are there for looking for errors, and wonders if there is a better way to access the database.

Sxooter

Simply having 50 connections or so open to your database should not make it slow.

What are you doing in these connections, transactions, table locks, select for update? Things like that can cause contention. Persistant connections with such statements can make them even worse. Note that if you are using persistant connections, the connections do NOT close, even when dbname_close() is called.

More info might help us help you a bit.

The_Referee

Thanks for responding to my rather unspecific post. Its lack of knowledge that makes our questions fluffy.

We are doing "select", "update" and "insert". Usually we select one table, but sometimes we select from two tables. We make inserts with "values". We make simple updates on a singe table. We are not knowlingly locking tables.

If this is causing contention...how to avoid it? We are currently running MySQL 3.23.52. We are planning to upgrade MySQL shortly. Would this help?

We are not aware what "persistant connections" would be.

Greatful for answer....🙂

Sxooter

If you're doing a fair bit of updates / inserts, MySQL will be slow, as it's really not optimized for heavy update traffic. Are your tables innodb or plain or myisam? innodb may be better for what you're doing as they support row level locks.

Basically, any update / insert in MySQL tends to lock the whole table for a short period of time. A lot of updates results in very slow performance as the table locking has to be done serially.

If you're going to handle a large amount of update traffic, look into either Postgresql (best solution) or firebird / interbase (second best solution) for better performance.