Hallo,
ich habe hier eine Seite, die mehr als 1000 Segmente hat. Die Datenbank ist mittlerweile über 500 GB. Das berechnen der Segmente dauert mehr als EINEN MONAT
Gibt es da eine alternative zu, die sowas schneller berechnet?
Gruß
Michael
Ich habe gute Erfahrungen gemacht, mit dem Server so viel RAM zuweisen, wie die DB gross ist und anschliessend mit vmtouch
die Datenbank ins Memory zu laden. Das macht Matomo extrem performanter. Aber das wird mit 500 GB vermutlich schwierig … Aber vielleicht reicht es nur schon, wenn du gewisse Tabellen (bei MariaDB gibt es pro Tabelle ein File) ins Memory lädst.
Ansonsten fände ich eine Antwort auf deine Frage auch spannend.
Hallo Markus,
da du ja auch an Beschleunigung deiner Segmentberechnung interessiert bist … hier mal mein SQL was über die DB rauscht:
INSERT INTO matomo_logtmpsegment2a71c85ced5a22ac57ad536e0eac08fa (idvisit)
SELECT
distinct log_visit.idvisit as idvisit
FROM
matomo_log_visit AS log_visit LEFT JOIN matomo_log_link_visit_action AS log_link_visit_action ON log_link_visit_action.idvisit = log_visit.idvisit
WHERE
( log_visit.visit_last_action_time >= '2024-09-21 22:00:00'
AND log_visit.visit_last_action_time <= '2024-09-22 21:59:59'
AND log_visit.idsite IN ('7') )
AND
( (( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1781', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1780', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1779', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1778', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1777', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1776', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1775', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1774', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1773', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1772', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1771', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1770', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1769', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1768', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1767', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1766', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1765', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1764', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1763', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1762', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1761', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1760', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1759', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1758', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1757', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1756', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1755', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1754', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1753', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1752', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1751', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1750', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1749', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1748', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1747', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1746', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1745', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1744', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1743', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1742', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1741', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1740', '%') AND type = 1 )) ) OR ( log_link_visit_action.idaction_url IN (SELECT idaction FROM matomo_log_action WHERE ( name LIKE CONCAT('%', 'api.example.app%2Forte%2F1739', '%') AND type = 1 )) ) ))
ORDER BY
log_visit.idvisit ASC
Ich erstelle die Segmente nicht - das sind alles IDs für einzelne Orte (300 Seelendörfer ) - ich habe für mich einen Weg gefunden die 43 Unter-Segmente auf nur eine Abfrage zu reduzieren.
Segmente habe ich hier insgesamt 2019 mit entsprechenden “Unter-Segmenten”
@foxpalace wie muss ich deine Antwort verstehen? Du hast das SQL-Statement im Code gepatcht oder holst dir alles direkt aus der Datenbank?
hi - nein - ich habe den Entwicklern gesagt, dass Sie mir das log-File modifizieren sollen und einen Wert für die 43 Unter-Segmente hinzufügen sollen, z.B.:
vorher im log:
1
2
3
4
…
jetzt:
117
fertig - mach aus 43 Segmenten EINE