Page 1 of 5 123 ... LastLast
Results 1 to 20 of 98

Thread: HOWTO: Optimize MySQL 4.x

  1. #1
    Join Date
    May 2003
    Location
    /home/admin
    Posts
    298

    HOWTO: Optimize MySQL 4.x

    Tweak MySQL

    I've found that these configuration options posted by "aussie" on the Rackshack forums work well with MySQL 4.x on my machines. I suggest implementing them to alieviate the load MySQL may cause when you run big SQL sites on your server:

    Code:
    vi /etc/my.cnf [ENTER]
    [mysqld]
    #datadir=/var/lib/mysql
    #socket=/var/lib/mysql/mysql.sock
    skip-locking
    skip-innodb
    query_cache_limit=1M
    query_cache_size=32M
    query_cache_type=1
    max_connections=500
    interactive_timeout=100
    wait_timeout=100
    connect_timeout=10
    thread_cache_size=128
    key_buffer=16M
    join_buffer=1M
    max_allowed_packet=16M
    table_cache=1024
    record_buffer=1M
    sort_buffer_size=2M
    read_buffer_size=2M
    max_connect_errors=10
    # Try number of CPU's*2 for thread_concurrency
    thread_concurrency=2
    myisam_sort_buffer_size=64M
    log-bin
    server-id=1

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

    [myisamchk]
    key_buffer=64M
    sort_buffer=64M
    read_buffer=16M
    write_buffer=16M

    [mysqlhotcopy]
    interactive-timeout
    Code:
    /etc/rc.d/init.d/mysqld restart [ENTER]
    Matt Savona
    serveIO
    Creator of the DirectAdmin Enhanced Theme

  2. #2
    Join Date
    Aug 2003
    Posts
    29
    thanks, this helps. How can I enable certain other host maks to access the server? I don't want all TLDs to be able to access, just ones on server and 2 other TLDs

  3. #3
    Join Date
    May 2003
    Location
    /home/admin
    Posts
    298
    Directadmin can handle host access in the database area of the user control panel. You can also control this through phpMyAdmin under your MySQL root user.

    Hope this helps.
    Matt Savona
    serveIO
    Creator of the DirectAdmin Enhanced Theme

  4. #4
    Join Date
    Jun 2003
    Location
    Perth, Australia
    Posts
    280
    log into phpmyadmin with the username da_admin and your admin password (the original one)
    Mark Harris
    mharris@octane-design.biz

  5. #5
    Hello,

    DirectAdmin does have the mysql hosts feature.. Just click your database name, and the "Access Hosts" table should be at the bottom.

    John

  6. #6
    Join Date
    Oct 2003
    Posts
    392
    i'm sorry but i don't find "/etc/my.cnf" in my server, is that how to compatible with DA?

    do i just creat a new file and copy the content there?

    btw, i've nothing at /var/log/mysql.log

    is there anything wrong here at my server?
    Last edited by neorder; 04-11-2004 at 09:44 PM.

  7. #7
    Join Date
    Jul 2003
    Location
    /dev/null
    Posts
    607
    The my.cnf it not added during the default install. You can search the forums for more information about this but you can basically add what was posted as a new file called my.cnf.

  8. #8
    Join Date
    Jun 2003
    Location
    UK
    Posts
    2,326
    MySQL simply runs on defaults unless the my.cnf exists, there are actually 3 places it looks for the config with /etc/my.cnf just being the first... add them and it will override the default settings..

    Chris
    OptimumServers » Managed Dedicated Servers & General Systems Management » Coming Soon!
    ProWebUK - Quality Web Services
    DirectAdmin Server Checklist

  9. #9
    Join Date
    Oct 2003
    Posts
    392
    thanks, i will read more.

  10. #10
    Join Date
    Aug 2003
    Location
    portland
    Posts
    39
    Hello, I need some help:

    [root]# /etc/rc.d/init.d/mysqld restart
    Killing mysqld with pid 2812
    Wait for mysqld to exit\c
    .\c
    .\c
    .\c
    .\c
    .\c
    done
    [root]# /etc/rc.d/init.d/mysqld restart
    No mysqld pid file found. Looked for /var/lib/mysql/purple.nemesis.net.pid.
    [root]# Starting mysqld daemon with databases from /var/lib/mysql
    040423 18:18:11 mysqld ended

    [root]# service mysqld restart
    No mysqld pid file found. Looked for /var/lib/mysql/purple.nemesis.net.pid.
    [root]# Starting mysqld daemon with databases from /var/lib/mysql
    040423 18:40:31 mysqld ended

    any idea on how to fix it?
    Last edited by Anzix; 04-23-2004 at 08:42 PM.

  11. #11
    Join Date
    May 2003
    Location
    /home/admin
    Posts
    298
    Have you modified this my.cnf file at all? I think mysqld is looking for the pid file in one location that you have specified, but it is non-existant.
    Matt Savona
    serveIO
    Creator of the DirectAdmin Enhanced Theme

  12. #12
    Join Date
    Jul 2003
    Posts
    474
    i had a problem with mysql server going down, because google bots tried to login a lot, causing many many cookies etc

    we now blocked google from entering the forums, that solved it, but will this maybe prevent mysql going down?

  13. #13
    Join Date
    Jul 2003
    Posts
    474
    in what way is this cnf better then the default 1?

  14. #14
    Join Date
    Oct 2003
    Posts
    392
    Originally posted by sander815
    in what way is this cnf better then the default 1?
    good question, i shouldn't just copy blindly, i just read the original post from ev1, it looks for different server specs, the setting varies.

  15. #15
    Join Date
    Oct 2003
    Posts
    392
    they recommand

    suggest the following my.cnf changes for people with a single-processor server and 512MB RAM:

    thread_cache_size=50
    key_buffer=40M
    table_cache=384
    sort_buffer_size=768K
    read_buffer_size=512K
    read_rnd_buffer_size=512K
    thread_concurrency=2
    how are these values are calculated?

    anyway, i did this optimization, it was a WOW when i see the out come, i have one site used to have 0.2 page generate speed, now it's only 0.08!

    a small question is

    /etc/rc.d/init.d/mysqld restart [ENTER]
    doesn't look like work, i ran it, but it hangs...i don't know if it's restarted, i guess it did.

  16. #16
    Join Date
    Jul 2003
    Posts
    474
    to m it seems this config is best suited if you have 1.5 gb ram

    key_buffer_size = (record_buffer + sort_buffer)*max_connections
    = 1500

  17. #17
    Join Date
    Jul 2003
    Posts
    474
    on what thread is this onfo based upon? on ev1 servers

  18. #18
    Join Date
    Jun 2003
    Location
    UK
    Posts
    2,326
    Originally posted by sander815
    on what thread is this onfo based upon? on ev1 servers
    it doesn't matter who provided the server - mysql is simply - mysql and this will work on any DA server, from any provider. (providing it has mysql and uses the linux mysql setup - if it had DA it would )
    OptimumServers » Managed Dedicated Servers & General Systems Management » Coming Soon!
    ProWebUK - Quality Web Services
    DirectAdmin Server Checklist

  19. #19
    Join Date
    Jul 2003
    Posts
    474
    no, i mean, i wanted to know the link to the thread on rack shack forums where this info comes from, so i can check what peoples replys are there

  20. #20
    Join Date
    Dec 2003
    Location
    Belgium
    Posts
    168
    any idea of the values to change for a dual processor ?

Page 1 of 5 123 ... LastLast

Similar Threads

  1. Could you help me Optimize MySQL
    By xuancong6000 in forum CentOS 6
    Replies: 2
    Last Post: 12-24-2012, 09:24 AM
  2. Who Can Help Me Optimize MySql ?
    By hamidi in forum General Technical Discussion & Troubleshooting
    Replies: 2
    Last Post: 09-10-2011, 10:45 AM
  3. Optimize MySQL 5.x
    By avx in forum System-Level Technical Discussion
    Replies: 1
    Last Post: 08-31-2007, 02:12 AM
  4. mysql wont start, after following mysql optimize in this forum
    By lkbryant in forum General Technical Discussion & Troubleshooting
    Replies: 3
    Last Post: 01-29-2006, 06:39 PM
  5. Optimize MySQL - Why ?
    By walo in forum General Technical Discussion & Troubleshooting
    Replies: 2
    Last Post: 03-21-2005, 10:52 PM

Posting Permissions

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