RE: Optimal MySQL server -- opinions?
Hi, My experience shows me that RAM and disk is fundamental for any database, especially MySQL. As much IO load, much more disk preading you have to take with. Personally, i have 2 databases with 11 millions Inserts / day (2GB / day), so my advise is that you spend as much money as you can in SAN disks, or similar (fiber, attached SCSI, etc). I have it on a 6G Poweredge 1855, 2 Xeon dual processors (my bottleneck is obviously disk I/O). The other counterpart is that if you can put the entire databases on RAM, your disk needs are not so high. So if you can afford that. Regards. -Mensaje original- De: Rene Fournier [mailto:[EMAIL PROTECTED] Enviado el: domingo 27 de abril de 2008 23:57 Para: mysql@lists.mysql.com Asunto: Optimal MySQL server -- opinions? Okay, the previous subject was too narrow, what I am really looking for are opinions on general disk/memory/cpu configurations, manufacturer notwithstanding... As stated previously, I'm configuring a PowerEdge 2950III, and trying to decide what will provide the best bang-for-buck. The server will be used strictly as a MySQL database server running atop Red Hat Linux. Two large databases, each about 2GB, heavy on both Inserts and Selects. Up until recently, I had spec'd: 2 x Quad-Core Xeon 5430 @ 2.66 GHz (6 MB cache) , 1333 MHz FSB 8 GB Ram (4x2GB) 4 x 146 GB 15K SAS drives (RAID 1/1 -- first set for OS, apps, second set for MySQL data) ...worked out to around $5,500. Now however there is a processor promotion, such that: 1 x Quad-Core Xeon 5450 @ 3.0 GHz (6 MB cache) , 1333 MHz FSB 8 GB Ram (4x2GB) 4 x 146 GB 15K SAS drives (RAID 1/1 -- first set for OS, apps, second set for MySQL data) ...works out to around $4,500. So what I'm wondering is, do I really need an eight-core box, since my experience tells me that MySQL's greatest bottleneck is disk I/O. I'm wondering if I would be better off with just one processor to start with (are they easy to add later, btw?), maybe add more RAM, and just save some cash. Any thoughts or suggestions are much appreciated. I have to pull the trigger on this soon. I was hoping they would bump the specs or drop the prices significantly... I've been watching these for months and there's been promo after promo... I anticipate a major update, has anyone heard anything? (Should I wait a little longer maybe?) Thanks. ...Rene Antes de imprimir este e-mail piense bien si es necesario hacerlo. 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 ningun sentido. Le rogamos lo comunique al remitente y borre dicho mensaje y cualquier documento adjunto que pudiera contener. El correo electronico via Internet no permite asegurar la confidencialidad de los mensajes que se transmiten ni su integridad o correcta recepcion. JAZZTEL no asume responsabilidad por estas circunstancias. Si el destinatario de este mensaje no consintiera la utilizacion del correo electronico via Internet y la grabacion de los mensajes, rogamos lo ponga en nuestro conocimiento de forma inmediata.Cualquier opinion expresada en este mensaje pertenece unicamente al autor remitente, y no representa necesariamente la opinion de JAZZTEL, a no ser que expresamente se diga y el remitente este 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. * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Error reading packet from server: Out of memory (Needed 6560 bytes) ( server_errno=5)
Hi, I have experienced similar problems to the one you have; the problem you have is that the time gap between the failure and now is so big that you cannot resume replication, because of the big data portion you have to replicate. When this happens to me, increasing the value of the variable you have posted did not work; i did: 1º Stop replication 2º Use mysqlbinlog to see what is on the repl files. 3º Manually apply the changes (on the slave, of course) to a defined position (50K queries far away the failure). 4º Start replication on this position, manually especifying the position on the command start slave (see manual). This worked fine to me. Regards. -Mensaje original- De: Dominik Klein [mailto:[EMAIL PROTECTED] Enviado el: lunes 28 de abril de 2008 16:28 Para: mysql@lists.mysql.com Asunto: Re: Error reading packet from server: Out of memory (Needed 6560 bytes) ( server_errno=5) Hi. Juan Eduardo Moreno wrote: Sorry, what is your setting for max_allowed_packet variable? mysql show variables like %allowed%; ++--+ | Variable_name | Value| ++--+ | max_allowed_packet | 16776192 | ++--+ 1 row in set (0.00 sec) Here's an excerpt from the logs. It does not look like that value is involved in this: 080428 13:07:04 [ERROR] Error reading packet from server: Out of memory (Needed 2704 bytes) ( server_errno=5) 080428 13:07:04 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'SRV02bin.000131' position 447892967 080428 13:07:04 [Note] Slave: connected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'SRV02-bin.000131' at position 447892967 080428 13:22:14 [ERROR] Error reading packet from server: Out of memory (Needed 2704 bytes) ( server_errno=5) 080428 13:22:14 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'SRV02-bin.000131' position 471157588 080428 13:22:14 [Note] Slave: connected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'SRV02-bin.000131' at position 471157588 080428 14:38:06 [ERROR] Error reading packet from server: Out of memory (Needed 3464 bytes) ( server_errno=5) 080428 14:38:06 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'SRV02-bin.000131' position 583864714 080428 14:38:06 [Note] Slave: connected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'SRV02-bin.000131' at position 583864714 080428 15:13:24 [ERROR] Error reading packet from server: Out of memory (Needed 3128 bytes) ( server_errno=5) 080428 15:13:24 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'SRV02-bin.000131' position 635409380 080428 15:13:24 [Note] Slave: connected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'SRV02-bin.000131' at position 635409380 080428 15:32:14 [ERROR] Error reading packet from server: Out of memory (Needed 2688 bytes) ( server_errno=5) 080428 15:32:14 [Note] Slave I/O thread: Failed reading log event, reconnecting to retry, log 'SRV02-bin.000131' position 665146758 080428 15:32:14 [Note] Slave: connected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'SRV02-bin.000131' at position 665146758 Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Antes de imprimir este e-mail piense bien si es necesario hacerlo. 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
RE: Performance problem
New queries, tuning the insert (DELAYED) we make on the database. The clients have not to wait to the io response of the thread of the database that inserts the data and the repl data. -Mensaje original- De: Tim McDaniel [mailto:[EMAIL PROTECTED] Enviado el: viernes 18 de abril de 2008 17:34 CC: mysql@lists.mysql.com Asunto: RE: Performance problem On Fri, 18 Apr 2008, Francisco Rodrigo Cortinas Maseda [EMAIL PROTECTED] wrote: 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: ... 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. I`ve resolved my problems without hardware manipulation. Me, I'd not like to see much technical detail, but I'm curious now: what sorts of things did you do? Restructuring, different queries, what? -- Tim McDaniel, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Antes de imprimir este e-mail piense bien si es necesario hacerlo. 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 ningun sentido. Le rogamos lo comunique al remitente y borre dicho mensaje y cualquier documento adjunto que pudiera contener. El correo electronico via Internet no permite asegurar la confidencialidad de los mensajes que se transmiten ni su integridad o correcta recepcion. JAZZTEL no asume responsabilidad por estas circunstancias. Si el destinatario de este mensaje no consintiera la utilizacion del correo electronico via Internet y la grabacion de los mensajes, rogamos lo ponga en nuestro conocimiento de forma inmediata.Cualquier opinion expresada en este mensaje pertenece unicamente al autor remitente, y no representa necesariamente la opinion de JAZZTEL, a no ser que expresamente se diga y el remitente este 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. * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Performance problem
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@lists.mysql.com 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 sobibo incs us sy id wa 0 4 0 4280956 40144 139245600 014 1853 1180 1 0 48 50 0 3 0 4279932 40152 139348800 010 1882 1258 2 0 42 56 0 3 0 4279908 40172 139450800 0 2052 1861 1202 2 1 45 52 0 4 0 4276452 40192 139552800 0 9179 1850 1164 2 1 66 31 1 3 0 4274748 40200 139630000 0 7 1957 1337 2 1 64 34 0 4 0 4272956 40212 139732800 024 1926 1283 2 1 41 56 0 3 0 4271484 40224 139861600 026 1906 1250 2 1 32 66 0 3 0 4270204 40228 139965200 0 9 1855 1154 2 0 24 74 0 3 0 4268924 40236 140016400 010 1852 1144 2 0 24 74 1 4 0 4267516 40248 140145200 013 2063 1480 2 1 27 71 0 3 0 4264476 40280 140272000 0 11134 1965 1363 2 1 49 48 0 4 0 4262772 40300 140374000 013 1971 1382 2 0 60 37 0 4 0 4261372 40316 140476400 015 1875 1213 2 1 46 52 0 3 0 4260028 40328 140553200 014 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 seesomething 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
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 sobibo incs us sy id wa 0 4 0 4280956 40144 139245600 014 1853 1180 1 0 48 50 0 3 0 4279932 40152 139348800 010 1882 1258 2 0 42 56 0 3 0 4279908 40172 139450800 0 2052 1861 1202 2 1 45 52 0 4 0 4276452 40192 139552800 0 9179 1850 1164 2 1 66 31 1 3 0 4274748 40200 139630000 0 7 1957 1337 2 1 64 34 0 4 0 4272956 40212 139732800 024 1926 1283 2 1 41 56 0 3 0 4271484 40224 139861600 026 1906 1250 2 1 32 66 0 3 0 4270204 40228 139965200 0 9 1855 1154 2 0 24 74 0 3 0 4268924 40236 140016400 010 1852 1144 2 0 24 74 1 4 0 4267516 40248 140145200 013 2063 1480 2 1 27 71 0 3 0 4264476 40280 140272000 0 11134 1965 1363 2 1 49 48 0 4 0 4262772 40300 140374000 013 1971 1382 2 0 60 37 0 4 0 4261372 40316 140476400 015 1875 1213 2 1 46 52 0 3 0 4260028 40328 140553200 014 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 seesomething 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. *
