I just benchmarked my Matomo 3.5.1 with Apache AB with 300,000 requests. Since then the widgets on the Dashbaord like "Visitors in real time" is not loading/timing out.
As soon as the request times out I get the error
WARNING: /var/www/html/piwik3.5.1/libs/Zend/Db/Statement/Pdo.php(228): Warning - Error while sending QUERY packet. PID=55294 - Matomo 3.5.1 on the page.
I have added
max_allowed_packet=1024M and restarted the database, same error. Archiving is done via cronjob.
It looks like one php-fpm process at 100% is blocking the whole web server (72core, 256GB RAM, 2TB SSD)
Any ideas or pointers?
When enabling the mysql slow query log it always fails on the following query:
# Time: 180808 13:07:09 # User<a class='mention' href='https://github.com/Host'>@Host</a>: piwik[piwik] @ localhost  # Thread_id: 20117 Schema: piwik QC_hit: No # Query_time: 0.001631 Lock_time: 0.000038 Rows_sent: 10 Rows_examined: 55 SET timestamp=1533726429; SELECT sub.* FROM ( SELECT log_visit.* FROM matomo_log_visit AS log_visit WHERE log_visit.idsite in ('18') AND log_visit.visit_last_action_time >= '2018-08-06 22:00:00' ORDER BY idsite DESC, visit_last_action_time DESC LIMIT 0, 10 ) AS sub GROUP BY sub.idvisit ORDER BY sub.visit_last_action_time DESC LIMIT 10;
I hit the same error when opening the Visitor-Log
What were those 300K requests? How many visits/actions were there within last 24 hours? Maybe increase
wait_timeout? I recommend you get in touch with the Matomo team re support: https://matomo.org/support/
It seems some server configuration is not quite right. Real time query can be a bit "slow" but wouldn't consider "0.001631" slow for this query.
Wait_timeout is already at 120s.
Those 300K requests were from Apache Benchmark sending requests to the tracking script.
Matomo recognises my requests as one user calling 300K pages, so colleagues and I are thinking that this could cause issues, since 1 User calling 300K pages is quite unrealistic.
Yes that's very likely the issue.
I just brought up an visitor log of 10 users each visiting ~30 pages. It is not timing out, but is taking way too long. Doing the request, MariaDB hits 100% utilization. Any ideas on where to look for mistakes?
[mysqld] datadir=/var/db/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # customize your systemd unit file for mariadb according to the # instructions in http://fedoraproject.org/wiki/Systemd slow_query_log = 1 long_query_time = 20 slow_query_log_file = /var/log/mariadb/slow-query.log log_queries_not_using_indexes wait_timeout=120 max_allowed_packet=1024M [mysqld_safe] log-error=/var/log/mariadb/mariadb.log pid-file=/var/run/mariadb/mariadb.pid # # include all files from the config directory # !includedir /etc/my.cnf.d binlog_cache_size=8M character_set_server=utf8 collation_server=utf8_general_ci innodb_buffer_pool_size=8G innodb_data_file_path=ibdata1:128M:autoextend innodb_flush_log_at_trx_commit=2 innodb_flush_method=O_DIRECT_NO_FSYNC innodb_log_buffer_size=64M innodb_log_file_size=512M innodb_open_files=8192 innodb_thread_concurrency=256 innodb_additional_mem_pool_size=32M innodb_doublewrite=0 innodb_file_per_table=ON join_buffer_size=32M max_allowed_packet=1024M max_connections=2048 max_heap_table_size=64M max_seeks_for_key=1000 query_cache_size=128M key_buffer_size=128M sort_buffer_size=32M thread_cache_size=32M tmp_table_size=512M read_buffer_size=16M read_rnd_buffer_size=32M bulk_insert_buffer_size=128M
but is taking way too long
can you define way too long? No idea for where to look on mistakes. This should be fast. I recommend you get in touch with the Matomo support or ask in the forums for community help: https://forum.matomo.org/ We don't provide support for specific server issues in the issue tracker. The more details you provide the better. Eg database size, number of entries per
log_ table etc. If familiar with MySQL check that it uses the indexes etc.