Using MEMORY tables in MySQL is very useful; they are stored in memory and they are usually not so big in order to avoid a huge utilization of RAM. They are very fast.

MEMORY tables are often used to store session user data, chat messages and to store small frames of data from a bigger table in order to achieve great performance.

But, if you use them in a replication environment, you can have some problem due to their “volatility”; MEMORY table rows are lost when the server shuts down.

If you restart a slave host for some reason, you will have empty MEMORY tables on it, while the master host will have the correct ones.

How to populate automatically MEMORY tables at slave start up copying data from master host?

We could use something like LOAD TABLE table_name FROM MASTER, but it’s deprecated, so we will use something else.

I have developed a stored procedure you have to create on the slave host in a database called checkmysql.
Using a cursor and the information_schema database, the routine, at every loop, creates a federated table (fed) that points to a MEMORY table on the master host. The CREATE statements are given by the cursor itself. Such queries are executed as dynamic queries using prepared statements. Then, a simple INSERT INTO db.slave_mem_table SELECT * FROM fed (given by the cursor too and executed with a prepared statemtn) will populate the entire table on the slave.

Here’s the code:

DELIMITER //
CREATE PROCEDURE `sync_mem_tables`()
BEGIN
   DECLARE creationcmd TEXT;
   DECLARE insertquery TEXT;
   DECLARE c_memtables CURSOR FOR
      SELECT
         CONCAT('CREATE TABLE checkmysql.fed(',
         GROUP_CONCAT(column_name,' ',column_type)),
         ') engine=federated connection='mysql://my_user:my_pwd@master_host/',
         t.table_schema,
         '/',
         t.table_name,
         ''') AS creation_cmd,
         CONCAT('INSERT INTO ',t.table_schema,'.',t.table_name,' SELECT * FROM fed') AS insert_query
      FROM information_schema.columns c JOIN information_schema.tables t
      ON c.table_name=t.table_name AND c.table_schema=t.table_schema
      WHERE engine='memory' AND t.table_schema NOT IN ('information_schema')
      GROUP BY t.table_schema, t.table_name;
   DROP TABLE IF EXISTS checkmysql.fed;
   OPEN c_memtables;
   BEGIN
      DECLARE EXIT HANDLER FOR SQLSTATE '02000' BEGIN END;
      LOOP
         FETCH c_memtables INTO creationcmd, insertquery;
         SET @v_creationcmd = creationcmd;
         SET @v_insertquery = insertquery;
         PREPARE ps_cc FROM @v_creationcmd;
         EXECUTE ps_cc;
         DEALLOCATE PREPARE ps_cc;
         PREPARE ps_insq FROM @v_insertquery;
         EXECUTE ps_insq;
         DEALLOCATE PREPARE ps_insq;
         DROP TABLE checkmysql.fed;
      END LOOP;
   END;
   CLOSE c_memtables;
END//
DELIMITER ;

Now, the stored procedure sync_mem_table() has to be executed during start up phase of the slave.
To do such a thing you have to create a simple text file initfile.sql (this name is not mandatory) with the CALL statement.

shell# cat /etc/mysql/initfile.sql
call checkmysql.sync_mem_tables();

then you have to add the variable init-file in my.cnf (MySQL configuration file) in the section [mysqld]:

init-file       = /etc/mysql/initfile.sql

So, you don’t have to worry about MEMORY tables shutting down a slave host!

Pay attention to:
1 - you have to create the procedure on the slaves, not on the master
2 - the procedure is a Beta release; no warranty for you if you decide to use it on your servers
3 - to populate every MEMORY table on every database, you have to create the procedure as the administrator user

Thanks

Giornata ad inseguire le performance del nostro cluster.
Di traffico ce ne è stato parecchio e, complice un paio di cache che hanno sbarellato, ci siamo ritrovati con Veleno carico oltre 150.
Perseguitato da Stewart e compagni che non riuscivano ad inserire le agognate notizie ho improvvisato una soluzione per consentire almeno a loro di operare: ho cambiato il master di MySQL da Veleno a Zio istruendo lo slave Balilla opportunamente. La cosa ha funzionato anche se sono rimasti in giro script malefici che continuavano ad aggiornare ancora Veleno … rendendomi poi più difficile la risincronizzazione dei dati. Beh, ha funzionato.
Alla fine abbiamo disabilitato le due cache e, con una sola, abbiamo retto bene il resto della giornata.
Ho ripristinato il master su Veleno, con qualche casino dovuto al riallineamento dei dati, ma tutto sembra essere tornato nei ranghi della normalità.
Sarà comunque opportuno affrontare al più presto un ripensamento del parco macchine e della struttura del nostro cluster.

Meno male che sto’ scudetto finalmente è arrivato. Non se ne poteva più del limbo in cui era finito il restyling … oltre che delle fisime del Muta.

Devo dire che lo switching è stato pressoché indolore, ci sono stati meno problemi del previsto. E’ bastato copiare qualche file, risistemare un paio di condizioni in altrettanti script e lanciare un programmino opportunamente predisposto e il sito ha cambiato faccia all’istante.
Un po’ di casino sui server visto l’alto traffico, ma c’era da aspettarselo!

E’ la vittoria di tutti.
Tutto molto bello e tutti molto bravi … peccato solo che a noi personalmente non ne verrà in tasca nulla …

Usare le tabelle di tipo MEMORY da’ degli indubbi vantaggi in termini di prestazioni; sono in memoria e, quasi sempre, chi le utilizza non le fa di grandi dimensioni. Sono molto utilizzate per registrare dati temporanei tipo sessioni utente, messaggi di chat o per mantenere porzioni di dati di tabelle più grosse che devono essere acceduti spesso. Una bella comodità.
Se vengono però usate nell’ambito di una architettura replicata c’è qualche problema in relazione alla loro volatilità. Se uno slave viene ad esempio riavviato per qualsiasi motivo (hardware failure o per una modifica di configurazione), il contenuto di tali tabelle viene perso e ci si ritrova con una serie di tabelle vuote disallineate rispetto alle omologhe del master. In taluni casi questo può essere un problema non sopportabile.

Come possiamo fare in modo che al riavvio di uno slave tutte le tabelle MEMORY si riallineino automaticamente con le copie presenti sul master, utilizzando solo strumenti forniti dal DBMS (quindi niente linguaggi di programmazione tipo Perl o simili)?

MySQL mette a disposizione un comando del tipo LOAD TABLE tabella FROM MASTER, che sarebbe anche comodo, ma purtroppo è considerato obsoleto e siccome verrà tolto in una delle prossime release non ne faremo uso. Utilizziamo quindi altri strumenti che non dovrebbero scomparire in futuro.

Quello che faremo è di creare sullo slave, per ogni tabella MEMORY, una tabella FEDERATED definita allo stesso modo, che punti alla memory omologa del master. Fatto ciò con un semplice INSERT INTO tabella_memory SELECT * FROM tabella_federated potremo ripopolare la tabella on-the-fly.

Per farlo scriviamo una stored procedure che ricava da information_schema tutte le tabelle MEMORY definite nel DBMS insieme alla relativa definizione dei campi. Il cursore utilizzato fornisce ad ogni ciclo le query necessarie: quella per creare la FEDERATED con la corretta struttura e quella di INSERT dei record. Le due query vengono eseguite con l’ausilio di prepared statement non parametrizzati.

Ecco di seguito il codice completo:

CREATE PROCEDURE `sync_mem_tables`()
BEGIN
   DECLARE creationcmd TEXT;
   DECLARE insertquery TEXT;
   DECLARE c_memtables CURSOR FOR
      SELECT
         CONCAT('CREATE TABLE checkmysql.fed(',
         GROUP_CONCAT(column_name,' ',column_type)),
         ') engine=federated connection='mysql://mio_utente:mia_password@master_host/',
         t.table_schema,
         '/',
         t.table_name,
         ''') AS creation_cmd,
         CONCAT('INSERT INTO ',t.table_schema,'.',t.table_name,' SELECT * FROM fed') AS insert_query
      FROM information_schema.columns c JOIN information_schema.tables t
      ON c.table_name=t.table_name AND c.table_schema=t.table_schema
      WHERE engine='memory' AND t.table_schema NOT IN ('information_schema')
      GROUP BY t.table_schema, t.table_name;
   DROP TABLE IF EXISTS checkmysql.fed;
   OPEN c_memtables;
   BEGIN
      DECLARE EXIT HANDLER FOR SQLSTATE '02000' BEGIN END;
      LOOP
         FETCH c_memtables INTO creationcmd, insertquery;
         SET @v_creationcmd = creationcmd;
         SET @v_insertquery = insertquery;
         PREPARE ps_cc FROM @v_creationcmd;
         EXECUTE ps_cc;
         DEALLOCATE PREPARE ps_cc;
         PREPARE ps_insq FROM @v_insertquery;
         EXECUTE ps_insq;
         DEALLOCATE PREPARE ps_insq;
         DROP TABLE checkmysql.fed;
      END LOOP;
   END;
   CLOSE c_memtables;
END

La routine presuppone di essere creata in un database specifico dello slave che si chiama checkmysql, bisogna quindi preventivamente crearlo, e l’utente deve avere tutti i privilegi di amministratore.

Creata la routine non resta altro che dire al server di eseguirla durante la fase di start-up. Per farlo creiamo un file dal nome initfile.sql contenente il comando:

shell# cat /etc/mysql/initfile.sql
call checkmysql.sync_mem_tables();

e aggiungiamo la seguente riga nella sezione [mysqld] di my.cnf (file di configurazione di MySQL)

init-file       = /etc/mysql/initfile.sql

Ecco fatto. Adesso ad ogni riavvio tutte le tabelle MEMORY dello slave saranno automaticamente riallineate.

Attenzione 1: non c’è niente da fare sul master, si fa tutto sul singolo slave.
Attenzione 2: se qualcuno vuole provare questa soluzione faccia pure, ma si ricordi che non risponderò di eventuali casini generatisi, il tutto è da considerarsi in “beta version”.
Attenzione 3: nella definizione del cursore bisogna sostuire nella parte mysql://mio_utente:mia_password@master_host/ i propri dati per accedere al master. Per il futuro prevedo di parametrizzarli.

Se qualcuno la prova, mi segnali migliorie o eventuali problemi. Grazie.

Vino rosso, scamorzine alla piastra con coppa di Parma, focaccia con pancetta toscana, fiorentina con radicchio e caffè.

Per noi del web lab è stato comunque un gran successo.