Mysql Connection is rapidly increasing including Queries

Question

I have Rest api built on Codeigniter (PHP 5.5 - MYSQLi 5.6). 8 queries fired for a single Api call. The connection increases on calling other api's and connection doesn't reduce at all.

For 4 users the queries and connections were increased to 25-32. I wonder how to reduce the connection & Queries.

I have this scenario - MY_MODEL extends CI_MODEL and USER_DB extends MY_MODEL and USER_MODEL extedning the User_DB. I suspect any problem in constructor chaining. Please suggest
1. The connection & queries are increasing rapidly.
2. Have used Persistent connection in Config file no use.
3. How to reduce the mysqli connections & queries fired.
4. Do i have to forcibly close the connection after every api call or should i leave it to Codeigniter.

just get rid of pconnect – Your Common Sense Feb 24 '17 at 07:50 — Your Common Sense, Feb 24 '17 at 07:50

score 0 · Accepted Answer · edited May 23 '17 at 10:29

0

pconnect is true in your case, that is, your settings are to use a persistent connection. In this case, whenever you create a new connection, the number of connections is increased. You need to close the connection in this case. You can set pconnect to false as well, but you need to think about it before you do it. You might need a persistent connections in some scenarios. See here:

Advantages / Disadvantages of pconnect option in CodeIgniter

edited May 23 '17 at 10:29

Community

1
1

answered Feb 24 '17 at 08:17

Lajos Arpad

64,414
37
100
175

let me check and revert on this. I 'll try the close scenario as suggested. can i close the connection in Destructor of the class. – Sabarish Feb 24 '17 at 09:08
You simply don't understand what a persistent connection is – Your Common Sense Feb 24 '17 at 10:07
@Sabarish the destructor of which class? Can you tell me its name and briefly describe it? – Lajos Arpad Feb 25 '17 at 10:07
The Connection is open at my_model and followed by user_model extending the My_model, CI_model -> My_Model-> User_model. I'm calling the destructor in user_model after all operations are performed. – Sabarish Feb 26 '17 at 10:57
@Sabarish does this stop the behavior you have experienced? – Lajos Arpad Feb 27 '17 at 07:35
No, I made a singleton class for Database and connections were drastically reduced and i was connecting to database at My_model constructor, Moved to a sigleton function , now for 1000 users only 16 connections are made. Thanks guys , Really appreciate your response . – Sabarish Mar 01 '17 at 13:18
@Sabarish if one of the answers solved your problem, then you can mark it as the correct answer by clicking on the check mark to the left of the answer. – Lajos Arpad Mar 01 '17 at 13:47

score -1 · Answer 2 · answered Feb 24 '17 at 09:20

If you're running your php on bare apache, you're doing it wrong.

Given a typical web workload of many small queries, your database will be at its best with 1-2 threads per core. If you let it grow to 10 or more, first it will consume more RAM, and then you will need to worry about locking a lot more.

For similar reasons, your web server will be a lot snappier with 1-3 PHP processes per core rather than a few hundreds.

A simple way to set things up is to run your php in fastcgi mode with a server like lighttpd or nginx. These servers will handle the long slow HTTP connections, gather request data, then quickly run the PHP code, buffer the response, and free up the php interpreter and database connection while they trickle the response through the client's HTTP connection.

You can then set a limit on the number of fastcgi processes in your lighttpd configuration to something small, like 3 per core. This also limits your persistent database connections.

Of course, you should optimize your SQL queries! But since you don't give any information about that...

I ll change the server to lighttpd or nginx as sugested. Is it a good practise to close the connection in destructor. Please suggest. — Sabarish, Feb 24 '17 at 09:57
Since you are going to use persistent connections, I believe it is not needed. — bobflux, Feb 24 '17 at 10:14
I made a singleton class for Database and connections were drastically reduced and i was connecting to database at My_model constructor, Moved to a sigleton function , now for 1000 users only 16 connections are made. Thanks guys , Really appreciate your response . — Sabarish, Mar 01 '17 at 13:19

Mysql Connection is rapidly increasing including Queries

2 Answers2