MySQL Server Crashes

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.
davideo7Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

celazkonCommented:
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
davideo7Author Commented:
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
celazkonCommented:
Most probably. See some general info about it:
http://www.mysqlperformanceblog.com/2006/05/17/mysql-server-memory-usage/
0
Simplify Active Directory Administration

Administration of Active Directory does not have to be hard.  Too often what should be a simple task is made more difficult than it needs to be.The solution?  Hyena from SystemTools Software.  With ease-of-use as well as powerful importing and bulk updating capabilities.

davideo7Author Commented:
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
davideo7Author Commented:
I'm still waiting for a response from someone.
0
gr8gonzoConsultantCommented:
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
gr8gonzoConsultantCommented:
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
ComputerTechieCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Linux

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.