Posted on 2011-10-23
Last Modified: 2012-05-12

I am running a java application with a MySQL DB.  Throughout the day the application becomes unresponsive and looking at innotop on the DB server shows that there are somewhere between 20 and 100 queries that are in the queue and not being processed.  Checking top at this point shows me that mysqld is running above 600% and the overall CPU usage is at 99%.  Restarting mysql multiple times is sometimes required to clear out the queries that seem to be stuck.

Here is my setup:

Dedicated box
CentOS 5.5
MySQL 5.0.77
12GB of RAM

Here is my my.cnf:


#long_query_time = 1
tmpdir = /home/mysql/tmp


# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).

# L3
max_allowed_packet = 32MB
transaction-isolation = READ-COMMITTED
default-table-type = INNODB
default-storage-engine = INNODB

low_priority_updates = 1
sort_buffer_size = 2097152
#table_open_cache = 8000

max_connections = 300
#key_buffer_size = 64M
key_buffer_size = 256M
query_cache_limit = 8M
query_cache_size = 128M
query_cache_type = 1
thread_cache_size = 200
table_cache = 1536

# Buffer
innodb_buffer_pool_size = 4500M
innodb_additional_mem_pool_size = 40M
innodb_lock_wait_timeout = 200
innodb_thread_concurrency = 8

# Tune
innodb_checksums = 0
innodb_doublewrite = 0
innodb_support_xa = 0
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_max_dirty_pages_pct = 15

#innodb_force_recovery = 8




Open in new window

I have run mysqltuner on the db but since I have had to restart it recently, the DB hasn't been up for 24 hours yet so I don't know that I fully trust the analysis that it is returning yet.

Thanks for the help!

Question by:GMoney99
    LVL 59

    Accepted Solution

    You may want to check the long-query and slow-query logs. The former appears to be turned off, so you may need to wait on that but see if you can get at queries in the latter. With queries not being processed, what comes to mind is some blocking activity.

    With 12GB of memory, if this server is MySQL only you may be able to increase some of the settings, but just depends on what exactly is going on. Here is a good reference:

    The mysqltuner perl script is pretty good, though, on Linux; therefore, that may do the trick as far as settings optimization goes. Knowing what the queries are doing and correcting any design issues should handle the other side of things.

    Author Closing Comment

    In the end it looks like we need to optimize some of our queries as 1 particular query was causing this issue.  However, the above link was a great read and gave me a better understanding of the settings in mysql.


    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
    Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

    755 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

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now