Activity Feed in MySQL!

[monteduro]

Certo, il modo migliore, probabilmente, sarebbe quello di utilizzare un NoSQL database, o Redis, ma in fase di startup comporta diversi costi e quindi, proviamo a usare MySQL!

Il succo della questione è questo:

Realizzare un Acitivity Feed (come FB, Twitter insomma) in MySQL senza ammazzare le risorse con query che utilizzino SELETE ... FROM activity_feed WHERE uid IN (mille id di persone che seguo)

Leggendo qua e la si capisce chiaramente che esistono due tipi di gestione per la creazione di un sistema del genere e sono Pull e Push. Vale a dire inserire questi dati un un sistema indicizzato dove ogni utente ha una PROPRIA lista di attività da visualizzare a seconda degli utenti che segue.

Con il metodo Pull, questa lista, viene generata nel momento in cui si cerca di visualizzare il news feed, ed è credo sconsigliabile. Perché comporterebbe la fatica di trovare le attività che l'utente deve visualizzare ATTRAVERSO alcune interrogazioni. Tipo vedere l'ultima attività importata nella lista e scorrere la tabella activity_feed cercando nuove attività delle persone che segue.

Il metodo Push invece, concede la possibilità di inserire una nuova attività fatta da un qualsiasi utente nella lista delle attività da visualizzare delle persone che lo seguono. Quindi avviene nel momento in cui l'attività viene creata. Questo permette all'utente che segue di richiedere la pagina Activity Feed e visualizzare, senza nessun processo oltre alla query di interrogazione dei dati, le notizie. Ovviamente, il problema di questo metodo è che nel momento in cui si crea un attività, si devono aggiornare le liste di tutti i follower (che potrebbero anche essere 100.000, ipotizziamo).

Oltre a chiedervi quale dei due metodi ritenete migliori, volevo anche ipotizzare una struttura possibile de DB.

Premesso che l'attività da visualizzare deve essere registrata in un campo TEXT come JSON, perché comporta diverse interrogazioni (e non è un problema, perché non si deve eseguire nessuna ricerca su questi dati), ecco cosa ho pensato.


ACTIVITY_FEED <- fixed/length
-----------------
AID - INT, PRIMARY KEY, A_I <- identificativo univoco delle attività
UID - INT <- identificativo dell'utente che ha svolto l'attività
TYPE - ENUM(post,commento,like,etc) <- Tipo di attività svolta
RID - INT <- id realtivo all'attività svolta (tipo l'id del post scritto dall'utente o del commento)
TIMESTAMP - DATETIME <- il timestamp dell'attività

ACTIVITY_FEED_INDEX <- fixed/length
------------------
UID - INT <- ID dell'utente che deve visualizzare l'attività
AID - INT <- ID dell'attività che l'utente deve visualizzre
TIMESTAMP <- Il Timestamp dell'attività
PRIMARY KEY (uid,aid)

ACTIVITY_FEED_CACHE
------------------
AID - INT <- ID dell'attività in cache
CACHE_DATA - TEXT <- Contiene il JSON dell'attività con tutti i dettagli

Ora Activity_feed è identificativo per un eventuale rielaborazione della chace, nel caso l'attività venga modificata o altro.

L'idea è quella di inserire in ACTIVITY_FEED_INDEX le righe relative a tutti gli utenti che seguono l'utente che ha realizzato l'attività. Così, che in fase di richiesta della pagina, l'utente con una query del tipo:

SELECT cache.cache FROM activity_feed_index as index INNER JOIN activity_feed_cache AS cache ON cache.aid = index.aid WHERE index.uid = 'USER_ID'

potrà ottenere i risultati.

Vi sembra efficiente? E se no come impostereste questo tipo di interrogazione?

[franzauker2.0]

Originariamente inviato da monteduro
Realizzare un Acitivity Feed (come FB, Twitter insomma) in MySQL senza ammazzare le risorse con query che utilizzino SELETE ... FROM activity_feed WHERE uid IN (mille id di persone che seguo)

Cominci proprio malissimo, o anche no.
Se IN mille id è una lista statica, ovvero creata con group_concat ad esempio (e ammesso che la dimensione del pacchettone non risulti eccessiva) allora mysql è efficientissimo.
Se invece IN è una select, allora mysql è inusabile, e ci vuole mariadb.

Per il resto mi sembra troppo complicato per me, quindi mi limito a...

TYPE - ENUM(post,commento,like,etc) <- Tipo di attività svolta

niente enum, sono devastanti come portabilità. un normalissimo campo intero va benissimo

Vi sembra efficiente? E se no come impostereste questo tipo di interrogazione?

Ci vuole più di 4 secondi per capire cosa hai scritto => spiacente

[monteduro]

Cominci proprio malissimo, o anche no.

Effettivamente credo anche io di aver cominciato male.

Sono praticamente due giorni che mi dimeno tra decine di discussioni, articoli e quant'altro per venire a capo di questo dilemma, e pare che nella stragrande maggior parte dei casi tutti consiglino di utilizzare un sistema NoSQL, almeno per la fruizione delle attività in maniera più rapida dalla memoria del server (anche memcache sarebbe una buona soluzione, combinato con sql).

Il fatto è, come detto in precedenza, che per ora, l'unico strumento è mysql.

Ho letto anche un articolo di qualche anno fa (http://backchannel.org/blog/friendfeed-schemaless-mysql), su come FriendFeed utilizzasse mysql in maniera atipica. Creando una tabella entities, con un identificativo primario e un campo MEDIUMBLOB con i contenuti dell'entità e diverse tabelle per indicizzare quei contenuti. Tabelle con due soli campi: l'indice (per esempio l'id dell'utente che realizza l'attività) e l'id dell'entità.

In questo modo, con una join, era possibile ottenere i dati dalla tabella entities senza stressare eccessivamente il database (credo che lo stress si abbia una volta arrivati a centinaia di richieste per minuto, o per secondo) e senza appesantire la tabella.

Un metodo completamente denormalizzato, ma efficace, credo.

Il mio problema resta sempre quello della selezione di quali attività mostrare allo specifico utente. Se un utente ha più di 1000 followers, un GROUP CONCAT su una join come scritto in precedenza, potrebbe migliorare le prestazioni? Considerando anche l'ordinamento dei risultati?

Scusami se sono poco comprensibile a volte

PS: grazie per la dritta su enum, potrei eventualmente utilizzare dei valori identificativi numerici a seconda del tipo di attività.

[franzauker2.0]

Originariamente inviato da monteduro
Effettivamente credo anche io di aver cominciato male.

Sono praticamente due giorni che mi dimeno tra decine di discussioni (...)NoSQL (...)

Il 99.999999% delle discussioni sono di bimbiminkia o simili, che sparano buzzword pensando che servano a darsi un tono.

Il mio problema resta sempre quello della selezione di quali attività mostrare allo specifico utente. Se un utente ha più di 1000 followers, un GROUP CONCAT su una join come scritto in precedenza, potrebbe migliorare le prestazioni? Considerando anche l'ordinamento dei risultati?

Fatico a comprendere perchè vuoi usare una IN anzichè un normalissimo JOIN.
Il problema di IN (con lista statica) è che devi aumentare sensibilmente la dimensione all'uopo predisposta, altrimenti non puoi passare liste gigantesche.
L'ordinamento dei risultati non mi turba.
Il problema del JOIN è se hai indici con bassa selettività, ma dubito sia questo il caso.

La versione breve è: vuoi fare un sistema per un miliardo di utenti?
Francamente dubito, ma proprio fortissimamente, che avrei grandi problemi sotto il profilo prestazioni, ho macchine che servono 500 query al secondo (che sono una ogni 2 millisecondi, e con dischi meccanici non son poche).

PS per carità di dio non chiamare i campi AID, UID, solcazzo

[monteduro]

Fatico a comprendere perchè vuoi usare una IN anzichè un normalissimo JOIN.

Effettivamente hai ragione. Quindi, utilizzando la struttura proposta da FriendFeed, con indici esterni, potrei effettuare una query del genere:

SELECT e.body 
FROM indice_utenti as iu 
INNER JOIN followers as f ON f.utente_seguito = iu.user_id
INNER JOIN entities as e ON e.entitiy_id = iu.entity_id 
WHERE f.utente_che_segue = 'id dell'utente che richiede il feed'
ORDER BY e.timestamp DESC
LIMIT 0, 10

Non sarebbe comunque più produttivo creare una tabella con tutte le attività da visualizzare (magari con una limitazione a massimo 100 per utente) piuttosto che dover eseguire una query di questo tipo.

Quello che intendo dire è che i vantaggi degli indici esterni, in questo modo, diventano poi dei svantaggi. A questo punto basterebbe inserire un campo indice user_id nella tabella entities e joinare con i follower solo ed esclusivamente quella tabella.

Mamma mia HAHAHAHA

La versione breve è: vuoi fare un sistema per un miliardo di utenti?

Visto che sono in fase di sviluppo vorrei creare i presupposti per eventuali miglioramenti e aggiornamenti. Se una cosa la devi fare, tanto vale che la fai al meglio possibile, per farla durare il più possibile, no?

Poi credo anche io che a questo punto stia diventando più una sorta di ossessione questo discorso. Ma è formativo anche discutere, credo, non solo trovare soluzioni.

[franzauker2.0]

Originariamente inviato da monteduro
Effettivamente hai ragione. Quindi, utilizzando la struttura proposta da FriendFeed, con indici esterni, potrei effettuare una query del genere:

SELECT e.body 
FROM indice_utenti as iu 
INNER JOIN followers as f ON f.utente_seguito = iu.user_id
INNER JOIN entities as e ON e.entitiy_id = iu.entity_id 
WHERE f.utente_che_segue = 'id dell'utente che richiede il feed'
ORDER BY e.timestamp DESC
LIMIT 0, 10

Non so che struttura sia, e soprattutto perchè vuoi ordinare per e.timestamp.
Se le entità vengono inserite per ordine (come mi sembra abbastanza logico) ti basta ordinare per un campo seriale

Visto che sono in fase di sviluppo vorrei creare i presupposti per eventuali miglioramenti e aggiornamenti. Se una cosa la devi fare, tanto vale che la fai al meglio possibile, per farla durare il più possibile, no?

no.
la stima deve essere realistica, non ha senso dire "voglio un sistema per gestire 60 miliardi di utenti", se ci sono 6 miliardi di persone, così come non ha senso pianificare per 6 utenti, quando sai che saranno almeno 6000 quelli che lo utilizzeranno

[franzauker2.0]

Comunque, personalmente, non userei questo approccio, bensì memorizzerei brutalmente l'elenco degli ID da mostrare (il group_concat) in un campo varchar (se il tuo server ha abbastanza memoria) o addirittura TEXT (se non ce l'ha), settando opportunamente group_concat_max_len, eventualmente gestendo da applicazione l'aggiunta, cancellazione e purging delle attività da mostrare (ad esempio in tal modo eliminando il LIMIT)

[monteduro]

no.
la stima deve essere realistica

Sei veramente pragmatico, mi piace!

memorizzerei brutalmente l'elenco degli ID da mostrare (il group_concat) in un campo varchar

Quindi, se ho capito bene, invece di avere una riga per ogni attività da realizzare, avrei una riga per ogni utente, con le relative (ordinate e gestite dall'applicazione) attività da visualizzare. Che, successivamente, utilizzerei in una query con IN a questo punto, giusto?

Potrebbe anche questa essere una buona soluzione.

[franzauker2.0]

Originariamente inviato da monteduro
Quindi, se ho capito bene, invece di avere una riga per ogni attività da realizzare, avrei una riga per ogni utente, con le relative (ordinate e gestite dall'applicazione) attività da visualizzare. Che, successivamente, utilizzerei in una query con IN a questo punto, giusto?

Potrebbe anche questa essere una buona soluzione.

Non puoi avere veloce l'aggiornamento e veloce la lettura.
In questo modo ottieni veloce la lettura.

tabella utente
(...) idutente
elenco_che_segue 1,8,3,4,10,567

ipotizzando che non siano frequentissimi gli aggiornamenti di "cosa segui" allora il costo di aggiornare la lista potrebbe non essere punitivo.

Come si scelgie? semplice, si misura. ogni valutazione a tavolino è pressochè sempre spazzata via dalla situazione concreta, soprattutto in produzione.

Non aspettarti che un progetto, per quanto valido, sopravviverà agli utenti. In generale ciò non accade, se non per miniprogetti.