HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials

HowtoForge Forums | HowtoForge - Linux Howtos and Tutorials (http://www.howtoforge.com/forums/index.php)
-   Server Operation (http://www.howtoforge.com/forums/forumdisplay.php?f=5)
-   -   Optimization MySQL for Server (http://www.howtoforge.com/forums/showthread.php?t=40743)

longvnit 6th November 2009 06:10

Optimization MySQL for Server
 
hi.
My server have CPU dual core 1.6 Ghz and 1GB RAM
I installed only Centos 5.3 And MySQL 5, Apache & PHP latest version.
I run only 1 website which have ~ 2000 visitor / day.

When I start your server i used command: free -m , i saw your server used < 200 MB RAM , but after server uptime in 3 days , i saw your server used > 800 MB RAM

I think I have a problem with MySQL ,

This is my my.conf

PHP Code:

 [mysqld]
    
datadir=/var/lib/mysql
    socket
=/var/lib/mysql/mysql.sock
    skip
-locking
    
#skip-networking
    
safe-show-database
    query_cache_limit
=1M
    query_cache_size
=32M ## 32MB for every 1GB of RAM
    
query_cache_type=1
    max_user_connections
=200
    max_connections
=500
    interactive_timeout
=10
    wait_timeout
=20
    connect_timeout
=20
    thread_cache_size
=128
    key_buffer
=64M ## 64MB for every 1GB of RAM
    
join_buffer=1M
    max_connect_errors
=20
    max_allowed_packet
=16M
    table_cache
=1024
    record_buffer
=1M
    sort_buffer_size
=1M ## 1MB for every 1GB of RAM
    
read_buffer_size=1M ## 1MB for every 1GB of RAM
    
read_rnd_buffer_size=1M  ## 1MB for every 1GB of RAM
    
thread_concurrency=## Number of CPUs x 2
    
myisam_sort_buffer_size=64M
    server
-id=1
    log_slow_queries
=/var/log/mysql-slow-queries.log
    long_query_time
=2
    collation
-server=latin1_general_ci
    old
-passwords

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

    
[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=32M
    sort_buffer
=32M
    read_buffer
=16M
    write_buffer
=16M

    
[myisamchk]
    
key_buffer=32M
    sort_buffer
=32M
    read_buffer
=16M
    write_buffer
=16M
    
[mysqlhotcopy]
    
interactive-timeout 

#top -c
PHP Code:

PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 1960 mysql     16   0  226m  37m 4344 S  0.0  2.5   0
:05.59 /usr/libexec/mysqld 

Please help me ! Thanks

Ben 6th November 2009 08:44

Well in my opinion a really cool tool that helps you with optimizing mysql is the mysql tuning primter script: http://www.day32.com/MySQL/ (first entry on the page).
It indicates which switches have a non optimal value and why. But it gives you at least (if not trusting the suggested values) a hint at which config switches you should take a look at, and read what the mysql doc (dev.mysql.com) says.

hth


All times are GMT +2. The time now is 04:04.

Powered by vBulletin® Version 3.8.7
Copyright ©2000 - 2014, vBulletin Solutions, Inc.