How do I know if db->query_write is reusing a SQL connection ?

[mihai11]

Hi,

I read somewhere that opening a connection for each transaction is an operation should be avoided because it can be intensive. I need to insert a big number of rows in a table so I need to find out if “db->query_write” is reusing the connection or not. (this is the first optimization that comes to my mind)

Regards,
Razvan

[Dismounted]

vBulletin only opens one connection per page and uses that until the end of execution. (Different if you turned on persistent connections in config.php.)

[mihai11]

vBulletin only opens one connection per page and uses that until the end of execution. (Different if you turned on persistent connections in config.php.)

So, if I call in a for loop the function query_write 1000 times then it will use the same connection, right ?

How can I be sure about this ? Is there a tool on mysql side to check how many connection were created ? (like a log file for connections)

One more thing: what about persistent connections ? I assume that you are talking about this:


// ****** MASTER DATABASE PERSISTENT CONNECTIONS ******
// This option allows you to turn persistent connections to MySQL on or off.
// The difference in performance is negligible for all but the largest boards.
// If you are unsure what this should be, leave it off. (0 = off; 1 = on)
$config['MasterServer']['usepconnect'] = 0;


What if I enable persistent connections ? What is changing in this case ?



Regards,
Razvan

[Dismounted]

So, if I call in a for loop the function query_write 1000 times then it will use the same connection, right ?

How can I be sure about this ? Is there a tool on mysql side to check how many connection were created ? (like a log file for connections)
You can be sure because the code only calls connect ONCE and it specifies the link variable for queries.
One more thing: what about persistent connections ? I assume that you are talking about this:


// ****** MASTER DATABASE PERSISTENT CONNECTIONS ******
// This option allows you to turn persistent connections to MySQL on or off.
// The difference in performance is negligible for all but the largest boards.
// If you are unsure what this should be, leave it off. (0 = off; 1 = on)
$config['MasterServer']['usepconnect'] = 0;


What if I enable persistent connections ? What is changing in this case ?
Persistent connections are links that do not close when the execution of your script ends. When a persistent connection is requested, PHP checks if there's already an identical persistent connection (that remained open from earlier) - and if it exists, it uses it. Also, as the config file says, it will not boost performance except on the largest boards.

[mihai11]

I did the following experiment:


I inserted 100.000 rows in a database table called "event_history" by calling the function "query_write" 100.000 times. The total execution time for this operation is of 217.99 seconds.

I copied the data from the above table (event_history) to another table (event_shadow) using an INSERT-SELECT operation, like this one:



INSERT INTO event_shadow (user_id, event_day, event_time, event_type, multiplication_factor, vp, red_points, blue_points, green_points, yellow_points, black_points)
SELECT user_id, event_day, event_time, event_type, multiplication_factor, vp, red_points, blue_points, green_points, yellow_points, black_points
FROM event_history;


Here it comes the big surprise: the second operation is finished in 2.15 seconds - that is about 100 times faster. What is the explanation for this ? Both operations perform 100.000 inserts !



Regards,
Razvan

[Eikinskjaldi]

Well, IMHO,

Every time you perform an insert operation, mysql has to adjust the index to cope with the new value. the table copy operation performs the insert as a single transaction, and then does a single re-index at the end.

You can achieve the same result by doing a multiple insert.

insert into table (f1,f2,f3) values (v1,v2,v3),(x1,x2,x3),(y1,y2,y3)

You can also use alter table tablename disable keys before the bulk insert, and alter table tablename enable keys once it is done.

Speed improvements will depend on what fields you have indexed.