Need suggestion with DBD::mysql, mysql-5.0 and connection limit
Dear OTRS users and admins, may I ask you for some suggestion please. We have a FreeBSD-6.2 system (2*Xeon, 6Gb RAM, PAE mode) which is running MySQL 5.0.something, Apache 1.3.fairly-recent, and an OTRS TT system (see http://otrs.org) of revision 2.0.* We are mostly happy with OTRS - it is a really great TT system, a real commercial grade product, it worth attention in any case. There are about 100-150 users working with the system in parallel. Unfortunately, we don't have any Perl/Apache/MySQL guru in staff at the moment, we all are not programmers (and didn't need to be, really, until now). System is running for year now, with no serious problems until last Friday. Than strange things started happening: client connections hang, fails, and in apache logs i see that: [Mon Jun 4 12:14:44 2007] index.pl: DBI connect('database=otrs;host=localhost;','otrs',...) failed: Too many connections at /home/otrs/bin/cgi-bin/../../Kernel/System/DB.pm line 191 or occasionally the same but at line 168. Those are just plain simple "connect" statements, nothing unusual. There are no limits on connections with our MySQL installation that I'm aware of. I checked the docs, no mention of connection limit for MySQL. I just doubled all the memory buffers and other constants in my.cnf - it seems to help but not to cure the problem. Ok I thought and started to look for some limit on connections in DBD::mysql. Also didn't find any. Now I am stuck: what limit are we hitting now? Where is it located? We need to bump it up twice and the system will work flawlessly as usual. Would you please mind providing me some help or suggestion on what is the limit and where to change it? Thanks a lot in advanse! p.s. And yet another 2 questions: if we decide to move to OTRS 2.2 (now beta), will we get a migration tool to import all the current ticket database? And will 2.2 work in Ukrainian or in Russian for us? Thank you! WBR, Andrew Stesin
Andrew Stesin wrote:
may I ask you for some suggestion please. We have a FreeBSD-6.2 system (2*Xeon, 6Gb RAM, PAE mode) which is running MySQL 5.0.something, Apache 1.3.fairly-recent, and an OTRS TT system (see http://otrs.org) of revision 2.0.* We are mostly happy with OTRS - it is a really great TT system, a real commercial grade product, it worth attention in any case. There are about 100-150 users working with the system in parallel. Unfortunately, we don't have any Perl/ Apache/MySQL guru in staff at the moment, we all are not programmers (and didn't need to be, really, until now).
System is running for year now, with no serious problems until last Friday. Than strange things started happening: client connections hang, fails, and in apache logs i see that:
[Mon Jun 4 12:14:44 2007] index.pl: DBI connect ('database=otrs;host=localhost;','otrs',...) failed: Too many connections at /home/otrs/bin/cgi-bin/../../Kernel/System/DB.pm line 191
or occasionally the same but at line 168. Those are just plain simple "connect" statements, nothing unusual.
There are no limits on connections with our MySQL installation that I'm aware of. I checked the docs, no mention of connection limit for MySQL. I just doubled all the memory buffers and other constants in my.cnf - it seems to help but not to cure the problem.
There are default limits and there is documentation available: http://dev.mysql.com/doc/refman/5.0/en/too-many-connections.html.
p.s. And yet another 2 questions: if we decide to move to OTRS 2.2 (now beta), will we get a migration tool to import all the current ticket database?
There's usually an SQL file to run which upgrades the database. No need to really 'import' anything.
And will 2.2 work in Ukrainian or in Russian for us? Thank you!
I don't know which languages are supported by OTRS. We run OTRS in Dutch just fine. Nils Breunese.
Dear Nils, thank you so much! Your answer was extremely helpful (or maybe I was too stupid, let's be honest). But just straight bumping "max_connections" up in my.cnf didn't help alot: now we are running with LA above 40.0 and the whole system is a bit slow. And even much worse it is, we are now getting the following out of memory diagnostics (never seen those before): [Mon Jun 4 17:05:22 2007] index.pl: DBD::mysql::st execute failed: Out of memory (Needed 1603040 bytes) at /home/otrs/bin/cgi-bin/../../Kernel/System/DB.pm line 440. ERROR: OTRS-CGI-10 Perl: 5.8.8 OS: freebsd Time: Mon Jun 4 17:05:22 2007 Message: Out of memory (Needed 1603040 bytes), SQL: 'SELECT DISTINCT st.id, st.tn, st.create_time_unix FROM ticket st, queue sq WHERE sq.id = st.queue_id AND st.ticket_state_id IN (1,4,6,7,8) AND st.queue_id IN (24,61) AND sq.group_id IN (44, 13) AND st.tn LIKE '2007060110510' ORDER BY st.create_time_unix DESC LIMIT 5000' Traceback (69122): Module: Kernel::System::Ticket::TicketSearch (v1.194) Line: 2484 Module: Kernel::Modules::AgentTicketSearch::Run (v1.10) Line: 288 Module: Kernel::System::Web::InterfaceAgent::Run (v1.8) Line: 651 Module: /home/otrs/bin/cgi-bin/index.pl (v1.80) Line: 47 [Mon Jun 4 17:05:22 2007] index.pl: DBD::mysql::st fetchrow_array failed: fetch() without execute() at /home/otrs/bin/cgi-bin/../../Kernel/System/DB.pm line 477. ERROR: OTRS-CGI-10 Perl: 5.8.8 OS: freebsd Time: Mon Jun 4 17:05:23 2007 Message: Out of memory (Needed 1603040 bytes), SQL: 'SELECT DISTINCT st.id, st.tn, st.create_time_unix FROM ticket st, queue sq WHERE sq.id = st.queue_id AND st.ticket_state_id IN (1,4,6,7,8) AND st.queue_id IN (24,61) AND sq.group_id IN (44, 13) AND st.tn LIKE '2007060110316' ORDER BY st.create_time_unix DESC LIMIT 5000' Traceback (69134): Module: Kernel::System::Ticket::TicketSearch (v1.194) Line: 2484 Module: Kernel::Modules::AgentTicketSearch::Run (v1.10) Line: 288 Module: Kernel::System::Web::InterfaceAgent::Run (v1.8) Line: 651 Module: /home/otrs/bin/cgi-bin/index.pl (v1.80) Line: 47 [Mon Jun 4 17:05:23 2007] index.pl: DBD::mysql::st fetchrow_array failed: fetch() without execute() at /home/otrs/bin/cgi-bin/../../Kernel/System/DB.pm line 477. ERROR: OTRS-CGI-10 Perl: 5.8.8 OS: freebsd Time: Mon Jun 4 17:05:24 2007 Message: Out of memory (Needed 1603040 bytes), SQL: 'SELECT DISTINCT st.id, st.tn, st.create_time_unix FROM ticket st, queue sq WHERE sq.id = st.queue_id AND st.ticket_state_id IN (1,4,6,7,8) AND st.queue_id IN (24,61) AND sq.group_id IN (44, 13) AND st.tn LIKE '2007060110532' ORDER BY st.create_time_unix DESC LIMIT 5000' Traceback (69119): Module: Kernel::System::Ticket::TicketSearch (v1.194) Line: 2484 Module: Kernel::Modules::AgentTicketSearch::Run (v1.10) Line: 288 Module: Kernel::System::Web::InterfaceAgent::Run (v1.8) Line: 651 Module: /home/otrs/bin/cgi-bin/index.pl (v1.80) Line: 47 ... and ad infinitum. What is the problem with those negligible 1.6Mb of memory to allocate? I don't bother much, let it consume even 16Mb, we aren't short in total RAM so far. But where's THIS limit hidden? Somewhere in DBD::mysql maybe? Or there is some place where system limits are enforced at OTRS instance? But I can't recall myself imposing any limits on process memory... and where should I look for it? At least in which direction... it's Perl started from Apache - I was in a hope that mod_perl works, but top utility shows me a number of httpd processes and a number of separate perl processes, so I'm in a doubt now... Thank you for your time and attention! WBR, Andrew
participants (2)
-
Andrew Stesin -
Nils Breunese (Lemonbit)