Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2005
    Location
    Earth
    Posts
    326

    mysql slamming server

    mysql constantly at the top of top command using up 100% cpu even after optimize mysql.


    currently running dual xeon 3.2ghz 2gb ram 80gb scsi hdd rhel7 ensim pro x mysql 4.1 php 4.3 upd to date php


    my.cnf

    [mysqld]
    socket=/home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/mysql.sock
    datadir=/var/lib/mysql
    # Default to using old password format for compatibility with mysql 3.x
    # clients (those using the mysqlclient10 compatibility package).
    old_passwords=1
    skip-locking
    skip-innodb
    query_cache_limit=8M
    query_cache_size=256M
    query_cache_type=1
    max_connections=800
    max_user_connections=50
    interactive_timeout=20
    wait_timeout=20
    connect_timeout=6
    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=4
    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
    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

    [client]
    socket=/home/virtual/FILESYSTEMTEMPLATE/.mysqlsock/mysql.sock
    No, i am not the guy from the simpson's, what's his name again?

  2. #2
    Join Date
    Feb 2004
    Location
    Atlanta, GA
    Posts
    5,627
    have you tried implementing a slow_log and looking to see what MySQL is doing?

    How about queries without indecies?

    bad queries will drive cpu regardless of how well you optimize MySQL. Look at optimizing your database, datasets, and query design.

  3. #3
    Join Date
    Sep 2005
    Location
    Southern California
    Posts
    179
    Try turning on slow query logging, and installing mytop. If you don't want to install mytop, you can open a mysql cmd line and type "show full processlist" to see what queries are being run.

    If you look at the slow query log, you should be able to see what queries are taking forever to finish. If there are none, then you probably have someone on your server receiving a large number of hits, or running inefficient queries on inefficient tables.

  4. #4
    Join Date
    Aug 2005
    Location
    Earth
    Posts
    326
    How would i turn on the slow query logging?
    No, i am not the guy from the simpson's, what's his name again?

  5. #5
    Join Date
    Sep 2006
    Location
    Michigan
    Posts
    64

    MySQL slow queries

    To activate the query log add:
    log-slow-queries = [slow_query_filename]
    in your configuration my.cnf file under the [mysqld] section.

    The slow query log logs all queries that take longer than long_query_time, which is usually 10 seconds by default.

    You may also want to refer to: http://dev.mysql.com/doc/refman/4.1/...query-log.html

    You may also want to check out the following, from the reference page:
    log-queries-not-using-indexes



    MNX.io - 100% SSD Cloud Hosting - Linux, FreeBSD, & SmartOS.

  6. #6
    Join Date
    Nov 2006
    Posts
    35
    You can often get a good idea by checking the processlist.

    Run this query
    SHOW PROCESSLIST;

    You'll see a bunch of locked processes ...but one of the queries is often the culprit...assuming mysql is the problem.

  7. #7
    Join Date
    Jul 2004
    Location
    Mount Vernon Ohio
    Posts
    35
    Something Steven turn us on to.
    http://ftp.linux360.ro/utilitare/mysql/tuning-primer.sh

    SSH in and run that.
    Boosted my servers performance.

    Anthony
    CRI Hosting - There's Now Another Choice.
    Shared Hosting :: Reseller Hosting :: Managed Dedicated
    cPanel/WHM :: Fantastico :: Private Name Servers
    http://www.crihosting.com

  8. #8
    Join Date
    Mar 2003
    Location
    California USA
    Posts
    13,290
    I agree with atorreso that is a great script to use.
    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
    Join Date
    Dec 2004
    Location
    New York, NY
    Posts
    10,574
    Very cool script!

  10. #10
    Join Date
    Dec 2002
    Location
    USA
    Posts
    62
    thanks for the script

Posting Permissions

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