Gossamer Forum
Home : Products : Gossamer Links : Discussions :

mySQL Restarts constantly

Quote Reply
mySQL Restarts constantly
Hello,

We have been using LinksSQL for over a year now, and it is working great. However, lately our mysql is crashing and being restarted automatically.

We have the databases hosted on one server (dual pIII 800, 2gb RAM), and the website with the scripts running mod_perl on two other servers using load balancing.

Our mysql is Distrib 3.23.42.

Below are some data that might be useful in giving us a hand. Please send any suggestions. Thanks.


-------
Code:

my.cnf file

[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
set-variable = max_connections=800
set-variable = key_buffer=384M
set-variable = max_allowed_packet=1M
set-variable = table_cache=512
set-variable = sort_buffer=2M
set-variable = record_buffer=2M
set-variable = thread_cache=8
# Try number of CPU's*2 for thread_concurrency
set-variable = thread_concurrency=4
set-variable = myisam_sort_buffer_size=64M
#log-bin
server-id = 1
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
set-variable = key_buffer=256M
set-variable = sort_buffer=256M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[myisamchk]
set-variable = key_buffer=256M
set-variable = sort_buffer=256M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[mysqlhotcopy]
interactive-timeout


************************************************************************
mysql.err file:

mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked agaist is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose
the problem, but since we have already crashed, something is definitely
wrong
and this may fail

key_buffer_size=402649088
record_buffer=2093056
sort_buffer=2097144
max_used_connections=501
max_connections=800
threads_connected=502
It is possible that mysqld could use up to
key_buffer_size + (record_buffer + sort_buffer)*max_connections = 3666805 K
bytes of memory
Hope that's ok, if not, decrease some variables in the equation

You seem to be running 32-bit Linux and have 502 concurrent connections.
If you have not changed STACK_SIZE in LinuxThreads and build the binary
yourself, LinuxThreads is quite likely to steal a part of global heap for
the thread stack. Please read http://www.mysql.com/doc/L/i/Linux.html

Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Stack range sanity check OK, backtrace follows:
0x80b35c8
0x2aacc27b
0x2abddc61
0x2abddb8a
0x2ab59006
0x80f3913
0x80f0bad
0x80d1748
0x80cfd07
0x80b9ba9
0x80bda89
0x80b8fba
0x80b84ce
Stack trace seems successful - bottom reached
Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and follow
instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x8579a80 =
SELECT mono3Category.ID, mono3Category.Full_Name
FROM mono3CatLinks, mono3Category
WHERE mono3CatLinks.CategoryID = mono3Category.ID
AND (mono3CatLinks.LinkID = '4492')


thd->thread_id=94


Successfully dumped variables, if you ran with --log, take a look at the
details of what thread 94 did to cause the crash. In some cases of really
bad corruption, the values shown above may be invalid

The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains
information that should help you find out what is causing the crash

Number of processes running now: 0
011001 13:11:42 mysqld restarted
Quote Reply
Re: [CyberLuke] mySQL Restarts constantly In reply to
Hi,

Some of these are a little worriesome:

Quote:
key_buffer_size + (record_buffer + sort_buffer)*max_connections = 3 666 805 Kbytes of memory

That's reading 3.6 gigabytes of memory when you only have 2 gigs. Try lowering your mysql settings a little. Also,

Quote:
You seem to be running 32-bit Linux and have 502 concurrent connections.

Do you really need 502 concurrent connections?! Sounds like you are not running mod_perl behind a proxy, which is essential for heavy loaded servers. See:

http://perl.apache.org/guide/strategy.html

for more information.

Hope that helps, and let me know what you find out!

Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] mySQL Restarts constantly In reply to
Alex,

Thanks for your quick response.

We will now install a proxy, and I just wanted to ask you if there is a recommended configuration for the httpd (for the dynamic as well as static one), and if you recommend a specific compilation parameter.

Thanks!

Quote Reply
Re: [CyberLuke] mySQL Restarts constantly In reply to
Hi,

You want to install a very very light apache, or possibly a proxy like squid http://www.squid-cache.org/. Any requests for cgi, php, ssl, or other server intensive work should be proxied back to a heavy mod_perl enabled server.

We have a ratio of 15:1 on our server (15 light apaches per mod_perl one). This is because you don't have a slow client (say on dial up) tieing up a mod_perl server. The mod_perl server does it's work and then sends the information to the front apache. The front apache sends the info to the client (which can take some time), and the mod_perl is free to handle a different request.

Cheers,

Alex
--
Gossamer Threads Inc.