# MySQL tunning help **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [November 15, 2013, 11:07am UTC](https://forums.percona.com/t/mysql-tunning-help/3090 "2013-11-15T11:07:14Z")\ **Posts on this page:** 1\ **Page:** 1
**Author:** ![stendelis](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/stendelis/32/973_2.png) [@stendelis](https://forums.percona.com/u/stendelis)\ **Post date:** [November 15, 2013, 11:07am UTC](https://forums.percona.com/t/mysql-tunning-help/3090/1 "2013-11-15T11:07:14Z")
Sorry for my bad english. I have a Managed VPS server 2GB Ram 64-bit CentOS 6, MySQL 5.5 & PHP 5.4 I run an application that takes an xml and inserts to the database are about 400,000 insertions per hour. In the next hour the system drop the database data and inserts again, this happens in the backend In the frontend of the website are connected near to 1000 users per hour, and mysql work is minimal. My experience with MySQL parameters is very low. But I need help tuning my MySQL to make it work better in the backend. I show my configuration and the results of mysqltuner.pl and tuning-primer.sh My.cnf————————————————- [mysqld] # Basic settings user = mysql datadir = /var/lib/mysql socket = /var/lib/mysql/mysql.sock # Security settings local-infile = 0 symbolic-links = 0 # Memory and cache settings query\_cache\_type = 1 query\_cache\_size = 98M query\_cache\_limit = 24M thread\_cache\_size = 24 table\_cache =12 tmp\_table\_size = 256M max\_heap\_table\_size = 48M join\_buffer\_size = 12M key\_buffer\_size = 460M max\_connections = 200 wait\_timeout = 3600 # Innodb settings innodb\_buffer\_pool\_size = 256M innodb\_additional\_mem\_pool\_size = 64M innodb\_log\_buffer\_size = 3M innodb\_thread\_concurrency = 6 [mysqld\_safe] # Basic safe settings log-error = /var/log/mysqld.log pid-file = /var/run/mysqld/mysqld.pid ### Log slow queries log-slow-queries = mysqld-slow.log # Duration a query must run (in seconds) to get logged long\_query\_time = 1 —————————————————— mysqltuner.pl > > MySQLTuner 1.2.0 – Major Hayden > > Bug reports, feature requests, and downloads at [http://mysqltuner.com/](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.1.54 > > [OK] Operating on 64-bit architecture > > ——– Storage Engine Statistics ——————————————- > > [–] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster > > [–] Data in MyISAM tables: 1G (Tables: 304) > > [–] Data in InnoDB tables: 2M (Tables: 173) > > [!!] Total fragmented tables: 191 > > ——– Security Recommendations ——————————————- > > [OK] All database users have passwords assigned > > ——– Performance Metrics ————————————————- > > [–] Up for: 21h 8m 26s (5M q [75.629 qps], 41K conn, TX: 1B, RX: 28B) > > [–] Reads / Writes: 0% / 100% > > [–] Total buffers: 929.0M global + 14.6M per thread (200 max threads) > > [!!] Maximum possible memory usage: 3.8G (110% of installed RAM) > > [OK] Slow queries: 0% (12/5M) > > [OK] Highest usage of available connections: 4% (8/200) > > [OK] Key buffer size / total MyISAM indexes: 460.0M/410.3M > > [OK] Key buffer hit rate: 99.4% (64M cached / 372K reads) > > [OK] Query cache efficiency: 93.6% (469K cached / 501K selects) > > [OK] Query cache prunes per day: 0 > > [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 1K sorts) > > [!!] Joins performed without indexes: 263 > > [OK] Temporary tables created on disk: 22% (1K on disk / 7K total) > > [OK] Thread cache hit rate: 99% (8 created / 41K connections) > > [!!] Table cache hit rate: 0% (12 open / 11K opened) > > [OK] Open file limit used: 2% (24/1K) > > [OK] Table locks acquired immediately: 99% (41K immediate / 41K locks) > > [OK] InnoDB data size / buffer pool: 2.8M/256.0M > > ——– Recommendations —————————————————– > > General recommendations: > > Run OPTIMIZE TABLE to defragment tables for better performance > > MySQL started within last 24 hours – recommendations may be inaccurate > > Reduce your overall MySQL memory footprint for system stability > > Enable the slow query log to troubleshoot bad queries > > Adjust your join queries to always utilize indexes > > Increase table\_cache gradually to avoid file descriptor limits > > Variables to adjust: > > \*\*\* MySQL’s maximum memory usage is dangerously high \*\*\* > > \*\*\* Add RAM before increasing MySQL buffer variables \*\*\* > > join\_buffer\_size (\> 12.0M, or always use indexes with joins) > > table\_cache (\> 12) > > ——————————————————————— > > tuning-primer.sh > > — MYSQL PERFORMANCE TUNING PRIMER – - By: Matthew Montgomery - MySQL Version 5.1.54 x86\_64 Uptime = 0 days 21 hrs 7 min 34 sec Avg. qps = 75 Total Questions = 5753619 Threads Connected = 2 Warning: Server has not been running for at least 48hrs. It may not be safe to use these recommendations To find out more information on how each of these runtime variables effects performance visit: [http://dev.mysql.com](http://dev.mysql.com/)…-variables.html Visit [http://www.mysql.com](http://www.mysql.com/)…e/advisors.html for info about MySQL’s Enterprise Monitoring and Advisory Service SLOW QUERIES The slow query log is NOT enabled. Current long\_query\_time = 10.000000 sec. You have 12 out of 5753646 that take longer than 10.000000 sec. to complete Your long\_query\_time seems to be fine BINARY UPDATE LOG The binary update log is NOT enabled. You will not be able to do point in time recovery See [http://dev.mysql.com](http://dev.mysql.com/)…e-recovery.html WORKER THREADS Current thread\_cache\_size = 24 Current threads\_cached = 6 Current threads\_per\_sec = 0 Historic threads\_per\_sec = 0 Your thread\_cache\_size is fine MAX CONNECTIONS Current max\_connections = 200 Current threads\_connected = 2 Historic max\_used\_connections = 8 The number of used connections is 4% of the configured maximum. You are using less than 10% of your configured max\_connections. Lowering max\_connections could help to avoid an over-allocation of memory See “MEMORY USAGE” section to make sure you are not over-allocating INNODB STATUS Current InnoDB index space = 2 M Current InnoDB data space = 2 M Current InnoDB buffer pool free = 96 % Current innodb\_buffer\_pool\_size = 256 M Depending on how much space your innodb indexes take up it may be safe to increase this value to up to 2 / 3 of total system memory MEMORY USAGE Max Memory Ever Allocated : 997 M Configured Max Per-thread Buffers : 2.85 G Configured Max Global Buffers : 881 M Configured Max Memory Limit : 3.71 G Physical Memory : 3.42 G Max memory limit exceeds 90% of physical memory KEY BUFFER Current MyISAM index space = 410 M Current key\_buffer\_size = 460 M Key cache miss rate is 1 : 171 Key buffer free ratio = 81 % Your key\_buffer\_size seems to be fine QUERY CACHE Query cache is enabled Current query\_cache\_size = 98 M Current query\_cache\_used = 25 M Current query\_cache\_limit = 24 M Current Query cache Memory fill ratio = 25.84 % Current query\_cache\_min\_res\_unit = 4 K Your query\_cache\_size seems to be too high. Perhaps you can use these resources elsewhere MySQL won’t cache query results that are larger than query\_cache\_limit in size SORT OPERATIONS Current sort\_buffer\_size = 2 M Current read\_rnd\_buffer\_size = 256 K Sort buffer seems to be fine JOINS Current join\_buffer\_size = 12.00 M You have had 263 queries where a join could not use an index properly join\_buffer\_size \>= 4 M This is not advised You should enable “log-queries-not-using-indexes” Then look for non indexed joins in the slow query log. OPEN FILES LIMIT Current open\_files\_limit = 1024 files The open\_files\_limit should typically be set to at least 2x-3x that of table\_cache if you have heavy MyISAM usage. Your open\_files\_limit value seems to be fine TABLE CACHE Current table\_open\_cache = 12 tables Current table\_definition\_cache = 256 tables You have a total of 500 tables You have 12 open tables. Current table\_cache hit rate is 0% , while 100% of your table cache is in use You should probably increase your table\_cache You should probably increase your table\_definition\_cache value. TEMP TABLES Current max\_heap\_table\_size = 48 M Current tmp\_table\_size = 256 M Of 5775 temp tables, 22% were created on disk Effective in-memory tmp\_table\_size is limited to max\_heap\_table\_size. Created disk tmp tables ratio seems fine TABLE SCANS Current read\_buffer\_size = 128 K Current table scan ratio = 5654 : 1 You have a high ratio of sequential access requests to SELECTs You may benefit from raising read\_buffer\_size and/or improving your use of indexes. TABLE LOCKING Current Lock Wait ratio = 1 : 6979 Your table locking seems to be fine