Announcement

Announcement Module
Collapse
No announcement yet.

my.cnf advice needed

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • my.cnf advice needed

    Hello,
    I've already made some changes based on other threads I've found here (increasing interactive_timeout from 10 to 600 and wait_timeout from 10 to 30 sec), however I'm looking for any additional suggestions for improving performance. I'm already tracking down some of the slow queries from the log.

    This server is dedicated for MySQL only. The servers connecting to them are mainly connecting via ColdFusion 9, but some PHP.

    Dell PowerEdge 185
    Red Hat Enterprise Linux ES release 3 (Taroon)
    MySQL Version: 5.0.22-standard-log
    4GB of RAM
    Dual 2.8GHz/2MB Cache, Xeon, 800MHz Front Side Bus
    36GB SCSI 15K HD

    my.cnf:
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    set-variable = max_connections=250
    set-variable = interactive_timeout=600
    set-variable = wait_timeout=30
    set-variable = max_allowed_packet=1M
    set-variable = max_connect_errors=999999

    skip-innodb
    skip-bdb
    skip-locking
    key_buffer_size = 1024M
    max_allowed_packet = 2M
    table_cache = 768
    sort_buffer_size = 8M
    record_buffer=1M
    read_buffer_size = 2M
    #read_rnd_buffer_size should be 1M per GB of RAM
    read_rnd_buffer_size = 6M
    myisam_sort_buffer_size = 64M
    thread_cache_size = 64
    query_cache_type=1
    query_cache_size= 64M
    query_cache_limit=2M
    thread_concurrency = 4
    #tmp_table_size should equal max_heap_table_size as mySQL uses the smaller of the two
    tmp_table_size = 1024MB
    max_heap_table_size = 1024MB
    join_buffer_size = 2M

    log-slow-queries
    long_query_time=1
    log-error= /var/lib/mysql/usint02-slow.log
    #log-queries-not-using-indexes

    [mysqldump]
    quick
    max_allowed_packet = 16M

    [mysql.server]
    user=mysql
    #basedir=/var/lib

    [safe_mysqld]
    err-log=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid

    [isamchk]
    set-variable = key_buffer=128M
    set-variable = sort_buffer=128M
    set-variable = read_buffer=2M
    set-variable = write_buffer=2M

    [myisamchk]
    set-variable = key_buffer=128M
    set-variable = sort_buffer=128M
    set-variable = read_buffer=2M
    set-variable = write_buffer=2M

    [mysqlhotcopy]
    interactive-timeout

    I'd be happy to provide any additional information if it would be helpful. We are using phpMyAdmin, so I have provide reports from it as well.

    Thank you,
    Jeff

  • #2
    5.0.22 is a steaming pile of... your first action should be to upgrade to the latest 5.0.x series. And why are you disabling InnoDB?

    Comment


    • #3
      Thanks for your reply xaprb. Obviously, I'm not a DBA and have been learning things on my own as I go. This system has been in place for several years, I wasn't the installer, and hasn't been updated. I appreciate your assistance.

      At the risk of sounding naive, shouldn't I upgrade to the latest version (5.1.51) instead of 5.0.x? Also, since I've never performed one, how complicated is the upgrade process?

      Regarding InnoDB, our tables have been MyISAM since they were set up originally (as the default I believe). I believe that skip-innodb came from when we had MySQL running on the web server and had limited RAM. Should I consider changing to InnoDB? I read this article ( http://www.mysqlperformanceblog.com/2009/01/12/should-you-mo ve-from-myisam-to-innodb/). All our tables together are 133MB and use mysqldump to make nightly backups. I run a website that displays information, there is some user interactivity, but we're mostly doing selects, some of which are pretty complex; very few inserts and deletes except for a couple tables.

      One thing is painfully clear, I think I need to pick up a copy of your book.

      Thanks again,
      Jeff

      Comment


      • #4
        Hi Jeff,

        I apologize for the tone of "why are you disabling InnoDB?" It wasn't meant as "you are wrong to do that," but more as "this is relevant, what are the reasons."

        5.0.22 is the default version shipped with your RHEL. It was an early release and was riddled with bugs. I'd upgrade to the latest 5.0 until you are comfortable with managing the database. The 5.1 upgrade will bring some potential incompatibilities that could cause problems. Not that they are guaranteed to be big, but if you aren't very familiar with MySQL, solving them could be tedious and time-consuming when you least want that.

        I'd like to dig in and be more specific about the performance issues you're having. What symptoms are you seeing?

        Comment


        • #5
          No offense taken.

          How complicated/risky is upgrading from 5.0.22 to the latest 5.0.x (looks like it's 5.0.91)?

          The main issue I'm tracking for now is sporadic connection issues. Occasionally the MySQL server will stop responding to ColdFusion 9 on the webserver which causes it to hang. We've already corrected some networking issues, but still experiencing the hangs. On Thursday I increased MySQL's interactive_timeout from 10 to 600 and wait_timeout from 10 to 30 sec thinking that would be the likely culprits, but experienced another outage last night. Is auto reconnect enabled in MySQL 5.0.22 by default? Is that even what I should be looking at? I had been working the issue from the ColdFusion side for weeks, but not really getting anywhere.

          Beyond the immediate issue, I'd like to tune the database for performance. Our traffic is very cyclical with a huge spike in late August and early September. I'd be happy to provide more info via PM if it would be helpful. Basically, I want to make sure that it's ready for next year's spike.

          Thanks again for your help,
          Jeff

          Comment


          • #6
            jeffchristiansen wrote on Sat, 02 October 2010 19:13

            How complicated/risky is upgrading from 5.0.22 to the latest 5.0.x (looks like it's 5.0.91)?


            It's not really complicated or risky, that's why baron suggests it. If you keep within the 5.0 version you essentially just replace the binary by uninstalling and installing the newer version and that's it.
            jeffchristiansen wrote on Sat, 02 October 2010 19:13


            The main issue I'm tracking for now is sporadic connection issues. Occasionally the MySQL server will stop responding to ColdFusion 9 on the webserver which causes it to hang. We've already corrected some networking issues, but still experiencing the hangs. On Thursday I increased MySQL's interactive_timeout from 10 to 600 and wait_timeout from 10 to 30 sec thinking that would be the likely culprits, but experienced another outage last night. Is auto reconnect enabled in MySQL 5.0.22 by default? Is that even what I should be looking at? I had been working the issue from the ColdFusion side for weeks, but not really getting anywhere.


            Unfortunately those thresholds was set low for a reason, your max_connections setting is limiting how many simultaneous connections that MySQL is allowing. And if you are using connection pooling of mysql connections(part of Apache) that doesn't properly reuse connections, then increasing these timeouts means that MySQL will not "garbage collect" the old connections and you will hit the roof with max_connections instead. I can't say for sure which way is best in your particular case since we haven't got more detailed information about what your "sporadic connection issues" actually are but generally they use to be set to about 10s on database servers that mainly service web applications.

            jeffchristiansen wrote on Sat, 02 October 2010 19:13


            Beyond the immediate issue, I'd like to tune the database for performance. Our traffic is very cyclical with a huge spike in late August and early September. I'd be happy to provide more info via PM if it would be helpful. Basically, I want to make sure that it's ready for next year's spike.


            As baron says, you must start by describing what symptoms you are seing, and here the main thing is if you experience high cpu or high I/O utilization during peak. Then you can continue with checking the slow-query-log that you have configured so see if you have any queries that end up there. And then you could provide us with the output from SHOW VARIABLES;. But remember you should have let the system run for a while and you should let it run over a peak time to get any meaningful data.
            So if you only have two peaks during the year then if you don't have any data from the last time, then you could need to wait until the load picks up before you can provide use with some really useful information.

            Comment


            • #7
              Jeff, I'd suggest that you configure the stalk & collect tools from aspersa http://code.google.com/p/aspersa to collect information when the problem occurs. It could be any of a large variety of things... we'd be stabbing in the dark to guess at it without more data.

              Comment

              Working...
              X