Results 1 to 9 of 9
  1. #1

    * Please help me for my.cnf optimization

    Hi...
    I have some load problems...
    My server:
    intel p4 3.0 dual core, 2GB Ram
    Centos 4.3, Mysql 5.0

    The site currently receives around 100,000 daily uniques and about 500,000 page impressions daily - it is pretty dynamic and serves a lot of search queries as well as PHP pages and scripts using php and mySQL.

    top:
    Code:
     
    top - 15:42:07 up 1 day, 1:16, 1 user, load average: 31.18, 14.91, 22.08 Tasks: 251 total, 2 running, 249 sleeping, 0 stopped, 0 zombie Cpu(s): 12.3% us, 3.0% sy, 0.0% ni, 0.0% id, 84.6% wa, 0.2% hi, 0.0% si Mem: 2066800k total, 1491372k used, 575428k free, 11040k buffers Swap: 2031608k total, 120980k used, 1910628k free, 888480k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 2172 mysql 16 0 859m 109m 2628 S 27 5.4 0:15.09 mysqld 1178 nobody 15 0 24004 11m 1920 S 0 0.6 0:00.49 httpd 1191 nobody 15 0 21264 9800 2312 S 0 0.5 0:00.38 httpd 1271 nobody 15 0 17260 5452 1924 S 0 0.3 0:00.21 httpd 1308 nobody 15 0 23992 11m 1932 S 0 0.6 0:00.91 httpd 1309 nobody 17 0 21364 9552 1948 S 0 0.5 0:00.32 httpd 1312 nobody 15 0 24000 11m 1940 S 0 0.6 0:00.58 httpd 1321 nobody 15 0 24000 11m 1928 S 0 0.6 0:00.54 httpd 1327 nobody 15 0 16968 5180 1924 S 0 0.3 0:00.10 httpd 1335 nobody 15 0 21384 9984 2372 S 0 0.5 0:00.70 httpd 2185 root 17 0 3416 1068 760 R 0 0.1 0:00.78 top 1 root 16 0 2056 272 244 S 0 0.0 0:12.93 init 2 root RT 0 0 0 0 S 0 0.0 0:00.09 migration/0 3 root 34 19 0 0 0 S 0 0.0 0:00.05 ksoftirqd/0
    my.cnf

    Code:
     
    [client] port = 3306 socket = /var/lib/mysql/mysql.sock [mysqld] datadir=/var/lib/mysql socket = /var/lib/mysql/mysql.sock port = 3306 skip-locking skip-innodb safe-show-database query_cache_limit=2M query_cache_size=64M ## 32MB for every 1GB of RAM query_cache_type=1 max_user_connections=300 max_connections=5000 interactive_timeout=5 wait_timeout=5 connect_timeout=5 thread_cache_size=128 key_buffer=256M ## 64MB for every 1GB of RAM join_buffer=2M max_connect_errors=10 max_allowed_packet=16M table_cache=512 record_buffer=2M sort_buffer_size=2M ## 1MB for every 1GB of RAM read_buffer_size=2M ## 1MB for every 1GB of RAM read_rnd_buffer_size=768 ## 1MB for every 1GB of RAM thread_concurrency=2 ## Number of CPUs x 2 myisam_sort_buffer_size=64M server-id=1 log_slow_queries=yavas.log long_query_time=10 [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/lib/mysql/mysql.pid open_files_limit=8192 [mysqldump] quick max_allowed_packet=16M [mysql] no-auto-rehash #safe-updates [isamchk] key_buffer=232M sort_buffer=132M read_buffer=36M write_buffer=36M [myisamchk] key_buffer=232M sort_buffer=132M read_buffer=36M write_buffer=36M

  2. #2
    Join Date
    Jul 2003
    Location
    Goleta, CA
    Posts
    5,550
    If your page is highly dynamic then query caching will hamper performance by 30% .
    Patron: I'd like my free lunch please.
    Cafe Manager: Free lunch? Did you read the fine print stating it was an April Fool's joke.
    Patron: I read the same way I listen, I ignore the parts I don't agree with. I'm suing you for false advertising.
    Cafe Owner: Is our lawyer still working pro bono?

  3. #3
    If your page is highly dynamic then query caching will hamper performance by 30% .
    Yes the site a kind of search engine.
    What can i do to decrease query caching ?

  4. #4
    Join Date
    Jul 2003
    Location
    Goleta, CA
    Posts
    5,550
    To disable the query cache at server startup, set the query_cache_size system variable to 0
    Patron: I'd like my free lunch please.
    Cafe Manager: Free lunch? Did you read the fine print stating it was an April Fool's joke.
    Patron: I read the same way I listen, I ignore the parts I don't agree with. I'm suing you for false advertising.
    Cafe Owner: Is our lawyer still working pro bono?

  5. #5
    To disable the query cache at server startup, set the query_cache_size system variable to 0
    Thank you. Now i am traying..

  6. #6
    [isamchk]
    key_buffer=232M
    sort_buffer=132M
    read_buffer=36M
    write_buffer=36M
    [myisamchk]
    key_buffer=232M
    sort_buffer=132M
    read_buffer=36M
    write_buffer=36M
    What is these ?? and is this normal ?

  7. #7
    Join Date
    Jul 2003
    Location
    Goleta, CA
    Posts
    5,550
    use http://hackmysql.com/mysqlreport

    following http://hackmysql.com/mysqlreportguide

    to properly tweak your configuration file.
    Patron: I'd like my free lunch please.
    Cafe Manager: Free lunch? Did you read the fine print stating it was an April Fool's joke.
    Patron: I read the same way I listen, I ignore the parts I don't agree with. I'm suing you for false advertising.
    Cafe Owner: Is our lawyer still working pro bono?

  8. #8
    Join Date
    Mar 2003
    Location
    California USA
    Posts
    13,290
    what kernel are you using? sounds like you are not using the latest considering you are on centos 4.3. Upgrade to the latest kernel and some of your problems _may_ go away.
    Steven Ciaburri | Industry's Best Server Management - Rack911.com
    Software Auditing - 400+ Vulnerabilities Found - Quote @ https://www.RACK911Labs.com
    Fully Managed Dedicated Servers (Las Vegas, New York City, & Amsterdam) (AS62710)
    FreeBSD & Linux Server Management, Security Auditing, Server Optimization, PCI Compliance

  9. #9
    please create a my.cnf for my server
    My load 120....

Posting Permissions

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