Pour nous contacter : soyez au rendez-vous sur IRC ! ⋅ Parcourir l'archive musicale Dogmazic ⋅ Notre Blog
Notre Documentation
Notre Documentation
Optimisation de la base de données
Bonjour à tous,
Aujourd'hui, j'étais bien motivé alors j'ai décidé de voir ce que je pouvais faire pour améliorer encore un peu les choses à mon niveau. Voici un petit compte rendu de mes investigations.
La cause principale de la latence de l'application est la taille de la base de données et des requêtes pas incroyable faites par l'application. J'ai amélioré un petit peu les performances en crééant du cache artificiellement mais cela ne suffit pas à améliorer suffisament les performances.
Du coup, j'ai analysé plus en profondeur la base de données et j'ai pu observé pas mal de choses.
Il y a 45349 utilisateurs dans la base de données.
2724 utilisateurs ont uploadé une ou plusieurs musiques.
2566 utilisateurs ont créé une ou plusieurs playlists.
Il y a 40632 utilisateurs qui ne sont pas connectés depuis janvier 2018 ou uploadés une ou plusieurs musiques ou créé une ou plusieurs playlists.
Il y a la journalisation de 4188632 activités des utilisateurs. Supprimer les inactifs depuis 2018 ou non uploadeurs permettrait de supprimer '33389' lignes
Il y a la journalisation de 5559116 actions des utilisateurs. Supprimer les inactifs depuis 2018 ou non uploadeurs permettrait de supprimer '298867' lignes
Il y a la conservation de 3992901 lignes concernant les préférences utilisateurs. Supprimer les inactifs depuis 2018 ou non uploadeurs permettrait de supprimer 3764035 lignes.
Personnellement je suis pour la suppression des utilisateurs inactifs depuis janvier 2018 et n'ayant pas uploadé une ou plusieurs musiques ou créé une ou plusieurs playlists ainsi que de leurs données (préférences, activités etc)
Cela permettrait de réduire le nombre de données personnelles en notre possession et d'améliorer sensiblement les performances de l'application.
Dans tous les cas, un backup sera réalisé avant opération si opération il y a afin de pouvoir revenir en arrière.
Qu'en pensez-vous ?
Pour information, les 10 tables avec le plus de lignes dans la base de données.
'object_count', '5559116'
'user_activity', '4188632'
'user_preference', '3992901'
'user_preference_bckp20180625', '2736890'
'playlist_data', '265979'
'tag_map', '90216'
'image', '59075'
'song_data', '58587'
'song', '58012'
'user', '45349'
'user_shout', '39407'
'rating', '16399'
'session', '14584'
'tmp_playlist', '12894'
Aujourd'hui, j'étais bien motivé alors j'ai décidé de voir ce que je pouvais faire pour améliorer encore un peu les choses à mon niveau. Voici un petit compte rendu de mes investigations.
La cause principale de la latence de l'application est la taille de la base de données et des requêtes pas incroyable faites par l'application. J'ai amélioré un petit peu les performances en crééant du cache artificiellement mais cela ne suffit pas à améliorer suffisament les performances.
Du coup, j'ai analysé plus en profondeur la base de données et j'ai pu observé pas mal de choses.
Il y a 45349 utilisateurs dans la base de données.
2724 utilisateurs ont uploadé une ou plusieurs musiques.
2566 utilisateurs ont créé une ou plusieurs playlists.
Il y a 40632 utilisateurs qui ne sont pas connectés depuis janvier 2018 ou uploadés une ou plusieurs musiques ou créé une ou plusieurs playlists.
Il y a la journalisation de 4188632 activités des utilisateurs. Supprimer les inactifs depuis 2018 ou non uploadeurs permettrait de supprimer '33389' lignes
Il y a la journalisation de 5559116 actions des utilisateurs. Supprimer les inactifs depuis 2018 ou non uploadeurs permettrait de supprimer '298867' lignes
Il y a la conservation de 3992901 lignes concernant les préférences utilisateurs. Supprimer les inactifs depuis 2018 ou non uploadeurs permettrait de supprimer 3764035 lignes.
Personnellement je suis pour la suppression des utilisateurs inactifs depuis janvier 2018 et n'ayant pas uploadé une ou plusieurs musiques ou créé une ou plusieurs playlists ainsi que de leurs données (préférences, activités etc)
Cela permettrait de réduire le nombre de données personnelles en notre possession et d'améliorer sensiblement les performances de l'application.
Dans tous les cas, un backup sera réalisé avant opération si opération il y a afin de pouvoir revenir en arrière.
Qu'en pensez-vous ?
Pour information, les 10 tables avec le plus de lignes dans la base de données.
'object_count', '5559116'
'user_activity', '4188632'
'user_preference', '3992901'
'user_preference_bckp20180625', '2736890'
'playlist_data', '265979'
'tag_map', '90216'
'image', '59075'
'song_data', '58587'
'song', '58012'
'user', '45349'
'user_shout', '39407'
'rating', '16399'
'session', '14584'
'tmp_playlist', '12894'
Réponses
Déjà, merci d'avoir regardé, ensuite, avant de donner mon avis, je voudrais d'autres précisions:
-Est-ce qu'il est possible de supprimer la journalisation d'actions passées d'utilisateurs, sans supprimer les utilisateurs ?
-même question pour les préférences
-même question pour le suivi de l'activité.
Sinon, je me demande pourquoi le choix de 2018 comme date butoir ? Spontanément, j'aurais pensé comme date butoir le "après la remise en ligne de l'archive", mais peut-être que les données d'avant cette date représentent trop peu sur l'ensemble ?
Bises !
Bonne année à toi aussi
-Est-ce qu'il est possible de supprimer la journalisation d'actions passées d'utilisateurs, sans supprimer les utilisateurs ?
-> Tout à fait, je ne pense pas que cela causera de soucis.
-même question pour les préférences
-> Tout à fait, mais il est possible que cela cause des problèmes. Il faut que je regarde ce que contient exactement la table préférences pour voir. Je crains que l'absence d'une préférence fasse planter l'application pour l'utilisateur concerné.
-même question pour le suivi de l'activité.
-> Tout à fait, je ne pense pas que cela causera de soucis.
Sinon, je me demande pourquoi le choix de 2018 comme date butoir ?
-> C'est une date arbitaire que j'ai prévu, me disant que deux ans cela représente quand même beaucoup de temps. Si l'utilisateur était vraiment intéressé par l'application, il serait revenu avant. Qu'elle est la date de la remise en ligne de l'archive ? Si j'ai cette date, je peux regarder la différence de données récupérées.
Autres information, on a toujours les adresses emails rattachées à tous ces comptes.
L'archive est revenue en ligne en mai 2015.
@luciolebrillante merci de la réponse !
Alors du coup, personnellement, je serais pour supprimer la journalisation et le suivi d'activité (les préférences, à voir donc) d'avant Mai 2018, sans forcément supprimer les utilisateurs, quitte à garder une backup (pour les statistiques, qui sait). Et, s'il y a un besoin important de supprimer des utilisateurs, de prendre comme date butoir Mai 2018, par égard aux 700 "nouveaux" utilisateurs y étant venus depuis
Il y a plusieurs choses à mon sens,
- L'activité d'un utilisateur sans compte artiste, label... à mon sens oui il doit y avoir des trucs à supprimer à changer (le nombre d'actions logguées, leur redondance, la précision de ces logs ?).
- L'activité d'un utilisateur artiste, label, pour qui ces infos sont intéressantes (les graphs d'activité notamment) et qui donnent un regard sur l'activité de leur compte (téléchargements/écoutes...).
Autre chose, beaucoup d'utilisateurs sont revenus suite à la remise en ligne de l'archive en 2015. Ce sont des utilisateurs qui étaient inscrits sur l'ancien site et qui ont attendu 2 ans et demi pour se réinscrire (notre réouverture était attendue quand mêmeQuelques facteurs d'explications autres que Dogmazic à mon sens à ne pas oublier :
La montée en puissance de Deezer / Youtube dans les habitudes de découverte de musiques
Les habitudes de personnes sensibilisées aux listes de lecture peuplées par des algorithmes (ce que nous ne faisons pas, et tant mieux ?)
quitte à supprimer de la base de données de nombreux utilisateurs,
dont les nombreux fakes arrivés en rafales depuis sur le forum (c'est autre chose).
Dans les discussions du forum, on y voit toujours la même dizaine de membres.
L'intérêt du site a fortement diminué, c'est un euphémisme.
Si pour les utilisateurs actuels, la navigation peut en être améliorée, je suis pour nettoyer tout ça.
Sans compter que pour les adhésions à l'asso, il n'y a quasiment plus personne, pour ce qui est de l'investissement personnel, idem. Et c'est là qu'on constatera ceux vraiment désireux de continuer à utiliser l'archive ou non.
C'est bien beau de se faire l'avocat de l'agneau... (qui n'en a rien à faire de l'évolution du Dogmazic et de ses membres.)
Je dirais, supprimer tous les inactifs depuis 2015, les comptes fantômes également.
Je vais regarder vous remonter le nombre d'utilisateur actif depuis 2015 couplé à ceux qui ont uploadés une musique et/ou créer une playlist et ceux qui sont des labels/artistes.
Les 9/10 de la table qui concernent les activités sont rattachés à un utilisateur avec l'identifiant -1. J'ai regardé et aucun utilisateur ne possède cet id. Est-ce que vous avez connaissance d'une situation qui génère cet id pour une activité ? Les journalisations des activités que j'ai énumérées ne prennent pas en compte cet utilisateur, je vais voir pour ressortir les chiffres sans omettre l'utilisateur -1.
Avant de faire quoi que ce soit, je posterai ici ce que je pense faire afin que vous validiez. Pour l'instant, il me manque les informations suivantes que je vais aller collecter et qui permettra à tous de mieux choisir ce que l'on veut garder et/ou supprimer.
table user_activity
- le nombre de lignes présentes dans user_activity qui ont plus de x d'anciennetées (x = 1 an, 2 ans, 3 ans, 4 ans) afin de pouvoir supprimer suffisament de lignes sans être trop large dans la suppression.
- le nombre de lignes présentes dans user_activity qui ont plus de x d'anciennetées (x = 1 an, 2 ans, 3 ans, 4 ans) en incluant l'utilisateur -1 afin de pouvoir supprimer suffisament de lignes sans être trop large dans la suppression.
table user_preference
- le nombre de lignes présentes dans user_preference qui ont plus de x d'anciennetées (x = 1 an, 2 ans, 3 ans, 4 ans) afin de pouvoir supprimer suffisament de lignes sans être trop large dans la suppression.
- le nombre de lignes présentes dans user_preference qui ont plus de x d'anciennet (x = 1 an, 2 ans, 3 ans, 4 ans) en incluant l'utilisateur -1 afin de pouvoir supprimer suffisament de lignes sans être trop large dans la suppression.
table user
- le nombre de lignes présentes dans user qui ont plus de x d'ancienneté (x = 1 an, 2 ans, 3 ans, 4 ans) afin de pouvoir supprimer suffisament de lignes sans être trop large dans la suppression incluant ceux qui sont artistes et/ou labels si j'arrive à pouvoir les distinguer
- le nombre de lignes présentes dans user qui ont plus de x d'ancienneté (x = 1 an, 2 ans, 3 ans, 4 ans) en incluant l'utilisateur -1 afin de pouvoir supprimer suffisament de lignes sans être trop large dans la suppression incluant ceux qui sont artistes et/ou labels si j'arrive à pouvoir les distinguer
Est-ce que vous pensez qu'il y a d'autres critères qui justifie la conservation de ces comptes à part (activités antérieur à 2015, label/artiste/?
Concernant l'adhésion à l'association, je ne suis toujours pas membre alors que j'avais demandé à en faire partie ...
Concernant les facteurs, je file un coup de main que depuis un an mais je pense que la lenteur du site était une des raisons principales de son abandon. Il ne faut pas oublier qu'avant que je travaille dessus, on avait pas de HTTPS, pas de HTTP2, pas de bibliothèques à jour, pas de javascript minifié, pas de cache applicatif. Je me souviens que j'avais benchmarké et il y a un an on était à 16 secondes entre le temps où on requête le site et le moment où l'on avait une réponse. Aujourd'hui on est à environ 6 secondes, ce qui est mieux mais tjrs pas incroyable.
Il faut que je regarde pour monitorer les temps de réponses de l'application afin de pouvoir évaluer les effets des améliorations que je réalise sur le site.
J’ai pu voir cet identifiant dans l’adresse en downloadant des morceaux sans être loggé il y a quelques mois.
le moins qu'on puisse dire est que tu utilises tes neurones.
Merci pour ta persévérance, et tes investissements.
De 16 secondes de temps de chargement à 6, c'est un exploit.
Pendant une demie journée (Hier), le site était injoignable, je pense que tu avais les mains dans le cambouis.
Pour la conservation des pages et des id artistes, je pars du principe que quelqu'un qui a déserté le site depuis 4 (bientôt 5) ans, sans se loguer, n'y reviendra sans doute pas, ou se serait manifesté d'un jour à l'autre, 2015 étant une date pivot (remise en ligne de l'archive avec Ampache). Je ne sais pas si c'est possible d'adresser un mail groupé comme pour une liste de diffusion, de prévenir les utilisateurs absents que tout compte inactif depuis 2015 se verrait supprimé sans nouvelle connexion, mais je ne crois pas que ce soit possible. Hormis en récupérant les inscrits au forum, mais souvent les pseudo du forum diffèrent de ceux de l'archive, c'est partir à la recherche de moustiques dans le système solaire.
Si on pouvait améliorer la rapidité de chargement, pour les quelques irréductibles restants,
ce serait tout bon. (toujours pas terminé la sélection de la radio, j'ai pris un peu de retard)
En attendant, un gros merci que je t'adresse,
tu es l'homme qu'il nous fallait.
++
@luciolebrillante Il y a des comptes utilisateur désactivés, ceux qui se sont inscrits mais qui n'ont pas confirmé leur inscription avec le mail qui leur a été envoyé. Je sais qu'on a eu quelques soucis à un moment avec cette fonctionnalité (les mails n'étaient pas envoyés). Je pense que tu peux supprimer ces comptes non activés, ce sont peut être les -1 ?
Sur l'activité, il y a peut être eu de l'activité de commentaires sur le site d'utilisateurs n'ayant pas uploadé ni créé de playlistes.
Il nous faut un système de gestion d'adhésions (galette par exemple)
@kidjazz Il est possible d'envoyer un mail explicatif à tous les membres inscrits sur le site (avec la liste des mails des utilisateurs). Pour cela il faut la liste des mails et un service de mails qui supporte l'envoi de milliers de mails sans qu'on se fasse bloqué comme spammeur.
Merci beaucoup pour ton retour ! Il s'agirait alors des journaux des utilisateurs non connectés à l'application, ce qui nous permettrait de purger la chose sans risque. Une très bonne nouvelle, à confirmer
@kidjazz
On fait ce que l'on peut. Je me suis toujours dis que c'était avec ma tête que je ferai la différence, notre corps est trop fragile pour tenir la route bien longtemps ^^ Cela me permet aussi de progresser donc c'est cool
Le plaisir est pour moi, cela me fait plaisir d'aider et je suis pour le partage libre de l'art.
@aizyk
Pour le système d'adhésion, pas besoin d'avoir un logiciel pour gérer cela vu le nombre de membres. Un tableur avec les informations suffit. Après, il faut s'en occuper aussi
Des nouvelles
Je reviens vers vous avec des nouvelles plus fraiches concernant mes investigations.
Concernant les utilisateurs dans la base de données (45349 lignes):
Liste des utilisateurs ayant eu une activités et ayant uploadés un fichier et/ou playlist depuis la date annoncées jusqu'à aujourd'hui
# Tue, 01 Jan 2019 12:00:00 GMT -> 4311
# Mon, 01 Jan 2018 09:46:40 GMT -> 5324
# Sun, 01 Jan 2017 05:53:20 GMT -> 5561
# Fri, 01 Jan 2016 12:06:40 GMT -> 5849
# Thu, 28 May 2015 05:13:20 GMT -> 6071
# Thu, 01 Jan 2015 05:26:40 GMT -> 6139
A savoir que 41934 utilisateurs ne se sont jamais connectés (last_seen = 0) à l'application et n'ont pas uploadés de fichiers ou créer de playlist.
Concernant la table user_activity dans la base de données (4188632 lignes):
# Mon, 01 Jan 2018 09:46:40 GMT -> 138948
# Sun, 01 Jan 2017 05:53:20 GMT -> 145686
# Fri, 01 Jan 2016 12:06:40 GMT -> 149563
# Thu, 28 May 2015 05:13:20 GMT -> 149563
# Thu, 01 Jan 2015 05:26:40 GMT -> 149563
Le nombre de lignes qui ont un user avec comme id -1 est estimé à '4188632' en 2015, c'est presque la totalité de la table. Les activités des utilisateurs inactifs et n'ayant pas uploadés un fichier et/ou playlist est marginal en comparaison.
Concernant la table object_count dans la base de données (5559116 lignes):
Entre 290000 et 300000 lignes pour les 5 valeurs. Une nouvelle fois, c'est l'utilisateur -1 qui prends le plus de lignes avec 5259965 lignes. Les 3000000 lignes des utilisateurs inactifs et n'ayant pas uploadés un fichier et/ou playlist est marginal en comparaison.
Concernant la table user_preferences dans la base de données (3992901 lignes):
Liste des lignes de préférences concernant utilisateurs ayant eu une activités et ayant uploadés un fichier et/ou playlist depuis la date annoncées jusqu'à aujourd'hui
# 1546344000 -> Tue, 01 Jan 2019 12:00:00 GMT -> 379352
# 1514800000 -> Mon, 01 Jan 2018 09:46:40 GMT -> 468407
# 1483250000 -> Sun, 01 Jan 2017 05:53:20 GMT -> 489131
# 1451650000 -> Fri, 01 Jan 2016 12:06:40 GMT -> 514676
# 1432790000 -> Thu, 28 May 2015 05:13:20 GMT -> 535335
# 1420090000 -> Thu, 01 Jan 2015 05:26:40 GMT -> 541705
On peut observer ici que ce sont les utilisateurs inactifs qui génèrent le plus de données, environ 3690737 lignes correspondent aux utilisateurs qui ne sont pas connectés depuis janvier 2015 et n'ayant pas uploadés un fichier et/ou playlist. L'utilisateur -1 est ici marginal, à savoir 115 lignes.
Voici comment je vois les choses, je vous proposer d'y aller par étape.
Dans un premier temps:
- Regarder s'il y a des effets de bords à supprimer les lignes qui référencent l'identifiant -1, si non, faire le ménage sur les tables où il est référencé. Ce ménage devrait faire énormément de biens à l'usage normal de l'application.
Dans un second temps:
- Réduire le nombre de données personnelles (supprimer) pour les utilisateurs non connectés depuis 2015 et n'ayant pas uploadés un fichier et/ou playlist. A savoir : nom, prénom, website, localisation géographique. Aucune raison de les conserver si ce n'est prendre des risques inutiles. Il restera que les emails comme donnée personnelle.
Dans un troisième temps:
- Supprimer tous les utilisateurs inactifs depuis janvier 2015 et n'ayant pas uploadés un fichier et/ou playlist, soit environ 41000. S'ils ne se sont pas reconnectés en 5 ans, il est très peu probable qu'ils le fassent de nouveaux. D'autant plus qu'en général il s'agit des comptes qui ne se sont jamais connectés. D'un point de vue RGPD, je pense qu'on a le droit de les contacter à ce sujet pour les informer de la suppression futur de leur compte en cas de non connexion à celui-ci dans un délai de d'un mois. Après la suppression, il restera environ 6000 utilisateurs dans l'application dont 1000 ne s'est plus connectés depuis 3 ans.
Je ne m'occuperai pas de la partie communication.
Qu'en pensez-vous ? Pouvez-vous m'indiquer si vous êtes d'accord ou pas d'accord ? Pourquoi ? J'aurai besoin d'une sorte de validation officielle pour lancer le processus de suppression, sachant que dans tous les as, il y aura un backup de fait.
Désolé je débarque, mais si on supprime des utilisateurs que va afficher l'appli quand on visualisera les playlists/morceaux/albums créés par eux ?Autre info, quand l'archive est revenus en ligne en 2015, j'ai envoyer un mail à tous les labels pour leur annoncer qu'ils pouvaient revenir publier leur catalogue. Je crois qu'à 2-3 exceptions prêt (le colibri nécrophile par ex), aucun n'est revenus.
Pour ce qui est de prévenir tous ces comptes, est-ce qu'on a une moyen de le faire de manière automatisé ou faudra-t-il se taper 35000 mails à envoyer à la main ?Après discussion sur l'IRC, je raye les mentions inutiles
Du coup :
- Premier temps :
Ok pour moi.
- Second temps :
Là j'avoue que j'ai du mal, on a aussi une mission d'archive, c'est à dire de conservation de ce type de donnée. À minima on doit conserver les données que l'appli affiche aux users.
- Troisième temps :
Suppression des comptes avec aucune connexion et aucun contenu posté datant d'avant 2015 = je suis d'accord.
Je pense même qu'on peut même le faire sans avoir à notifier les gens par mail. Je veux dire c'est des robots non ?
Extra !
- Deuxième temps :
Je ne vois pas d'obstacle, puisque les comptes sont vides ou presque, depuis 2015.
- Troisième temps :
Idem pour les suppressions de non-logués / activés, d'avant 2015, et même à partir de 2015, si non-activité.
ça bosse, ça bosse !
41934 utilisateurs sans manifestation de présence ? (!!!) Ou fantômes, ou Fakes, ou Remplisseurs de vide.
Merci pour le taf !
Première étape : OK
Seconde étape : OK sur le principe, conserver autant de temps ces données persos me pose question. J'ai eu quelques mails de demande de suppression de ces infos.
Troisième étape : demande de l'investissement technique pour pouvoir envoyer x mails sans se faire blacklister en spammeur (qui sait faire ?).
Différence à faire entre compte inactifs et comptes s'étant déjà connecté ? (On pourrait trier les robots comme ça, non ?). Quelques comptes inactifs sont dans doute des essais de création de compte.
Je reviens vers vous avec quelques nouvelles.
En regardant en détail la table object_count, je me suis aperçu qu'il y avait un certain nombre de robots qui peuplaient les historiques d'actions. Je les ai identifié par le user_agent et j'en ai comptait 73.
73, cela ne paraît pas beaucoup mais ces 73 robots ont généré plus de 4.186.431 lignes dans la table object_count. Pour aller plus loin, j'ai recherché le nombre de lignes regroupés par user_agent. Ce qui m'a permis d'identifier d'autres lignes avec des user-agent non légitimes et m'a permis d'enlever 248639 lignes supplémentaires.
Avant mes actions d'aujourd'hui, la table object_count faisait 5.559.116 de lignes. Après le nettoyage des robots, elle n'en fait plus que 1.278.303. Ce qui représente une diminution de 77% des lignes présentes.
En parallèle, afin que ce genre de nettoyage ne soit pas à refaire, j'ai ajouté une tache journalière pour purger ce type d'activités.
Le ménage réalisé a dû améliorer certaines actions, le plus gros des ralentissements observés restent dû à la table user_activity, la prochaine étape