性能低下的MySql [英] Slow performance MySql
问题描述
我正在尝试构建的系统的性能，它确实很慢，而且我不知道为什么或应该这么慢.我正在测试的是我可以对数据库执行多少次单个INSERT，并且每秒可以达到22个左右.这听起来真的很慢，当我尝试用一个大型的SQL查询进行插入时，我可以在大约0.5秒内插入30000条记录.在现实生活中，插入是由系统中的不同用户完成的，因此连接，发送查询，解析查询等开销总是存在的.到目前为止，我已经尝试过:
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，代码尽可能少. =每秒22次插入
- PDO，代码尽可能少. =每秒22次插入
- 将连接主机从localhost更改为127.0.0.1 =每秒22 INSERT
- 不带语句对象的mysqli，并检查SQL注入= 22 INSERT每秒
所以这里有些错误.
系统规格:
- 英特尔i5
- 16 gig ram
- 7200 rpm磁盘驱动器
软件:
- Windows 10
- XAMPP，对于MariaDB来说还很新
- 数据库引擎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();
调用数据库的类:
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;
}
}
}
用户"表有4列，其结构如下:
The "user" table has 4 columns with the following structure:
- id INT无符号主键
- 通过电子邮件发送VARCHAR(60)
- company_id INT无符号INDEX
- 指导文本
我在这里很茫然，不知道下一步该怎么看.在正确方向上的任何帮助将不胜感激.
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.
推荐答案
就像注释中解释的那样，应该归咎于InnoDB.默认情况下，此引擎过于谨慎，并且在返回成功消息之前不会使用磁盘缓存来确保数据确实已写入磁盘.因此，您基本上有两个选择.
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.
-
大多数时候，您只是不在乎已确认的写入.因此，您可以通过将此mysql选项设置为零来配置mysql:
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
只要以此方式设置，您的InnoDB写入速度将几乎与MyISAM一样快.
as long as it's set this way, your InnoDB writes will be almost as fast as MyISAM.
另一个选择是将所有写操作包装在一个事务中.由于只需要所有写入的一次确认，因此速度也很快.
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.
这篇关于性能低下的MySql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
