CodeIgniter Multiple Databases Persistent Connection?

Question

I'm using multiple database in my CodeIgniter application and have been reading a lot that persistent connections should be turned off.

Why is this measure recommended and is this still necessary in the newest version, 2.0.2?

I'm doing things like

$db2 = $this->load->database("dbname", TRUE);

Comitative

See this: [pconnect option in CodeIgniter][1]: #1831330 – Metamorphism

Well, this is obviously duplicate. @Metamorphism - nice catch! – Coreligionist

Can someone close this please since it has been answered, though maybe not with the answer the OP hoped for. – Gripping

I'm guessing because there were previous answers to this which have recently been deleted by the mods that this is not considered a duplicate because of the bold items. – Seeley

Answer 1

Code Igniter Documentation Doesn't Explain

Unfortunately, the Code Igniter documentation for 2.0.2 does not explain why they should be turned off. It merely explains that there is a setting to do so. The reasoning for this is most likely because it really isn't a Code Igniter functionality but more of an underlying PHP/MySQL functionality. PHP has a very good and detailed page in their documentation about persistent connections.

The Explanation From a Server Admin (me)

Basically, it comes down to performance. If you have a small number of users that are located in California and your MySQL database server is in Switzerland, there will be a substantial overhead to make a connection to the MySQL server (when compared to a connection to MySQL on the same server in California). Having a persistent connection will prevent the overhead of re-connecting every time you want to look something up with MySQL. There's also many other issues that can cause connection overhead, such as the way the server or database is configured. Besides performance, it can also cause issues if a connection closes improperly, leading to locked tables.

Why It's Recommended to be Turned Off

The reason this setting is not ideal for most servers is that if you have more than 60 users using your database simultaneously, or what ever your MySQL configuration has set as the maximum number of connections, you will quickly reach the maximum number of connections and this could cause database errors or server crashes etc.

Seeley

Answer 2

This is default database : $db['default'] = array( 'dsn' => '', 'hostname' => 'localhost', 'username' => 'root', 'password' => '', 'database' => 'mydatabase', 'dbdriver' => 'mysqli', 'dbprefix' => '', 'pconnect' => TRUE, 'db_debug' => (ENVIRONMENT !== 'production'), 'cache_on' => FALSE, 'cachedir' => '', 'char_set' => 'utf8', 'dbcollat' => 'utf8_general_ci', 'swap_pre' => '', 'encrypt' => FALSE, 'compress' => FALSE, 'stricton' => FALSE, 'failover' => array(), 'save_queries' => TRUE ); Add another database at the bottom of database.php file $db['second'] = array( 'dsn' => '', 'hostname' => 'localhost', 'username' => 'root', 'password' => '', 'database' => 'mysecond', 'dbdriver' => 'mysqli', 'dbprefix' => '', 'pconnect' => TRUE, 'db_debug' => (ENVIRONMENT !== 'production'), 'cache_on' => FALSE, 'cachedir' => '', 'char_set' => 'utf8', 'dbcollat' => 'utf8_general_ci', 'swap_pre' => '', 'encrypt' => FALSE, 'compress' => FALSE, 'stricton' => FALSE, 'failover' => array(), 'save_queries' => TRUE ); In autoload.php config file $autoload['libraries'] = array('database', 'email', 'session'); The default database is worked fine by autoload the database library but second database load and connect by using constructor in model and controller... db2 = $this->load->database('second', TRUE); } public function getsecondUsers(){ $query = $this->db2->get('members'); return $query->result(); } } ?>

Dessau