Long loading of any matomo page

Support & Bugs
1

Very long loading of any Matomo pages (from a minute to 5).

Sometimes I get 502/504 http errors, but this behavior is not predictable.

Often before problems I see a log:
Unable to fetch site type for host “<example.com>”: /opt/bitnami/matomo/core/Http.php(740): curl_exec: Operation timed out after 5000 milliseconds with 0 out of 0 bytes received. Hostname requested was: <example.com> [Query: ?date=yesterday&module=SitesManager&format=html&action=siteWithoutDataTabs&idSite=1&period=day&segment=&widget=&showtitle=1&random=9969, CLI mode: 0]

The reason for the problem may be in the log above?

Information about setting up Matomo:
I have 2 Matomo servers, each has 4 vCPUs and 8 Gb RAM. Matomo runs in Docker (bitnami image). Hosts with Matomo are preceded by a Load Balancer, which distributes the load between hosts.

2

Do you use CRON for data archiving?
How do you also process reports for segments?

3
  1. Yes, I use cron for archiving.
  2. I don’t know, how do I find out?

I will add that Matomo is not loaded at all.

4

Did you also disable “Archive reports when viewed from the browser”?

image
image908×413 12.2 KB

Sorry, you mentioned earlier that it was “just” long to load…
Can you share the matomo error log file?

5

Sorry, I meant the load on the system from the sites. I haven’t enabled data collection yet.

But earlier I talked about page loading time.

Yes.

matomo_archive_settings
matomo_archive_settings1238×388 29.3 KB

I don’t see how to add a log file to this forum.

I output the last 50 lines of the error file:
ERROR Piwik\Session[2022-10-26 18:11:16 UTC] [47fb4] Unable to start session: /opt/bitnami/matomo/libs/Zend/Db/Statement/Mysqli.php(214): Mysqli statement execute error : Lock wait timeout exceeded; try restarting transaction [Query: , CLI mode: 0]
ERROR Piwik\ExceptionHandler[2022-10-26 18:11:16 UTC] [47fb4] Uncaught exception: /opt/bitnami/matomo/core/Session.php(136): Error: Unable to start session.
ERROR Piwik\ExceptionHandler[2022-10-26 18:11:16 UTC] [47fb4] 

Debug: the original error was

ERROR Piwik\ExceptionHandler[2022-10-26 18:11:16 UTC] [47fb4] Mysqli statement execute error : Lock wait timeout exceeded; try restarting transaction
[Query: , CLI mode: 0]
ERROR Piwik\Session[2022-10-26 19:10:59 UTC] [1e781] Unable to start session: /opt/bitnami/matomo/libs/Zend/Db/Statement/Mysqli.php(214): Mysqli statement execute error : Lock wait timeout exceeded; try restarting transaction [Query: , CLI mode: 0]
ERROR Piwik\ExceptionHandler[2022-10-26 19:10:59 UTC] [1e781] Uncaught exception: /opt/bitnami/matomo/core/Session.php(136): Error: Unable to start session.
ERROR Piwik\ExceptionHandler[2022-10-26 19:10:59 UTC] [1e781] 
Debug: the original error was

ERROR Piwik\ExceptionHandler[2022-10-26 19:10:59 UTC] [1e781] Mysqli statement execute error : Lock wait timeout exceeded; try restarting transaction
[Query: , CLI mode: 0]
ERROR Piwik\Session[2022-10-27 03:24:22 UTC] [d513a] Unable to start session: /opt/bitnami/matomo/libs/Zend/Db/Statement/Mysqli.php(214): Mysqli statement execute error : Lock wait timeout exceeded; try restarting transaction [Query: , CLI mode: 0]
ERROR Piwik\ExceptionHandler[2022-10-27 03:24:22 UTC] [d513a] Uncaught exception: /opt/bitnami/matomo/core/Session.php(136): Error: Unable to start session.
ERROR Piwik\ExceptionHandler[2022-10-27 03:24:22 UTC] [d513a] 
Debug: the original error was

ERROR Piwik\ExceptionHandler[2022-10-27 03:24:22 UTC] [d513a] Mysqli statement execute error : Lock wait timeout exceeded; try restarting transaction
[Query: , CLI mode: 0]
ERROR Piwik\ExceptionHandler[2022-10-28 12:43:25 UTC] [56d37] Uncaught exception: /bitnami/matomo/plugins/Installation/Installation.php(57):

Could not connect to the database:


ERROR Piwik\ExceptionHandler[2022-10-28 12:43:25 UTC] [56d37]
ERROR Piwik\ExceptionHandler[2022-10-28 12:43:25 UTC] [56d37]

php_network_getaddresses: getaddrinfo for rc1a-0wothptysc5b0l56.mdb.yandexcloud.net failed: Temporary failure in name resolution


ERROR Piwik\ExceptionHandler[2022-10-28 12:43:25 UTC] [56d37]
ERROR Piwik\ExceptionHandler[2022-10-28 12:43:25 UTC] [56d37]

This may be a temporary issue, try refreshing the page. Please contact your Matomo administrator if the problem persists.

[Query: , CLI mode: 0]
ERROR Piwik\Session[2022-11-01 22:43:45 UTC] [fd374] Unable to start session: /opt/bitnami/matomo/libs/Zend/Db/Statement/Mysqli.php(214): Mysqli statement execute error : Lock wait timeout exceeded; try restarting transaction [Query: , CLI mode: 0]
ERROR Piwik\ExceptionHandler[2022-11-01 22:43:45 UTC] [fd374] Uncaught exception: /opt/bitnami/matomo/core/Session.php(136): Error: Unable to start session.
ERROR Piwik\ExceptionHandler[2022-11-01 22:43:45 UTC] [fd374] 
Debug: the original error was

ERROR Piwik\ExceptionHandler[2022-11-01 22:43:45 UTC] [fd374] Mysqli statement execute error : Lock wait timeout exceeded; try restarting transaction
[Query: , CLI mode: 0]
ERROR Piwik\Session[2022-11-02 00:14:50 UTC] [8274d] Unable to start session: /opt/bitnami/matomo/libs/Zend/Db/Statement/Mysqli.php(214): Mysqli statement execute error : Lock wait timeout exceeded; try restarting transaction [Query: , CLI mode: 0]
ERROR Piwik\ExceptionHandler[2022-11-02 00:14:50 UTC] [8274d] Uncaught exception: /opt/bitnami/matomo/core/Session.php(136): Error: Unable to start session.
ERROR Piwik\ExceptionHandler[2022-11-02 00:14:50 UTC] [8274d] 
Debug: the original error was

ERROR Piwik\ExceptionHandler[2022-11-02 00:14:50 UTC] [8274d] Mysqli statement execute error : Lock wait timeout exceeded; try restarting transaction
[Query: , CLI mode: 0]
ERROR Piwik\Session[2022-11-02 15:09:46 UTC] [28f0b] Unable to start session: /opt/bitnami/matomo/libs/Zend/Db/Statement/Mysqli.php(214): Mysqli statement execute error : Lock wait timeout exceeded; try restarting transaction [Query: , CLI mode: 0]
ERROR Piwik\ExceptionHandler[2022-11-02 15:09:46 UTC] [28f0b] Uncaught exception: /opt/bitnami/matomo/core/Session.php(136): Error: Unable to start session.
ERROR Piwik\ExceptionHandler[2022-11-02 15:09:46 UTC] [28f0b] 
Debug: the original error was

ERROR Piwik\ExceptionHandler[2022-11-02 15:09:46 UTC] [28f0b] Mysqli statement execute error : Lock wait timeout exceeded; try restarting transaction
[Query: , CLI mode: 0]
ERROR Piwik\Session[2022-11-03 00:11:18 UTC] [63cb2] Unable to start session: /opt/bitnami/matomo/libs/Zend/Db/Statement/Mysqli.php(214): Mysqli statement execute error : Lock wait timeout exceeded; try restarting transaction [Query: , CLI mode: 0]
ERROR Piwik\ExceptionHandler[2022-11-03 00:11:18 UTC] [63cb2] Uncaught exception: /opt/bitnami/matomo/core/Session.php(136): Error: Unable to start session.
ERROR Piwik\ExceptionHandler[2022-11-03 00:11:18 UTC] [63cb2] 
Debug: the original error was

ERROR Piwik\ExceptionHandler[2022-11-03 00:11:18 UTC] [63cb2] Mysqli statement execute error : Lock wait timeout exceeded; try restarting transaction
[Query: , CLI mode: 0]
ERROR Piwik\Session[2022-11-03 05:55:23 UTC] [fe61a] Unable to start session: /opt/bitnami/matomo/libs/Zend/Db/Statement/Mysqli.php(214): Mysqli statement execute error : Lock wait timeout exceeded; try restarting transaction [Query: , CLI mode: 0]
ERROR Piwik\ExceptionHandler[2022-11-03 05:55:23 UTC] [fe61a] Uncaught exception: /opt/bitnami/matomo/core/Session.php(136): Error: Unable to start session.
ERROR Piwik\ExceptionHandler[2022-11-03 05:55:23 UTC] [fe61a] 
Debug: the original error was

ERROR Piwik\ExceptionHandler[2022-11-03 05:55:23 UTC] [fe61a] Mysqli statement execute error : Lock wait timeout exceeded; try restarting transaction
[Query: , CLI mode: 0]
ERROR Piwik\Session[2022-11-03 05:55:24 UTC] [99404] Unable to start session: /opt/bitnami/matomo/libs/Zend/Db/Statement/Mysqli.php(214): Mysqli statement execute error : Lock wait timeout exceeded; try restarting transaction [Query: , CLI mode: 0]
ERROR Piwik\ExceptionHandler[2022-11-03 05:55:24 UTC] [99404] Uncaught exception: /opt/bitnami/matomo/core/Session.php(136): Error: Unable to start session.
ERROR Piwik\ExceptionHandler[2022-11-03 05:55:24 UTC] [99404] 
Debug: the original error was

ERROR Piwik\ExceptionHandler[2022-11-03 05:55:24 UTC] [99404] Mysqli statement execute error : Lock wait timeout exceeded; try restarting transaction
[Query: , CLI mode: 0]
ERROR Piwik\Session[2022-11-03 06:00:31 UTC] [6f3bd] Unable to start session: /opt/bitnami/matomo/libs/Zend/Db/Statement/Mysqli.php(214): Mysqli statement execute error : Lock wait timeout exceeded; try restarting transaction [Query: , CLI mode: 0]
ERROR Piwik\ExceptionHandler[2022-11-03 06:00:31 UTC] [6f3bd] Uncaught exception: /opt/bitnami/matomo/core/Session.php(136): Error: Unable to start session.
ERROR Piwik\ExceptionHandler[2022-11-03 06:00:31 UTC] [6f3bd] 
Debug: the original error was

ERROR Piwik\ExceptionHandler[2022-11-03 06:00:31 UTC] [6f3bd] Mysqli statement execute error : Lock wait timeout exceeded; try restarting transaction
[Query: , CLI mode: 0]
ERROR API[2022-11-03 09:58:04 UTC] [8b36f] Uncaught exception in API: /opt/bitnami/matomo/core/Validators/BaseValidator.php(44): Fire Trigger: Значение должно быть предоставлено. [Query: ?date=yesterday&module=API&format=json&idTag=&method=TagManager.addContainerTag&idContainer=NOnSRiLF&idContainerVersion=1&type=CustomHtml&name=Custom+HTML&startDate=&endDate=&fireLimit=unlimited&fireDelay=0&priority=999&segment=&idSite=1&period=day, CLI mode: 0]
ERROR Piwik\Session[2022-11-03 14:11:29 UTC] [28b2b] Unable to start session: /opt/bitnami/matomo/libs/Zend/Db/Statement/Mysqli.php(214): Mysqli statement execute error : Lock wait timeout exceeded; try restarting transaction [Query: , CLI mode: 0]
ERROR Piwik\ExceptionHandler[2022-11-03 14:11:29 UTC] [28b2b] Uncaught exception: /opt/bitnami/matomo/core/Session.php(136): Error: Unable to start session.
ERROR Piwik\ExceptionHandler[2022-11-03 14:11:29 UTC] [28b2b] 
Debug: the original error was

ERROR Piwik\ExceptionHandler[2022-11-03 14:11:29 UTC] [28b2b] Mysqli statement execute error : Lock wait timeout exceeded; try restarting transaction
[Query: , CLI mode: 0]

6

It seems there is some error on the connection between your Matomo and the database…
Is the charset well configured?
Has Matomo user full right access on the DB?
Is there any error in the Matomo configuration check?
Which Matomo version do you use? Which plugins?

7

Take a look, please:

mysql> SELECT default_character_set_name FROM information_schema.SCHEMATA 
    -> WHERE schema_name = "schemaname";WHERE schema_name = "matomo";
+----------------------------+
| DEFAULT_CHARACTER_SET_NAME |
+----------------------------+
| utf8mb4                    |
+----------------------------+

Yes, I think so:

mysql> SHOW GRANTS FOR 'matomo'@'%';
+--------------------------------------------------------------------------+
| Grants for matomo@%                                                      |
+--------------------------------------------------------------------------+
| GRANT REPLICATION CLIENT ON *.* TO `matomo`@`%`                          |
| GRANT ALL PRIVILEGES ON `matomo`.* TO `matomo`@`%` WITH GRANT OPTION     |
| GRANT ALL PRIVILEGES ON `matomodata`.* TO `matomo`@`%` WITH GRANT OPTION |
+--------------------------------------------------------------------------+

There is a Geolocation recommendation, but I don’t think that could be the cause of my problem:

изображение
изображение858×350 15.4 KB

Matomo version: 4.11.0
Plugins:

  • QueuedTracking

  • CustomDimensions

  • CoreVue

  • CorePluginsAdmin

  • CoreAdminHome

  • CoreHome

  • WebsiteMeasurable

  • IntranetMeasurable

  • Diagnostics

  • CoreVisualizations

  • Proxy

  • API

  • Widgetize

  • Transitions

  • LanguagesManager

  • Actions

  • Dashboard

  • MultiSites

  • Referrers

  • UserLanguage

  • DevicesDetection

  • Goals

  • Ecommerce

  • SEO

  • Events

  • UserCountry

  • GeoIp2

  • VisitsSummary

  • VisitFrequency

  • VisitTime

  • VisitorInterest

  • RssWidget

  • Feedback

  • Monolog

  • Login

  • TwoFactorAuth

  • UsersManager

  • SitesManager

  • Installation

  • CoreUpdater

  • CoreConsole

  • ScheduledReports

  • UserCountryMap

  • Live

  • PrivacyManager

  • ImageGraph

  • Annotations

  • MobileMessaging

  • Overlay

  • SegmentEditor

  • Insights

  • Morpheus

  • Contents

  • BulkTracking

  • Resolution

  • DevicePlugins

  • Heartbeat

  • Intl

  • Marketplace

  • ProfessionalServices

  • UserId

  • CustomJsTracker

  • Tour

  • PagePerformance

  • TagManager

  • MobileAppMeasurable

  • DBStats

  • LoginOIDC

8

I also see a lot of long queries. Is it ok?

I will give you a few, there are 120 of them in total:

| 60388994 | matomo | matomo-02.yc.lamoda.tech:35298       | matomo | Execute |   92 | updating | DELETE FROM matomo_session WHERE modified + lifetime < '1667812488'                                  |   92755 |         0 |       6757924 |
| 60389000 | matomo | matomo-01.ru-central1.internal:51034 | matomo | Execute |   90 | update   | INSERT INTO matomo_session (id,modified,lifetime,data) VALUES ('110bff87699fa77f29dfad48f9863fd98cbb |   90499 |         0 |             0 |
| 60389005 | matomo | matomo-01.ru-central1.internal:51044 | matomo | Execute |   90 | update   | INSERT INTO matomo_session (id,modified,lifetime,data) VALUES ('06003d2f821c897364c8883ef6aaf48080d9 |   90079 |         0 |             0 |
| 60389010 | matomo | matomo-01.ru-central1.internal:51054 | matomo | Execute |   90 | update   | INSERT INTO matomo_session (id,modified,lifetime,data) VALUES ('157e484c6c8f3840b0b89f509aadbf25b7a3 |   90058 |         0 |             0 |
| 60389021 | matomo | matomo-01.ru-central1.internal:51076 | matomo | Execute |   90 | update   | INSERT INTO matomo_session (id,modified,lifetime,data) VALUES ('18b068256e6587c81e58ba10cb16e3f376ed |   89954 |         0 |             0 |
| 60389024 | matomo | matomo-01.ru-central1.internal:51086 | matomo | Execute |   90 | update   | INSERT INTO matomo_session (id,modified,lifetime,data) VALUES ('13a8dab7430d80629ddd05117a4663a67da5 |   89947 |         0 |             0 |
| 60389039 | matomo | matomo-01.ru-central1.internal:51106 | matomo | Execute |   90 | update   | INSERT INTO matomo_session (id,modified,lifetime,data) VALUES ('1781628db01e5d67639b6b590c33842fd815 |   89974 |         0 |             0 |
| 60389047 | matomo | matomo-01.ru-central1.internal:51128 | matomo | Execute |   90 | update   | INSERT INTO matomo_session (id,modified,lifetime,data) VALUES ('169cdd8c0be1fdc6a75e6bc9bed7faa11d6e |   89925 |         0 |             0 |

After a while, these requests pass and for a short period of time Matomo starts working fine, all pages open instantly.

9

It seems that the problem was just in the long queries to the matomo_sessions table, which had millions of records. Deleted the entries from the matomo_sessions table and Matomo started working fine.

It seems that this is a bug. I don’t understand why there are so many recordings with sessions. The table filling rate is about 1,500 records per minute

So far, I decided to use a workaround with hourly deletion of the table with sessions.

10

That’s very strange…
@innocraft, do you know why this table is taking so much traffic?

11

Hi @mdubakin_lamoda, this has nothing to do with the Heatmaps and Session recordings plugin. The matomo_sessions table holds all of the sessions for Users that are logged into the Matomo instance.

This may be a bug in your Login plugin (LoginOIDC), or it could be expected if you have a lot of users (I.e. maybe thousands of users)

1 Like