[acao-it] Analisi situazione SQL server
Daniele Orlandi
daniele at orlandi.com
Tue Nov 3 03:16:27 CET 2015
Ciao,
Tra Sabato e oggi io e François ci siamo messi in hard-hacking-mode per
capire com'era possibile che il cluster Xen fosse più lento del server
attuale.
Inizialmente abbiamo fatto dei benchmark per verificare le prestazioni
generali del server nuovo (CPU/Disco) confrontato con quello vecchio e i
dati sono comparabili.
Abbiamo anche testato le prestazioni del servizio SQL Server ottenendo
anche lì prestazioni comparabili. In altre parole la stessa query
eseguita sul vecchio (virtual_acao) e sul nuovo (onda2) ha circa lo
stesso tempo di esecuzione. Si veda l'allegato.
Siamo poi scesi nel dettaglio arrivando a sniffare il traffico di rete e
abbiamo rilevato che:
1- Aliandre2016 per ogni query al database si connette, si autentica,
esegue la query e si disconnette.
2- Il processo di autenticazione al database impiegava consistentemente
circa 1.5 secondi ad ogni connessione.
La ricerca delle cause è stata assai complessa perché d'un tratto il
tempo impiegato per l'autenticazione crollava a 6 ms e tutto funzionava
perfettamente e non era evidente il motivo per cui il comportamento
cambiasse in modo così radicale.
Tralasciamo tutti gli altri test che abbiamo fatto per escludere fattori
che si sono rivelati non rilevanti.
Il risultato è che abbiamo scoperto che esiste una funzionalità di SQL
Server chiamata "AUTOCLOSE" che chiude il database, libera la RAM e le
risorse allocate al DB quando l'ultimo client si disconnette.
Con molti client collegati, la condizione penalizzante (database chiuso)
si verifica raramente, mentre con il server Onda2 non in produzione, con
un solo client, la funzionalità di auto-close viene attivata ad ogni
operazione introducendo dei ritardi enormi.
Detto questo, però, l'autoclose non dovrebbe essere rilevante nel nostro
caso perché il normale pattern d'uso di un server SQL è di mantenere una
o un pool di connessioni sempre aperte.
L'overhead per connessione, anche in assenza di autoclose, può essere
persino più grande del tempo per eseguire la query e ritornare il risultato.
Per riassumere:
- Le prestazioni dei server Xen sono uguali o superiori al server usato
attualmente
- L'autoclose introduce un delay sensibile ad ogni connessione quando
Aliandre è l'unico client a connettersi.
- Aliandre non deve aprire una nuova connessione per ogni query.
- Non è detto che questo problema sia lo stesso che si è verificato
l'ultima volta ma siamo pronti a misurare le prestazioni passo per passo
per arrivare all'obbiettivo senza intoppi.
Per il futuro:
- Ho disabilitato l'autoclose perché comunque è sconsigliato su un
database server dedicato.
- Procederei a programmare la migrazione sul server nuovo, mantenendo lo
storage locale.
- Lascerei lavorare il tutto per una ventina di giorni per avere la
certezza che le prestazioni siano consistentemente buone.
- Dopo 20 giorni sposterei lo storage a quello locale a quello condiviso
sulla SAN e verificherei le prestazioni, eventualmente tornando allo
storage locale in caso di problemi.
- Bisogna avere l'accortezza di fare dei backup espliciti della macchina
nei 20 giorni di test perché lo storage locale non è protetto dal backup
della SAN.
- Nel frattempo ho predisposto un sistema di misurazione delle
prestazioni di SQL Server così da avere dei dati da confrontare durante
tutte le modifiche.
Ciao,
--
Daniele Orlandi
-------------- next part --------------
> Disk Sequential 64.0 Read 845.61 MB/s 7.9
> Disk Random 16.0 Read 5.00 MB/s 5.1
> Responsiveness: Average IO Rate 5.84 ms/IO 5.1
> Responsiveness: Grouped IOs 14.19 units 6.2
> Responsiveness: Long IOs 10.97 units 7.3
> Responsiveness: Overall 155.66 units 6.6
> Responsiveness: PenaltyFactor 0.0
> Disk Sequential 64.0 Write 110.49 MB/s 6.7
> Tempo medio di lettura con scritture sequenziali5.633 ms 5.8
> Latenza: 95° percentile 35.306 ms 2.7
> Latenza: massima 75.432 ms 7.8
> Tempo medio di lettura con scritture casuali 11.142 ms 4.2
> Tempo di esecuzione totale 00:01:56.64
ONDA2
====================================
select * from log_bollini join soci_dati_generale as sdg on
sdg.codice_socio_dati_generale=codice_pilota;
---------------------------------------------------------------
Rows returned: 32938
Elapsed Time: 00:00:09.4837335
00:00:07.2046191
00:00:05.4997628
00:00:04.7148894
00:00:05.6172780
Client Execution Time 23:47:53
Query Profile Statistics
Number of INSERT, DELETE and UPDATE statements 0 0.0000
Rows affected by INSERT, DELETE, or UPDATE statements 0 0.0000
Number of SELECT statements 1 1.0000
Rows returned by SELECT statements 32938 32938.0000
Number of transactions 0 0.0000
Network Statistics
Number of server roundtrips 1 1.0000
TDS packets sent from client 1 1.0000
TDS packets received from server 25758 25758.0000
Bytes sent from client 240 240.0000
Bytes received from server 9.108648E+07 91086480.0000
Time Statistics
Client processing time 4594 4594.0000
Total execution time 4859 4859.0000
Wait time on server replies 265 265.0000
--------------------------------------------------------------
select id from log_bollini join soci_dati_generale as sdg on
sdg.codice_socio_dati_generale=codice_pilota;
---------------------------------------------------------------
Elapsed Time: 00:00:00.1609776
00:00:00.1435547
00:00:00.1562500
VIRTUAL_ACAO
========================
select * from log_bollini join soci_dati_generale as sdg on
sdg.codice_socio_dati_generale=codice_pilota;
---------------------------------------------------------------
Rows returned: 33107
Elapsed time: 00:00:08.3281250
00:00:05.9687500
00:00:06.0156250
00:00:05.9531250
00:00:06.0000000
Client Execution Time 23:45:29
Query Profile Statistics
Number of INSERT, DELETE and UPDATE statements 0 0.0000
Rows affected by INSERT, DELETE, or UPDATE statements 0 0.0000
Number of SELECT statements 1 1.0000
Rows returned by SELECT statements 33107 33107.0000
Number of transactions 0 0.0000
Network Statistics
Number of server roundtrips 1 1.0000
TDS packets sent from client 1 1.0000
TDS packets received from server 22532 22532.0000
Bytes sent from client 240 240.0000
Bytes received from server 9.17692E+07 91769200.0000
Time Statistics
Client processing time 5609 5609.0000
Total execution time 5727 5727.0000
Wait time on server replies 118 118.0000
select id from log_bollini join soci_dati_generale as sdg on
sdg.codice_socio_dati_generale=codice_pilota;
---------------------------------------------------------------
Elapsed Time: 00:00:00.1562500
00:00:00.1406250
00:00:00.1406250
-------------- next part --------------
A non-text attachment was scrubbed...
Name: smime.p7s
Type: application/pkcs7-signature
Size: 4326 bytes
Desc: S/MIME Cryptographic Signature
URL: <http://lists.acao.it/pipermail/it/attachments/20151103/be6a8350/attachment.bin>
More information about the It
mailing list