GET 24/7 LIVE HELP NOW

Announcement

Announcement Module
Collapse
No announcement yet.

Configuring my.cnf

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

  • Configuring my.cnf

    Hello,

    It would be just great if someone could provide me some tips how to get the best MySQL performance on my server. I have server with 2xDual Opteron CPUs, 2 GB RAM and 160 GB SATA drive. There is Apache 1.3.x, PHP 4.4.4 and MySQL 4.1.21 installed. Server hosts about 100 domains, each domain has database with about 25.000 rows (about 100 MB of data per database).

    Basically all the queries are like these:

    "select * from keywords where keyword='xxx'";
    "update keywords set some_field='very_big_text' where id='xx'";

    But the performance isn't good (yes, I use full index for column "keyword"). Server gets about 200 Apache requests at the same time, and mysql drives me crazy. I think Apache is tweaked well itself, because when I try to open any site (without mysql database, only simple html) it's being opened in speed of light. But when I try to open page with content from mysql database, it takes about 20-60 seconds (depends on current server load) before anything is displayed.

    May someone suggest optimal values for my.cnf file and some other tips (if any), please?

  • #2
    Post your current my.cnf and are you using MyISAM or InnoDB engine?

    Comment


    • #3
      I'm using MyISAM. Actually, my.cnf has nothing at the moment (except this line: set-variable = max_connections=500), because everything was reinstalled today (it's cPanel powered server).

      Comment


      • #4
        Can you post the output of top sorted by memory usage?

        Comment


        • #5
          3901 mysql 15 0 168m 51m 3436 S 1 2.6 7:19.85 mysqld
          28921 nobody 16 0 75808 52m 7180 S 1 2.6 0:06.47 httpd
          1061 nobody 16 0 71044 43m 2772 S 1 2.2 0:00.03 httpd
          20743 nobody 15 0 77620 53m 6840 S 0 2.7 0:11.93 httpd
          837 nobody 15 0 71820 44m 3264 S 0 2.2 0:00.10 httpd
          1037 nobody 15 0 71072 43m 2456 S 0 2.1 0:00.03 httpd
          1055 nobody 15 0 70924 43m 2380 S 0 2.1 0:00.02 httpd
          1077 nobody 15 0 71248 43m 2448 S 0 2.1 0:00.04 httpd
          1475 root 16 0 3680 1156 760 R 0 0.1 0:00.40 top
          1494 nobody 15 0 70924 43m 2380 S 0 2.1 0:00.02 httpd
          1539 nobody 17 0 73636 45m 2412 S 0 2.3 0:00.05 httpd
          1 root 16 0 2484 552 472 S 0 0.0 0:00.90 init

          Comment


          • #6
            After clicking "Show MySQL Processes" on WHM, I see over 200 proccesses now

            Comment


            • #7
              Is it possible that issue is related to number of available mysql connections, or something like this?

              I think so, because server gets the same number of requests all the time, but mysql makes very high load only every 20-30 minutes. When load is normal, after clicking "View mysql proccesses", I see only few proccesses. But about 20 minutes later ALL databases on server seem to be having tons of connections (at the moment there are over 300 proccesses). Then server fails for 5 minutes and mysql is restarted automatically. Everything works fine then (server still gets many apache requests, but sites work well), and after another 20 minutes same problem happens - absolutely all databses start making queries at the same time.


              My configuration looks like this now:


              [mysqld]
              skip-locking
              skip-innodb
              query_cache_limit=1M
              query_cache_size=8M
              query_cache_type=1
              max_connections=900
              interactive_timeout=150
              wait_timeout=40
              connect_timeout=10
              thread_cache_size=80
              key_buffer_size=64M
              join_buffer=1M
              max_allowed_packet=16M
              table_cache=768
              sort_buffer_size=1M
              read_buffer_size=1M
              read_rnd_buffer_size=768K
              max_connect_errors=10
              thread_concurrency=8
              myisam_sort_buffer_size=64M
              tmp_table_size=512M
              low_priority_updates=1
              #log-bin
              server-id=1
              log-slow-queries
              long_query_time = 5
              max_user_connections=20


              However, it didn't help at all. If I want to see any data from mysql, website loads about 20-60 seconds. What can I improve here?

              Comment

              Working...
              X