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. *********
