[SOLVED] MySQL keeps crashing

MichaelGMorgan · 02-25-2011, 08:10 AM

Hello,
I'm currently running LAMP on an Amazon instance. The OS is from the Amazon 64 bit image.

Basically, every so often MySQL seems to go down. All of my sites fail to connect to their databases. I then have to SSH in and restart mysqld.

Heres the last 30 lines of /var/log/mysqld.log

Quote:

110225 13:35:33 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
110225 13:35:33 InnoDB: Started; log sequence number 0 38288352
110225 13:35:33 [Note] Event Scheduler: Loaded 0 events
110225 13:35:33 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.47' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution
110225 13:36:01 mysqld_safe Number of processes running now: 0
110225 13:36:01 mysqld_safe mysqld restarted
InnoDB: Error: pthread_create returned 11
110225 13:36:02 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
110225 13:36:39 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
110225 13:36:39 InnoDB: Started; log sequence number 0 38288352
110225 13:36:39 [Note] Event Scheduler: Loaded 0 events
110225 13:36:39 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.47' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution
110225 13:54:16 mysqld_safe Number of processes running now: 0
110225 13:54:16 mysqld_safe mysqld restarted
InnoDB: Error: pthread_create returned 11
110225 13:54:16 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
110225 13:54:53 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
110225 13:54:53 InnoDB: Started; log sequence number 0 38288352
110225 13:54:53 [Note] Event Scheduler: Loaded 0 events
110225 13:54:53 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.47' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution
110225 14:00:01 mysqld_safe Number of processes running now: 0
110225 14:00:15 mysqld_safe mysqld restarted
110225 14:00:40 InnoDB: Started; log sequence number 0 38288352
110225 14:00:44 [Note] Event Scheduler: Loaded 0 events
110225 14:00:44 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.1.47' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution
110225 14:00:47 mysqld_safe A mysqld process already exists

I've removed mysql and mysql-server using 'yum remove' and then reinstalled it but the same is happening. I don't know what to try next.

Thanks!

quanta · 02-25-2011, 08:35 AM

Quote:

Code:

InnoDB: Error: pthread_create returned 11
110225 13:36:02 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

Someone suggest disable SELinux. Give it a try and let me know if it work.

MichaelGMorgan · 02-25-2011, 01:04 PM

Surely SELinux needs to remain in place? This system was created from the exact same image as another system (so they're identical in terms of configuration). The other system works fine but handles a much lower load. Do you think this could be caused by running out of memory? How can I check this?

Thanks

quanta · 02-25-2011, 11:21 PM

As my understand, the above error seems that MySQL cannot create enough threads at startup. Did you take a look at /var/log/messages to see if it has anything relate to mysqld?

I suggest you start MySQL in debug mode for more details: http://forge.mysql.com/wiki/How_to_R...ith_a_Debugger

markseger · 02-26-2011, 06:32 AM

I would start by running collectl on the box. When mySQL crashes, go back and plot the collectl data to see which resource was getting starved. If nothing jumps out, drop the monitoring interval to 1 second and try again. There's often a clue in the data somewhere if it's a system/resource problem.
-mark

MichaelGMorgan · 02-26-2011, 06:44 AM

Ahh yes, it's a memory issue. Looking at that I can see that several mysqld and httpd processes have been killed off.

When I run 'top' I can see that most of the swap figures are at 0. 0k total, 0k used, 0k free, 24000k cached
I'm not great at this, but should I be seeing some figures here? I mean, once memory gets used up doesn't it try using pagefiles?

Is there a way that I can prevent the processes from being killed off other than upgrading the memory?
Thanks!

bathory · 02-26-2011, 03:51 PM

I guess you need to increase the stack size. Run

Code:

ulimit -s

to find its actual size (usually it's 8192k)and use

Code:

ulimit -s xxxx

in order increase it

Regards

MichaelGMorgan · 03-02-2011, 04:35 AM

I've got around this with some optimization. I've removed a load of services I don't actually need. I've optimized both httpd and mysqld to be more efficient. It's been running smoothly for a few days now and memory usage is much lower than what it used to be

Thanks for all your help!