Bug 1026968 - mysql performance does not scale
Summary: mysql performance does not scale
Keywords:
Status: CLOSED WONTFIX
Alias: None
Product: OpenShift Online
Classification: Red Hat
Component: Image
Version: 2.x
Hardware: Unspecified
OS: Unspecified
unspecified
medium
Target Milestone: ---
: ---
Assignee: Paul Morie
QA Contact: libra bugs
URL:
Whiteboard:
Depends On:
Blocks:
TreeView+ depends on / blocked
 
Reported: 2013-11-05 18:20 UTC by Eric Rich
Modified: 2015-05-15 00:34 UTC (History)
2 users (show)

Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Clone Of:
Environment:
Last Closed: 2013-11-18 22:46:25 UTC
Target Upstream Version:


Attachments (Terms of Use)
Data set (6.81 KB, application/zip)
2013-11-06 19:42 UTC, Eric Rich
no flags Details

Description Eric Rich 2013-11-05 18:20:57 UTC
Description of problem:

When making calls to a mysql gear (using php, as an example) the calls or connections to provide adequate performance when threaded. 

How reproducible:
PHP SCRIPT: 

<?php

// Create connection
$dns = '127.10.1.2';
$port = '3306';
$user = 'admin8gNAWKu';
$pass = 'FqWI5TxtkYw8';
$db = 'mysql';

echo "DNS='$dns', PORT='$port', USER='$user', PASS='$pass', DB='$db' \n";
echo "Testing connection...";

$start = microtime(true);

$mysqli = new mysqli($dns, $user, $pass, $db, $port);

// $con=mysqli_connect($dns,$user,$pass,$db,$port);
// Check connection
if ($mysqli->connect_errno)
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  } else {
  echo "Connected!";
  }

echo $mysqli->host_info . "\n";
$result = $mysqli->query("SELECT COUNT(*) FROM user");

while ($row = $result->fetch_assoc()) {
    echo " id = " . $row["COUNT(*)"] . "\n";
}

$time_taken = microtime(true) - $start;
echo $time_taken;

?>

Steps to Reproduce:
1. rhc app create bench php-5.3 mysql-5.1
2. Copy code (script above) into gear 
   - I ssh in, create a file in app-root/data/ and copy its contents into that file. 
3. Average the time against sequential calls vs threaded calls.
   - Sequential:
for x in $(seq 1 2); do php -f test.php; done | grep TIME | awk '{ total += $2; count++ } END { print total/count }'
   - Threaded:
for x in $(seq 1 2); do php -f test.php & done | grep TIME | awk '{ total += $2; count++ } END { print total/count }'

You will see that by treading the calls (or backgrounding each test) they queue up and cause performance issues to be noticed. This is likely caused by queuing of the database connections. Are there any best practices for avoiding this, or things that can be done to improve the databases performance in threaded scenarios.

Comment 1 Eric Rich 2013-11-06 19:41:26 UTC
All I was able to get data from the customer regarding this and if I review (average the times) for sequential calls vs threaded calls the results are roughly the same. 

14:28:12 erich@fenrir:~/Downloads/run2
$ --> awk '{ total += $2; count++ } END { print total/count }' baseline_data.dat 
0.014702

14:28:29 erich@fenrir:~/Downloads/run2
$ --> awk '{ total += $2; count++ } END { print total/count }' baseline-2_data.dat 
0.0835006

14:28:35 erich@fenrir:~/Downloads/run2
$ --> awk '{ total += $2; count++ } END { print total/count }' sql1_data.dat 
0.213246

14:29:08 erich@fenrir:~/Downloads/run2
$ --> awk '{ total += $2; count++ } END { print total/count }' sql1-2_data.dat 
0.228275

14:29:12 erich@fenrir:~/Downloads/run2
$ --> awk '{ total += $2; count++ } END { print total/count }' sql2_data.dat 
0.203016

14:29:29 erich@fenrir:~/Downloads/run2
$ --> awk '{ total += $2; count++ } END { print total/count }' sql2-2_data.dat 
0.207391

14:29:36 erich@fenrir:~/Downloads/run2
$ --> awk '{ total += $2; count++ } END { print total/count }' sql3_data.dat 
0.110054

14:29:43 erich@fenrir:~/Downloads/run2
$ --> awk '{ total += $2; count++ } END { print total/count }' sql3-2_data.dat 
0.110654

This to me points to an issue with the sql queries and not with OpenShift, can engineering confirm.

Comment 2 Eric Rich 2013-11-06 19:42:11 UTC
Created attachment 820700 [details]
Data set

Data set for review (run 1 is thought to be a bad collection).

Comment 3 Paul Morie 2013-11-06 20:55:35 UTC
We will need to see the select plan for the queries the customer is using.


Note You need to log in before you can comment on or make changes to this bug.