#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    2
    Rep Power
    0

    Question Using MySQL engine resources


    Greetings friends,

    I want to share with you a situation I have been watching to see if anyone has had a similar experience and could share the experience with the aim of finding an explanation for the situation.

    First I start with say that my database server has the following characteristics:
    CPU: Dual Xeon Hex (6) Core 2.53GHz Westmere H/T
    Mem: 64GB DDR3
    Disc: Intel X25-M MLC 80 GB (4)
    OS: CentOS - 64 bit
    MySQL: 5.5.33

    The MySQL setting are:
    Code:
    [mysqld]
    log-error=/var/log/mysqld.log
    init_connect=SET CHARACTER SET utf8
    datadir=/data/mysql
    tmpdir=/var/tmp
    socket=/data/mysql/mysql.sock
    
    #General
    #----------
    skip-name-resolve
    max_connections = 1000
    wait_timeout = 30
    thread_concurrency = 3
    max_allowed_packet = 2M
    
    #Slow Querys
    #----------
    #log-slow-queries = /var/log/mysqlslowq.log
    #long_query_time = 5
    #log-queries-not-using-indexes
    
    #Cache
    #----------
    query_cache_type = 1
    query_cache_size = 72M
    query_cache_limit = 72M
    thread_cache_size = 84
    table_cache = 2560
    table_open_cache = 576
    
    #Buffers
    #---------
    join_buffer_size = 38M
    key_buffer_size = 768M
    myisam_sort_buffer_size = 86M
    sort_buffer_size = 5M
    read_rnd_buffer_size = 15M
    read_buffer_size = 5M
    
    [Server]
    server-id = 1
    log-bin=/data/mysql/drbd
    expire_logs_days = 5
    max_binlog_size = 100M
    
    [client]
    socket=/data/mysql/mysql.sock
    The situation is:

    The point is that I keep daily the server behavior and have frequently observed major peaks occur in the processor usage, sometimes reaching 1500% above where memory is always in 3.5 Gb of consumption.

    Could someone tell me the reason for this behavior and if indeed the configuration of MySQL is the most suitable for this server?

    Thanks you very much for the information.
  2. #2
  3. No Profile Picture
    Problem Solver
    Devshed Specialist (4000 - 4499 posts)

    Join Date
    Jan 2001
    Location
    Stockholm, Sweden
    Posts
    4,495
    Rep Power
    534
    What do you get in the slow query log if you turn it on again?

    The thing is that getting high spikes in CPU on a DB server can be anything from normal behavior due to clients asking heavy queries (or just a lot of them at the same time) to backups to badly written query with a cross join in it.

    So you will have to dig through the slow-query-log and/or the query log to find out what is happening at the exact time when you get the CPU spikes to be able to determine why they occur.
    /Stefan
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Aug 2013
    Posts
    2
    Rep Power
    0
    Originally Posted by sr
    What do you get in the slow query log if you turn it on again?

    The thing is that getting high spikes in CPU on a DB server can be anything from normal behavior due to clients asking heavy queries (or just a lot of them at the same time) to backups to badly written query with a cross join in it.

    So you will have to dig through the slow-query-log and/or the query log to find out what is happening at the exact time when you get the CPU spikes to be able to determine why they occur.
    Thanks you very much;

    For your advices, I 'll turn on the slow querys to findout what happen....

    Thanks again...

IMN logo majestic logo threadwatch logo seochat tools logo