mysql usage users
Hi there,
How do I find out which databases or users are using most ram or connections?
Cheers
-
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 mysqladmin proc
0 -
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. 0 -
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? 0 -
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 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 output0 -
[quote="24x7server, post: 1481231">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 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
My current mysqltuner.pl without the above code is: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 >> 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:[mysqld] max_connections=500 innodb_file_per_table=1 default-storage-engine=MyISAM open_files_limit=2098
0 -
Hello :) I have moved this over to the "Optimization" forum. You will likely get more input for MySQL configuration values here. Thank you. 0 -
Thank you very very much. I made changes, and I will be waiting few days, and see how it is working. 0
Please sign in to leave a comment.
Comments
7 comments