You are viewing a plain text version of this content. The canonical link for it is here.
Posted to users@httpd.apache.org by URLTrak Support <ur...@hotmail.com> on 2003/10/02 07:49:02 UTC
[users@httpd] "Too many connections" error fix?
Hello,
I run Apache with PHP and MySQL on a RedHat 7.2 box with 1GB Ram @ 2GHZ
(p4). 80 GB IDE hd. 700 GB monthly bandwidth.
I last increased my max connections to 500. That seemed to fix things for a
while. Now I am getting the problems (database freezing up because too many
connections) again. The site I run is pretty high traffic.
I switched from using pconnect for the last year to connect. This did not
solve the problem. So I
do not thing pconnect vs connect is the issue. I believe some proper
configuration of
the httpd.conf and my.cnf files will cure this problem.
(Update: I just set max_connections to 600 tonight. Will have to wait to see
if that helps any.)
My apache shows (which were the defaults):
---------------------------
MaxClients 150
MaxKeepAliveRequests 100
I wondered if anyone encountered a solution to handle this sort of problem.
TIA!
--
Nicole
aeontrek.com
(Atlanta, GA USA - Eastern TZ)
---------------------------------------------------------------------
The official User-To-User support forum of the Apache HTTP Server Project.
See <URL:http://httpd.apache.org/userslist.html> for more info.
To unsubscribe, e-mail: users-unsubscribe@httpd.apache.org
" from the digest: users-digest-unsubscribe@httpd.apache.org
For additional commands, e-mail: users-help@httpd.apache.org
Re: [users@httpd] "Too many connections" error fix?
Posted by Richard Gration <ri...@zync.co.uk>.
Hi there,
I don't have any specific answers, but I can suggest some things to try,
based on my experience with Postgres.
I don't know if this applies to MySQL, but I had to recompile Postgres
to increase the default compiled in limits to the number connections it
would allow. Any configuration arguments about the number of connections
have no effect above the compiled in limit. Just a thought, it might not
be the same for MySQL.
Check that the operating system isn't running out of files and inodes.
These settings can be read and changed using
/proc/sys/fs/{file,inode}-max like this:
cat /proc/sys/fs/file-max
to show you the maximum number of open files at any one time. Similarly
for inode-max. You can change them with something like:
echo 16384 > /proc/sys/fs/file-max
echo 65536 > /proc/sys/fs/inode-max
As a rule of thumb, inode-max should be 4 * file-max. This only applies
to the 2.2.x kernels. For 2.4.x kernels, file-max is the same, but
inodes are allocated dynamically, so /proc/sys/fs/inode-max is not
present. The settings above are the actual values I used to eliminate
"too many open files" errors on a dual 1GHz, 1Gb RAM, 70Gb SCSI machine.
You may need to change the number of allowed open files for httpd by
placing a ulimit command in the httpd startup script (usually
/etc/init.d/httpd on redhat). "man ulimit" to find out how to set open
files, it's straightforward.
You might find these URLs useful
http://httpd.apache.org/docs/misc/perf-tuning.html
http://php.weblogs.com/tuning_apache_unix
This is mod_perl tuning, but this section has very good information on
how to choose MaxClients based on the amount of memory in your machine:
http://perl.apache.org/docs/1.0/guide/performance.html#Choosing_MaxClients
HTH
Rick
---------------------------------------------------------------------
The official User-To-User support forum of the Apache HTTP Server Project.
See <URL:http://httpd.apache.org/userslist.html> for more info.
To unsubscribe, e-mail: users-unsubscribe@httpd.apache.org
" from the digest: users-digest-unsubscribe@httpd.apache.org
For additional commands, e-mail: users-help@httpd.apache.org
Re: [users@httpd] "Too many connections" error fix?
Posted by Nathan Ollerenshaw <na...@valuecommerce.ne.jp>.
On Oct 2, 2003, at 3:23 PM, Brian Dessent wrote:
> Not true:
>
> <http://www.mysql.com/doc/en/Table_locking.html>
>
> "In MySQL Version 3.23.7 and above, you can insert rows into MyISAM
> tables at the same time other threads are reading from the table. Note
> that currently this only works if there are no holes after deleted rows
> in the table at the time the insert is made. When all holes has been
> filled with new data, concurrent inserts will automatically be enabled
> again."
Learn something new every day ...
Nathan.
--
Nathan Ollerenshaw - Unix Systems Engineer
ValueCommerce - http://www.valuecommerce.ne.jp/
---------------------------------------------------------------------
The official User-To-User support forum of the Apache HTTP Server Project.
See <URL:http://httpd.apache.org/userslist.html> for more info.
To unsubscribe, e-mail: users-unsubscribe@httpd.apache.org
" from the digest: users-digest-unsubscribe@httpd.apache.org
For additional commands, e-mail: users-help@httpd.apache.org
Re: [users@httpd] "Too many connections" error fix?
Posted by URLTrak Support <ur...@hotmail.com>.
> However, it may not be a DB problem at all, and something completely
> different. It's all localhost connections, right, so it's not the
> network?
Correct. MySQL runs on the same server as httpd. And I connect using
"localhost" ...
----- Original Message -----
From: "Nathan Ollerenshaw" <na...@valuecommerce.ne.jp>
To: <us...@httpd.apache.org>
Sent: Thursday, October 02, 2003 4:02 AM
Subject: Re: [users@httpd] "Too many connections" error fix?
> On Oct 2, 2003, at 4:33 PM, URLTrak Support wrote:
>
> > PIBKAC? Maybe. I haven't had any problems until recently. My
> > membership base
> > is growing, the tracking stored in the tables is growing. I have almost
> > 900,000 rows in this tracking table. And some queries load slower than
> > others, I've noticed. Time to do some thorough bench marking.
>
> My colloquial experience with InnoDB is that it's faster than MyISAM.
>
> Don't take my word for it, however, take a dump and benchmark as you
> say.
>
> Play around with the innodb settings. A lot of performance can be
> gained for just that alone. (Same could also be said for MyISAM)
>
> However, it may not be a DB problem at all, and something completely
> different. It's all localhost connections, right, so it's not the
> network?
>
> Nathan.
>
> --
> Nathan Ollerenshaw - Unix Systems Engineer
> ValueCommerce - http://www.valuecommerce.ne.jp/
>
>
> ---------------------------------------------------------------------
> The official User-To-User support forum of the Apache HTTP Server Project.
> See <URL:http://httpd.apache.org/userslist.html> for more info.
> To unsubscribe, e-mail: users-unsubscribe@httpd.apache.org
> " from the digest: users-digest-unsubscribe@httpd.apache.org
> For additional commands, e-mail: users-help@httpd.apache.org
>
>
---------------------------------------------------------------------
The official User-To-User support forum of the Apache HTTP Server Project.
See <URL:http://httpd.apache.org/userslist.html> for more info.
To unsubscribe, e-mail: users-unsubscribe@httpd.apache.org
" from the digest: users-digest-unsubscribe@httpd.apache.org
For additional commands, e-mail: users-help@httpd.apache.org
Re: [users@httpd] "Too many connections" error fix?
Posted by Nathan Ollerenshaw <na...@valuecommerce.ne.jp>.
On Oct 2, 2003, at 4:33 PM, URLTrak Support wrote:
> PIBKAC? Maybe. I haven't had any problems until recently. My
> membership base
> is growing, the tracking stored in the tables is growing. I have almost
> 900,000 rows in this tracking table. And some queries load slower than
> others, I've noticed. Time to do some thorough bench marking.
My colloquial experience with InnoDB is that it's faster than MyISAM.
Don't take my word for it, however, take a dump and benchmark as you
say.
Play around with the innodb settings. A lot of performance can be
gained for just that alone. (Same could also be said for MyISAM)
However, it may not be a DB problem at all, and something completely
different. It's all localhost connections, right, so it's not the
network?
Nathan.
--
Nathan Ollerenshaw - Unix Systems Engineer
ValueCommerce - http://www.valuecommerce.ne.jp/
---------------------------------------------------------------------
The official User-To-User support forum of the Apache HTTP Server Project.
See <URL:http://httpd.apache.org/userslist.html> for more info.
To unsubscribe, e-mail: users-unsubscribe@httpd.apache.org
" from the digest: users-digest-unsubscribe@httpd.apache.org
For additional commands, e-mail: users-help@httpd.apache.org
Re: [users@httpd] "Too many connections" error fix?
Posted by URLTrak Support <ur...@hotmail.com>.
> No! You MUST make random and unwarranted changes at whim!
LOL. That's Suicide! Been there (a good few times) and learned my lessons
the hard way. =P
> Always blame the application ... and the developer ... :)
PIBKAC? Maybe. I haven't had any problems until recently. My membership base
is growing, the tracking stored in the tables is growing. I have almost
900,000 rows in this tracking table. And some queries load slower than
others, I've noticed. Time to do some thorough bench marking.
But even still, I don't know what's causing the "too many connections" error
message. And yes, that messsage is generated by PHP mysql_error().
Nicole
aeontrek.com
----- Original Message -----
From: "Nathan Ollerenshaw" <na...@valuecommerce.ne.jp>
To: <us...@httpd.apache.org>
Sent: Thursday, October 02, 2003 3:25 AM
Subject: Re: [users@httpd] "Too many connections" error fix?
> On Oct 2, 2003, at 3:34 PM, URLTrak Support wrote:
>
> > I will have to do some thorough researching before making any major
> > changes
>
> No! You MUST make random and unwarranted changes at whim!
>
> > I ran that "mysqladmin processlist" command and I got nothing that
> > could
> > help me. Here's what I see after running that as root via ssh (putty):
>
> Hmm, well you might be getting it between busy moments? Try doing it
> during a high load time, though it is certainly looking less like a DB
> issue at this stage.
>
> Always blame the application ... and the developer ... :)
>
> Nathan.
>
> --
> Nathan Ollerenshaw - Unix Systems Engineer
> ValueCommerce - http://www.valuecommerce.ne.jp/
>
>
> ---------------------------------------------------------------------
> The official User-To-User support forum of the Apache HTTP Server Project.
> See <URL:http://httpd.apache.org/userslist.html> for more info.
> To unsubscribe, e-mail: users-unsubscribe@httpd.apache.org
> " from the digest: users-digest-unsubscribe@httpd.apache.org
> For additional commands, e-mail: users-help@httpd.apache.org
>
>
---------------------------------------------------------------------
The official User-To-User support forum of the Apache HTTP Server Project.
See <URL:http://httpd.apache.org/userslist.html> for more info.
To unsubscribe, e-mail: users-unsubscribe@httpd.apache.org
" from the digest: users-digest-unsubscribe@httpd.apache.org
For additional commands, e-mail: users-help@httpd.apache.org
Re: [users@httpd] "Too many connections" error fix?
Posted by Nathan Ollerenshaw <na...@valuecommerce.ne.jp>.
On Oct 2, 2003, at 3:34 PM, URLTrak Support wrote:
> I will have to do some thorough researching before making any major
> changes
No! You MUST make random and unwarranted changes at whim!
> I ran that "mysqladmin processlist" command and I got nothing that
> could
> help me. Here's what I see after running that as root via ssh (putty):
Hmm, well you might be getting it between busy moments? Try doing it
during a high load time, though it is certainly looking less like a DB
issue at this stage.
Always blame the application ... and the developer ... :)
Nathan.
--
Nathan Ollerenshaw - Unix Systems Engineer
ValueCommerce - http://www.valuecommerce.ne.jp/
---------------------------------------------------------------------
The official User-To-User support forum of the Apache HTTP Server Project.
See <URL:http://httpd.apache.org/userslist.html> for more info.
To unsubscribe, e-mail: users-unsubscribe@httpd.apache.org
" from the digest: users-digest-unsubscribe@httpd.apache.org
For additional commands, e-mail: users-help@httpd.apache.org
Re: [users@httpd] "Too many connections" error fix?
Posted by Robert Andersson <ro...@profundis.nu>.
URLTrak wrote:
> I said I have a high traffic site and I keep getting "too many
connections" error
> message.
I've also had this problem with PHP / MySQL, where zoombie PHP-MySQL
connections lumped up, until it reached MySQL's max_connections, and PHP's
"too many connections" error occurs. This was not a very loaded or
high-traffic server either, but within about 24 hours from a total restart,
it always came to this. Restarting Apache didn't help most of the time, but
restarting MySQL did.
My workaround was to raise max_connections to 1000, and schedule a restart
of MySQL each night. I haven't experienced it since.
Regards,
Robert Andersson
---------------------------------------------------------------------
The official User-To-User support forum of the Apache HTTP Server Project.
See <URL:http://httpd.apache.org/userslist.html> for more info.
To unsubscribe, e-mail: users-unsubscribe@httpd.apache.org
" from the digest: users-digest-unsubscribe@httpd.apache.org
For additional commands, e-mail: users-help@httpd.apache.org
Re: [users@httpd] "Too many connections" error fix?
Posted by Brian Dessent <br...@dessent.net>.
URLTrak Support wrote:
>
> I didn't say anything about getting hundreds of connections. I said I have a
> high traffic site and I keep getting "too many connections" error message.
> This is causing the database to be inaccessible. I don't know what is
> causing this. I'm trying to find out. Not much documenation on this issue
> out there that I could find (from searching google).
>
> I ran that "mysqladmin processlist" command and I got nothing that could
> help me. Here's what I see after running that as root via ssh (putty):
So the error is reported by PHP, one of those "mysql_connect(): can't
connect: xxx" type things? Or something else? From what you've pasted
and the fact that your MaxClients is 150, I can't see why there would be
more than 150 simultaneous connections at any time.
Hmm... I remember reading somewhere that if you don't use persistent
connections, there's some small time inbetween when a connection is
closed and when it can be reused, and I suppose if there are a lot of
these connections going on then that might cause the number of needed
connections to be larger than the number of clients. Did you say that
you were or weren't using persistent connections? I would definitely
try to use them unless there's something they break.
Brian
---------------------------------------------------------------------
The official User-To-User support forum of the Apache HTTP Server Project.
See <URL:http://httpd.apache.org/userslist.html> for more info.
To unsubscribe, e-mail: users-unsubscribe@httpd.apache.org
" from the digest: users-digest-unsubscribe@httpd.apache.org
For additional commands, e-mail: users-help@httpd.apache.org
Re: [users@httpd] "Too many connections" error fix?
Posted by URLTrak Support <ur...@hotmail.com>.
I didn't say anything about getting hundreds of connections. I said I have a
high traffic site and I keep getting "too many connections" error message.
This is causing the database to be inaccessible. I don't know what is
causing this. I'm trying to find out. Not much documenation on this issue
out there that I could find (from searching google).
I ran that "mysqladmin processlist" command and I got nothing that could
help me. Here's what I see after running that as root via ssh (putty):
+-------+---------+-----------+-------------+---------+------+-------+------
------------+
| Id | User | Host | db | Command | Time | State | Info
|
+-------+---------+-----------+-------------+---------+------+-------+------
------------+
| 7985 | urltrak | localhost | urltrak_com | Sleep | 5811 | |
|
| 8021 | urltrak | localhost | urltrak_com | Sleep | 5764 | |
|
| 13046 | urltrak | localhost | urltrak_com | Sleep | 13 | |
|
| 13060 | root | localhost | | Query | 0 | | show
processlist |
+-------+---------+-----------+-------------+---------+------+-------+------
------------+
I do not see much there to help me.
Nicole
----- Original Message -----
From: "Brian Dessent" <br...@dessent.net>
To: <us...@httpd.apache.org>
Sent: Thursday, October 02, 2003 2:55 AM
Subject: Re: [users@httpd] "Too many connections" error fix?
> URLTrak Support wrote:
> >
> > Thanks. I will have to do some thorough researching before making any
major
> > changes. The site is too high traffic to take chances at this point. I
need
> > certainties before taking actions.
> >
> > So do you think that by modifying the my.cnf to reflect higher
connections
> > allowed will help? It helped for a while, at 500 connections. I just
bumped
> > it up to 600 tonight. I will have to wait to see if this helps.
> >
> > Any suggestion on a good number in httpd.conf for MaxClients or some
other
> > var? Someone said that I should have a higher max_connections number
than
> > the one defined in the httpd.conf for MaxClients.
>
> I don't understand where all these connections are going? There should
> be, at most, one MySQL connection per Apache worker process, which you
> said was set to a maximum of 150 (MaxClients). If persistent
> connections are enabled then each worker will create a connection and
> then keep it open until that process ends. How in the world are you
> getting all these hundreds of connections then? I agree with the person
> that said to run the "SHOW PROCESSLIST" command, that will show you
> exactly what's going on.
>
> Brian
>
> ---------------------------------------------------------------------
> The official User-To-User support forum of the Apache HTTP Server Project.
> See <URL:http://httpd.apache.org/userslist.html> for more info.
> To unsubscribe, e-mail: users-unsubscribe@httpd.apache.org
> " from the digest: users-digest-unsubscribe@httpd.apache.org
> For additional commands, e-mail: users-help@httpd.apache.org
>
>
---------------------------------------------------------------------
The official User-To-User support forum of the Apache HTTP Server Project.
See <URL:http://httpd.apache.org/userslist.html> for more info.
To unsubscribe, e-mail: users-unsubscribe@httpd.apache.org
" from the digest: users-digest-unsubscribe@httpd.apache.org
For additional commands, e-mail: users-help@httpd.apache.org
Re: [users@httpd] "Too many connections" error fix?
Posted by Brian Dessent <br...@dessent.net>.
URLTrak Support wrote:
>
> Thanks. I will have to do some thorough researching before making any major
> changes. The site is too high traffic to take chances at this point. I need
> certainties before taking actions.
>
> So do you think that by modifying the my.cnf to reflect higher connections
> allowed will help? It helped for a while, at 500 connections. I just bumped
> it up to 600 tonight. I will have to wait to see if this helps.
>
> Any suggestion on a good number in httpd.conf for MaxClients or some other
> var? Someone said that I should have a higher max_connections number than
> the one defined in the httpd.conf for MaxClients.
I don't understand where all these connections are going? There should
be, at most, one MySQL connection per Apache worker process, which you
said was set to a maximum of 150 (MaxClients). If persistent
connections are enabled then each worker will create a connection and
then keep it open until that process ends. How in the world are you
getting all these hundreds of connections then? I agree with the person
that said to run the "SHOW PROCESSLIST" command, that will show you
exactly what's going on.
Brian
---------------------------------------------------------------------
The official User-To-User support forum of the Apache HTTP Server Project.
See <URL:http://httpd.apache.org/userslist.html> for more info.
To unsubscribe, e-mail: users-unsubscribe@httpd.apache.org
" from the digest: users-digest-unsubscribe@httpd.apache.org
For additional commands, e-mail: users-help@httpd.apache.org
Re: [users@httpd] "Too many connections" error fix?
Posted by URLTrak Support <ur...@hotmail.com>.
Thanks. I will have to do some thorough researching before making any major
changes. The site is too high traffic to take chances at this point. I need
certainties before taking actions.
So do you think that by modifying the my.cnf to reflect higher connections
allowed will help? It helped for a while, at 500 connections. I just bumped
it up to 600 tonight. I will have to wait to see if this helps.
Any suggestion on a good number in httpd.conf for MaxClients or some other
var? Someone said that I should have a higher max_connections number than
the one defined in the httpd.conf for MaxClients.
Nicole
aeontrek.com
----- Original Message -----
From: "Brian Dessent" <br...@dessent.net>
To: <us...@httpd.apache.org>
Sent: Thursday, October 02, 2003 2:23 AM
Subject: Re: [users@httpd] "Too many connections" error fix?
> Nathan Ollerenshaw wrote:
>
> > If you see a lot of SELECT all taking a long time to complete, you will
> > probably find that any INSERT or UPDATEs going on are locking the whole
> > table and stopping the SELECTs from completing (I'm sure there is a
> > better explanation but I'm not a real DBA).
>
> Not true:
>
> <http://www.mysql.com/doc/en/Table_locking.html>
>
> "In MySQL Version 3.23.7 and above, you can insert rows into MyISAM
> tables at the same time other threads are reading from the table. Note
> that currently this only works if there are no holes after deleted rows
> in the table at the time the insert is made. When all holes has been
> filled with new data, concurrent inserts will automatically be enabled
> again."
>
> Brian
>
> ---------------------------------------------------------------------
> The official User-To-User support forum of the Apache HTTP Server Project.
> See <URL:http://httpd.apache.org/userslist.html> for more info.
> To unsubscribe, e-mail: users-unsubscribe@httpd.apache.org
> " from the digest: users-digest-unsubscribe@httpd.apache.org
> For additional commands, e-mail: users-help@httpd.apache.org
>
>
---------------------------------------------------------------------
The official User-To-User support forum of the Apache HTTP Server Project.
See <URL:http://httpd.apache.org/userslist.html> for more info.
To unsubscribe, e-mail: users-unsubscribe@httpd.apache.org
" from the digest: users-digest-unsubscribe@httpd.apache.org
For additional commands, e-mail: users-help@httpd.apache.org
Re: [users@httpd] "Too many connections" error fix?
Posted by Brian Dessent <br...@dessent.net>.
Nathan Ollerenshaw wrote:
> If you see a lot of SELECT all taking a long time to complete, you will
> probably find that any INSERT or UPDATEs going on are locking the whole
> table and stopping the SELECTs from completing (I'm sure there is a
> better explanation but I'm not a real DBA).
Not true:
<http://www.mysql.com/doc/en/Table_locking.html>
"In MySQL Version 3.23.7 and above, you can insert rows into MyISAM
tables at the same time other threads are reading from the table. Note
that currently this only works if there are no holes after deleted rows
in the table at the time the insert is made. When all holes has been
filled with new data, concurrent inserts will automatically be enabled
again."
Brian
---------------------------------------------------------------------
The official User-To-User support forum of the Apache HTTP Server Project.
See <URL:http://httpd.apache.org/userslist.html> for more info.
To unsubscribe, e-mail: users-unsubscribe@httpd.apache.org
" from the digest: users-digest-unsubscribe@httpd.apache.org
For additional commands, e-mail: users-help@httpd.apache.org
Re: [users@httpd] "Too many connections" error fix?
Posted by Nathan Ollerenshaw <na...@valuecommerce.ne.jp>.
On Oct 2, 2003, at 3:12 PM, MD Raziuddin Haqqani wrote:
> [mysqld]
> datadir=/var/lib/mysql
> socket=/var/lib/mysql/mysql.sock
> set-variable = max_connections=900
> set-variable = wait_timeout=1200
>
> [mysql.server]
> user=mysql
> basedir=/var/lib
> set-variable = max_connection=100000
> set-variable = table_cache=10000
> set-variable = wait_timeout=900
increasing the number of max connections will help too, though I would
not increase them beyond say 1000. Any more than that and you have a
different problem ..
nathan.
--
Nathan Ollerenshaw - Unix Systems Engineer
ValueCommerce - http://www.valuecommerce.ne.jp/
---------------------------------------------------------------------
The official User-To-User support forum of the Apache HTTP Server Project.
See <URL:http://httpd.apache.org/userslist.html> for more info.
To unsubscribe, e-mail: users-unsubscribe@httpd.apache.org
" from the digest: users-digest-unsubscribe@httpd.apache.org
For additional commands, e-mail: users-help@httpd.apache.org
Re: [users@httpd] "Too many connections" error fix?
Posted by MD Raziuddin Haqqani <ha...@sahara.com.sa>.
I to got the same problem but I change my my.cnf to something like this:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
set-variable = max_connections=900
set-variable = wait_timeout=1200
[mysql.server]
user=mysql
basedir=/var/lib
set-variable = max_connection=100000
set-variable = table_cache=10000
set-variable = wait_timeout=900
[safe_mysqld]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Hope this will help you
----- Original Message -----
From: "Nathan Ollerenshaw" <na...@valuecommerce.ne.jp>
To: <us...@httpd.apache.org>
Sent: Thursday, October 02, 2003 9:06 AM
Subject: Re: [users@httpd] "Too many connections" error fix?
> On Oct 2, 2003, at 2:49 PM, URLTrak Support wrote:
>
> > I last increased my max connections to 500. That seemed to fix things
> > for a
> > while. Now I am getting the problems (database freezing up because too
> > many
> > connections) again. The site I run is pretty high traffic.
>
> Look at your MySQL DB.
>
> You sound like your running vBulletin or phpNuke or something like that.
>
> If you're using a standard install, your MySQL tables will all be using
> MyISAM table format, which only supports table-level locking.
>
> do a mysqladmin processlist (you might need to authenticate with that
> command depending on your setup). This will show you the state of all
> the connections currently to your MySQL database.
>
> If you see a lot of SELECT all taking a long time to complete, you will
> probably find that any INSERT or UPDATEs going on are locking the whole
> table and stopping the SELECTs from completing (I'm sure there is a
> better explanation but I'm not a real DBA).
>
> The fix is to convert all your tables to INNODB, which does row-level
> locking and allows a select to happen at the same time as an insert or
> update.
>
> One DB I manage, I set up the my.cnf like so:
>
> # Uncomment the following if you are using Innobase tables
> innodb_data_file_path = ibdata1:1000M
> innodb_data_home_dir = /usr/local/mysql-max/data/innodb
> innodb_log_group_home_dir = /usr/local/mysql-max/data/innodb/log
> innodb_log_arch_dir = /usr/local/mysql-max/data/innodb/log
> set-variable = innodb_mirrored_log_groups=1
> set-variable = innodb_log_files_in_group=3
> set-variable = innodb_log_file_size=5M
> set-variable = innodb_log_buffer_size=8M
> innodb_flush_log_at_trx_commit=1
> innodb_log_archive=0
> set-variable = innodb_buffer_pool_size=16M
> set-variable = innodb_additional_mem_pool_size=2M
> set-variable = innodb_file_io_threads=4
> set-variable = innodb_lock_wait_timeout=50
>
> paths obviously need to match your configuration. Note that the
> innodb_data_file_path will create a file of 1GB when you restart mysql!
> You can make it smaller, if you want.
>
> Check out the InnoDB documentation for MySQL:
> http://mysql.us.themoes.org/doc/en/InnoDB.html
>
> Then, I converted all my tables one by one with:
>
> ALTER TABLE table_name TYPE=INNODB;
>
> at the mysql command line. This is generally safe to run on a live
> system. I ran it on a large vBulletin site and nobody noticed anything
> but a slight sluggishness.
>
> Hope this helps.
>
> Regards,
>
> Nathan.
>
> --
> Nathan Ollerenshaw - Unix Systems Engineer
> ValueCommerce - http://www.valuecommerce.ne.jp/
>
>
> ---------------------------------------------------------------------
> The official User-To-User support forum of the Apache HTTP Server Project.
> See <URL:http://httpd.apache.org/userslist.html> for more info.
> To unsubscribe, e-mail: users-unsubscribe@httpd.apache.org
> " from the digest: users-digest-unsubscribe@httpd.apache.org
> For additional commands, e-mail: users-help@httpd.apache.org
>
---------------------------------------------------------------------
The official User-To-User support forum of the Apache HTTP Server Project.
See <URL:http://httpd.apache.org/userslist.html> for more info.
To unsubscribe, e-mail: users-unsubscribe@httpd.apache.org
" from the digest: users-digest-unsubscribe@httpd.apache.org
For additional commands, e-mail: users-help@httpd.apache.org
Re: [users@httpd] "Too many connections" error fix?
Posted by URLTrak Support <ur...@hotmail.com>.
Thanks Nathan. I am not running either of the things you mentioned. I simply
run an link tracking site (which I wrote in PHP) that involves a bunch of
inserts, updates, selects, and so forth. And the site is pretty high
traffic, with tons of hits to the db (MySQL).
So your solution sounds like a good idea, as far as the row-level locking
instead of table locking. Thanks. I will have to read up more on this before
attempting to do it, though. And yes, it is defaulting to MyISAM table
types.
Again, thanks.
Nicole
aeontrek.com
----- Original Message -----
From: "Nathan Ollerenshaw" <na...@valuecommerce.ne.jp>
To: <us...@httpd.apache.org>
Sent: Thursday, October 02, 2003 2:06 AM
Subject: Re: [users@httpd] "Too many connections" error fix?
> On Oct 2, 2003, at 2:49 PM, URLTrak Support wrote:
>
> > I last increased my max connections to 500. That seemed to fix things
> > for a
> > while. Now I am getting the problems (database freezing up because too
> > many
> > connections) again. The site I run is pretty high traffic.
>
> Look at your MySQL DB.
>
> You sound like your running vBulletin or phpNuke or something like that.
>
> If you're using a standard install, your MySQL tables will all be using
> MyISAM table format, which only supports table-level locking.
>
> do a mysqladmin processlist (you might need to authenticate with that
> command depending on your setup). This will show you the state of all
> the connections currently to your MySQL database.
>
> If you see a lot of SELECT all taking a long time to complete, you will
> probably find that any INSERT or UPDATEs going on are locking the whole
> table and stopping the SELECTs from completing (I'm sure there is a
> better explanation but I'm not a real DBA).
>
> The fix is to convert all your tables to INNODB, which does row-level
> locking and allows a select to happen at the same time as an insert or
> update.
>
> One DB I manage, I set up the my.cnf like so:
>
> # Uncomment the following if you are using Innobase tables
> innodb_data_file_path = ibdata1:1000M
> innodb_data_home_dir = /usr/local/mysql-max/data/innodb
> innodb_log_group_home_dir = /usr/local/mysql-max/data/innodb/log
> innodb_log_arch_dir = /usr/local/mysql-max/data/innodb/log
> set-variable = innodb_mirrored_log_groups=1
> set-variable = innodb_log_files_in_group=3
> set-variable = innodb_log_file_size=5M
> set-variable = innodb_log_buffer_size=8M
> innodb_flush_log_at_trx_commit=1
> innodb_log_archive=0
> set-variable = innodb_buffer_pool_size=16M
> set-variable = innodb_additional_mem_pool_size=2M
> set-variable = innodb_file_io_threads=4
> set-variable = innodb_lock_wait_timeout=50
>
> paths obviously need to match your configuration. Note that the
> innodb_data_file_path will create a file of 1GB when you restart mysql!
> You can make it smaller, if you want.
>
> Check out the InnoDB documentation for MySQL:
> http://mysql.us.themoes.org/doc/en/InnoDB.html
>
> Then, I converted all my tables one by one with:
>
> ALTER TABLE table_name TYPE=INNODB;
>
> at the mysql command line. This is generally safe to run on a live
> system. I ran it on a large vBulletin site and nobody noticed anything
> but a slight sluggishness.
>
> Hope this helps.
>
> Regards,
>
> Nathan.
>
> --
> Nathan Ollerenshaw - Unix Systems Engineer
> ValueCommerce - http://www.valuecommerce.ne.jp/
>
>
> ---------------------------------------------------------------------
> The official User-To-User support forum of the Apache HTTP Server Project.
> See <URL:http://httpd.apache.org/userslist.html> for more info.
> To unsubscribe, e-mail: users-unsubscribe@httpd.apache.org
> " from the digest: users-digest-unsubscribe@httpd.apache.org
> For additional commands, e-mail: users-help@httpd.apache.org
>
>
---------------------------------------------------------------------
The official User-To-User support forum of the Apache HTTP Server Project.
See <URL:http://httpd.apache.org/userslist.html> for more info.
To unsubscribe, e-mail: users-unsubscribe@httpd.apache.org
" from the digest: users-digest-unsubscribe@httpd.apache.org
For additional commands, e-mail: users-help@httpd.apache.org
Re: [users@httpd] "Too many connections" error fix?
Posted by Nathan Ollerenshaw <na...@valuecommerce.ne.jp>.
On Oct 2, 2003, at 2:49 PM, URLTrak Support wrote:
> I last increased my max connections to 500. That seemed to fix things
> for a
> while. Now I am getting the problems (database freezing up because too
> many
> connections) again. The site I run is pretty high traffic.
Look at your MySQL DB.
You sound like your running vBulletin or phpNuke or something like that.
If you're using a standard install, your MySQL tables will all be using
MyISAM table format, which only supports table-level locking.
do a mysqladmin processlist (you might need to authenticate with that
command depending on your setup). This will show you the state of all
the connections currently to your MySQL database.
If you see a lot of SELECT all taking a long time to complete, you will
probably find that any INSERT or UPDATEs going on are locking the whole
table and stopping the SELECTs from completing (I'm sure there is a
better explanation but I'm not a real DBA).
The fix is to convert all your tables to INNODB, which does row-level
locking and allows a select to happen at the same time as an insert or
update.
One DB I manage, I set up the my.cnf like so:
# Uncomment the following if you are using Innobase tables
innodb_data_file_path = ibdata1:1000M
innodb_data_home_dir = /usr/local/mysql-max/data/innodb
innodb_log_group_home_dir = /usr/local/mysql-max/data/innodb/log
innodb_log_arch_dir = /usr/local/mysql-max/data/innodb/log
set-variable = innodb_mirrored_log_groups=1
set-variable = innodb_log_files_in_group=3
set-variable = innodb_log_file_size=5M
set-variable = innodb_log_buffer_size=8M
innodb_flush_log_at_trx_commit=1
innodb_log_archive=0
set-variable = innodb_buffer_pool_size=16M
set-variable = innodb_additional_mem_pool_size=2M
set-variable = innodb_file_io_threads=4
set-variable = innodb_lock_wait_timeout=50
paths obviously need to match your configuration. Note that the
innodb_data_file_path will create a file of 1GB when you restart mysql!
You can make it smaller, if you want.
Check out the InnoDB documentation for MySQL:
http://mysql.us.themoes.org/doc/en/InnoDB.html
Then, I converted all my tables one by one with:
ALTER TABLE table_name TYPE=INNODB;
at the mysql command line. This is generally safe to run on a live
system. I ran it on a large vBulletin site and nobody noticed anything
but a slight sluggishness.
Hope this helps.
Regards,
Nathan.
--
Nathan Ollerenshaw - Unix Systems Engineer
ValueCommerce - http://www.valuecommerce.ne.jp/
---------------------------------------------------------------------
The official User-To-User support forum of the Apache HTTP Server Project.
See <URL:http://httpd.apache.org/userslist.html> for more info.
To unsubscribe, e-mail: users-unsubscribe@httpd.apache.org
" from the digest: users-digest-unsubscribe@httpd.apache.org
For additional commands, e-mail: users-help@httpd.apache.org