I`ve resolved my problems without hardware manipulation.
Thanks to all.
-----Mensaje original-----
De: Francisco Rodrigo Cortinas Maseda
Enviado el: miércoles 16 de abril de 2008 18:57
Para: mysql@stripped
Asunto: RV: Performance problem
Hi all,
im new on the performance tuning of this database (MySQL 5.0.45, rpm-based installation),
and i have one performance problem on our new installation:
- The radius servers (that are written on perl) we have are writing the auth and acct log
to one mysql database. The conn we have is an TCPIP conn.
- We have two databases, one for auth data and another for acct data.
- We have one table for each day on each database, on which we insert the auth and acct
data. We also have three indexes on each table, that occupy almost 300M per day.
- The volume of traffic is nearly 10 million rows per day.
- The partition of the database is mounted on a LVM partition of a RAID1 disk.
We are experiencing problems about the performance of the database, in the way that we are
seeing that the radius clients are seeing the radius servers gone away for the acct
service.
The server of the database is a Dell Poweredge 1855 with 6 GB of RAM and RHEL4. We have
modified the variables of the database with:
SET GLOBAL thread_cache_size=8;
SET GLOBAL table_cache=256;
set GLOBAL max_connections=200;
set GLOBAL key_buffer_size=1610416128;
set GLOBAL read_buffer_size=524288;
set GLOBAL read_rnd_buffer_size=1048576;
SET GLOBAL delayed_insert_limit=400;
SET GLOBAL delayed_queue_size=12000;
SET GLOBAL net_buffer_length=32768;
The queries that we are doing are:
INSERT DELAYED IGNORE () VALUES ();
Originally, the server has 2GB of RAM, but seeing this problems, we have installed another
4 GB of RAM. From the statistics of vmstat we see that we are suffering som IO bottleneck
(i think):
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
0 4 0 4280956 40144 1392456 0 0 0 14 1853 1180 1 0 48 50
0 3 0 4279932 40152 1393488 0 0 0 10 1882 1258 2 0 42 56
0 3 0 4279908 40172 1394508 0 0 0 2052 1861 1202 2 1 45 52
0 4 0 4276452 40192 1395528 0 0 0 9179 1850 1164 2 1 66 31
1 3 0 4274748 40200 1396300 0 0 0 7 1957 1337 2 1 64 34
0 4 0 4272956 40212 1397328 0 0 0 24 1926 1283 2 1 41 56
0 3 0 4271484 40224 1398616 0 0 0 26 1906 1250 2 1 32 66
0 3 0 4270204 40228 1399652 0 0 0 9 1855 1154 2 0 24 74
0 3 0 4268924 40236 1400164 0 0 0 10 1852 1144 2 0 24 74
1 4 0 4267516 40248 1401452 0 0 0 13 2063 1480 2 1 27 71
0 3 0 4264476 40280 1402720 0 0 0 11134 1965 1363 2 1 49 48
0 4 0 4262772 40300 1403740 0 0 0 13 1971 1382 2 0 60 37
0 4 0 4261372 40316 1404764 0 0 0 15 1875 1213 2 1 46 52
0 3 0 4260028 40328 1405532 0 0 0 14 1831 1152 2 0 48 50
The "wa" column shows a quite large number, so we think that it is an IO bottleneck.
The question is:
¿ has anybody have see something similar? ¿has anybody an idea about how
to resolve this problem?
Thanks.
Antes de imprimir este e-mail piense bien si es necesario hacerlo.
*********
Este mensaje es privado y CONFIDENCIAL y se dirige exclusivamente a su destinatario. Si
usted ha recibido este mensaje por error, no debe revelar, copiar, distribuir o usarlo en
ningún sentido. Le rogamos lo comunique al remitente y borre dicho mensaje y
cualquier documento adjunto que pudiera contener. El correo electrónico via
Internet no permite asegurar la confidencialidad de los mensajes que se transmiten ni su
integridad o correcta recepción. JAZZTEL no asume responsabilidad por estas
circunstancias. Si el destinatario de este mensaje no consintiera la utilización
del correo electrónico via Internet y la grabación de los mensajes, rogamos
lo ponga en nuestro conocimiento de forma inmediata.Cualquier opinión expresada en
este mensaje pertenece únicamente al autor remitente, y no representa
necesariamente la opinión de JAZZTEL, a no ser que expresamente se diga y el
remitente esté autorizado para hacerlo.
*********
This message is private and CONFIDENTIAL and it is intended exclusively for its addressee.
If you receive this message in error, you should not disclose, copy, distribute this
e-mail or use it in any other way. Please inform the sender and delete the message and
attachments from your system.Internet e-mail neither guarantees the confidentiality nor
the integrity or proper receipt of the messages sent. JAZZTEL does not assume any
liability for those circumstances. If the addressee of this message does not consent to
the use of Internet e-mail and message recording, please notify us immediately.Any views
or opinions contained in this message are solely those of the author, and do not
necessarily represent those of JAZZTEL, unless otherwise specifically stated and the
sender is authorised to do so.
*********
