Artim96
August 25, 2024, 1:10pm
1
We have quite a small web page with only a couple of sites and < 10.000 visitors per year. The WordPress website exists since late 2020, Matomo is installed as a WordPRess plugin since roughly 2021, yet the mysql database for that page is almost 700 MB big, while it normally shouldn’t be much more than 100 MB. The raw data retention time is set to 30 days, the retention for reports to 12 months with both recommended boxes ticked. Old data is set to be deleted on a weekly base, yet it claims the database currently only holds 116.5 MB.
Yet when I look at the /var/lib/mysql// directory, while hardly any file is really more than 10 MB, most of the files clearly belong to Matomo with names like “wp_matomo_archive_blob_2020_08.ibd” and “wp_matomo_archive_numeric_2020_08.ibd” and when I calculate the sum of all files including the word matomo in their file name (find . -type f -iname '*matomo*' -exec du -b {} + | awk '{sum += $1} END {print sum / 1024 / 1024 " MB"}') I get 599 MB. So what on earth is going on? What can I do to shrink the size of the database to a sensible size, and why does Matomo display such a low size estimation when it’s that much larger?
Hi @Artim96 Diagnostic > Database usage
Which one is the biggest?
Artim96
August 28, 2024, 11:12am
3
It doesn’t. Just that there are 217 tables. That’s the system report
# Matomo
* Matomo plugin version: 5.1.2
* Config exists and is writable: Yes (“$abs_path/wp-content/uploads/matomo/config/config.ini.php” )
* JS Tracker exists and is writable: Yes (“$abs_path/wp-content/uploads/matomo/matomo.js” )
* Plugin directories: Yes ([{“pluginsPathAbsolute”:“$abs_path\/wp-content\/plugins\/matomo\/plugins”, “webrootDirRelativeToMatomo”:“..\/plugins”}])
* Tmp directory writable: Yes ($abs_path/wp-content/cache/matomo)
* Matomo version: 5.1.0
* Matomo Blog idSite: 1
* Installed Matomo version: 1.1.3 (Install date: 2020-07-20 17:24:51)
* Upgrades outstanding: No
* Upgrade in progress: No
## Endpoints
* Matomo JavaScript Tracker URL: ($site_url/wp-content/uploads/matomo/matomo.js)
* Matomo JavaScript Tracker - WP Rest API: ($site_url/wp-json/matomo/v1/hit/)
* Matomo HTTP Tracking API: ($site_url/wp-content/plugins/matomo/app/matomo.php)
* Matomo HTTP Tracking API - WP Rest API: ($site_url/wp-json/matomo/v1/hit/)
## Crons
* Server time: 2024-08-28 11:05:22
* Blog time: 2024-08-28 13:05:22 (The following data is displayed in the blog time zone)
* Sync users & sites: Next run: 2024-08-28 17:24:50 (4 hours 19 minutes) ( Last started: 2024-08-27 17:25:40 (-19 hours 39 minutes). Last ended: 2024-08-27 17:25:40 (-19 hours 39 minutes). Interval: daily)
* Archive: Next run: 2024-08-28 13:38:56 (33 minutes 34s) ( Last started: 2024-08-28 12:39:24 (-25 minutes 58s). Last ended: 2024-08-28 12:39:26 (-25 minutes 56s). Interval: hourly)
* Update GeoIP DB: Next run: 2024-08-28 17:24:50 (4 hours 19 minutes) ( Last started: 2024-07-29 17:29:54 (-29 days 19 hours). Last ended: 2024-07-29 17:29:58 (-29 days 19 hours). Interval: matomo_monthly)
## Mandatory checks
* PHP version >= 7.2.5: ok
* PDO extension: ok
* PDO\MYSQL extension: ok
* MYSQLI extension: ok
* Other necessary requirements: ok
* Required functions: ok
* Required PHP configuration (php.ini): ok
* Directories with write access: ok
* Directories with write access to Tag Manager: ok
## Optional checks
* 64-bit PHP binary: ok
* Tracker status: ok
* Memory limit: ok
* Time zone: ok
* Open a URL: ok
* GD > 2.x + FreeType (graphics): ok
* Other extensions: ok
* Other functions: ok
* File system: ok
* Last successful completion of archiving: ok
* Database capabilities: ok
* Maximum packet size: ok
* Location detection: ok
* Update via HTTPS: ok
* Mobile Messaging SMS Provider: ok
* Supports Async Archiving: Yes
* Async Archiving Disabled in Setting: No
* Location provider ID: geoip2php
* Location provider available: Yes
* Location provider working: Yes
* Had visit in last 5 days: Yes
* Matomo URL: Yes ($site_url/wp-content/plugins/matomo/app)
## Matomo Settings
* Track mode: default
* Track ecommerce: No
* Track codeposition: header
* Track api endpoint: default
* Track js endpoint: default
* Version history: 5.1.2, 5.1.1, 5.1.0, 5.0.8, 5.0.6
* Core version: 5.1.0
* Last tracking settings update: 1717491568
* Last settings update: 1717491662
* Track noscript: Yes
* Track content: visible
* Track ecommerce: No
* Track search: Yes
* Track 404: Yes
* Disable cookies: Yes
* Limit cookies visitor: 2629800
* Limit cookies referral: 2629800
* Show get started page: 0
* Track jserrors: Yes
## Logs
* None:
# WordPress
* Home URL: $site_url
* Site URL: $site_url
* WordPress Version: 6.6.1
* Number of blogs: 1
* Multisite Enabled: No
* Network Enabled: No
* WP_DEBUG: No
* WP_DEBUG_DISPLAY: No
* WP_DEBUG_LOG: wp-content/debug.log
* DISABLE_WP_CRON: -
* FORCE_SSL_ADMIN: Yes
* WP_CACHE: Yes
* CONCATENATE_SCRIPTS: -
* COMPRESS_SCRIPTS: -
* COMPRESS_CSS: -
* ENFORCE_GZIP: -
* WP_LOCAL_DEV: -
* WP_CONTENT_URL: $site_url/wp-content
* WP_CONTENT_DIR: $abs_path/wp-content
* UPLOADS: -
* BLOGUPLOADDIR: -
* DIEONDBERROR: -
* WPLANG: -
* ALTERNATE_WP_CRON: -
* WP_CRON_LOCK_TIMEOUT: 60
* WP_DISABLE_FATAL_ERROR_HANDLER: -
* MATOMO_SUPPORT_ASYNC_ARCHIVING: -
* MATOMO_ENABLE_TAG_MANAGER: -
* MATOMO_SUPPRESS_DB_ERRORS: -
* MATOMO_ENABLE_AUTO_UPGRADE: -
* MATOMO_DEBUG: -
* MATOMO_SAFE_MODE: -
* MATOMO_GLOBAL_UPLOAD_DIR: -
* MATOMO_LOGIN_REDIRECT: -
* Permalink Structure: /%postname%/
* Possibly uses symlink: No
* Upload base url: $site_url/wp-content/uploads
* Upload base dir: $abs_path/wp-content/uploads
* Upload url: $site_url/wp-content/uploads/2024/08
* Custom upload_path:
* Custom upload_url_path:
* Compatible content directory: Yes
* WP_Filesystem Initialized: Yes
# WordPress Plugins
* AuthLDAP: 2.6.3
* Autoptimize: 3.1.12
* Clarity - Ad blocker for WordPress: 1.3.231102
* Classic Widgets: 0.3
* Custom Sidebars: 3.36
* Easy Updates Manager: 9.0.18 (Network enabled)
* Events Manager: 6.5.2
* Forminator: 1.34.1
* Matomo Analytics - Ethical Stats. Powerful Insights: 5.1.2
* No Nonsense: 3.4.0
* PublishPress Revisions: 3.5.11
* Redirection: 5.5.0
* Redis Object Cache: 2.5.3 (Network enabled)
* Safe SVG: 2.2.5
* Smart Slider 3: 3.5.1.23
* TranslatePress - Multilingual: 2.8.3
* Two Factor Authentication: 1.14.23
* User Role Editor: 4.64.2
* WebP Express: 0.25.9 (Network enabled)
* WPCode Lite: 2.2.1
* WP Mail SMTP: 4.1.1
* Active Plugins: 20 (authldap:2.6.3 autoptimize:3.1.12 clarity-ad-blocker:1.3.231102 classic-widgets:0.3 custom-sidebars:3.36 events-manager:6.5.2 forminator:1.34.1 insert-headers-and-footers:2.2.1 matomo:5.1.2 no-nonsense:3.4.0 redis-cache:2. 5.3 revisionary:3.5.11 safe-svg:2.2.5 smart-slider-3:3.5.1.23 stops-core-theme-and-plugin-updates:9.0.18 translatepress-multilingual:2.8.3 two-factor-authentication:1.14.23 user-role-editor:4.64.2 webp-express:0.25.9 wp-mail-smtp:4.1.1)
* Theme: customify (customify)
# Server
* Server Info: nginx/1.26.2
* PHP OS: Linux
* PHP Version: 8.2.20
* PHP SAPI: fpm-fcgi
* PHP Binary Name: /usr/sbin/php-fpm8.2
* PHP Maxmind DB extension: Not loaded
* PHP Error Reporting: 4437 After bootstrap: 4437
* PHP Found Binary: /usr/bin/php -q
* Timezone: UTC
* WP timezone: Europe/Berlin
* Locale: de_DE
* User Locale: de_DE
* Memory Limit: 256M (At least 128MB recommended. Depending on your traffic 256MB or more may be needed).
* WP Memory Limit: 256M
* WP Max Memory Limit: 256M
* Timezone version: 0.system
* Time: 1724843123
* Max Execution Time: 300
* Max Post Size: 100M
* Max Upload Size: 52428800
* Max Input Vars: 1000
* Disabled PHP functions: No
* zlib.output_compression is off: Yes
* Curl Version: 7.88.1, OpenSSL/3.0.13
* Suhosin installed: No
# PHP cli
* PHP CLI Version: 8.2.20
* MySQLi support: ok
* PHP CLI configuration: Configured correctly
# Database
* MySQL Version: 10.11.6
* Mysqli Connect: Yes
* Force MySQL over Mysqli: No
* DB Prefix: wp_
* DB CHARSET: utf8
* DB COLLATE:
* SHOW ERRORS: No
* SUPPRESS ERRORS: No
* Uses Socket: No
* Uses IPv6: No
* Matomo tables found: 217
* DB tables exist: Yes
* Matomo users found: 8
* Matomo sites found: 1
* Required authorizations: OK
# Browser
* Browser: (Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/130.0.0.0 Safari/537.36)
* Language: en-us,en,de-de-de,de
Artim96
August 28, 2024, 11:14am
4
The biggest file in the directory is wp_eum_logs.ibd with 16M, the biggest file with Matomo related name (second biggest overall) wp_matomo_archive_blob_2020_08.ibd with 11 MB
> Diagnostic > Database usage