@Samichio opened this Issue on August 8th 2018

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 wait_timeout=120 and 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?

@Samichio commented on August 8th 2018

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

@tsteur commented on August 8th 2018 Member

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.

@Samichio commented on August 9th 2018

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.

@tsteur commented on August 9th 2018 Member

Yes that's very likely the issue.

@Samichio commented on August 9th 2018

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
@tsteur commented on August 9th 2018 Member

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.

This Issue was closed on August 10th 2018
Powered by GitHub Issue Mirror