Stress Testing for Mysql via php

Question

I want to check Mysql Connection Limit with PHP. I inserted mysql_connect inside loop also mysql_pconnect but it doesnot increase number of connections on Mysql.... I also came to know that php doesnot support multi-threading

So how can i do it, i am optimizing mysql.ini file for max-connection and want to make sure by stress testing

Are you using msyqli? If so, why are you using `mysql_connect`? — tadman, May 08 '13 at 15:17
It is probably better to use existing benchmarking tools. Start here: http://dev.mysql.com/doc/refman/5.0/en/mysql-benchmarks.html (this is for mysql 5.0, make sure you select the right version). — Tasos Bitsios, May 08 '13 at 15:23
@tadman plz tell if there is any solution of my problem in case of mysqli_connect......i used mysql_connect instead of mysqli_connect because $new_link was supported in it.....But it($new_link) also generates new connection after closing previous i am not using any close connection command any where in my php script — Umair Aziz, May 08 '13 at 15:49
`mysqli` is a completely separate library from `mysql`, they don't share anything. You can't mix and match functions, either. Since you shouldn't be using `mysql_query` and associated functions at all in new applications, you should investigate [persistent connections](http://php.net/manual/en/mysqli.persistconns.php) for `mysqli`. — tadman, May 08 '13 at 17:44

score 2 · Answer 1 · edited May 23 '17 at 12:18

2

The fourth parameter to mysql_connect is $new_link.

By default, mysql_connect will not create a new link if given the same arguments within the same request, which is what it happening to you. Override this behaviour by setting $new_link to true.

http://php.net/manual/en/function.mysql-connect.php

Do not use mysql_ functions: The Mysql extension is deprecated and will be removed in the future. Use Mysqli or PDO.

edit: added mysql_ warning

edited May 23 '17 at 12:18

Community

1
1

answered May 08 '13 at 15:16

Tasos Bitsios

2,699
1
16
22

I know it's deprecated and i used $new_link in mysql_connect, i used mysql_connect instead of mysqli_connect because it $new_link was supported in mysqli.....But it($new_link) also generates new connection after closing previous i am not using any close connection command – Umair Aziz May 08 '13 at 15:25
If you know it's deprecated, then you know you're creating code that will self-destruct when that function is deleted from PHP. – tadman May 08 '13 at 17:44
@Engr.UmairAzizAttari "generates new connection after closing previous" are you sure about this? There's nothing about this in the docs, and AFAIK it isn't true. If the old connection handles were invalidated, that would create all sorts of problems and kind of invalidate the usefulness of $new_link option. – Tasos Bitsios May 08 '13 at 18:35

user1693885 · Answer 2 · 2013-09-25T11:31:12.273

Here's a simple stress test tool in PHP that creates a test table, loads 1000 entries into it, selects them then deletes them one by one. Run together with ab (apache benchmark), you can simulate an intensive load on your DB and compare how several servers perform. Save it as stress.php, update the connection information then run it from your browser:

<pre>
<?php
$sStart = microtime(true);

function random_str(){

$result="";
for ($i = 1; $i <= 16; $i++) {
$base10Rand = mt_rand(0, 15);
$newRand = base_convert($base10Rand, 10, 36);
$result.=$newRand;
}
return $result;

}

if ($_REQUEST['db']=='mon2') {
    mysql_connect ("server_test1", "db_user", "db_pass" || die (print 'no db');
} elseif ($_REQUEST['db']=='scooter') {
    mysql_connect ("192.168.33.3331", "umbrella", "umbrella") || die (print 'no db');
} elseif ($_REQUEST['db']=='camilla') {
    mysql_connect ("192.168.33.3332", "umbrella", "umbrella") || die (print 'no db');
} else {
    die (print 'no server');    
}
echo number_format(microtime(true) - $sStart, 2) ."s connect done\n";flush();
mysql_select_db ("test") || die (print 'no select_db');
$tbl_number=rand(1000, 9999);

$query = "CREATE TABLE IF NOT EXISTS test_tbl_$tbl_number (test1 int(100) NOT NULL, test2 int(100) NOT NULL, test3 int(100) NOT NULL, test4 int(100) NOT NULL) ENGINE=MyISAM DEFAULT CHARSET=latin1;";
mysql_query($query) || die (print 'no query');
echo number_format(microtime(true) - $sStart, 2) ."s CREATE done\n";flush();
$aa=array();
for ($i=1; $i <= 1000; $i++) {
$a=random_str();
$aa[]=$a;
$b=random_str();
$c=random_str();
$d=random_str();
$query="INSERT INTO test_tbl_$tbl_number SET test1='$a', test2='$b', test3='$c', 
test4='$d'";
mysql_query($query) || die (print 'no insert');
}
echo number_format(microtime(true) - $sStart, 2) ."s INSERT (1.000) 
done\n";flush();

$query="SELECT SQL_NO_CACHE * FROM test_tbl_$tbl_number";
mysql_query($query) || die (print 'no select_db');
echo number_format(microtime(true) - $sStart, 2) ."s SELECT (1) done\n";flush();

foreach ($aa as $value) {
$query="DELETE FROM test_tbl_$tbl_number WHERE test1='$value'";
mysql_query($query) || die (print 'no delete');
}
echo number_format(microtime(true) - $sStart, 2) ."s DELETE (1.000) 
done\n";flush();

$query="DROP TABLE test_tbl_$tbl_number";
mysql_query($query) || die (print 'no drop');
echo number_format(microtime(true) - $sStart, 2) ."s DROP (1.000) done\n";flush();

?>

Not sure why the earlier down vote but this is exactly what I was searching for to test my PHP + MYSQL load (using ab). Maybe the down vote because its not copy/pasteable... — Will Bowman, Apr 28 '15 at 00:14

score 1 · Answer 3 · answered May 08 '13 at 15:19

1

Just by creating n connections to MySQL doesn't mean you'll get any meaningful results. If you have 1000 connections as your max_connections variable in MySQL and you actually do establish 1000 connections, the 1001st will be refused.

Also, there can be 999 connections spending no MySQL resources and 1 can be running super-expensive query.

In regards to PHP and multithreading - sadly, you came to know wrong. PHP does support user-land multithreading.

answered May 08 '13 at 15:19

N.B.

13,688
3
45
55

Keeping in view http://dev.mysql.com/doc/refman/5.7/en/too-many-connections.html i am want to make connections and execute some queries on DB i have changed limits also but how can i test that – Umair Aziz May 08 '13 at 15:22
Just lower the connection limit to 2, connect to MySQL via GUI and then via PHP and test what you need. There's no need to actually exceed 151 connections. – N.B. May 08 '13 at 15:25
With reference to my link Windows is limited to (open tables × 2 + open connections) < 2048 .... i have random cases for different cases and want to exceed limit to 2000 concurrent connection .... if u know any stressing logic in php or any other software to bombard traffic with multiple connection on mysql then plz tell – Umair Aziz May 08 '13 at 15:29

Stress Testing for Mysql via php

3 Answers3