Performance problems - first settings of percona

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/[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?

Ignore, seriously.

Enable the slow query log to log ALL queries, and set log_slow_verbosity=ALL, gather a bunch of queries, and process the resulting file with mk-query-digest as a start. But probably more importantly, you are possibly running InnoDB on hardware that isn’t really suited for it. MyISAM doesn’t give a hoot about your data and doesn’t even try to write it to disk, so it can run fast on slow disks. InnoDB needs decent disk performance. This is ONE possible thing that can cause the problem, I am not saying it’s the problem, but you need to measure what is happening to understand where the problem really is. It sounds like you are not very experienced with this, so I am suggesting a query log analysis as a very simple first step.

xaprb wrote on Wed, 03 August 2011 20:24

I just want to clarify what Baron’s meaning.

Since InnoDB has transaction support and wants to be ACID compliant (which MyISAM isn’t), it needs due to durability reasons to write to the transaction log for every transaction.
And it needs to flush the log to disk before returning to the application that the query went well.
Since the seek times for a normal harddrive is somewhere around 5-10ms this will give you only about 100-200 inserts per second.

So if you haven’t got a battery backed up RAID controller with Write Back cache enabled, this writing and flushing of the transaction log will slow down inserts/updates/deletes a lot.

If you on the other hand are willing to sacrifice robustness and the possibility to loose a couple of transactions in case of a OS/hardware failure then you should check out this MySQL variable:

If you set it to:

innodb_flush_log_at_trx_commit = 2

the transaction log will only be flushed to disk about once a second and greatly speeding up inserts on poor hardware (no pun intended :slight_smile: ).