Results 1 to 5 of 5
  1. #1
    Join Date
    Jul 2011
    Posts
    78

    mysql performance

    I have a dedicated server and this is its information :
    1 SSD for mysql
    1 SAS hdd (15k ) for files
    processors : 2 X E5-2620
    memory : 64 Gig DDR3
    I always see my server is using very little of memory : Memory Used 8.17% (5,384,608 of 65,936,792)


    but it uses processors very mush
    here is the top-c of my server

    Code:
    Swap:        0k total,        0k used,        0k free, 15668996k cached
    
      PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
     3666 mysql      5 -15 4851m 675m 7220 S 234.1  1.0   1522:29 /usr/sbin/mysqld -
    27284 funtoop   20   0  238m  44m 7912 S 70.8  0.1   0:02.25 lsphp5
    27211 songsara  20   0  259m  64m 8068 R 56.9  0.1   0:07.04 lsphp5:/home/songsa
    24758 songsara  20   0  357m  66m 8640 R 49.6  0.1   3:02.04 lsphp5:/home/songsa

    as you can easily the main process is on mysql ,
    I think it doesn't allow my memory to cache or something like this !
    is there any solution in this condition ?

  2. #2
    Join Date
    Oct 2009
    Location
    United States
    Posts
    2,612
    Can you provide us your /etc/my.cnf (MySQL config) file.

    Also try running MySQL tuner (https://github.com/major/MySQLTuner-perl) on the server and see what recommendations it suggests.
    Snoork Hosting - Enterprise Servers | DDoS Protected Network
    99.9% Network Uptime | 15 Minute Ticket Response Time | 24/7 Live Chat
    Check Out Our Dedicated Server Specials For Amazing Discounts & Promotions

  3. #3
    Join Date
    Jul 2011
    Posts
    78
    Quote Originally Posted by Snoork Hosting View Post
    Can you provide us your /etc/my.cnf (MySQL config) file.

    Also try running MySQL tuner (https://github.com/major/MySQLTuner-perl) on the server and see what recommendations it suggests.
    before using that I'm eager to show you my.cnf

    Code:
    [mysqld_safe]
    nice= -15
    [isamchk]
    sort_buffer_size=12M
    read_buffer=12M
    key_buffer=64M
    write_buffer=2M
    [mysqlhotcopy]
    interactive-timeout
    [client]
    default-character-set= utf8
    port=3306
    socket="/var/lib/mysql/mysql.sock"
    [mysqld]
    datadir=/mysql/mysql
    character-set-server= utf8
    collation-server = utf8_general_ci
    
    back_log = 300
    
    open-files-limit = 8192
    open-file = 1024
    port= 3306
    socket= /var/lib/mysql/mysql.sock
    pid-file= /var/lib/mysql/mysql.pid
    skip-external-locking
    skip-name-resolve
    
    relay_log = mysql-relay-bin
    relay_log_index = mysql-relay-index
    #log = mysql-gen.log
    log_error = mysql-error.err
    log_warnings
    log_bin = mysql-bin
    log_slow_queries = mysql-slow.log
    #log_queries_not_using_indexes
    long_query_time = 10        #default: 10
    max_binlog_size = 256M        #max size for binlog before rolling
    
    expire_logs_days = 4        #binlog files older than this will be purged
    
    ## Per-Thread Buffers * (max_connections) = total per-thread mem usage
    thread_stack= 512K     #default: 32bit: 192K, 64bit: 256K
    sort_buffer_size= 2M         #default: 2M, larger may cause perf issues
    read_buffer_size = 2M         #default: 128K, change in increments of 4K
    read_rnd_buffer_size= 2M         #default: 256K
    join_buffer_size= 2M         #default: 128K
    binlog_cache_size = 128K         #default: 32K, size of buffer to hold TX queri$
    ## total per-thread buffer memory usage: 17664000K = 17.250GB
    
    ## Query Cache
    query_cache_size = 64M        #global buffer
    query_cache_limit= 512K        #max query result size to put in cache
    
    ## Connections
    max_connections= 2000        #multiplier for memory usage via per-thread buffers
    max_connect_errors = 100        #default: 10
    
    concurrent_insert= 2        #default: 1, 2: enable insert for all instances
    connect_timeout  = 30        #default -5.1.22: 5, +5.1.22: 10
    
    
    ## Default Table Settings
    sql_mode                         = NO_AUTO_CREATE_USER
    
    ## Table and TMP settings
    max_heap_table_size = 1G        #recommend same size as tmp_table_size
    bulk_insert_buffer_size= 1G        #recommend same size as tmp_table_size
    tmp_table_size = 1G #recommend 1G min
    #tmpdir = /data/mysql-tmp0:/data/mysql-tmp1 #Recommend using RAMDISK for tmpdir
    
    ## Table cache settings
    #table_cache = 512        #5.0.x <default: 64>
    #table_open_cache  = 512        #5.1.x, 5.5.x <default: 64>
    
    ## Thread settings
    thread_concurrency = 48 #recommend 2x CPU cores
    thread_cache_size = 100 #recommend 5% of max_connections
    
    myisam_sort_buffer_size=256M
    server-id=1
    query_cache_size=128M
    query_cache_type=1
    interactive_timeout=60
    sort_buffer_size=8M
    thread_concurrency=16
    local-infile=0
    long_query_time=1
    port=3306
    key_buffer=64M
    thread_cache_size=32
    wait_timeout=50
    join_buffer_size=6M
    max_delayed_threads=1024
    max_user_connections= 30
    skip-external-locking
    read_buffer_size=80M
    skip-federated
    query_cache_limit=32M
    table_cache=1024
    max_heap_table_size=1G
    read_rnd_buffer_size=12M
    tmp_table_size=512M
    open_files_limit=50000
    innodb_buffer_pool_size=124M
    socket="/var/lib/mysql/mysql.sock"
    thread_cache=8192
    max_allowed_packet=512M
    connect_timeout=10
    default-storage-engine=MyISAM
    innodb_file_per_table=1
    [mysqldump]
    quick
    quote-names
    max_allowed_packet=1000M
    [mysql]
    no-auto-rehash
    [myisamchk]
    sort_buffer_size=256M
    read_buffer=2M
    key_buffer=64M
    write_buffer=2M

  4. #4
    Join Date
    Sep 2009
    Posts
    259
    I would also recommend using mysqltuner it will output some useful information to tweak mysql.

  5. #5
    m-mehdipoor,

    A couple quick things. Try turning on slow query processing to identify what queries are talking long to run. These can help you identify what is churning your cpu. Once you identify these queries you can do a describe to find out what is going on in the query execution. You may just need to do some index tuning as well to reduce the churn.

    Now on a side note you could also look to break out your MySQL to seperate Cluster and use something like Clustrix to push the read/write over multiple nodes in a cluster to increase the IO available and reduce query time. It really depends on what your doing.

    Another thing is if this is for some sort of web application or similiar you might be able to implement either page caching with Varnish to reduce the dynamic requests hammering the DB or implement Memcached on queries that don't change that often for the result set. This way you can cache the results in memory for near instant return bypassing the DB for repeated queries.

    Let me know if you want me to go in more depth.
    PCLHS | SAS70 Datacenters in New Jersey/Texas
    100TB Dedicated Servers 1U - Full Cab Colocation Complex Hosting Horizontally Scalable Hosting DR/HA Hosting Public and Private Clouds Web Farms Innovative, Reliable, and Responsive
    Contact Us E: mark [at] pclhs.net | W: www.pclhs.net

Similar Threads

  1. MySQL Performance
    By KyleMoore in forum Dedicated Server
    Replies: 5
    Last Post: 09-24-2012, 04:01 PM
  2. MySQL 5.0 performance better than 4.1 ?
    By glace in forum Hosting Security and Technology
    Replies: 2
    Last Post: 01-07-2007, 04:20 PM
  3. mysql performance
    By OnlineRack in forum Programming Discussion
    Replies: 3
    Last Post: 01-03-2007, 10:18 AM
  4. MySQL performance
    By xmats in forum Hosting Security and Technology
    Replies: 8
    Last Post: 12-15-2004, 04:42 PM
  5. MySQL Performance
    By XTStrike in forum Web Hosting Lounge
    Replies: 22
    Last Post: 06-30-2001, 11:30 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •