{"id":36704,"date":"2019-10-31T22:13:15","date_gmt":"2019-10-31T19:13:15","guid":{"rendered":"https:\/\/prohoster.info\/blog\/optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej\/"},"modified":"2019-10-31T22:13:15","modified_gmt":"2019-10-31T19:13:15","slug":"optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej","status":"publish","type":"post","link":"https:\/\/prohoster.info\/it\/blog\/administrirovanie\/optimizatsiya-zaprosov-bazy-dannyh-na-primere-b2b-servisa-dlya-stroitelej","title":{"rendered":"Ottimizzazione delle query del database nell'esempio di un servizio B2B per costruttori","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Come far crescere le richieste al database dieci volte senza passare a un server pi\u00f9 potente e mantenendo la funzionalit\u00e0 del sistema? Vi racconter\u00f2 come abbiamo affrontato il calo delle prestazioni del nostro database, come abbiamo ottimizzato le query SQL per servire il maggior numero possibile di utenti senza aumentare le spese per le risorse di calcolo.
\n<\/a><\/noindex>
\nFaccio un servizio per la gestione dei processi aziendali nelle aziende di costruzione. Collaboriamo con circa 3.000 aziende. Oltre 10.000 persone lavorano ogni giorno con il nostro sistema per 4-10 ore. Risolve vari compiti di pianificazione, avviso, allerta, convalida... Utilizziamo PostgreSQL 9.6. Nel nostro database ci sono circa 300 tabelle e ogni giorno riceve fino a 200 milioni di richieste (10.000 diverse). In media abbiamo 3-4.000 richieste al secondo, nei momenti pi\u00f9 attivi oltre 10.000 richieste al secondo. La maggior parte delle richieste \u00e8 OLAP. Aggiunte, modifiche e cancellazioni sono di gran lunga inferiori, cio\u00e8 il carico OLTP \u00e8 relativamente ridotto. Ho fornito tutti questi numeri per darvi un'idea della portata del nostro progetto e per capire quanto la nostra esperienza possa esservi utile.<\/p>\n

Immagine prima. Lirica<\/h3>\n

\nQuando abbiamo iniziato lo sviluppo, non ci siamo particolarmente preoccupati del carico che avrebbe sopportato il database e di cosa avremmo fatto se il server non ce l'avesse fatta. Nella progettazione del database siamo stati guidati da raccomandazioni generali e abbiamo cercato di non spararci nelle piedi, ma oltre ai consigli generali come 'non usare il pattern Entity Attribute Values<\/a><\/noindex> non siamo andati. Abbiamo progettato basandoci sui principi di normalizzazione evitando la ridondanza dei dati e non ci siamo preoccupati di accelerare determinate richieste. Non appena sono arrivati i primi utenti, ci siamo trovati di fronte a problemi di prestazioni. Come al solito, eravamo assolutamente impreparati. I primi problemi si sono rivelati semplici. In genere, tutto si risolveva aggiungendo un nuovo indice. Ma \u00e8 arrivato un momento in cui le semplici soluzioni hanno smesso di funzionare. Rendendoci conto che ci mancava esperienza e che era sempre pi\u00f9 difficile capire qual era la causa dei problemi, abbiamo assunto specialisti che ci hanno aiutato a configurare correttamente il server, a collegare il monitoraggio, ci hanno mostrato dove guardare per ottenere statistiche<\/a><\/noindex>.<\/p>\n

Immagine seconda. Statistica<\/h3>\n

\nAbbiamo quindi circa 10.000 diverse query che vengono eseguite sul nostro DB in un giorno. Di queste 10.000 ci sono dei mostri che vengono eseguiti 2-3 milioni di volte con un tempo medio di esecuzione di 0,1-0,3 ms e ci sono query con un tempo medio di esecuzione di 30 secondi, invocate 100 volte al giorno.<\/p>\n

Ottimizzare tutte e 10.000 le query non si \u00e8 rivelato possibile, quindi abbiamo deciso di capire dove concentrare gli sforzi per migliorare correttamente le prestazioni del DB. Dopo alcune iterazioni, abbiamo iniziato a suddividere le query in categorie.<\/p>\n

QUERY TOP<\/h4>\n

\nQueste sono le query pi\u00f9 pesanti, che richiedono pi\u00f9 tempo (tempo totale). Sono query che vengono invocate molto frequentemente o query che richiedono molto tempo per essere completate (le query lunghe e frequenti sono state ottimizzate nelle prime iterazioni nella corsa per la velocit\u00e0). Di conseguenza, il server spende pi\u00f9 tempo sulla loro esecuzione. \u00c8 importante separare le query top in base al tempo di esecuzione totale e separatamente in base al tempo di IO. I metodi di ottimizzazione per queste query sono leggermente diversi.<\/p>\n

La pratica comune di tutte le aziende \u00e8 lavorare con le QUERY TOP. Ce ne sono poche, l'ottimizzazione anche di una sola query pu\u00f2 liberare il 5-10% delle risorse. Tuttavia, man mano che il progetto \u201ccresce\u201d, l'ottimizzazione delle QUERY TOP diventa un compito sempre pi\u00f9 non banale. Tutti i metodi semplici sono gi\u00e0 stati sfruttati, e la query \u201cpi\u00f9 pesante\u201d consuma \u201csolo\u201d il 3-5% delle risorse. Se le QUERY TOP occupano complessivamente meno del 30-40% del tempo, \u00e8 probabile che abbiate gi\u00e0 fatto uno sforzo per farle funzionare rapidamente e sia giunto il momento di passare all'ottimizzazione delle query del gruppo successivo.
\nRimane da rispondere alla domanda su quante query superiori includere in questo gruppo. Di solito prendo non meno di 10, ma non pi\u00f9 di 20. Cerco di fare in modo che il tempo della prima e dell'ultima query nel gruppo TOP non differisca pi\u00f9 di 10 volte. Quindi, se il tempo di esecuzione delle query scende bruscamente dal 1\u00b0 posto al 10\u00b0, prendo il TOP-10; se il calo \u00e8 pi\u00f9 graduale, aumento le dimensioni del gruppo a 15 o 20.
\n\"Ottimizzazione
\n<\/p>\n

MEDIO<\/h4>\n

\nQueste sono tutte le query che seguono immediatamente le QUERY TOP, escluse le ultime 5-10%. Di solito, nell'ottimizzazione di queste query si trova la possibilit\u00e0 di aumentare notevolmente le prestazioni del server. Queste query possono rappresentare fino all'80%. Ma anche se la loro quota supera il 50%, \u00e8 tempo di esaminarle con maggiore attenzione.<\/p>\n

CODA<\/h4>\n

\nCome detto, queste richieste arrivano alla fine e richiedono il 5-10% del tempo. Si possono dimenticare, a meno che non si utilizzino strumenti automatici per l'analisi delle richieste; in tal caso, l'ottimizzazione potrebbe anche risultare poco costosa.<\/p>\n

Come valutare ogni gruppo?<\/p>\n

Uso una query SQL che aiuta a fare tale valutazione per PostgreSQL (sono sicuro che per molti altri DBMS si possa scrivere una query simile)<\/p>\n

Query SQL per valutare la dimensione dei gruppi TOP-MEDIUM-TAIL<\/b><\/p>\n

SELECT sum(time_top) AS sum_top, sum(time_medium) AS sum_medium, sum(time_tail) FROM ( SELECT CASE WHEN rn  20 AND rn  800 THEN tt_percent ELSE 0 END AS time_tail FROM ( SELECT total_time \/ (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query, ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn FROM pg_stat_statements ORDER BY total_time DESC ) AS t ) AS ts\n<\/code><\/pre>\n
Il risultato della query consiste in tre colonne, ognuna delle quali contiene la percentuale di tempo spesa per trattare le richieste di questo gruppo. All'interno della query ci sono due numeri (nel mio caso 20 e 800), che separano le richieste di un gruppo da un altro.<\/p>\n
Ecco come si rapportano le quote delle richieste all'inizio dei lavori di ottimizzazione e ora.<\/p>\n
\"Ottimizzazione
\n
\nDal diagramma \u00e8 evidente che la quota delle richieste TOP \u00e8 drasticamente diminuita, mentre sono aumentate quelle \"medie\".
\nAll'inizio le richieste TOP includevano evidenti errori. Col tempo, i problemi iniziali sono scomparsi, la quota delle richieste TOP si \u00e8 ridotta e abbiamo dovuto impegnarci sempre di pi\u00f9 per velocizzare le richieste pesanti. <\/p>\n
Per ottenere il testo delle richieste utilizziamo la seguente query<\/b><\/p>\n
SELECT * FROM ( SELECT ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn, total_time \/ (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query FROM pg_stat_statements ORDER BY total_time DESC ) AS T WHERE rn  20 AND rn  800  -- TAIL\n<\/code><\/pre>\n
Ecco un elenco delle tecniche pi\u00f9 comunemente utilizzate che ci hanno aiutato a velocizzare le richieste TOP:<\/p>\n