Results 1 to 10 of 10

Thread: MySQL high load

  1. #1
    Join Date
    Sep 2007
    Posts
    195

    MySQL high load

    Hello,

    I have the following my.cnf in my server:

    ===
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    skip-locking
    query_cache_limit=1M
    query_cache_size=32M
    query_cache_type=1
    max_user_connections=25
    max_connections=500
    interactive_timeout=10
    wait_timeout=10
    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
    thread_concurrency=8
    myisam_sort_buffer_size=64M
    server-id=1
    set-variable = max_connections=500
    safe-show-database
    max_tmp_tables=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

    [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
    ===

    I thought that MySQL would be optimized with this my.cnf above.

    But often I see MySQL on top load process:

    ===
    4027 mysql 15 0 2397m 402m 4396 S 100 19.9 2:44.20 mysqld
    ===


    What I can to do to have MySQL better in my server???

    Thanks

  2. #2
    Check the running mysql processes when it hits to 100%:

    watch -n 0.5 mysqladmin proc

    You will get the username, database name and queries details from the above command.
    || Web Hosting Blog - Web Hosting security & latest web hosting industry Announcements
    || Web Hosting Discussion - A Web Hosting community

  3. #3
    Join Date
    Sep 2007
    Posts
    195
    Quote Originally Posted by Kailash12 View Post
    Check the running mysql processes when it hits to 100%:

    watch -n 0.5 mysqladmin proc

    You will get the username, database name and queries details from the above command.

    Nice command Kailash, thank you.

  4. #4

  5. #5
    Join Date
    Sep 2007
    Posts
    195
    Hello, Guys,

    After I change my.cnf with configuration in my first post, some clients start to get the following error:

    ===
    MySQL server has gone away.
    ===

    What I can do to have not this error message???

    Thanks

  6. #6
    Join Date
    Aug 2000
    Posts
    2,750
    Make sure the datadir is actually where your MySQL install is.

  7. #7
    Join Date
    Sep 2007
    Posts
    195
    Quote Originally Posted by GeekSRV View Post
    Make sure the datadir is actually where your MySQL install is.

    Hi,

    Yes, seens that is corretct:

    ===
    -bash-3.2# pwd
    /var/lib/mysql
    -bash-3.2#
    ===

    The error "MySQL server has gone away" dont occur all time, only in specific queries.

  8. #8
    Join Date
    Aug 2000
    Posts
    2,750
    have you enabled slow query logging? I'd suggest upping the values you a bit - what's your system specifications?

  9. #9
    Check the mysql processes causing load on the server using the command given below,
    # mysqladmin proc
    Support Facility | 24/7 web hosting technical support services
    Technical support | Server management | Data migration

    Technical Articles

  10. #10
    Join Date
    Sep 2007
    Posts
    195
    Quote Originally Posted by inspiron View Post
    Check the mysql processes causing load on the server using the command given below,
    # mysqladmin proc
    Hi,

    At now have not any load issue more. The problem now is error "MySQL server has gone away".

    I think that this error is referring to time out and maybe this 3 my.cnf settings are responsible:

    ====
    interactive_timeout=10
    wait_timeout=10
    connect_timeout=10
    ===

Similar Threads

  1. High mysql load ...
    By jack12344 in forum Hosting Security and Technology
    Replies: 2
    Last Post: 10-12-2008, 10:15 AM
  2. MySql die with high load
    By elcidvn in forum Hosting Security and Technology
    Replies: 9
    Last Post: 10-17-2007, 11:43 PM
  3. High MySQL load
    By alphix in forum Hosting Security and Technology
    Replies: 7
    Last Post: 02-16-2007, 01:44 PM
  4. MySQL high load
    By Im_Goodspeed in forum Hosting Security and Technology
    Replies: 7
    Last Post: 06-20-2005, 06:14 PM
  5. mysql and high load.
    By solyalex in forum Hosting Security and Technology
    Replies: 6
    Last Post: 02-18-2004, 10:56 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
  •