Forums:
Saludos.
Por favor necesito que me guien como puedo optimizar my servidor Mysql, puesto que constantemente me sale el siguiente mensaje ..... en el sitio web : Too many connections.
Revisando en internet, me recomendaron ejecutar el comando show processlist y este es el resultado .......llega maximo a 100 rows in set (0.00 sec) antes de mostrar el mensaje.
mysql> show processlist;
+------+---------+---------------+---------+---------+------+-------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+------+---------+---------------+---------+---------+------+-------+------------------+
| 4311 | postfix | webmail:43053 | postfix | Sleep | 5 | | NULL |
| 4312 | postfix | webmail:43054 | postfix | Sleep | 5 | | NULL |
| 4313 | postfix | webmail:43055 | postfix | Sleep | 5 | | NULL |
| 4314 | postfix | webmail:43056 | postfix | Sleep | 5 | | NULL |
| 4532 | postfix | webmail:43451 | postfix | Sleep | 471 | | NULL |
| 4533 | postfix | webmail:43452 | postfix | Sleep | 471 | | NULL |
| 4534 | postfix | webmail:43453 | postfix | Sleep | 471 | | NULL |
| 4535 | postfix | webmail:43454 | postfix | Sleep | 471 | | NULL |
| 4653 | postfix | webmail:43820 | postfix | Sleep | 1682 | | NULL |
| 4654 | postfix | webmail:43821 | postfix | Sleep | 1682 | | NULL |
| 4655 | postfix | webmail:43822 | postfix | Sleep | 1682 | | NULL |
| 4656 | postfix | webmail:43823 | postfix | Sleep | 1682 | | NULL |
| 4724 | postfix | webmail:44065 | postfix | Sleep | 1638 | | NULL |
| 4726 | postfix | webmail:44067 | postfix | Sleep | 1638 | | NULL |
| 4728 | postfix | webmail:44069 | postfix | Sleep | 1638 | | NULL |
| 4736 | postfix | webmail:44115 | postfix | Sleep | 1638 | | NULL |
| 4750 | postfix | webmail:44135 | postfix | Sleep | 831 | | NULL |
| 4751 | postfix | webmail:44136 | postfix | Sleep | 831 | | NULL |
| 4752 | postfix | webmail:44137 | postfix | Sleep | 831 | | NULL |
| 4753 | postfix | webmail:44138 | postfix | Sleep | 831 | | NULL |
| 4777 | postfix | webmail:44172 | postfix | Sleep | 749 | | NULL |
| 4778 | postfix | webmail:44173 | postfix | Sleep | 749 | | NULL |
| 4779 | postfix | webmail:44174 | postfix | Sleep | 749 | | NULL |
| 4780 | postfix | webmail:44175 | postfix | Sleep | 749 | | NULL |
| 4798 | postfix | webmail:44218 | postfix | Sleep | 675 | | NULL |
| 4799 | postfix | webmail:44219 | postfix | Sleep | 675 | | NULL |
| 4800 | postfix | webmail:44220 | postfix | Sleep | 675 | | NULL |
| 4801 | postfix | webmail:44221 | postfix | Sleep | 675 | | NULL |
| 4809 | postfix | webmail:44240 | postfix | Sleep | 466 | | NULL |
| 4810 | postfix | webmail:44241 | postfix | Sleep | 466 | | NULL |
| 4811 | postfix | webmail:44242 | postfix | Sleep | 466 | | NULL |
| 4812 | postfix | webmail:44243 | postfix | Sleep | 466 | | NULL |
| 4837 | postfix | webmail:44303 | postfix | Sleep | 37 | | NULL |
| 4838 | postfix | webmail:44304 | postfix | Sleep | 37 | | NULL |
| 4839 | postfix | webmail:44305 | postfix | Sleep | 37 | | NULL |
| 4846 | postfix | webmail:44320 | postfix | Sleep | 1563 | | NULL |
| 4847 | postfix | webmail:44321 | postfix | Sleep | 1563 | | NULL |
| 4848 | postfix | webmail:44322 | postfix | Sleep | 1563 | | NULL |
| 4849 | postfix | webmail:44323 | postfix | Sleep | 1563 | | NULL |
| 4850 | postfix | webmail:44324 | postfix | Sleep | 232 | | NULL |
| 4851 | postfix | webmail:44325 | postfix | Sleep | 232 | | NULL |
| 4852 | postfix | webmail:44326 | postfix | Sleep | 232 | | NULL |
| 4862 | postfix | webmail:44344 | postfix | Sleep | 14 | | NULL |
| 4864 | postfix | webmail:44346 | postfix | Sleep | 12 | | NULL |
| 4866 | postfix | webmail:44348 | postfix | Sleep | 12 | | NULL |
| 4868 | postfix | webmail:44350 | postfix | Sleep | 12 | | NULL |
| 4871 | postfix | webmail:44374 | postfix | Sleep | 289 | | NULL |
| 4872 | postfix | webmail:44375 | postfix | Sleep | 289 | | NULL |
| 4873 | postfix | webmail:44376 | postfix | Sleep | 289 | | NULL |
| 4891 | postfix | webmail:44483 | postfix | Sleep | 78 | | NULL |
| 4892 | postfix | webmail:44484 | postfix | Sleep | 78 | | NULL |
| 4893 | postfix | webmail:44485 | postfix | Sleep | 78 | | NULL |
| 4894 | postfix | webmail:44488 | postfix | Sleep | 78 | | NULL |
| 4902 | postfix | webmail:44538 | postfix | Sleep | 37 | | NULL |
| 4910 | postfix | webmail:44574 | postfix | Sleep | 48 | | NULL |
| 4911 | postfix | webmail:44575 | postfix | Sleep | 48 | | NULL |
| 4912 | postfix | webmail:44576 | postfix | Sleep | 48 | | NULL |
| 4914 | postfix | webmail:44580 | postfix | Sleep | 1929 | | NULL |
| 4915 | postfix | webmail:44581 | postfix | Sleep | 1929 | | NULL |
| 4916 | postfix | webmail:44582 | postfix | Sleep | 1929 | | NULL |
| 4917 | postfix | webmail:44583 | postfix | Sleep | 1929 | | NULL |
| 4918 | postfix | webmail:44586 | postfix | Sleep | 193 | | NULL |
| 4920 | postfix | webmail:44606 | postfix | Sleep | 1631 | | NULL |
| 4921 | postfix | webmail:44607 | postfix | Sleep | 1631 | | NULL |
| 4922 | postfix | webmail:44608 | postfix | Sleep | 1631 | | NULL |
| 4923 | postfix | webmail:44609 | postfix | Sleep | 1631 | | NULL |
| 4924 | postfix | webmail:44612 | postfix | Sleep | 1508 | | NULL |
| 4925 | postfix | webmail:44613 | postfix | Sleep | 1508 | | NULL |
| 4926 | postfix | webmail:44614 | postfix | Sleep | 1508 | | NULL |
| 4927 | postfix | webmail:44615 | postfix | Sleep | 1508 | | NULL |
| 4930 | postfix | webmail:44661 | postfix | Sleep | 128 | | NULL |
| 4931 | postfix | webmail:44662 | postfix | Sleep | 128 | | NULL |
| 4944 | postfix | webmail:44735 | postfix | Sleep | 94 | | NULL |
| 4946 | postfix | webmail:44742 | postfix | Sleep | 731 | | NULL |
| 4947 | postfix | webmail:44743 | postfix | Sleep | 731 | | NULL |
| 4948 | postfix | webmail:44744 | postfix | Sleep | 731 | | NULL |
| 4949 | postfix | webmail:44745 | postfix | Sleep | 731 | | NULL |
| 4950 | postfix | webmail:44746 | postfix | Sleep | 128 | | NULL |
| 4952 | postfix | webmail:44765 | postfix | Sleep | 42 | | NULL |
| 4955 | postfix | webmail:44781 | postfix | Sleep | 153 | | NULL |
| 4958 | postfix | webmail:44854 | postfix | Sleep | 14 | | NULL |
| 4962 | postfix | webmail:44876 | postfix | Sleep | 14 | | NULL |
| 4963 | postfix | webmail:44877 | postfix | Sleep | 14 | | NULL |
| 4964 | postfix | webmail:44878 | postfix | Sleep | 14 | | NULL |
| 4965 | postfix | webmail:44879 | postfix | Sleep | 14 | | NULL |
| 4966 | postfix | webmail:44882 | postfix | Sleep | 176 | | NULL |
| 4967 | postfix | webmail:44884 | postfix | Sleep | 77 | | NULL |
| 4970 | postfix | webmail:44907 | postfix | Sleep | 146 | | NULL |
| 4971 | postfix | webmail:44918 | postfix | Sleep | 77 | | NULL |
| 4972 | root | localhost | NULL | Query | 0 | NULL | show processlist |
| 4973 | postfix | webmail:44939 | postfix | Sleep | 14 | | NULL |
| 4976 | postfix | webmail:44945 | postfix | Sleep | 5 | | NULL |
| 4981 | postfix | webmail:44967 | postfix | Sleep | 12 | | NULL |
| 4982 | postfix | webmail:44968 | postfix | Sleep | 5 | | NULL |
+------+---------+---------------+---------+---------+------+-------+------------------+
94 rows in set (0.00 sec)
GRACIAS DE ANTEMANO POR SUS APORTES.
revisa el webmail que usas
revisa el webmail que usas (del cual no das razón) y pidele que no ataque tan duro a tu servicio mysql.
Seguro estás usando autenticación basada en mysql, o quizá estás guardando los correos en una BD mysql (cosa que es realmente poco performante) y por eso te sucede esto.
Quizá deberías analizar optimizar el mysql. Pero mejor aún, usar algún sistema que no dependa de BD para almacenar los mensajes, pobre mysql... creo que necesitaq ue lo optimices.
Saludos
epe
--
EcuaLinux.com
Ecuador: +(593) 9 9246504, +(593) 2 3412402
USA: +1 404 795 0321
Saludos
epe
EcuaLinux.com
+(593) 9 9924 6504
Servicios en Software Libre
Too many connections
El detalle de lo que utilizo es
Mysql Versión 4.0.18
Webmail squirrelmail version 1.2.11
Apache versión 2.0.46
Postfix version 2.0.16
La autenticación si es basada en mysql.(Usuarios Virtuales)
Pero, NO GUARDO LOS CORREOS EN MYSQL sino en /home/vmail/midominio.
MODIFIQUE EL ARCHIVO right_main.php del squierrelmail ...para personalizarle y que me muestre el nombre del usuario activo,su correo y la barra indicadora del porcentaje utilizado de su cuota.
El codigo es el siguiente(dentro del cual tengo ciudado en cerrar cada conexion abierta a la bd) :
<?php
//****** pantalla derecha del webmail
require_once('../src/validate.php');
require_once('../functions/imap.php');
require_once('../functions/date.php');
require_once('../functions/array.php');
require_once('../functions/mime.php');
require_once('../functions/mailbox_display.php');
require_once('../functions/display_messages.php');
$username = $_SESSION['username'];
function porcentaje($cuotaocupada,&$restante,$cuotatotal)
{
$porcentaje = ($cuotaocupada*100)/$cuotatotal;
$restante = 100-$porcentaje;
return ($porcentaje);
}
function Get_Nombre_Usuario_Activo($username)
{
include_once('coneccion.php');
$nombres='';
$link=conectarse();
$sql= "select nombres from personal where mail='$username'";
$result = mysql_query($sql,$link);
$row = mysql_fetch_array($result);
if($row) $nombres=$row['nombres'];
mysql_close($link);
return $nombres;
}// fin function Get_Nombre_Usuario_Activo()
function Get_Tamanio_Path($username,&$cuota_desig)
{
include_once('coneccion.php');
$link=conectarse();
$sql = "select homedir,maildir,quota from cuenta where mail='$username'";
$result = mysql_query($sql,$link);
$row = mysql_fetch_array($result);
$dirdu = $row['homedir']."/".$row['maildir'];
$com = "du -sh $dirdu";
$pasa= exec("$com");
$cant=strlen($pasa);
$valor="";
$aux=0;
for($i=0;$i<$cant;$i++)
{
if($pasa[$i]=="K" || $pasa[$i]=="M")
{
if($pasa[$i]=="M")
{
//echo "
si entra por megas";
$aux=1;
$i=$i+$cant;
}
if($pasa[$i]=="K") $i=$i+$cant;
}
else $valor=$valor.$pasa[$i];
}// fin for
if($aux==1) $valor=($valor*1024)*1024;
else $valor=($valor*1024);
$cuota_desig=$row['quota'];
mysql_close($link);
return $valor;
}// fin Get_Tamanio_Path($username)
echo "
.dir{
color: red;
font-size: 11px;
font-weight: bold;
font-family: Arial, Helvetica, sans-serif;
}
.dir1{
color: ;
font-size: 11px;
font-weight: bold;
font-family: Arial, Helvetica, sans-serif;
}
";
$nombres=Get_Nombre_Usuario_Activo($username);
if ($nombres!='')
{
echo "BIENVENIDO : ", $nombres," ","!!";
$valor=Get_Tamanio_Path($username,&$cuota_desig);
//******* Muestra el estado de la barra del tamaño del buzon
$restante=0;
$cuota = porcentaje($valor,&$restante,$cuota_desig);
$cuota_detalle = round($cuota);
$cuota_detalle=$cuota_detalle."%";
$esp_detalle=" ";
$dd="de";
$cuota_desig =($cuota_desig/1024)/1024;
$mbb="MB";
$tamanio = "17%"; //tamaño de la tabla q contiene la barra del buzon
if ($cuota < 1 )
{
$cuota = "3%";// ancho del td
$cuota_detalle="1%";// para escribir porcetaje
$restante = "97%";
$colortam = "green";
$info_espacio = "1%";
}//fin if
else
{
if ($restante > 20 )$colortam = "green";
else $colortam = "red";
$restante=$restante.$simb;// tamaño td espacio restante
}// fin else
$cuota_desig = round($cuota_desig);
echo "ESPACIO EN BUZON".
"$cuota_detalle $es_destalle $dd $cuota_desig $mbb".
"".
"".
"".
"".
"".
"";
//***** muestra el mail del usuario activo
echo "",$username,"";
}// fin if ($row['cp_nombres']!='')
$key = $_COOKIE['key'];
$onetimepad = $_SESSION['onetimepad'];
$base_uri = $_SESSION['base_uri'];
$delimiter = $_SESSION['delimiter'];
if (isset($_GET['startMessage']))
{
$startMessage = (int) $_GET['startMessage'];
}
if (isset($_GET['mailbox']))
{
$mailbox = $_GET['mailbox'];
}
if (isset($_GET['PG_SHOWNUM']))
{
$PG_SHOWNUM = (int) $_GET['PG_SHOWNUM'];
}
elseif (isset($_SESSION['PG_SHOWNUM']))
{
$PG_SHOWNUM = (int) $_SESSION['PG_SHOWNUM'];
}
if (isset($_GET['PG_SHOWALL']))
{
$PG_SHOWALL = (int) $_GET['PG_SHOWALL'];
}
if (isset($_GET['newsort']))
{
$newsort = (int) $_GET['newsort'];
}
if (isset($_GET['checkall']))
{
$checkall = (int) $_GET['checkall'];
}
if (isset($_GET['set_thread']))
{
$set_thread = (int) $_GET['set_thread'];
}
if (isset($_SESSION['lastTargetMailbox']))
{
$lastTargetMailbox =$_SESSION['lastTargetMailbox'];
}
if (isset($_SESSION['msgs']))
{
$msgs = $_SESSION['msgs'];
}
if (isset($_SESSION['numMessages']))
{
$numMessages = $_SESSION['numMessages'];
}
/* end of get globals */
/* Open a connection on the imap port (143) */
$imapConnection = sqimap_login($username, $key, $imapServerAddress, $imapPort, 0);
if (isset($PG_SHOWALL))
{
if ($PG_SHOWALL)
{
$PG_SHOWNUM=999999;
$show_num=$PG_SHOWNUM;
sqsession_register($PG_SHOWNUM, 'PG_SHOWNUM');
}
else
{
sqsession_unregister('PG_SHOWNUM');
unset($PG_SHOWNUM);
}
}
else if( isset( $PG_SHOWNUM ) )
{
$show_num = $PG_SHOWNUM;
}
if (isset($newsort) && $newsort != $sort) {
setPref($data_dir, $username, 'sort', $newsort);
}
/* If the page has been loaded without a specific mailbox, */
/* send them to the inbox
*/
if (!isset($mailbox))
{
$mailbox = 'INBOX';
$startMessage = 1;
}
if (!isset($startMessage) || ($startMessage == ''))
{
$startMessage = 1;
}
/* compensate for the UW vulnerability. */
if ($imap_server_type == 'uw' && (strstr($mailbox, '../') ||
substr($mailbox, 0, 1) == '/')) {
$mailbox = 'INBOX';
}
/* decide if we are thread sorting or not */
if ($allow_thread_sort == TRUE)
{
if (isset($set_thread))
{
if ($set_thread == 1)
{
setPref($data_dir, $username, "thread_$mailbox", 1);
$thread_sort_messages = '1';
}
elseif ($set_thread == 2)
{
setPref($data_dir, $username, "thread_$mailbox", 0);
$thread_sort_messages = '0';
}
}
else
{
$thread_sort_messages = getPref($data_dir, $username, "thread_$mailbox");
}
}
else
{
$thread_sort_messages = 0;
}
sqimap_mailbox_select($imapConnection, $mailbox);
if (isset($composenew) && $composenew)
{
$comp_uri = "../src/compose.php?mailbox=". urlencode($mailbox).
"&session=$composesession&attachedmessages=true&";
displayPageHeader($color, $mailbox, "comp_in_new(false,'$comp_uri');", false);
}
else
{
displayPageHeader($color, $mailbox);
}
echo "
\n";
do_hook('right_main_after_header');
if (isset($note))
{
echo "$note
\n";
}
if (isset($_SESSION['just_logged_in']))
{
$just_logged_in = $_SESSION['just_logged_in'];
if ($just_logged_in == true)
{
$just_logged_in = false;
if (strlen(trim($motd)) > 0)
{
echo "
" .
'' .
"" .
"$motd";
do_hook('motd');
echo '' .
'' .
'';
}
}
}// fin if
if (isset($newsort))
{
$sort = $newsort;
sqsession_register($sort, 'sort');
}
/*********************************************************************
* Check to see if we can use cache or not. Currently the only time *
* when you will not use it is when a link on the left hand frame is *
* used. Also check to make sure we actually have the array in the *
* registered session data. :) *
*********************************************************************/
if (! isset($use_mailbox_cache))
{
$use_mailbox_cache = 0;
}
if (!$use_mailbox_cache && sqsession_is_registered('msgs'))
{
if (sqsession_is_registered('msgs')) {
unset($msgs);
}
if (sqsession_is_registered('msort')) {
unset($msort);
}
if (sqsession_is_registered('numMessages')) {
unset($numMessages);
}
}
/*
* If $numMessages isn't set, it's probably coming from a new session
* or $use_mailbox_cache is OFF, so get a new count on the list
*/
if (!isset($numMessages)) {
$numMessages = sqimap_get_num_messages($imapConnection, $mailbox);
}
//Muestra la tabla con los mensajes del buzon de correo
showMessagesForMailbox($imapConnection, $mailbox, $numMessages,
$startMessage, $sort, $color, $show_num,
$use_mailbox_cache);
if (sqsession_is_registered('msgs') && isset($msgs)) {
sqsession_register($msgs , 'msgs');
}
if (sqsession_is_registered('msort') && isset($msort)) {
sqsession_register($msort , 'msort');
}
if (sqsession_is_registered('numMessages') && isset($numMessages)) {
sqsession_register($numMessages , 'numMessages');
}
do_hook('right_main_bottom');
sqimap_logout ($imapConnection);
echo '';
?>
ADEMAS BUSCANDO EN INTERNET ENCONTRE QUE DEBO MODIFICAR LA VARIABLE max_connections y lo hice mediante el webmin ingresando al servidor mysql....en la base mysql dentro de esta la tabla user ...y en esta tabla la columna max_connections tenia el valor de 0(pero igual me soportaba 100 conexiones concurrentes) y le cambie al valor 100, una vez cambiado el valor a 100; cuando se saturaba de conexiones concurrentes el mysql ya no me salio Too many connections sino salio el siguiente error:
Warning: mysql_connect(): User 'postfix' has exceeded the 'max_connections' resource (current value: 100), quiero subirle a 200 ó a 300 pero no se si esa sea la solucion optima
GRACIAS DE ANTEMANO.
revisa, las conexiones no se
revisa, las conexiones no se están cerrando y eso no te está haciendo bien.
Saludos
epe
--
EcuaLinux.com
Ecuador: +(593) 9 9246504, +(593) 2 3412402
USA: +1 404 795 0321
Saludos
epe
EcuaLinux.com
+(593) 9 9924 6504
Servicios en Software Libre
No se si estás usando
No se si estás usando conexiones persistentes o no, si no las estás usando prueba cambiando:
mysql_connect(....
por
mysql_pconnect(....
Suerte ...
bye
:)