Slow performance MySql

问题:

I'm trying out performance of a system I'm building, and it's really slow, and I don't know why or if it should be this slow. What I'm testing is how many single INSERT I can do to the database and I get around 22 per second. That sounds really slow and when I tried to do the inserts i a singel big SQL-query I can insert 30000 records in about 0.5 seconds. In real life the inserts is made by different users in the system so the overhead of connecting, sending the query, parsing the query etc. will always be there. What I have tried so far:

• mysqli with as little code as possible. = 22 INSERT per second
• PDO with as little code as possible. = 22 INSERT per second
• Changing the connection host from localhost to 127.0.0.1 = 22 INSERT per second
• mysqli without statement object and check for SQL-injection = 22 INSERT per second

So something seams to be wrong here.

System specs:

• Intel i5
• 16 gig ram
• 7200 rpm diskdrive

Software:

• Windows 10
• XAMPP, fairly new with MariaDB
• DB engine innoDB.

The code I used to do the tests:

$amountToInsert = 1000;
//$fakeData is an array with randomly generated emails 
$fakeData = getFakeData($amountToInsert); 
$db = new DatabaseHandler();
for ($i = 0; $i < $amountToInsert; $i++) {
    $db->insertUser($fakeUsers[$i]);
}
$db->closeConnection();

The class that calls the database:

class DatabaseHandler {
    private $DBHOST = 'localhost';
    private $DBUSERNAME = 'username';
    private $DBPASSWORD = 'password';
    private $DBNAME = 'dbname';
    private $DBPORT = 3306;

    private $mDb;

    private $isConnected = false;

    public function __construct() {
        $this->mDb = new mysqli($this->DBHOST, $this->DBUSERNAME
                              , $this->DBPASSWORD, $this->DBNAME
                              , $this->DBPORT);
        $this->isConnected = true;
    }

    public function closeConnection() {
        if ($this->isConnected) {
            $threadId = $this->mDb->thread_id;
            $this->mDb->kill($threadId);
            $this->mDb->close();
            $this->isConnected = false;
        }
    }

    public function insertUser($user) {
        $this->mDb->autocommit(true);
        $queryString = 'INSERT INTO `users`(`email`, `company_id`) '
                        .'VALUES (?, 1)';
        $stmt = $this->mDb->prepare($queryString);
        $stmt->bind_param('s', $user);
        if ($stmt->execute()) {
            $stmt->close();
            return 1;
        } else {
            $stmt->close();
            return 0;
        }
    }
}

The "user" table has 4 columns with the following structure:

• id INT unsigned primary key
• email VARCHAR(60)
• company_id INT unsigned INDEX
• guid TEXT

I'm at a loss here and don't really know where to look next. Any help in the right direction would be very much appreciated.

回答1:

Like it's explained in the comments, it's InnoDB to blame. By default this engine is too cautious and doesn't utilize the disk cache, to make sure that data indeed has been written on disk, before returning you a success message. So you basically have two options.

1. Most of time you just don't care for the confirmed write. So you can configure mysql by setting this mysql option to zero:

   innodb_flush_log_at_trx_commit = 0
   

   as long as it's set this way, your InnoDB writes will be almost as fast as MyISAM.

2. Another option is wrapping all your writes in a single transaction. As it will require only single confirmation from all the writes, it will be reasonable fast too.

Of course, it's just sane to prepare your query only once with multiple inserts but the speed gain is negligible compared to the issue above. So it doesn't count neither as an explanation nor as a remedy for such an issue.

回答2:

Your test isn't a very good way of judging performance. Why because you are preparing a statement 1000 times. That's not the way prepared statements are supposed to be used. The statement is prepared once and different parameters bound multiple times. Try this:

public function __construct() {
    $this->mDb = new mysqli($this->DBHOST, $this->DBUSERNAME
                          , $this->DBPASSWORD, $this->DBNAME
                          , $this->DBPORT);
    $this->isConnected = true;
    $queryString = 'INSERT INTO `users`(`email`, `company_id`) '
                    .'VALUES (?, 1)';
    $this->stmt_insert = $this->mDb->prepare($queryString);

}

and

public function insertUser($user) {
    $this->stmt_insert->bind_param('s', $user);
    if ($this->stmt_insert->execute()) {
        return 1;
    } else {
        return 0;
    }
}

And, you will be seeing a huge boost in performance. So to recap, there's nothing wrong with your system, it's just the test that was bad.

Update:

Your Common Sense has a point about preparing in advance and reusing the prepared statement not giving a big boost. I tested and found it to be about 5-10%

however, There is something that does give a big boost. Turning Autocommit to off!. Inserting 100 records which previously took about 4.7 seconds on average dropped to < 0.5s on average!

$con->autocommit(false);

/loop/ $con->commit();