Please whitelist cPanel in your adblocker so that you’re able to see our version release promotions, thanks!

The Community Forums

Interact with an entire community of cPanel & WHM users!

mysql usage users

Discussion in 'Workarounds and Optimization' started by charliekapper, Oct 11, 2013.

  1. charliekapper

    charliekapper Member

    Joined:
    Aug 18, 2013
    Messages:
    18
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Hi there,

    How do I find out which databases or users are using most ram or connections?

    Cheers
     
    #1 charliekapper, Oct 11, 2013
  2. 24x7server

    24x7server Well-Known Member

    Joined:
    Apr 17, 2013
    Messages:
    1,872
    Likes Received:
    89
    Trophy Points:
    78
    Location:
    India
    cPanel Access Level:
    Root Administrator
    Hello,

    You can monitor your MySQL server resources on your server through following command, You will see the MySQL user and MySQL DB which is having the lot of MySQL process on your server

    Code:
    mysqladmin proc
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #2 24x7server, Oct 12, 2013
  3. HostingH

    HostingH Well-Known Member

    Joined:
    Jan 13, 2008
    Messages:
    125
    Likes Received:
    17
    Trophy Points:
    68
    cPanel Access Level:
    Root Administrator
    Hi,

    Try to use Mytop to monitor mysql process on the server. Mytop is a very useful tool to see current running queries on the server as well as which user is executing them.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #3 HostingH, Oct 12, 2013
  4. charliekapper

    charliekapper Member

    Joined:
    Aug 18, 2013
    Messages:
    18
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Thanks for the replies.

    This is the result of "mysqladmin proc":


    | Id | User | Host | db | Command | Time | State | Info |
    +---------+--------------+-----------------------------------+----------------------+----------------+-------+--------------------+------------------+
    | 63416 | lemon1 | 1.1.1.1:85773 | dog | Sleep | 1145 | | |
    | 8715207 | eximstats | localhost | eximstats | Sleep | 1 | | |
    | 6718398 | harley1 | 1.1.1.1:85773 | frank1 | Sleep | 10543 | | |
    | 9182611 | harley1 | 1.1.1.1:85773 | frank1 | Sleep | 5697 | | |
    | 1637254 | harley1 | 1.1.1.1:85773 | frank1 | Sleep | 16 | | |
    | 7364816 | harley1 | 1.1.1.1:85773 | frank1 | Sleep | 7 | | |
    | 9173551 | harley1 | 1.1.1.1:85773 | frank1 | Sleep | 19 | | |
    | 9162733 | harley1 | 1.1.1.1:85773 | frank1 | Sleep | 9 | | |
    | 7162534 | harley1 | 1.1.1.1:85773 | frank1 | Sleep | 11 | | |
    | 1292716 | harley1 | 1.1.1.1:85773 | frank1 | Sleep | 10 | | |
    | 7162549 | harley1 | 1.1.1.1:85773 | frank1 | Sleep | 7 | | |
    | 9713546 | lemon1 | 1.1.1.1:85773 | dog | Sleep | 20 | | |
    | 8374625 | salt | 1.1.1.1:85773 | lenny | Sleep | 6 | | |
    | 1827365 | greg1 | 1.1.1.1:85773 | froy | Sleep | 363 | | |
    | 7463728 | jemma1 | 1.1.1.1:85773 | harry1 | Sleep | 4335 | | |
    | 8273847 | jemma1 | 1.1.1.1:85773 | harry1 | Sleep | 3 | | |
    | 7584938 | harley1 | 1.1.1.1:85773 | frank1 | Sleep | 1241 | | |
    | 4747830 | nicechap1 | 1.1.1.1:85773 | roan | Sleep | 22911 | | |
    | 5983767 | harley1 | 1.1.1.1:85773 | frank1 | Sleep | 15899 | | |
    | 4734837 | kenna1 | 1.1.1.1:85773 | young | Sleep | 74 | | |
    | 8754239 | leechprotect | localhost | leechprotect | Sleep | 4436 | | |
    | 9854873 | harley1 | 1.1.1.1:85773 | frank1 | Sleep | 540 | | |
    | 5475475 | DELAYED | localhost | eximstats | Delayed insert | 1 | Waiting for INSERT | |
    | 8798983 | lemon2 | 1.1.1.1:85773 | cat | Sleep | 1 | | |
    | 3497346 | lemon2 | 1.1.1.1:85773 | cat | Sleep | 0 | | |
    | 3497987 | root | localhost | | Query | 0 | | show processlist |
    +---------+--------------+-----------------------------------+------------

    Could someone help make sense out of this.

    Would you recommend having a separate mysql server away from my webserver just for some of the databases?
     
    #4 charliekapper, Oct 12, 2013
  5. 24x7server

    24x7server Well-Known Member

    Joined:
    Apr 17, 2013
    Messages:
    1,872
    Likes Received:
    89
    Trophy Points:
    78
    Location:
    India
    cPanel Access Level:
    Root Administrator
    Hello,

    At this stage I will suggest you please try to add the following setting in your my.cnf file and monitor your MySQL process

    Code:
    interactive_timeout=10
wait_timeout=20
connect_timeout=20
query_cache_limit=1M
query_cache_size=32M
thread_cache_size=128
key_buffer_size = 190M
table_open_cache = 64

    Also for the MySQL optimization you can run " mysqltuner.pl " on your server and check the mysqltuner output
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #5 24x7server, Oct 13, 2013
  6. charliekapper

    charliekapper Member

    Joined:
    Aug 18, 2013
    Messages:
    18
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    My current mysqltuner.pl without the above code is:
    Code:
    charlie@webvps [~]# /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl
Name "XML::Simple::PREFERRED_PARSER" used only once: possible typo at /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl line 148.

 >>  MySQLTuner 1.2.0_1 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.32-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 112M (Tables: 269)
[--] Data in InnoDB tables: 59M (Tables: 66)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 21

-------- Performance Metrics -------------------------------------------------
[--] Up for: 5d 3h 5m 2s (34M q [77.284 qps], 2M conn, TX: 22B, RX: 3B)
[--] Reads / Writes: 85% / 15%
[--] Total buffers: 168.0M global + 2.8M per thread (500 max threads)
[!!] Maximum possible memory usage: 1.5G (150% of installed RAM)
[OK] Slow queries: 0% (0/34M)
[OK] Highest usage of available connections: 7% (38/500)
[OK] Key buffer size / total MyISAM indexes: 8.0M/171.8M
[OK] Key buffer hit rate: 99.9% (23M cached / 28K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 55K sorts)
[!!] Temporary tables created on disk: 26% (1M on disk / 7M total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 79% (400 open / 502 opened)
[OK] Open file limit used: 24% (610/2K)
[OK] Table locks acquired immediately: 99% (6M immediate / 6M locks)
[OK] InnoDB data size / buffer pool: 59.5M/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce your overall MySQL memory footprint for system stability
    Enable the slow query log to troubleshoot bad queries
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Set thread_cache_size to 4 as a starting value
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    query_cache_size (>= 8M)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    Current my.cnf:
    Code:
    [mysqld]
max_connections=500
innodb_file_per_table=1
default-storage-engine=MyISAM
open_files_limit=2098
     
    #6 charliekapper, Oct 13, 2013
  7. cPanelMichael

    cPanelMichael Technical Support Community Manager
    Staff Member

    Joined:
    Apr 11, 2011
    Messages:
    44,802
    Likes Received:
    1,895
    Trophy Points:
    363
    cPanel Access Level:
    Root Administrator
    Twitter:
    Hello :)

    I have moved this over to the "Optimization" forum. You will likely get more input for MySQL configuration values here.

    Thank you.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
    #7 cPanelMichael, Oct 15, 2013
  8. bbbrk

    bbbrk Registered

    Joined:
    Oct 11, 2013
    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    1
    cPanel Access Level:
    Root Administrator
    Thank you very very much.
    I made changes, and I will be waiting few days, and see how it is working.
     
    #8 bbbrk, Oct 15, 2013
(You must log in or sign up to post here.)
Loading...
Similar Threads - mysql usage users
  1. Maning

    New Thread MYSQL high cpu usage issue

    Maning, Oct 13, 2018 at 12:16 PM, in forum: Workarounds and Optimization
    Replies:
    0
    Views:
    48
    Maning
    Oct 13, 2018 at 12:16 PM
  2. Samet Chan

    MySQL Disk Usage Quota issue

    Samet Chan, Aug 21, 2018, in forum: General Discussion
    Replies:
    8
    Views:
    123
    cPanelMichael
    Aug 24, 2018
  3. Clixer

    Excessive resource usage: mysql

    Clixer, May 14, 2018, in forum: Database Discussion
    Replies:
    8
    Views:
    539
    cPanelLauren
    May 17, 2018
  4. baowesome

    MySQL Very High CPU Usage issue

    baowesome, Apr 2, 2018, in forum: Workarounds and Optimization
    Replies:
    1
    Views:
    717
    cPanelMichael
    Apr 2, 2018
  5. chetanmadaan

    Mysql usage by DB/Account.

    chetanmadaan, Oct 16, 2017, in forum: Database Discussion
    Replies:
    1
    Views:
    490
    cPWilliamL
    Oct 16, 2017

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Accept Learn More...
    Dismiss Notice