Results 1 to 5 of 5
  1. #1

    Check my mysql config - optimise or upgrade?

    Hi

    we've currently got a 2 server setup, apache+mysql

    Mysql server is a Xeon 3.2Ghz with 2Gb ram, scsi 73gb raid1 disks

    Server has been running a while, steadily increasing in traffic. whilst 90% of the time it's running ok (load around 0.5 - 1.0, cpu around 25-50% busy mainly mysql, disk i/o around 3% busy) during heavy traffic times everything slows right down

    At the moment the mysql box is only running mysql so we've allocated about 1.5gb out of the 2gb to mysql.

    At present, when we get busy the apache server is queing up requests as it waits for the mysql server. Query log shows most queries are <1sec, any other's are being optimised but due to the nature of our website there are a few pesky queries (only occasionally being slow though).

    question is, can this config be tuned any more or is it time for an upgrade - mainly ram to add more innodb buffers?

    Here's the result of tuning-primer.sh

    Code:
    SLOW QUERIES
    The slow query log is enabled.
    Current long_query_time = 2 sec.
    You have 9 out of 159346 that take longer than 2 sec. to complete
    Your long_query_time seems to be fine
    
    BINARY UPDATE LOG
    The binary update log is enabled
    The expire_logs_days is not set.
    The mysqld will retain the entire binary log until RESET MASTER or PURGE MASTER LOGS commands are run manually
    Setting expire_log_days will allow you to remove old binary logs automatically
    See http://dev.mysql.com/doc/refman/4.1/en/purge-master-logs.html
    
    WORKER THREADS
    Current thread_cache_size = 384
    Current threads_cached = 15
    Current threads_per_sec = 0
    Historic threads_per_sec = 0
    Your thread_cache_size is fine
    
    MAX CONNECTIONS
    Current max_connections = 150
    Current threads_connected = 4
    Historic max_used_connections = 18
    The number of used connections is 12% of the configured maximum.
    Your max_connections variable seems to be fine.
    
    MEMORY USAGE
    Max Memory Ever Allocated : 1 G
    Configured Max Per-thread Buffers : 5 G
    Configured Max Global Buffers : 803 M
    Configured Max Memory Limit : 6 G
    Physical Memory : 1.99 G
    
    Max memory limit exceeds 90% of physical memory
    
    KEY BUFFER
    Current MyISAM index space = 82 K
    Current key_buffer_size = 5 M
    Key cache miss rate is 1 : 1862
    Key buffer fill ratio = 100.00 %
    Your key_buffer_size seems to be fine
    
    QUERY CACHE
    Query cache is supported but not enabled
    Perhaps you should set the query_cache_size
    
    SORT OPERATIONS
    Current sort_buffer_size = 16 M
    Current read_rnd_buffer_size = 7 M
    Sort buffer seems to be fine
    
    JOINS
    Current join_buffer_size = 4.00 M
    You have had 0 queries where a join could not use an index properly
    Your joins seem to be using indexes properly
    join_buffer_size >= 4 M
    This is not advised
    
    OPEN FILES LIMIT
    Current open_files_limit = 11095 files
    The open_files_limit should typically be set to at least 2x-3x
    that of table_cache if you have heavy MyISAM usage.
    Your open_files_limit value seems to be fine
    
    TABLE CACHE
    Current table_cache value = 5467 tables
    You have a total of 732 tables
    You have 155 open tables.
    The table_cache value seems to be fine
    
    TEMP TABLES
    Current max_heap_table_size = 255 M
    Current tmp_table_size = 256 M
    Of 21109 temp tables, 35% were created on disk
    Effective in-memory tmp_table_size is limited to max_heap_table_size.
    Perhaps you should increase your tmp_table_size and/or max_heap_table_size
    to reduce the number of disk-based temporary tables
    Note! BLOB and TEXT columns are not allow in memory tables.
    If you are using these columns raising these values might not impact your
    ratio of on disk temp tables.
    
    TABLE SCANS
    Current read_buffer_size = 7 M
    Current table scan ratio = 929 : 1
    read_buffer_size seems to be fine
    
    TABLE LOCKING
    Current Lock Wait ratio = 1 : 117
    You may benefit from selective use of InnoDB.
    If you have long running SELECT's against MyISAM tables and perform
    frequent updates consider setting 'low_priority_updates=1'
    Server is mainly Innodb due to previous row-locking issues.

  2. #2
    Join Date
    Jul 2003
    Location
    Olean, NY
    Posts
    143
    Doesn't look too far from ideal. Two things: More memory, and also look at what pages you can cache at the web server. In most web apps people generate way more mysql queries than they really need to.
    System administration, application development, and project management.
    http://erek.blumenthals.com/blog/

  3. #3
    We're already using memcached to cache a lot of page level elements, as well as queries in places which does help a lot.

    Going to have a look at tuning a few queries but as you say I think memory will help a lot here.

  4. #4
    Join Date
    Mar 2008
    Location
    kolkata, India
    Posts
    102
    If I have the issue I will go for optimization. If you don’t know how to, Let me know.
    Sysfirm
    So you think your server is secure?
    Try our security Service
    With SysFirm

  5. #5
    Join Date
    Dec 2006
    Posts
    477
    The primier doesn't tell you much about innodb. What does mysqlreport output? http://www.hackmysql.com/mysqlreport EDIT: Also how many processor cores do you have and which version of mysql.

Posting Permissions

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