Hello everybody,
To introduce myself and explain why I get these troubles - I am working as SAP administrator, mainly with DB2 or Oracle databases (but only small part, settings are doing mainly db teams in our company), but for fun I am administration also one small web server. Setting up databases like mysql is really new for me and after few weeks I realized I am not able to do it without help from you.
We started to migrating our web and database to new server, due old server had in peak situations problems to manage all requests.
We found out that we have also option to try percona server, so we wanna try it.
But situation now isnt good I have serious troubles with setting my.cnf because previously was whole database on myisam format and I tried to change some tables with high write concurrency change to innodb, but system has still high response time which wasnt normal even on old server, which was much slower.
So here is output from mysql optimizer, which looks ok
-------- General Statistics --------------------------------------------------[–] Skipped version check for MySQLTuner script[OK] Currently running supported MySQL version 5.1.57-rel12.8[OK] Operating on 64-bit architecture-------- Storage Engine Statistics -------------------------------------------[–] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster[–] Data in MyISAM tables: 569M (Tables: 150)[–] Data in InnoDB tables: 561M (Tables: 24)[!!] Total fragmented tables: 32-------- Security Recommendations -------------------------------------------[OK] All database users have passwords assigned-------- Performance Metrics -------------------------------------------------[–] Up for: 1d 2h 57m 14s (118M q [1K qps], 1M conn, TX: 55B, RX: 18B)[–] Reads / Writes: 64% / 36%[–] Total buffers: 2.6G global + 104.2M per thread (600 max threads)[!!] Maximum possible memory usage: 63.7G (405% of installed RAM)[OK] Slow queries: 2% (2M/118M)[OK] Highest usage of available connections: 17% (105/600)[OK] Key buffer size / total MyISAM indexes: 1.0G/184.9M[OK] Key buffer hit rate: 99.9% (247M cached / 220K reads)[OK] Query cache efficiency: 48.1% (42M cached / 89M selects)[!!] Query cache prunes per day: 712959[OK] Sorts requiring temporary tables: 0% (21 temp sorts / 13M sorts)[OK] Temporary tables created on disk: 3% (253K on disk / 7M total)[OK] Thread cache hit rate: 99% (105 created / 1M connections)[OK] Table cache hit rate: 45% (673 open / 1K opened)[OK] Open file limit used: 1% (596/33K)[OK] Table locks acquired immediately: 99% (90M immediate / 90M locks)[OK] InnoDB data size / buffer pool: 561.4M/1.5G-------- 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 queriesVariables to adjust: *** MySQL’s maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** query_cache_size (> 32M)
Slow queries are not enabled because if response time is so high every query is determined to be slow, so after it that log is useless.
Recommendation about query_cache_size is confusing for me, because when I started mysql-summary I get this value
Query cache ################################################ query_cache_type | ON Size | 32.0M Usage | 30% HitToInsertRatio | 100%
So I dont think its necessary to increase value.
My setting of my.cnf is following:
MySQL settings----------------------------[client]port = 3306socket = /var/lib/mysql/mysql.sock[mysqld]port = 3306datadir=/var/lib/mysqlsocket=/var/lib/mysql/mysql.sock# Default to using old password format for compatibility with mysql 3.x# clients (those using the mysqlclient10 compatibility package).old_passwords=1sql_mode = NO_UNSIGNED_SUBTRACTIONskip-lockingback_log = 50max_connections = 600key_buffer_size = 1Gmyisam_sort_buffer_size = 64Mjoin_buffer_size = 4Mread_buffer_size = 4Msort_buffer_size = 4Mrecord_buffer = 64Mtable_cache = 16384thread_cache_size = 500skip-networkingwait_timeout = 60connect_timeout = 10interactive_timeout = 60tmp_table_size = 512Mmax_allowed_packet = 32Mmax_connect_errors = 999999read_rnd_buffer_size = 32Mbulk_insert_buffer_size = 16Mthread_concurrency = 4query_cache_limit = 8Mquery_cache_size = 32Mquery_cache_type = 1query_prealloc_size = 16384query_alloc_block_size = 16384log-queries-not-using-indexes = 1slow_query_log = 0low_priority_updates = 1log_long_formatmyisam_repair_threads = 1myisam_recoverdelayed_insert_timeout = 10innodb_buffer_pool_size=1500Minnodb_additional_mem_pool_size=50Minnodb_log_file_size=256Minnodb_log_buffer_size=20Minnodb_force_recovery = 0innodb_data_file_path = ibdata1:20M:autoextend[mysql.server]user=mysql#basedir=/var/lib[mysqld_safe]log-error=/var/log/mysqld.logpid-file=/var/lib/mysql/ded1394.pid[mysqldump]quickmax_allowed_packet = 16M[myisamchk]key_buffer = 256Msort_buffer = 256Mread_buffer = 16Mwrite_buffer = 16M—
Top command output is here:
top - 18:34:58 up 7 days, 15:56, 2 users, load average: 2.04, 2.08, 2.11Tasks: 256 total, 2 running, 253 sleeping, 0 stopped, 1 zombieCpu(s): 11.5%us, 2.4%sy, 0.0%ni, 81.6%id, 4.4%wa, 0.0%hi, 0.1%si, 0.0%stMem: 16459544k total, 13763560k used, 2695984k free, 187924k buffersSwap: 1048568k total, 1132k used, 1047436k free, 9685912k cached
Please could you help me with determine my newbies faults in setting?
).