Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

MySQL Server Crashes

Posted on 2011-10-10
11
Medium Priority
?
1,032 Views
Last Modified: 2013-11-08
Seems like a few times a day, usually during busy hours, the MySQL server crashes (it usually restarts itself).

In the administrative events, I see a lot of these before it crashes:
C:\Program Files (x86)\Parallels\Plesk\Databases\MySQL\bin\mysqld-nt.exe: Out of memory (Needed 8382532 bytes)
C:\Program Files (x86)\Parallels\Plesk\Databases\MySQL\bin\mysqld-nt.exe: Out of memory (Needed 8382480 bytes)
C:\Program Files (x86)\Parallels\Plesk\Databases\MySQL\bin\mysqld-nt.exe: Out of memory (Needed 8382532 bytes)
...

And than when it crashes, I see this:
The MySQL Server service terminated unexpectedly.  It has done this 1 time(s).  The following corrective action will be taken in 60000 milliseconds: Restart the service.

Why is this happening and how can I fix it?

I have a Windows 2008 64-bit Server with Plesk 10+.
Thanks.
0
Comment
Question by:davideo7
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 7

Expert Comment

by:celazkon
ID: 36945782
Generally, any relational database will use up all available memory, or reach the peak limit set up for it.
What ammount of memory is totaly available on your Windows server?
0
 

Author Comment

by:davideo7
ID: 36945814
celazkon: My server has 4 GB of RAM and the database is the only thing on that server.  Is it possible that the my.ini file needs adjustments?
0
 
LVL 7

Expert Comment

by:celazkon
ID: 36945943
Most probably. See some general info about it:
http://www.mysqlperformanceblog.com/2006/05/17/mysql-server-memory-usage/
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:davideo7
ID: 36945984
Here's the contents of my.ini file

 
[MySQLD]
port=3306
basedir=C:\\Program Files (x86)\\Parallels\\Plesk\\Databases\\MySQL
datadir=C:\\Program Files (x86)\\Parallels\\Plesk\\Databases\\MySQL\\Data
default-character-set=latin1
default-storage-engine=MYISAM
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=16M
innodb_log_file_size=10M
innodb_thread_concurrency=8
max_connections=9000
old_passwords=1
max_user_connections=9000
max_connect_errors=1007
thread_cache_size=512
thread_stack=196608
binlog_cache_size=4M
net_read_timeout=30
net_retry_count=10
net_write_timeout=30
thread_concurrency=8
open_files_limit=0

# This Caches Select Queries
query_cache_size=128M
query_cache_limit=16M

# Added by AccuPro to log slow queries
log-slow-queries=C:\Program Files (x86)\Parallels\Plesk\Databases\MySQL\slow-log.txt
# More added by David
#log-queries-not-using-indexes
long_query_time = 3
#log_long_format

#*** MyISAM Specific options
# Modified by AccuPro myisam_max_sort_file_size from 100G to 5G
myisam_max_sort_file_size=10G
# Modified by AccuPro myisam_sort_buffer_size by AccuPro from 8M to 256M
myisam_sort_buffer_size=256M
# Modified by AccuPro read_buffer_size from 64K to 8M
read_buffer_size=8M
# Modified by AccuPro read_buffer_size from 256K to 8M
read_rnd_buffer_size=8M

# Added by David
max_allowed_packet = 16M
join_buffer=4M

max_heap_table_size=256M
tmp_table_size=256M

record_buffer=8M


# Modified by David
key_buffer_size=512M
table_cache=2048M
sort_buffer_size=4M

Open in new window

0
 

Author Comment

by:davideo7
ID: 37338308
I'm still waiting for a response from someone.
0
 
LVL 36

Assisted Solution

by:gr8gonzo
gr8gonzo earned 1332 total points
ID: 37793143
I would definitely question having 9000 max connections / user connections on a server with only 4 Gb RAM, ESPECIALLY if you're running the DB and web services on the same physical server. That seems like WAY too much to me. It's like adding 1000 doors into a room that can only hold 100 people.

If you're running Plesk, then I'm assuming you're trying to run a hosting service of some kind. With this kind of setup, you need more limits on the usage of each of your clients, since a bad query can start hogging the server. Check out the following page:

http://dev.mysql.com/doc/refman/5.0/en/user-resources.html

It talks about the constraints you can add on to each user account to help keep your resource usage under control. It might be good to check the active queries throughout the day (MySQL command is SHOW PROCESSLIST) and dump them to a log or database table so you can look for any trends that you need to control (e.g. user X is running an adult site that is eating up all your resources, or user Y is running a cron job every minute that starts a query that takes two minutes to run).
0
 
LVL 36

Assisted Solution

by:gr8gonzo
gr8gonzo earned 1332 total points
ID: 37793148
This is also a good resource:
http://dba.stackexchange.com/questions/1229/how-do-you-calculate-mysql-max-connections-variable

...and it's good to run MySQLTuner to get some automated analysis of your config:
http://mysqltuner.pl/mysqltuner.pl

Ultimately, all the configuration in the world won't help you if you let users run almost any query, so your primary goal should be to figure out how to limit resource consumption by your users without negatively impacting their service (although in some cases, you just need to put your foot down).
0
 
LVL 23

Accepted Solution

by:
ComputerTechie earned 668 total points
ID: 37793191
Here a thought outside the box. check the code being used on website and make sure all the open connections are being closed when done. this can make a big improvement.

CT
0

Featured Post

Has Powershell sent you back into the Stone Age?

If managing Active Directory using Windows Powershell® is making you feel like you stepped back in time, you are not alone.  For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
The title says it all. Writing any type of PHP Application or API code that provides high throughput, while under a heavy load, seems to be an arcane art form (Black Magic). This article aims to provide some general guidelines for producing this typ…
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question