Database

Dove un sito è veloce o lento: nel suo database.

Installiamo, ottimizziamo, replichiamo e salviamo il database dietro la vostra applicazione, e dimostriamo che i backup funzionano ripristinandoli.

Richiedi un preventivo Vedi i piani di hosting

Funziona su e costruito con

MySQL
MariaDB
PostgreSQL
Redis
MongoDB
Elasticsearch

Sette motori, disegnati come un unico schema

Ogni riquadro è un database che installiamo, ottimizziamo e salviamo. Le linee mostrano come lavorano insieme nei sistemi che gestiamo - la maggior parte delle applicazioni ne usa due o tre, non uno.

SQLiteincorporato

Uso: App e strumenti con un solo file di dati e un solo scrittore alla volta
Regoliamo: journal_mode=WAL
Backup: Una copia a caldo del file, mai la copia di un file in uso

MySQLrelazionale

Uso: Negozi, WordPress e la maggior parte delle applicazioni PHP
Regoliamo: innodb_buffer_pool_size
Backup: Backup completi più log binari, a qualsiasi istante

Perché usiamo MySQL

Redisin memoria

Uso: Cache, sessioni, code e contatori
Regoliamo: maxmemory-policy
Backup: Snapshot e un file append-only

PostgreSQLrelazionale

Uso: Contabilità, ERP, report e query complesse
Regoliamo: shared_buffers · work_mem
Backup: Backup di base più WAL archiviato, a qualsiasi istante

MariaDBrelazionale

Uso: Gli stessi usi di MySQL, più i cluster Galera
Regoliamo: innodb_buffer_pool_size
Backup: mariabackup più log binari, a qualsiasi istante

OpenSearch · Elasticsearchricerca

Uso: Ricerca tra prodotti e articoli, filtri, log
Regoliamo: JVM heap · shards
Backup: Snapshot; l'indice si può ricostruire dal database principale

MongoDBdocumenti

Uso: Record dalla forma sempre diversa: eventi, cataloghi, contenuti
Regoliamo: wiredTiger cacheSizeGB
Backup: Un replica set, con l'oplog per un istante preciso

Quando un'app non sta più in un solo file, passa a un server di database.
MariaDB è nato come fork di MySQL; la maggior parte delle applicazioni passa dall'uno all'altro senza modifiche.
Redis sta davanti: le risposte ripetute e le sessioni restano in memoria.
La stessa cache davanti alle applicazioni PostgreSQL.
L'indice di ricerca è alimentato dal database principale, mai il contrario.
Il JSONB di PostgreSQL copre molti usi documentali prima che serva MongoDB.

Quale database per quale lavoro

Di solito decide l'applicazione, e noi la seguiamo. Dove la scelta è aperta, ecco come scegliamo.

Il lavoro	La nostra prima scelta	Va bene anche	Perché
Un negozio, un sito WordPress o un'applicazione PHP	MySQL · MariaDB	PostgreSQL	Ciò su cui l'applicazione è stata costruita e testata, con il supporto più ampio.
Contabilità, ERP e report con molte join	PostgreSQL	MySQL 8	Transazioni rigorose, funzioni finestra e un planner pensato per le query complesse.
Cache, sessioni, code e limiti di frequenza	Redis	Memcached	Risposte dalla memoria. Tenete lì ciò che si può ricostruire se va perso.
Ricerca tra prodotti o articoli	OpenSearch · Elasticsearch	MySQL · PostgreSQL full-text	Pertinenza, errori di battitura, filtri e faccette che una query LIKE non può dare. Per un piccolo catalogo basta la ricerca full-text del database stesso.
Un'app mobile, uno strumento desktop o un piccolo servizio interno	SQLite	—Nessun altro	Nessun server da gestire: un file, salvato copiandolo in modo sicuro.
Eventi, log o record dalla forma sempre diversa	MongoDB	PostgreSQL JSONB	Documenti flessibili; PostgreSQL quando servono anche join e transazioni.
Letture che superano un solo server	MySQL · MariaDB · PostgreSQL replicas	—Nessun altro	Le letture si distribuiscono sulle repliche mentre le scritture restano su un solo primario.

Cinque motori a confronto

Che cosa fa da solo ciascun motore, secondo la sua stessa documentazione. La parola accanto a ogni indicatore dice fin dove arriva.

Confronto tra MySQL, MariaDB, PostgreSQL, Redis e MongoDB
Funzionalità	MySQL	MariaDB	PostgreSQL	Redis	MongoDB
Uso	Negozi, WordPress e la maggior parte delle applicazioni PHP	Gli stessi usi di MySQL, più i cluster Galera	Contabilità, ERP, report e query complesse	Cache, sessioni, code e contatori	Record dalla forma sempre diversa: eventi, cataloghi, contenuti
Modello dei dati	Tabelle e righe, interrogate con SQL.	Tabelle e righe, interrogate con SQL.	Tabelle e righe, interrogate con SQL, con un tipo JSONB per i documenti.	Chiavi che contengono stringhe, hash, liste, insiemi, insiemi ordinati o stream.	Documenti simili a JSON in collezioni, con il linguaggio di interrogazione proprio di MongoDB.
Transazioni	Supportato InnoDB, il motore predefinito: commit, rollback e ripristino dopo un arresto anomalo.	Supportato Anche qui InnoDB è il motore predefinito.	Supportato Ogni modifica è una transazione che viene confermata o annullata.	In parte Un blocco di comandi viene eseguito uno dopo l'altro senza interruzioni, ma non c'è rollback.	Supportato Su più documenti, con replica set e cluster con sharding.
Dove vivono i dati di lavoro	Su disco; InnoDB tiene in memoria un buffer pool con i dati più usati.	Su disco; InnoDB tiene in memoria un buffer pool con i dati più usati.	Su disco; gli shared buffer tengono in memoria i dati più usati.	In memoria; snapshot e un file append-only la scrivono su disco.	Su disco; WiredTiger tiene in memoria una cache dei dati più usati.
Replica integrata	Supportato Dalla sorgente alle repliche, asincrona o semisincrona.	Supportato Dalla sorgente alle repliche, più Galera Cluster per il multi-primary sincrono.	Supportato Replica in streaming; le repliche hot standby possono rispondere alle query di lettura.	Supportato Dal leader alle repliche, asincrona per impostazione predefinita; le repliche possono servire le letture.	Supportato Replica set: più server con gli stessi dati e failover automatico.
Distribuire i dati su più server	Componente aggiuntivo NDB Cluster, un motore a parte che partiziona i dati automaticamente.	Componente aggiuntivo Il motore di archiviazione Spider, che distribuisce le tabelle su più server MariaDB (sharding).	In parte Il partizionamento per intervallo, elenco o hash divide una tabella all'interno di un solo server.	Supportato Redis Cluster distribuisce le chiavi tra i nodi.	Supportato Lo sharding è integrato, per collezione.
Ripristino a un istante preciso	Supportato a qualsiasi secondo coperto dal log	Supportato a qualsiasi secondo coperto dal log	Supportato a qualsiasi secondo coperto dal log	In parte fino all'ultima scrittura del file append-only	Supportato a qualsiasi secondo coperto dal log

Scorri di lato per vedere tutti e cinque i motori.

Supportato il motore lo fa da sé
In parte con una riserva
Componente aggiuntivo un componente o un motore separato

Le funzionalità variano con la versione, e la documentazione della versione che usi ha l'ultima parola.

Fonti: MySQL Reference Manual MariaDB Knowledge Base PostgreSQL Documentation Redis Documentation MongoDB Manual

Perché usiamo MySQL

Cinque livelli in cui un database diventa più veloce

Un database lento raramente si sistema in un solo punto. Questi sono i livelli, nell'ordine in cui di solito si guardano: prima la modifica più economica, per ultima la più costosa.

I numeri indicano l'ordine abituale del lavoro.

Query
Lo slow query log indica le query peggiori, ed EXPLAIN mostra perché ciascuna è lenta.
slow_query_log · pg_stat_statements · EXPLAIN ANALYZE
Schema e indici
Una tabella pensata per le domande a cui risponde, e l'indice giusto, trasformano la lettura di ogni riga in una ricerca breve: l'esempio qui sotto è un indice che fa esattamente questo.
CREATE INDEX · ADD INDEX
Impostazioni del motore
La memoria viene divisa tra database, applicazione e sistema, poi i buffer di ciascun motore vengono impostati di conseguenza.
innodb_buffer_pool_size · shared_buffers · work_mem · wiredTiger cacheSizeGB
Cache
Le risposte ripetute e le sessioni restano in memoria, così il database viene interrogato meno spesso.
maxmemory-policy
Hardware
Più memoria, dischi più veloci o più core del processore, una volta esaurito ciò che offrono i livelli sopra.
RAM · CPU · disk I/O

Ottimizzare anche il lato PHP

Una query lenta, trovata e corretta

Dietro la maggior parte delle pagine lente c'è una query. Lo slow query log la indica, EXPLAIN mostra perché è lenta, e spesso un solo indice è tutta la correzione.

SELECT id, total, created_at
FROM orders
WHERE customer_id = 4821
ORDER BY created_at DESC
LIMIT 20;

Una query di esempio su una tabella di esempio

Prima

type: ALL
key: NULL
Extra: Using where; Using filesort

Legge ogni riga della tabella, poi ordina ciò che ha trovato.

La correzione: un indice

ALTER TABLE orders
  ADD INDEX idx_customer_created (customer_id, created_at);

Dopo

type: ref
key: idx_customer_created
Extra: Backward index scan

Legge solo le righe di questo cliente, già in ordine.

Un backup conta quando è stato ripristinato

Una copia notturna da sola fa perdere la giornata. Conservando anche il log delle modifiche, possiamo riportare il database al secondo prima di un errore.

Backup completo notturno00:00 Ripristinato fino a qui14:31:59 Un DELETE senza WHERE14:32:00

Ogni modifica, scritta nel log nel momento in cui avviene Rieseguito sul backup

Una giornata di esempio. Gli orari sono inventati.

Ripristinare l'ultimo backup completo
Su un server separato, così il database in produzione resta com'è mentre lavoriamo.
Rieseguire il log fino all'istante prima
I log binari per MySQL e MariaDB, il WAL archiviato per PostgreSQL, l'oplog per MongoDB.
Verificarlo, poi riportare i dati
O le righe perse vengono ricopiate, o la copia ripristinata prende il posto dell'originale - la decisione è vostra, con la differenza davanti agli occhi.

Il ripristino a un istante preciso dipende dal motore:

MySQL · MariaDB · PostgreSQL · MongoDBa qualsiasi secondo coperto dal log
Redisfino all'ultima scrittura del file append-only
SQLitefino all'ultima copia del file
OpenSearch · Elasticsearchfino all'ultimo snapshot, poi ricostruito dalla fonte

I test di ripristino fanno parte della cura che concordiamo con voi: su un server separato, con un calendario fisso, ogni risultato messo per iscritto. Un backup che nessuno ha ripristinato è una speranza, non un backup.

Il resto del lavoro

Ciò di cui un database ha bisogno dal giorno dell'installazione al giorno dell'aggiornamento, e le impostazioni che ogni passo tocca.

Installazione e dimensionamento
La versione supportata dalla vostra applicazione, dal repository ufficiale del produttore. La memoria viene divisa tra database, applicazione e sistema prima di impostare qualsiasi altra cosa.
innodb_buffer_pool_size · shared_buffers · maxmemory · -Xmx
Indici e query lente
Lo slow query log viene letto, le query peggiori spiegate, e gli indici aggiunti o rimossi annotandone il motivo.
slow_query_log · pg_stat_statements · EXPLAIN ANALYZE
Replica
Una seconda copia che segue la prima, per distribuire le letture e subentrare quando il primario cade.
GTID · Galera · streaming replication · replica set
Sicurezza
In ascolto solo sulla rete privata, un utente per applicazione con i soli permessi necessari, e connessioni cifrate ovunque il traffico lasci il server.
bind-address · TLS · GRANT · SCRAM · ACL
Monitoraggio
Connessioni, ritardo di replica, spazio su disco e query lente, con un avviso prima che un limite venga raggiunto, non dopo.
max_connections · Seconds_Behind_Source · pg_stat_replication
Aggiornamenti
Provati prima su una copia, le repliche prima del primario, e la via del ritorno scritta prima di iniziare.
pg_upgrade · mariadb-upgrade · rolling restart

Diteci cosa sta facendo il vostro database

Quale motore, quanto è grande, dove gira e cosa vi preoccupa. Prima guardiamo, poi prepariamo il preventivo per la messa in opera o la cura dopo una conversazione.

Richiedi un preventivo Vedi i piani di hosting

Servizi correlati

Velocità PHP OPcache, pool PHP-FPM, cache e query: dove vanno i millisecondi e come li riprendiamo.Servizio gestito Il server lo gestiamo noi: aggiornamenti, monitoraggio, backup, guasti.Server dedicati Una macchina intera, pronta in poche ore, gestita dalla stessa squadra.Laravel Applicazioni Laravel con code, pianificazioni e rilasci che non fermano il sito.