Extremely slow database

[areddxx]

ISSUE TYPE

• Question

SUMMARY

Extremely slow database, using mariadb-10.3, apache 2.4, php 7.2 with Expression Engine 4.3.6

I'm on macOS and have enabled the cache as instructed in the docs.

This is from my debugging:
Database Execution Time: 9.7989
Total Execution Time: 11.0985

Goal

Is there a way to speed up this? My database is about 1.7gb and I'm doing everything I can to reduce the size but need to reduce the database execution time by about half.

Thank you.

[cytopia]

@areddxx Docker volume performance for databases should be resolved in this PR: #383

Just ensure to backup your database first, before checking out this branch and then import it again: https://devilbox.readthedocs.io/en/latest/maintenance/backup-and-restore-mysql.html#mysqldump-secure

The branch will be merged soon and is currently constantly kept up-to-date, so it is safe to use for now. Let me know if this improves your performance and by what degree.

[areddxx]

@cytopia Thank you for the quick response, I think this PR will resolve things for some other users but I'm still having the same issue.

I saw no improvement in speed, I tried your PR and I also allocated 32gb, 8 CPUs and 4gb of Swap to docker.

Any other thoughts?

Does this error log provide any helpful information?
error.log

Thanks

[cytopia]

When you have those data on a native MySQL (on your host), is it as slow as via the dockerized version?

[areddxx]

Heres a comparison between local and our staging server for the exact same page using the same database and files in both environments.

Staging
Load Time: 1.4559s
Database Execution Time: 0.4574
Total Execution Time: 1.4559

Local
Load Time: 36.5792s
Database Execution Time: 29.3461
Total Execution Time: 36.5792

[cytopia]

This is indeed slightly slower ;-)

Do you have any custom/specific my.cnf configuration on your local database which is different from the dockerized DB?

[Cipa]

Hi, I experience the same issue with codeigniter(I think) and I have a few test cases
I believe it's connected to the complexity of the code, but I can't be sure, I just can't point to the problem, it's like the more complex the code, the more queries are executed, the execution time increases more or less exponentially

In my case I use Eloquent with Codeigniter

Program::orderBy('year', 'DESC')->get()
Total Execution Time | 2.8261

Program::orderBy('year', 'DESC')->get()->unique('id');
Total Execution Time | 6.6038
Unique() is just getting the full result(which is an object/array) and removing duplicated ids(I use multiple keys and I only want the first). More here https://laravel.com/docs/5.7/collections#method-unique. Maybe the cpu is slow but Docker is using 4CPUs 8G mem. I noticed that by increasing from 1CPU to 4CPUs the execution time was faster but still slow as you can see.

Program::orderBy('year', 'DESC')->with(array('type'))->get()->unique('id');
Total Execution Time | 8.4285

Program::orderBy('year', 'DESC')->with(array('offers'))->get()->unique('id');
Total Execution Time | 12.308

Program::orderBy('year', 'DESC')->with(array('offers', 'type'))->get()->unique('id');
Total Execution Time | 14.4322

Program::orderBy('year', 'DESC')->with(array('offers', 'type'))->get()
Total Execution Time | 10.8079

I the last 2 and first 2 queries just by adding the unique() I get a 4 sec delay. So no matter the complexity of the generated object to be parsed by unique the time increase is the same. what seems to matter in this case is the number of rows(~1300)

All queries should return ~1300 rows.

If I use the default codeigniter controller, not my custom one that loads a few more things
$query = $this->db->query('select * from programs order by year desc');
$results = $query->result();
Total Execution Time | 0.3846

Maybe it helps. Since it only happens on localhost for a couple of pages I can live with it

Remembered this:
Also I can't be sure but for one year I've been running a very old version of devilbox, I recently updated(December) maybe, and that's when I started to notice the slowness. So either I added a lot more complexity in my code or something is slower than before. All code is fast in production even if not optimized.

[cytopia]

Another thing you should do right away on MacOS is to add the ,cached flag to .env: https://devilbox.readthedocs.io/en/latest/getting-started/install-the-devilbox.html#osx-performance

Once done you should:

docker-compose down
docker-compose rm -f
docker-compose up

Let's see if this gives some performance boost.

Update

I'm on macOS and have enabled the cache as instructed in the docs.

Just saw it now, so nevermind that post.

[Cipa]

I added that when I updated in December. Let me test without see what happens

[Cipa]

removing ,cached, down and restarting gave the same times

MacBook-Pro:devilbox cipa$ docker-compose down
Stopping devilbox_mysql_1 ... done
Stopping devilbox_httpd_1 ... done
Stopping devilbox_php_1 ... done
Stopping devilbox_bind_1 ... done
Removing devilbox_mysql_1 ... done
Removing devilbox_httpd_1 ... done
Removing devilbox_php_1 ... done
Removing devilbox_bind_1 ... done
Removing network devilbox_app_net
MacBook-Pro:devilbox cipa$ docker-compose rm -f
No stopped containers
MacBook-Pro:devilbox cipa$ docker-compose up -d httpd php mysql
Creating network "devilbox_app_net" with driver "bridge"
Creating devilbox_bind_1 ... done
Creating devilbox_php_1 ... done
Creating devilbox_httpd_1 ... done
Creating devilbox_mysql_1 ... done

I checked my old .env, seems like MOUNT_OPTIONS was not present at that time

[areddxx]

Do you have any custom/specific my.cnf configuration on your local database which is different from the dockerized DB?

As far as I know I do not. It seems like theres something simple causing this issue but I'm just not aware of what information is needed to troubleshoot.

During my google searches I ran across a couple people saying to add barrier=0 to the disk? Is that something that may help and if so could you provide instructions on how I'd go about doing so?

Link that someone referred to that fixed their slow mysql docker issues

[Cipa]

What should be the UUID, my disk, or some other volume that is mounted?
MacOs is not using fstab by default.

[areddxx]

Below is a forum discussion where I got the link from above. I'll be honest I'm out of my element with this stuff but maybe you can pull something useful from the discussion.

Forum discussion