CODEWITHSUNDEEP
databasecodeigniter
Benoit Poux
Benoit Poux

Reputation: 77

Codeigniter multiple database connection slows my pages. Do I have to close the connections? Where?

I'm using CodeIgniter 3.0 for a web app. I have 2 databases, on a same server (I guess) but with 2 different hostnames. I don't use the second database, except for one kind of user.

When this kind of user connects to the web app, the pages take a very long time to display, but for my other users, no problem, so I guess it's a multiple databases connection issue.

In my database.php file, I write the 2 arrays including the databases informations. In my model files using the second database, I just write something like that:

$db1 = $this->load->database('db1', TRUE);
...
// I do my query as usual
$db1->...
...
return $db1->get();

I do not close the connection.

Questions:

1) In each page, I use several functions using the second database. Is this issue due to theses multiple connections to my second database?

2) Do I have to close the connection in my functions's model, just before the return? Or is it beter to connect and disconnect in the controler?

3) I saw about the CI reconnect function, but how to use it well? To reconnect, I have to connect first, but where to connect first?

4) Or do you think the issue is due to something else, like some bad SQL queries?

Upvotes: 0

Views: 463

Answers (1)

DFriend
DFriend

Reputation: 8964

Let's go through your questions one at a time and I'll comment.

1) In each page, I use several functions using the second database. Is this issue due to theses multiple connections to my second database?

I say no because I have used the same multiple DB approach many times and have never seen a performance hit. Besides, if a performance hit was a common problem there would be lots of online complaints and people looking for solutions. I've seen none. (And I spend way too much time helping people with CodeIgniter.)

2) Do I have to close the connection in my function's model, just before the return? Or is it better to connect and disconnect in the controller?

If closing the connection did help then the answer to when to do it depends on the overall structure of the logic. For instance, if a controller is using several methods from the same model the create a page then close the connection in the controller. On the other hand, if only one model method is used to create a given page then close the connection in the model.

What you don't want to do is repeatedly open and close a DB connection while building a page.

3) I saw about the CI reconnect function, but how to use it well? To reconnect, I have to connect first, but where to connect first?

reconnect() is only useful when database server drops the connection due to it being idle for too long. You'll know you need to use reconnect() when you start getting "no database connection" or "cannot connect to database" errors.

4) Or do you think the issue is due to something else, like some bad SQL queries?

Because the other approaches you ask about won't help this is the strongest possibility. Again, my reasoning is because I've never had this problem using multiple database connections.

I suggest you do some performance profiling on the queries to the second database. Check out the following bits of documentation for help with that.

Profiling Your Application

Benchmarking Class

There are lots of reasons for slow page loads and the use of the second DB might just be a coincidence.

About Closing Connections

The question is, "If I do not close the DB connection by myself, CI will do it for me, but when?".

The answer is found in the PHP manual, "Open non-persistent MySQL connections and result sets are automatically destroyed when a PHP script finishes its execution." That quote is from the mysqli documentation, but, to the best of my knowledge, it is true for all of PHP's database extensions, i.e. Oracle, Mssql, PDO, etc.

In short, DB connection closing is baked into PHP and happens when the script is done. In CI, the script is done very shortly after the Controller returns. (Examine the end of /system/core/Codeigniter.php if you want to see what happens when the controller returns.) In effect, a Controller returning is, more or less, another way of saying "after the page is loaded".

Unless you happen to be using persistent connections (usually a bad idea) you seldom need to explicitly close DB connections. One reason to close them yourself is when a lot (really a lot) of time is required to process the query results. Manually closing connections will help assure the DB server won't reach it's connection limit when the web server is under heavy usage.

To determine what "really a lot" means you have to consider multiple factors, i.e. how many connections the database server allows, how the time-to-process compares to the DB idle connection dropout duration, and the amount of traffic the site needs to handle.

There are likely other considerations too. I'm not a database performance tuning expert.

Upvotes: 2

Related Questions