Devilbox: 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: https://github.com/cytopia/devilbox/pull/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

[cytopia]

Is the query slow running this directly against MySQL (phpMyAdmin or mysql cli`) or is it slow because of PHP running the query against MySQL?

[areddxx]

In phpMyAdmin and through mysql cli I'm able to run things smoothly, so I think you've actually brought up a good point. It looks like the issue is likely due to something with PHP running queries.

For example, If I change content in my CMS, like remove 5 entries, it takes a few minutes and can time out if I try to remove 10+ entries. Through phpmyadmin I can clear a whole table with no trouble.

Also I imported a new db through cli today, it was about 3gb and imported in 15 min or so.

My php memory limit is set to 4096M is there anything else you would recommend increasing or changing?

[cytopia]

Also make sure in your configuration that php connects to MySQL via mysql hostname instead of 127.0.0.1 (which is still wrongly put in the documentation).

[cytopia]

@Cipa I recently switched php-fpm from dynamic to ondemand which will use very little RAM at first. This might (or might not) impact PHP performance on first calls. To set it back to dynamic or even static follow this general approach: https://devilbox.readthedocs.io/en/latest/configuration-files/php-fpm-conf.html#change-child-process-on-pool-www-for-php-5-6

[Cipa]

For me the queries are fast. It's page execution that is slow. Maybe sending data from container to another, or file read?
Could be slow php, will try the ondemand

[cytopia]

will try the ondemand

Ondemand is currently set, you should give it a shot with dynamic or static

[areddxx]

@cytopia I changed the mysql settings to connect to the hostname, that helped but it was very minimal.

I'm running php 7.2, how do I change from ondemand to static in php 7.2?

[cytopia]

@Cipa

Hmm, I am not sure I running php-fpm, I am still on php-ini-5.6. Could that be an issue?

If you're using the Devilbox, you're running php-fpm.

---

@areddxx

I'm running php 7.2, how do I change from ondemand to static in php 7.2?

The link above shows you how to do it for 5.6, you just have to replace 5.6 with 7.2 for the example.

[Cipa]

@cytopia after changing to dynamic shouldn't we also update the devilbox-fpm.conf-default file, pm = value.

I also see only
devilbox-fpm.conf-pm_dynamic and
devilbox-fpm.conf-pm_ondemand

For static only, seems like the file is missing, or maybe pm= static is not needed

Anyway it did not work for me

[cytopia]

Everything not ending by (.conf) are just examples for convenience. You can copy any of these files to make them end by .conf and adjust the values according to your needs.

[areddxx]

@cytopia That looks like it also did not work for me, some pages seem to load a little faster but its still _slightly_ slower than our live servers.

edit I take that back, things are actually much slower, some page load times are double after switching to dynamic.

Is there a chance that somehow my docker system allocated RAM etc did not get tied to my dockers? Is it possible to allocate specific dockers with more resources?

[cytopia]

slightly slower

The slightly was only meant sarcastically as I saw it was way slower. ;-)

What will probably solve all your performance issues is docker-sync, which I did not have the chance yet to implement due to lack of MacOS: https://github.com/cytopia/devilbox/issues/105

[areddxx]

All good on your sarcasm, I was being sarcastic as well, I appreciate your help!

What're your thoughts on a timeline for implementing docker-sync? Or is it something I can try now?

[cytopia]

Timeline was supposed to be Q1 this year - at least to have a prototype working to be tested. I do however rely on others with access to MacOS and Windows to try/test this properly

[areddxx]

If you have a docker-sync.yml that I could test I have docker-sync installed. If not I will try to set it up, I can test anything you'd like me to I've been using devilbox for a while and never run into these issues so I think this is only an issue when you have large projects.

[cytopia]

Unfortunately, I haven't really read into docker-sync, so not much work done on my side and no docker-sync.yml available yet.

[cytopia]

@areddxx @Cipa
I will close this issue in favour of: https://github.com/cytopia/devilbox/issues/105 (this is the issue where all docker-sync related discussions are)