Results 1 to 18 of 18
  1. #1
    Join Date
    Oct 2007
    Posts
    95

    problem in mysql make high Query

    hello ,,

    i have problem in my server i see load in it increase

    when see that load i goto see process mysql i see that

    | 430732 | root | localhost | mysql | Query | 676 | Opening tables | SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.ta |

    all Query from mysql not from any database of users in server all database users work normlly without any problem

    i hope any one help me in this problem and solve it

    Regards

  2. #2
    Join Date
    May 2011
    Location
    N/A
    Posts
    116
    Did you optimize your my.cnf yet? What is the configuration?

    And you are running vps or, dedicated which one? And whats about TOP and W process?

  3. #3
    Join Date
    Oct 2007
    Posts
    95
    yes optimize my.cnf

    this the optimize :
    ====================
    [mysqld]
    local-infile=0
    datadir=/var/lib/mysql
    skip-locking
    skip-networking
    safe-show-database
    max_connect_errors=20
    max_connections=600
    max_user_connections=200
    max_allowed_packet=16M
    max_error_count=64
    max_sort_length=1024
    interactive_timeout=20
    wait_timeout=60
    connect_timeout=20
    query_cache_limit=1M
    query_cache_size=384M
    query_cache_type=1M
    table_cache=2048M
    table_lock_wait_timeout=15
    key_buffer_size=1024M
    key_buffer=1536M
    join_buffer=1M
    record_buffer=1M
    sort_buffer_size=12M
    read_buffer_size=12M
    read_rnd_buffer_size=12M
    thread_cache_size=512M
    thread_concurrency=4
    myisam_sort_buffer_size=64M

    # Log slow queries
    long_query_time=50
    log_slow_queries=/var/log/mysqld.slow.log

    [safe_mysqld]
    err-log=/var/log/mysqld.log
    pid-file=/var/lib/mysql/mysql.pid
    open_files_limit=8192

    [mysqldump]
    quick
    max_allowed_packet=2048M

    [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
    local-infile=0




    my server dedicated and this specs of it :
    =================================================
    2 x Xeon Quad Core 2.13GHz Nehalem
    RAM 12GB DDR3
    uplink : 100Mbp
    Centos 5 64Bit

  4. #4
    Join Date
    Oct 2007
    Posts
    95
    and this top :
    ================
    PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
    16546 mysql 15 0 3360m 1.9g 4796 S 38.9 16.3 866:47.56 mysqld

  5. #5
    Join Date
    Oct 2007
    Posts
    95
    hello,,

    this problem make every day in that time and one can help me ?

  6. #6
    Maybe now it is right time to reinstall your database?

  7. #7
    Join Date
    Oct 2004
    Location
    Kerala, India
    Posts
    4,750
    Enable profiling and run the slow queries manually to get a clue.
    David | www.cliffsupport.com
    Affordable Server Management Solutions sales AT cliffsupport DOT com
    CliffWebManager | Access WHM from iPhone and Android

  8. #8
    Join Date
    Oct 2007
    Posts
    95
    Quote Originally Posted by david510 View Post
    Enable profiling and run the slow queries manually to get a clue.
    how can i make that ?

  9. #9
    Enable slow query logging with a reasonable time limit based on your application, find and optimize the queries which are overloading the system.

    [mysqld]
    set-variable=long_query_time=20 # in seconds
    log-slow-queries=/var/log/mysql/log-slow-queries.log

    Also make sure that you have indexing enabled for tables.

    ./arun

  10. #10
    Join Date
    Oct 2007
    Posts
    95
    it's already enabled and this report in logs :

    /usr/sbin/mysqld, Version: 5.0.92-community-log (MySQL Community Edition (GPL)). started with:
    use mysql;
    use mysql;
    SELECT ENGINE,SUM(DATA_LENGTH),COUNT(ENGINE) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND ENGINE IS NOT NULL GROUP BY ENGINE ORDER BY ENGINE ASC;
    use mysql;
    use mysql;
    use mysql;
    use mysql;
    /usr/sbin/mysqld, Version: 5.0.92-community-log (MySQL Community Edition (GPL)). started with:
    /usr/sbin/mysqld, Version: 5.0.92-community-log (MySQL Community Edition (GPL)). started with:
    /usr/sbin/mysqld, Version: 5.0.92-community-log (MySQL Community Edition (GPL)). started with:
    use mysql;
    use mysql;
    use mysql;
    use mysql;
    use mysql;
    use mysql;
    use mysql;
    use mysql;
    use mysql;
    use mysql;
    use mysql;
    use mysql;
    use mysql;
    use mysql;
    use mysql;
    use mysql;
    use mysql;
    /usr/sbin/mysqld, Version: 5.0.92-community-log (MySQL Community Edition (GPL)). started with:
    use mysql;
    use mysql;
    use mysql;
    use mysql;
    use mysql;
    use mysql;
    use mysql;
    use mysql;
    use mysql;
    /usr/sbin/mysqld, Version: 5.0.92-community-log (MySQL Community Edition (GPL)). started with:
    use mysql;
    /usr/sbin/mysqld, Version: 5.0.92-community-log (MySQL Community Edition (GPL)). started with:

  11. #11
    Did you see any of your database queries (not the information_schema) in slow queries log file?

    <<signatures to be set up in your profile>>
    Last edited by bear; 10-26-2011 at 08:08 AM.

  12. #12
    Join Date
    Oct 2007
    Posts
    95
    here the result :
    =================

    SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
    SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
    SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
    SELECT ENGINE,SUM(DATA_LENGTH),COUNT(ENGINE) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND ENGINE IS NOT NULL GROUP BY ENGINE ORDER BY ENGINE ASC;
    SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
    SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
    SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
    SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
    SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
    SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
    SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
    SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
    SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
    SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
    SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
    SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
    SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
    SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
    SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
    SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
    SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
    SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
    SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
    SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
    SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
    SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
    SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
    SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
    SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
    SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
    SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
    SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
    SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
    SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
    SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
    SELECT ENGINE,SUM(DATA_LENGTH),COUNT(ENGINE) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN
    ('information_schema','mysql') AND ENGINE IS NOT NULL GROUP BY ENGINE ORDER BY ENGINE ASC;
    SELECT COUNT(TABLE_NAME) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN
    ('information_schema','mysql') AND Data_free > 0 AND NOT ENGINE='MEMORY';
    SELECT IFNULL(SUM(INDEX_LENGTH),0) FROM information_schema.TABLES WHERE TABLE_SCHEMA
    NOT IN ('information_schema') AND ENGINE = 'MyISAM';
    /*!50000 SELECT IFNULL(SUM(INDEX_LENGTH),0) from information_schema.TABLES where ENGINE='InnoDB' */;
    /*!50000 SELECT IFNULL(SUM(DATA_LENGTH),0) from information_schema.TABLES where ENGINE='InnoDB' */;
    /*!50000 SELECT IFNULL(SUM(INDEX_LENGTH),0) from information_schema.TABLES where ENGINE='InnoDB' */;
    /*!50000 SELECT IFNULL(SUM(DATA_LENGTH),0) from information_schema.TABLES where ENGINE='InnoDB' */;
    /*!50000 SELECT IFNULL(SUM(INDEX_LENGTH),0) from information_schema.TABLES where ENGINE='MyISAM' */;
    /*!50000 SELECT IFNULL(SUM(INDEX_LENGTH),0) from information_schema.TABLES where ENGINE='InnoDB' */;
    /*!50000 SELECT IFNULL(SUM(DATA_LENGTH),0) from information_schema.TABLES where ENGINE='InnoDB' */;
    /*!50000 SELECT IFNULL(SUM(INDEX_LENGTH),0) from information_schema.TABLES where ENGINE='MyISAM' */;
    /*!50000 SELECT IFNULL(SUM(INDEX_LENGTH),0) from information_schema.TABLES where ENGINE='InnoDB' */;
    SELECT ENGINE,SUM(DATA_LENGTH),COUNT(ENGINE) FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN
    ('information_schema','mysql') AND ENGINE IS NOT NULL GROUP BY ENGINE ORDER BY ENGINE ASC;
    SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;
    SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA;

  13. #13
    Join Date
    Oct 2004
    Location
    Kerala, India
    Posts
    4,750
    Quote Originally Posted by police_3001 View Post
    how can i make that ?
    From the sql promt issue this command.

    Code:
    set profiling=1 ;
    Then run the query that causes the issue.

    Then run the following queries to get details.

    Code:
    show profile cpu for query 1;
    show profile all for query 1;
    David | www.cliffsupport.com
    Affordable Server Management Solutions sales AT cliffsupport DOT com
    CliffWebManager | Access WHM from iPhone and Android

  14. #14
    Join Date
    Oct 2007
    Posts
    95
    when add this command

    set profiling=1 ;

    it's result :

    ERROR 1193 (HY000): Unknown system variable 'profiling'

  15. #15
    Is it a CPanel server ? if so try uncheck "Include databases in disk usage calculations"

    <<signatures to be set up in your profile>>
    Last edited by bear; 10-26-2011 at 08:07 AM.

  16. #16
    Join Date
    Oct 2007
    Posts
    95
    yes i make check for Include database in disk usage calculations but why make uncheck for it

    This the question

  17. #17
    Join Date
    Jan 2005
    Location
    Scotland, UK
    Posts
    2,549
    Quote Originally Posted by police_3001 View Post
    yes i make check for Include database in disk usage calculations but why make uncheck for it

    This the question
    Because it's that which is running the above query which is causing your issues.


    Quote Originally Posted by maniondagger View Post
    Maybe now it is right time to reinstall your database?
    What??? Why???
    Server Management - AdminGeekZ.com
    Infrastructure Management, Web Application Performance, mySQL DBA. System Automation.
    WordPress/Magento Performance, Apache to Nginx Conversion, Varnish Implimentation, DDoS Protection, Custom Nginx Modules
    Check our wordpress varnish plugin. Contact us for quote: [email protected]

  18. #18
    Join Date
    Oct 2007
    Posts
    95
    thanks very much arunns and all who replay in my post

    the problem solved

    when remove check on Include databases in disk usage calculations

    Best regards for all

Similar Threads

  1. Please help me make a MySQL query!
    By AlexBlundell in forum Programming Discussion
    Replies: 1
    Last Post: 08-11-2010, 07:21 AM
  2. MySQL query problem
    By makoReactor in forum Programming Discussion
    Replies: 8
    Last Post: 12-15-2006, 07:42 PM
  3. problem with mysql query
    By mikey1090 in forum Programming Discussion
    Replies: 1
    Last Post: 07-04-2006, 05:30 AM
  4. MySQL Query Problem
    By arkin in forum Programming Discussion
    Replies: 10
    Last Post: 03-14-2006, 06:05 PM
  5. MySQL sub query problem
    By aussie_dude in forum Programming Discussion
    Replies: 3
    Last Post: 10-21-2005, 06:55 AM

Posting Permissions

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