Laravel Migrations/db:seed超级慢 [英] Laravel migrations/db:seed super slow

问题描述

我最近从旧的Windows计算机升级到了gen. 4个I7 Ubuntu 15.04

运行就像梦一样，好吧……除了任何接触数据库的Laravel工匠命令要花一百万年才能完成，而我的旧计算机却可以在几秒钟内完成所有这些命令.

主要区别在于，我不是XAMPP，而是将MySQL作为本地服务运行.

在旧计算机上，我还可以看到迁移过程在屏幕上的显示速度如何缓慢地显示出来，而现在大约需要2到5分钟，完成后立即显示整个迁移列表. 这可能表明某个地方存在瓶颈.

有人在互联网上某处说使用 '127.0.0.1' 而不是 'localhost' DSN分辨率.没解决.

确保不是我遇到的问题

    $time = microtime(true);

    //also with host=127.0.0.1
    new PDO("mysql:host=localhost;dbname=some_db", "username", "password");

    //some simple queries here...

    //yielded similar marginal times
    echo microtime(true) - $time;

〜halp

---

修改

我从框架收集了每个查询的运行时间，进行了很多的迁移，分析了我发现没有任何异常慢的数据(就像查询运行了一分钟).

此处是数据链接.实际的查询被遗漏了，对我的雇主有利.

尽管有许多查询运行一秒钟或更长时间，但最慢的number 221却需要花费3.5秒的时间.

我大约有一百次迁移(我知道)，这就是问题，如果每次迁移花费一秒钟，我将等待大约一分半钟才能完成迁移，但是通常需要4到5分钟.

根据Oliverpool的建议，我通过终端在mysql上重播查询来进行一些性能分析，但是我不必等待超过一秒钟的时间，平均查询时间约为一秒钟. /p>

这是最慢查询的配置文件图片(迁移3.5秒，在图片中不接近一秒钟)

也许这指向了联系?顺便说一句，慢速查询日志在运行迁移后为空.

此外，我现在正在使用laravel 5，其行为是相同的.我不知道该如何排除连接故障，所以我想应该进行一些谷歌搜索，欢迎提出任何建议.

谢谢.

解决方案

在这种情况下，我认为需要增加缓冲池的大小.设置以下内容:

[mysqld]
innodb_io_capacity = 2000
innodb_read_io_threads = 64
innodb_thread_concurrency = 0
innodb_write_io_threads = 64

I recently upgraded from my old Windows computer into a gen. 4 I7 Ubuntu 15.04

Runs like a dream, well...Except that any Laravel artisan command that touch the database takes a million years to complete, while my old computer performed any of those commands in seconds.

The major difference is that instead of XAMPP I'm running MySQL as a local service.

Also in my old computer I could see how migrations slowly showed on screen as they appear to be processed while now, it takes like 2~5 minutes and when done the whole migration list is shown at once. This could indicate a bottle neck of sort somewhere.

Somewhere around the internet someone said to use '127.0.0.1' instead of 'localhost' because of DSN resolution. Didn't solve it.

To make sure that's not the issue i ran

    $time = microtime(true);

    //also with host=127.0.0.1
    new PDO("mysql:host=localhost;dbname=some_db", "username", "password");

    //some simple queries here...

    //yielded similar marginal times
    echo microtime(true) - $time;

~halp

---

Edit

I collected the running times for each query from the framework, I have LOTS of migrations, analyzing the data I found there's nothing insanely slow (like a query running for a minute).

Here's a link with the data. Actual queries were ommited in favor of my employer.

Although there's many queries running for a second or longer, the slowest, number 221 takes a whoping 3.5 seconds.

I have around a hundred migrations(I know) but here's the thing, if each migration took a second I'd be waiting around a minute and a half to migrate, but it often takes from 4 to 5 minutes.

As suggested by oliverpool, I did some profiling by replaying queries on the mysql via terminal, but I never had to wait longer than a second with the average query taking around thenth of a second.

Here is a pic of the profiling for the slowest query (3.5secs in migration, not close to a second in the pic)

Maybe this points towards the connection? By the way the slow query log is empty after running the migrations.

Also, I'm using laravel 5 now and the behaviour is the same. I dont know how to trouble shoot connection so I guess some googling is in order, any suggestions are welcome.

Thanks in advance.

解决方案

In this case, I think that the buffer pool size increment is required. Set the following:

[mysqld]
innodb_io_capacity = 2000
innodb_read_io_threads = 64
innodb_thread_concurrency = 0
innodb_write_io_threads = 64

这篇关于Laravel Migrations/db:seed超级慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！