@limitstudios opened this Issue on January 24th 2020 Contributor

Hi,

I've setup quite a few matomo instances at our hosting company and have noticed that as soon as we upgrade a customers PHP to 7.2 or above, the matomo instance starts showing lots of "MySQL has gone away" errors and performance goes down.

As soon as we downgrade back to 7.1 these errors go away and the system functions and performs fine. With PHP 7.1 being end of life, it would probably be a good idea looking to support the newer versions and ensuring performance is on par or better, and that errors like the above don't appear.

I'm a PHP developer myself so happy to work with you guys and provide a test environment to help debug and fix.

@tsteur commented on January 26th 2020 Member

@limitstudios It be great if you look into this and find something. Have you tested if there is again a difference for PHP 7.3? Are you maybe planning to give it a try?

Maybe PHP 7.2 regressed performance in some particular features. It's possible these performance issues only apply when using specific features/configurations. Some issues might only happen on a specific load etc. While we do support newer PHP versions, it's impossible for us to do advanced performance/load tests for different PHP versions unfortunately.

@limitstudios commented on January 27th 2020 Contributor

Hi @tsteur I would imagine there is, from my testing on a live environment it seems to affect 7.2 and 7.3.

I've almost got my profiling working and I'm able to reproduce the performance difference locally so will let you know what I find.

@Findus23 commented on April 20th 2020 Member

There is a suspiciously large amount of people reporting something similar to this:
After updating from PHP<7.2 to any version >=7.2, they get a lot of MySQL has gone away errors to the point where Matomo is unusable.
My initial guess was that someone had a custom fix in the old PHP config and didn't apply it to their new PHP config after updating, but there are too many reports for this to accidentally happen.

Another common thing I see, is that this happens more often on shared hosters, so we can't get more information about the MySQL setup.

Some examples:

If someone has a clue on how to reproduce this or what could be causing this change, please comment.

@limitstudios commented on April 20th 2020 Contributor

I can provide a shared hosting environment where this is reproducible if it helps?

@Findus23 commented on April 20th 2020 Member

@limitstudios Is your shared hoster by chance in Germany? Maybe it is even the same shared hoster for everyone reporting the issue?

@limitstudios commented on April 20th 2020 Contributor

Nope UK based it could be they all have the same CPanel setup and setting the website version from <7.2 to >=7.2 makes something trigger unexpectedly

@Findus23 commented on April 24th 2020 Member
@tsteur commented on April 26th 2020 Member

Interesting. If this indeed improves it, might be worth switching to nd_mysqli or nd_pdo_mysql fyi @mattab

@limitstudios commented on April 26th 2020 Contributor

This does seem somewhat connected, the options in that forum thread didn't fix it for me; however these steps did

  • PHP Extensions Enabled
    • mysqli
    • mysqlnd
    • pdo_mysql

Now when I was getting a lot of the "MySQL has gone away errors" my adapter in config.ini.php was "MYSQLI" when I removed this line, which I think defaults to "PDO_MYSQL" the errors went away under 7.2 and 7.3 (currently on 7.3.17)

And I can access the system config status page and the widgets on the dashboard without any "MySQL has gone away" errors, I'll continue to monitor the beginning of this week when the data collection starts to ramp up again.

@louiswolf commented on May 8th 2020

For me the nd_mysqli solution seems to work for PHP 7.4.5

This might be a hint in the right direction:
https://forum.matomo.org/t/php-version-change-causes-server-gone-away-error/36978/2?u=lukas

I've been fooling around with PHP and PHP-CLI versions to solve an old archiving issue (https://forum.matomo.org/t/mysql-server-has-gone-away/31760/8) that had slipped my mind, but returned after upgrading PHP on several domains.

The issue seems to appear exclusively when there are archiving tasks, breaking both core:archive (mysql server has gone away) and the web interface (all websites).

I also noticed 2 ways in which the archive task (triggered by cron) fails:

  • sometimes it will just hang consuming a bit of memory, which after a few crons will consume a lot of memory.
  • In other cases the cronjob just finishes with errors.

The above seems dependent on which combination of PHP versions I set. I'm using cpanel, where I can set PHP for both domains and CLI. However, I always get one or more Error: Got invalid response from API request: caused by a Mysqli prepare error: MySQL server has goneaway.

Not sure of the above is helpful, but let me know if I can provide you with more information.

@Findus23 commented on May 9th 2020 Member

One more user reported back that switching from MySQLi to PDO fixed the issue:
https://forum.matomo.org/t/problems-with-php-7-2/36803/15?u=lukas

@limitstudios commented on May 9th 2020 Contributor

@Findus23 I wonder if PDO has some sort of connection cleanup/closing where as the MySQLi method doesn't do this in some cases?

Powered by GitHub Issue Mirror