Alternative zu Segmenten

foxpalace · September 3, 2024, 3:18pm

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

mritzmann · September 4, 2024, 7:05am

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.

foxpalace · October 14, 2024, 12:26pm

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”

utrautma · October 17, 2024, 2:07pm

@foxpalace wie muss ich deine Antwort verstehen? Du hast das SQL-Statement im Code gepatcht oder holst dir alles direkt aus der Datenbank?

foxpalace · October 17, 2024, 5:09pm

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