Hello
The database size on my shared server is growing quite large. Right now its size is 3649 Mbs after 19 days uptime. However maximum possible memory usage is at 1,8 Gbs according to mysql tuner. I guess that something in my config causes this. Can you please take a look?
The database size on my shared server is growing quite large. Right now its size is 3649 Mbs after 19 days uptime. However maximum possible memory usage is at 1,8 Gbs according to mysql tuner. I guess that something in my config causes this. Can you please take a look?
Code:
# /usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl
>> MySQLTuner 1.2.0_1 - Major Hayden <[email protected]>
>> 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.28-cll
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 2G (Tables: 13464)
[--] Data in InnoDB tables: 7M (Tables: 243)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 58)
[!!] Total fragmented tables: 248
-------- Performance Metrics -------------------------------------------------
[--] Up for: 18d 23h 26m 29s (651M q [397.507 qps], 3M conn, TX: 11801B, RX: 1419B)
[--] Reads / Writes: 94% / 6%
[--] Total buffers: 1.3G global + 2.9M per thread (200 max threads)
[OK] Maximum possible memory usage: 1.8G (11% of installed RAM)
[OK] Slow queries: 0% (48K/651M)
[OK] Highest usage of available connections: 26% (52/200)
[OK] Key buffer size / total MyISAM indexes: 1.0G/480.0M
[OK] Key buffer hit rate: 100.0% (45B cached / 458K reads)
[OK] Query cache efficiency: 72.3% (448M cached / 620M selects)
[!!] Query cache prunes per day: 3347568
[OK] Sorts requiring temporary tables: 0% (47K temp sorts / 12M sorts)
[!!] Joins performed without indexes: 1184561
[!!] Temporary tables created on disk: 26% (4M on disk / 15M total)
[OK] Thread cache hit rate: 99% (55 created / 3M connections)
[!!] Table cache hit rate: 0% (26K open / 71M opened)
[OK] Open file limit used: 15% (39K/262K)
[OK] Table locks acquired immediately: 99% (258M immediate / 260M locks)
[OK] InnoDB data size / buffer pool: 7.6M/8.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Adjust your join queries to always utilize indexes
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (> 128M)
join_buffer_size (> 256.0K, or always use indexes with joins)
tmp_table_size (> 128M)
max_heap_table_size (> 128M)
table_cache (> 65536)
Code:
[mysql]
# CLIENT #
port = 3306
socket = /var/lib/mysql/mysql.sock
[mysqld]
# GENERAL #
user = mysql
default_storage_engine = MyISAM
socket = /var/lib/mysql/mysql.sock
pid_file = /var/lib/mysql/mysql.pid
# MyISAM #
key_buffer_size = 1G
myisam_recover = FORCE,BACKUP
# SAFETY #
max_allowed_packet = 16M
max_connect_errors = 1000000
local-infile = 0
# DATA STORAGE #
datadir = /var/lib/mysql/
# BINARY LOGGING #
log_bin = /var/lib/mysql/mysql-bin
expire_logs_days = 1
sync_binlog = 1
# CACHES AND LIMITS #
tmp_table_size = 128M
max_heap_table_size = 128M
query_cache_type = 1
query_cache_size = 128M
query_cache_limit = 4M
query_cache_min_res_unit = 2K
max_connections = 200
thread_cache_size = 40
open_files_limit = 262144
table_definition_cache = 65536
table_open_cache = 65536
join_buffer_size = 256K
# INNODB #
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2
innodb_log_file_size = 32M
innodb_flush_log_at_trx_commit = 1
innodb_file_per_table = 1
innodb_buffer_pool_size = 8M
# LOGGING #
log_error = /var/lib/mysql/mysql-error.log
#log_queries_not_using_indexes = 1
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/mysql-slow.log
long_query_time = 1
Code:
mysql 666532 15.0 23.0 7134136 3737104 ? Sl Jan18 4101:38 \_ /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql/ --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/lib/mysql/mysql-error.log --open-files-limit=262144 --pid-file=/var/lib/mysql//hostname.pid --socket=/var/lib/mysql/mysql.sock
Last edited:
