{"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 con un esempio di servizio B2B per costruttori","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Come crescere dieci volte il numero di richieste al database senza passare a un server pi\u00f9 potente, mantenendo al contempo 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 i costi delle risorse di calcolo.
\n<\/a><\/noindex>
\nSto sviluppando un servizio per la gestione dei processi aziendali nelle imprese di costruzione. Operiamo con circa 3.000 aziende. Oltre 10.000 persone utilizzano il nostro sistema ogni giorno, per 4-10 ore. Esso risolve varie attivit\u00e0 di pianificazione, notifiche, avvisi, convalide... Utilizziamo PostgreSQL 9.6. Nel nostro database abbiamo circa 300 tabelle e ogni giorno riceve fino a 200 milioni di richieste (10.000 diverse). In media, abbiamo 3.000-4.000 richieste al secondo, nei momenti di maggior attivit\u00e0 oltre 10.000 richieste al secondo. La maggior parte delle richieste sono OLAP. Aggiunte, modifiche e cancellazioni sono molto meno comuni, quindi il carico OLTP \u00e8 relativamente ridotto. Ho fornito tutte queste cifre affinch\u00e9 possiate valutare la portata del nostro progetto e capire quanto la nostra esperienza possa essere utile per voi.<\/p>\n

Primo quadro. Lirico<\/h3>\n

\nQuando abbiamo iniziato lo sviluppo, non ci siamo particolarmente preoccupati del carico che la base dati avrebbe dovuto sostenere e di cosa avremmo fatto se il server non avesse pi\u00f9 retto. Durante la progettazione della base dati, abbiamo seguito raccomandazioni generali e cercato di non spararci sui piedi, ma oltre ai consigli generali come \"non utilizzare il pattern Entity Attribute Values<\/a><\/noindex> , non siamo andati. Abbiamo progettato sulla base dei principi di normalizzazione, evitando la ridondanza dei dati e non ci siamo preoccupati di velocizzare determinate query. Non appena sono arrivati i primi utenti, ci siamo trovati di fronte a problemi di prestazioni. Come al solito, ci siamo trovati completamente impreparati. I primi problemi sono stati semplici. Di norma, tutto \u00e8 stato risolto con l'aggiunta di un nuovo indice. Ma \u00e8 arrivato un momento in cui le semplici soluzioni hanno smesso di funzionare. Rendendoci conto che non avevamo esperienza e che era sempre pi\u00f9 difficile capire quale fosse la causa dei problemi, abbiamo assunto specialisti che ci hanno aiutato a configurare correttamente il server, collegare il monitoraggio, e ci hanno mostrato dove guardare per ottenere statistiche<\/a><\/noindex>.<\/p>\n

Secondo quadro. Statistico<\/h3>\n

\nAbbiamo circa 10.000 diverse query che vengono eseguite nel nostro database ogni giorno. Di queste 10.000, ci sono mostri che vengono eseguiti 2-3 milioni di volte con tempi di esecuzione medi di 0,1-0,3 ms, e ci sono query con tempi di esecuzione medi di 30 secondi, che vengono chiamate 100 volte al giorno.<\/p>\n

Ottimizzare tutte le 10.000 query non era possibile, quindi abbiamo deciso di capire dove concentrare gli sforzi per migliorare correttamente le performance del database. Dopo diverse 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 chiamate molto frequentemente o query che impiegano molto tempo per essere eseguite (quelle lunghe e frequenti erano state ottimizzate gi\u00e0 nelle prime iterazioni per migliorare la velocit\u00e0). Alla fine, il server spende complessivamente pi\u00f9 tempo per eseguirle. \u00c8 importante distinguere le query top per il tempo di esecuzione totale e separatamente per il tempo IO. I metodi di ottimizzazione per queste query sono leggermente diversi.<\/p>\n

\u00c8 pratica comune per tutte le aziende lavorare con le parole chiave TOP. Ce ne sono poche, e l'ottimizzazione anche di una sola parola chiave pu\u00f2 liberare il 5-10% delle risorse. Tuttavia, man mano che il progetto 'cresce', l'ottimizzazione delle parole chiave TOP diventa sempre pi\u00f9 una sfida non banale. Tutti i metodi semplici sono gi\u00e0 stati esauriti, e persino la parola chiave pi\u00f9 'pesante' consuma 'solo' il 3-5% delle risorse. Se le parole chiave TOP occupano complessivamente meno del 30-40% del tempo, \u00e8 probabile che tu abbia gi\u00e0 fatto uno sforzo affinch\u00e9 funzionassero rapidamente, e sia giunto il momento di passare all'ottimizzazione delle parole chiave del gruppo successivo.
\nRimane da rispondere alla domanda su quante parole chiave nella parte alta includere in questo gruppo. Di solito ne prendo almeno 10, ma non pi\u00f9 di 20. Cerco di fare in modo che il tempo di esecuzione del primo e dell'ultimo nella TOP group non differisca di pi\u00f9 di dieci volte. Cio\u00e8, se il tempo di esecuzione delle query diminuisce nettamente dal primo al decimo posto, prendo il TOP-10; se la diminuzione \u00e8 pi\u00f9 graduale, aumento il numero del gruppo a 15 o 20.
\n\"Ottimizzazione
\n<\/p>\n

Parole chiave medie (medium)<\/h4>\n

\nQueste sono tutte le query che seguono immediatamente TOP, escluse le ultime 5-10%. Di solito, l'ottimizzazione di queste query offre l'opportunit\u00e0 di migliorare significativamente le prestazioni del server. Queste query possono costituire fino all'80%. Ma anche se la loro percentuale supera il 50%, \u00e8 il momento di osservarle pi\u00f9 da vicino.<\/p>\n

Coda (tail)<\/h4>\n

\nCome detto, queste query si trovano alla fine e richiedono il 5-10% del tempo. Possono essere ignorate, a meno che non si utilizzino strumenti analitici automatici; in tal caso, la loro ottimizzazione pu\u00f2 essere comunque a basso costo.<\/p>\n

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

Utilizzo una query SQL che aiuta a fare tale valutazione per PostgreSQL (sono sicuro che si possa scrivere una query simile per molti altri DBMS)<\/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) AS sum_tail\nFROM\n(\n  SELECT CASE WHEN rn  20 AND rn  800 THEN tt_percent ELSE 0 END AS time_tail\n  FROM (\n    SELECT total_time \/ (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query,\n    ROW_NUMBER() OVER (ORDER BY total_time DESC) AS rn\n    FROM pg_stat_statements\n    ORDER BY total_time DESC\n  ) AS t\n)\nAS ts\n<\/code><\/pre>\n
Il risultato della query consiste in tre colonne, ciascuna delle quali contiene la percentuale di tempo impiegato per elaborare le richieste di questo gruppo. All'interno della query ci sono due numeri (nel mio caso sono 20 e 800) che separano le richieste di un gruppo dall'altra.<\/p>\n
Ecco come si rapportano le quote delle richieste al momento dell'inizio dei lavori di ottimizzazione e adesso.<\/p>\n
\"Ottimizzazione
\n
\nDal grafico si evince che la quota delle richieste TOP \u00e8 diminuita drasticamente, mentre sono aumentate le \"richieste medie\".
\nInizialmente, le richieste TOP includevano evidenti errori. Col tempo, i difetti iniziali sono scomparsi, la quota delle richieste TOP si \u00e8 ridotta, e abbiamo dovuto fare sempre pi\u00f9 sforzi per velocizzare le richieste pesanti. <\/p>\n
Per ottenere il testo delle richieste, utilizziamo questa query<\/b><\/p>\n
SELECT * FROM (\n  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\n  FROM pg_stat_statements\n  ORDER BY total_time DESC\n) AS T\nWHERE\nrn  20 AND rn  800  -- TAIL\n<\/code><\/pre>\n
Ecco l'elenco delle tecniche pi\u00f9 comunemente utilizzate che ci hanno aiutato a velocizzare le richieste TOP:<\/p>\n