Optimizing MySql Server
I have a 2gb ram memory, and somehow mysqld.bin is utilizing 20%+ memory when nobody is accessing the server. I just installed everything fresh. not sure why it is taking so much memory. I am utilizing InnoDb MySQL 5.6.23.
When the database grows, does the mysqld.bin memory usage grow?
I tried tuning-primer.sh (./tuning-primer.sh > result.txt), but i have a couple of errors:
I downloaded sqltuner, and ran it by: sudo perl mysqltuner.pl > result.txt. How does the recommendation below helps me to lower the memory usage? Result.txt
tput: Unknown terminal "linux"
tput: Unknown terminal "linux"
tput: Unknown terminal "linux"
tput: Unknown terminal "linux"
tput: Unknown terminal "linux"
tput: Unknown terminal "linux"
would you like to provide a diff. socket? : n
do you have your login handy? y
user: xxxx
password: xxxxxxxx
tput: Unknown terminal "linux"
woudl you like me to crate a ~/.mycnf file for you? n
tput: Unknown terminal "linux"
tput: Unknown terminal "linux"
user@linux:~$I downloaded sqltuner, and ran it by: sudo perl mysqltuner.pl > result.txt. How does the recommendation below helps me to lower the memory usage? Result.txt
>> MySQLTuner 1.5.0 - Major Hayden
>> Bug reports, feature requests, and downloads at http://mysqltuner.com/
>> Run with '--help' for additional options and output filtering
[[0;32mOK[0m] Currently running supported MySQL version 5.6.23
[[0;32mOK[0m] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[[0;34m--[0m] Status: [0;32m+ARCHIVE [0m[0;32m+BLACKHOLE [0m[0;32m+CSV [0m[0;31m-FEDERATED [0m[0;32m+InnoDB [0m[0;32m+MRG_MYISAM [0m
[[0;34m--[0m] Data in MyISAM tables: 118K (Tables: 1)
[[0;34m--[0m] Data in InnoDB tables: 6M (Tables: 49)
[[0;31m!![0m] Total fragmented tables: 2
-------- Security Recommendations -------------------------------------------
[[0;31m!![0m] User '@linux' is an anonymous account.
[[0;31m!![0m] User '@localhost' is an anonymous account.
[[0;31m!![0m] User '@linux' has no password set.
[[0;31m!![0m] User '@localhost' has no password set.
[[0;31m!![0m] User '@linux' has user name as password.
[[0;31m!![0m] User '@localhost' has user name as password.
[[0;34m--[0m] There is 605 basic passwords in the list.
-------- Performance Metrics -------------------------------------------------
[[0;34m--[0m] Up for: 3h 48m 17s (12K q [0.924 qps], 3K conn, TX: 4M, RX: 2M)
[[0;34m--[0m] Reads / Writes: 98% / 2%
[[0;34m--[0m] Binary logging is disabled
[[0;34m--[0m] Total buffers: 169.0M global + 1.1M per thread (151 max threads)
[[0;32mOK[0m] Maximum reached memory usage: 175.8M (8.78% of installed RAM)
[[0;32mOK[0m] Maximum possible memory usage: 338.9M (16.93% of installed RAM)
[[0;32mOK[0m] Slow queries: 0% (0/12K)
[[0;32mOK[0m] Highest usage of available connections: 3% (6/151)
[[0;32mOK[0m] Aborted connections: 0.96% (37/3835)
[[0;31m!![0m] Key buffer used: 18.2% (1M used / 8M cache)
[[0;32mOK[0m] Key buffer size / total MyISAM indexes: 8.0M/161.0K
[[0;32mOK[0m] Read Key buffer hit rate: 96.0% (25 cached / 1 reads)
[[0;31m!![0m] Query cache is disabled
[[0;32mOK[0m] Sorts requiring temporary tables: 0% (0 temp sorts / 12 sorts)
[[0;32mOK[0m] Temporary tables created on disk: 0% (0 on disk / 83 total)
[[0;32mOK[0m] Thread cache hit rate: 99% (6 created / 3K connections)
[[0;32mOK[0m] Table cache hit rate: 94% (131 open / 138 opened)
[[0;32mOK[0m] Open file limit used: 0% (48/5K)
[[0;32mOK[0m] Table locks acquired immediately: 100% (4K immediate / 4K locks)
-------- InnoDB Metrics -----------------------------------------------------
[[0;34m--[0m] InnoDB is enabled.
[[0;32mOK[0m] InnoDB buffer pool / data size: 128.0M/6.8M
[[0;31m!![0m] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1).
[[0;31m!![0m] InnoDB Used buffer: 8.02% (657 used/ 8192 total)
[[0;32mOK[0m] InnoDB Read buffer efficiency: 96.68% (18522 hits/ 19159 total)
[[0;31m!![0m] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[[0;32mOK[0m] InnoDB log waits: 0.00% (0 waits / 158 writes)
-------- Replication Metrics -------------------------------------------------
[[0;34m--[0m] No replication slave(s) for this server.
[[0;34m--[0m] This is a standalone server..
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Remove Anonymous User account - there is 2 Anonymous account.
Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
MySQL started within last 24 hours - recommendations may be inaccurate
Variables to adjust:
query_cache_type (=1)
innodb_buffer_pool_instances (=1)
Please sign in to leave a comment.
Comments
1 comment