RevengeFNF Posted February 22, 2015 Posted February 22, 2015 How to upgrade it on CentOS 6?Download it: wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.plTem run it: perl mysqltuner.pl
sobrenome Posted February 22, 2015 Author Posted February 22, 2015 >> MySQLTuner 1.4.0 - Major Hayden <major@mhtx.net> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering [!!] Currently running unsupported MySQL version 10.0.16-MariaDB [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MyISAM [--] Data in MyISAM tables: 3G (Tables: 1033) [--] Data in InnoDB tables: 6G (Tables: 679) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52) [--] Data in MEMORY tables: 0B (Tables: 2) [!!] Total fragmented tables: 208 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 1d 4h 48m 25s (2M q [27.217 qps], 126K conn, TX: 56B, RX: 4B) [--] Reads / Writes: 66% / 34% [--] Total buffers: 6.3G global + 2.8M per thread (252 max threads) [OK] Maximum possible memory usage: 7.0G (44% of installed RAM) [OK] Slow queries: 0% (68/2M) [OK] Highest usage of available connections: 21% (54/252) [OK] Key buffer size / total MyISAM indexes: 128.0M/2.3G [OK] Key buffer hit rate: 96.1% (14M cached / 549K reads) [OK] Query cache efficiency: 26.5% (576K cached / 2M selects) [!!] Query cache prunes per day: 194846 [OK] Sorts requiring temporary tables: 0% (7 temp sorts / 88K sorts) [OK] Temporary tables created on disk: 23% (17K on disk / 76K total) [OK] Thread cache hit rate: 99% (476 created / 126K connections) [!!] Table cache hit rate: 1% (400 open / 32K opened) [OK] Open file limit used: 3% (362/10K) [OK] Table locks acquired immediately: 99% (3M immediate / 3M locks) [!!] InnoDB buffer pool / data size: 6.0G/6.7G [OK] InnoDB log waits: 0 -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Increase table_open_cache gradually to avoid file descriptor limits Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C Variables to adjust: query_cache_size (> 64M) table_open_cache (> 400) innodb_buffer_pool_size (>= 6G)
Makoto Posted February 22, 2015 Posted February 22, 2015 I might recommend something like this to use as a base configuration,# MariaDB database server configuration file. [mysqld] ############################### # Basic Settings # ############################### skip-external-locking performance_schema = off bind-address = 127.0.0.1 ############################### # Fine Tuning # ############################### max_connections = 192 connect_timeout = 30 wait_timeout = 600 max_allowed_packet = 256M thread_cache_size = 128 tmp_table_size = 64M max_heap_table_size = 64M thread_handling = pool-of-threads ############################### # MyISAM # ############################### myisam_recover = BACKUP concurrent_insert = 2 ############################### # Query Cache Configuration # ############################### query_cache_limit = 128K query_cache_size = 0M query_cache_type = OFF ############################### # Logging and Replication # ############################### #general_log_file = /var/log/mysql/mysql.log #general_log = 1 ############################### # InnoDB # ############################### default_storage_engine = InnoDB innodb_log_file_size = 2G innodb_buffer_pool_size = 8G innodb_log_buffer_size = 32M innodb_file_per_table = 1 innodb_open_files = 800 innodb_io_capacity = 800 innodb_flush_method = O_DIRECT innodb_flush_log_at_trx_commit = 0I've lowered your maximum connections a bit, since your peak connection usage is only 52 at the moment it would probably be safe to lower this even further but you're not using that much memory per thread at the moment anyways.I keep query cache disabled, as I don't believe it provides any real benefit for complex applications such as IP.Board (and can even potentially degrade performance.) Specifically, notice how many query cache prunes you have per day? This is because complex applications such as IP.Board can have a lot of complex queries that can't be cached properly and end up needing to be frequently flushed.I've increased the InnoDB buffer pool to 8GB, which still gives you a little breathing room for scaling at 6.7GB of current usage.This includes some various general purpose performance tweaks for InnoDB and MariaDB in general (e.g. O_DIRECT flushing and flushing the log once per second instead of at every transaction commit, thread pooling, making sure performance_schema is disabled (I can't remember if this is still enabled by default in the latest stable release of MariaDB but it degrades performance in production and should only be enabled when you're using it)).Going off your previous MySQLtuner output, I'm assuming you're running on 16GB of total available memory. With this configuration, you'll be dedicated a little over half your available memory pool to MySQL. This should still allow plenty of room for other web services to run as long as everything else is properly configured.I can't remember if you still need to do this with MariaDB 10 or not, but just to be safe, after updating your configuration please run the following commands to restart MySQL. You generally need to do this any time you change the innodb_log_file_size. (I think MariaDB 10 may have removed this requirement but I honestly can't remember)mysql -uroot -p -e"SET GLOBAL innodb_fast_shutdown = 0" service mysql stop mv /var/lib/mysql/ib_logfile[01] /tmp service mysql start
RevengeFNF Posted February 22, 2015 Posted February 22, 2015 Kirito, in Mariadb 10 we can change the logfile size without any problem.I also think he should add this to my.cnfinnodb_buffer_pool_instances=8 innodb_buffer_pool_dump_at_shutdown = 1 innodb_buffer_pool_load_at_startup = 1Instead of having a big 8Gb pool, he will have 8 pools of 1Gb each.The other two is to when he restart Mariadb, there is no warm up time.Also i think he should raise the Key Buffer Size to the size of Myisam cache. In this case, 2.3Gb.
Makoto Posted February 23, 2015 Posted February 23, 2015 innodb_buffer_pool_instances=8 innodb_buffer_pool_dump_at_shutdown = 1 innodb_buffer_pool_load_at_startup = 1Instead of having a big 8Gb pool, he will have 8 pools of 1Gb eachThe default value for innodb_buffer_pool_instances in MariaDB 10 is actually already 8. I don't mind having to let my forum warm up again after restarting, as restarts are so rare and warm up doesn't take that long anyways, but it's certainly a fair suggestion.
sobrenome Posted February 23, 2015 Author Posted February 23, 2015 I had one problem with this:############################### # Basic Settings # ############################### skip-external-locking performance_schema = off bind-address = 127.0.0.1I had to remove this 3 settings for the service restart.I don't know which one was causing the problem, but as they are the ones that I am not familiar with, I turned all 3 off.
RevengeFNF Posted February 23, 2015 Posted February 23, 2015 skip-external-locking performance_schema = offThese two are already the default of Mariadb.The other one i never used it.
RevengeFNF Posted February 23, 2015 Posted February 23, 2015 @Kirito i added thread_handling = pool-of-threads as you suggested because i have read very good thing about it, but now my Thread Cache is not caching. Is this normal with this option?WORKER THREADS Current thread_cache_size = 8 Current threads_cached = 0 Current threads_per_sec = 0 Historic threads_per_sec = 0 Your thread_cache_size is fine PS:Ok, Mariadb explain this:NotesThe thread_cache_size system variable is not used when the thread pool is used and theThreads_cached status variable will have a value of 0. So you can remove this from my.cnf, because Mariadb will not use it.thread_cache_size = 128
sobrenome Posted February 23, 2015 Author Posted February 23, 2015 The settings suggested by @Kirito and @RevengeFNF resulted in a better TTFB, about 400ms for repeated view. But it's still 30% worse then MyISAM, which was about 300ms.So I guess there is space for more tuning.
RevengeFNF Posted February 23, 2015 Posted February 23, 2015 You have set your innodb_log_file_size to 2G, which means you have 4Gb for logs.In the Percona Blog they say the best log size is to contain 1 hour of transactions.I did this for mine:In the peak hour of your site, run the query: SHOW GLOBAL STATUSThen search for the value of: innodb_lsn_currentWrite the value in some place.After exacly 1 minute, run again the SHOW GLOBAL STATUS and check the new value of innodb_lsn_current.Now you need to subtract this last value with the first one, and you will get the amount of transaction to the log file in bytes for 1 minute.Convert the value in Bytes to Mb. In my case it was 8Mb. Multiple that value with 60, and you get the trabnsaction in 1 hour. In my case its 480Mb. I'll use 512Mb.So i just set the innodb_log_file_size to 256Mb and i have the log for 1 hour of transactions.
sobrenome Posted February 24, 2015 Author Posted February 24, 2015 The first value was 37242829093 bytes. The second value was 37243410062 bytes. The difference is 580969 bytes or 0.55406 megabytes.So 0.55406 x 60 = 33,24 megabytes.I will set innodb_logo_file_size to 64Mb. Is that correct?
Makoto Posted February 24, 2015 Posted February 24, 2015 That seems like a really low value. I generally just use 1/4th the buffer pool for the log file size. I'm not sure if it well help (or hurt) at all to decrease it that low.
RevengeFNF Posted February 24, 2015 Posted February 24, 2015 The first value was 37242829093 bytes. The second value was 37243410062 bytes. The difference is 580969 bytes or 0.55406 megabytes.So 0.55406 x 60 = 33,24 megabytes.I will set innodb_logo_file_size to 64Mb. Is that correct?It seems a little low. This is the peak time of your site?
sobrenome Posted February 24, 2015 Author Posted February 24, 2015 Yes, the site lost almost 80% of its traffic along the last year. Most people access from smartphones, and 3.4.7 is terrible on mobile devices.So I can safely reduce do 256Mb?
Makoto Posted February 24, 2015 Posted February 24, 2015 The only negative impact of having a large buffer pool that I know of is that it makes recovery slower in the event of a crash. Quoting from the MySQL docs,The size in bytes of each log file in a log group. The combined size of log files (innodb_log_file_size *innodb_log_files_in_group) cannot exceed a maximum value that is slightly less than 4GB. A pair of 2047 MB log files, for example, would allow you to approach the range limit but not exceed it. The default value is 5MB. Sensible values range from 1MB to 1/N-th of the size of the buffer pool, where N is the number of log files in the group. The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. But larger log files also mean that recovery is slower in case of a crash.32MB may be a perfectly fair value for your current traffic requirements, but keeping burst traffic and scaling in mind I would avoid setting this value too low, and leaving it at 1/4th the buffer pool I think should be fine unless you are constrained on storage and have a really large buffer pool.
sobrenome Posted February 24, 2015 Author Posted February 24, 2015 Last mysqltuner recommendations: >> MySQLTuner 1.4.0 - Major Hayden <major@mhtx.net> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering [!!] Currently running unsupported MySQL version 10.0.16-MariaDB [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MyISAM [--] Data in MyISAM tables: 3G (Tables: 1034) [--] Data in InnoDB tables: 5G (Tables: 669) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52) [--] Data in MEMORY tables: 0B (Tables: 2) [!!] Total fragmented tables: 191 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 21h 40m 43s (3M q [40.513 qps], 116K conn, TX: 44B, RX: 1B) [--] Reads / Writes: 56% / 44% [--] Total buffers: 8.2G global + 2.8M per thread (192 max threads) [OK] Maximum possible memory usage: 8.7G (56% of installed RAM) [OK] Slow queries: 0% (5/3M) [OK] Highest usage of available connections: 4% (9/192) [OK] Key buffer size / total MyISAM indexes: 128.0M/2.3G [OK] Key buffer hit rate: 99.8% (8M cached / 17K reads) [!!] Query cache is disabled [OK] Sorts requiring temporary tables: 0% (31 temp sorts / 87K sorts) [OK] Temporary tables created on disk: 22% (17K on disk / 77K total) [OK] Thread cache hit rate: 98% (1K created / 116K connections) [!!] Table cache hit rate: 2% (400 open / 16K opened) [OK] Open file limit used: 16% (342/2K) [OK] Table locks acquired immediately: 100% (5M immediate / 5M locks) [OK] InnoDB buffer pool / data size: 8.0G/5.9G [OK] InnoDB log waits: 0 -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate Increase table_open_cache gradually to avoid file descriptor limits Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C Variables to adjust: query_cache_size (>= 8M) table_open_cache (> 400)
Makoto Posted February 24, 2015 Posted February 24, 2015 You can ignore the suggestion to enable query cache. I've seen mixed information regarding increasing table_open_cache, you can continually try and increase this and mysqltuner will just immediately tell you to increase it further. This is what I currently have configured, and you can try increasing this steadily yourself and see if it makes any noticeable improvements for you,table_open_cache = 4096 table_cache = 4096 table_definition_cache = 4096
RevengeFNF Posted February 24, 2015 Posted February 24, 2015 Raise your table_open_cache to at least 1000.Also you have 1703 tables, so you need to set your table definition cache to at least 1703.You have 22% of your temporary tables created on disk. You can create a tmpfs partition of 2GB, and set your mysql tmpdir to that partition. This way, even those 22% of temporary tables are created in your Ram.
RevengeFNF Posted February 24, 2015 Posted February 24, 2015 You can ignore the suggestion to enable query cache. I've seen mixed information regarding increasing table_open_cache, you can continually try and increase this and mysqltuner will just immediately tell you to increase it further. This is what I currently have configured, and you can try increasing this steadily yourself and see if it makes any noticeable improvements for you, table_open_cache = 4096 table_cache = 4096 table_definition_cache = 4096 Kirito, table_open_cache and table_cache(this one is deprecated) are the same thing He will not gain anything with the definition cache so high, because he only have 1703 tables. It will cache each .frm file. Mysqltuner is dumb regarding the Table Caches. He can install Tuning Primer that will show him the real Tables cache opened.
Makoto Posted February 24, 2015 Posted February 24, 2015 Ah, yeah, my current configuration is a bit of a mess, sorry. I haven't actually seriously touched it in probably a year . I need to review it and clean it up a bit sometime.
sobrenome Posted February 24, 2015 Author Posted February 24, 2015 I have tried to change tmpdir="/mysqltmp" to a RAM directory but MySQL didn't restart.
RevengeFNF Posted February 24, 2015 Posted February 24, 2015 I have tried to change tmpdir="/mysqltmp" to a RAM directory but MySQL didn't restart.chown mysql:mysql /mysqltmpid mysql -> For you to know the id of mysql for the next line. In my example, the id is 27.nano /etc/fstabAdd -> tmpfs /var/mysqltmp tmpfs rw,gid=27,uid=27,size=2G,nr_inodes=10k,mode=0700 0 0mount -aAdd to my.cnf -> tmpdir = /mysqltmp
RevengeFNF Posted February 24, 2015 Posted February 24, 2015 I was reading the last blog posts from Peter in the Percona blog, and i noticed this two posts from him: http://www.percona.com/blog/2015/01/14/mysql-performance-implications-of-innodb-isolation-modes/http://www.percona.com/blog/2015/01/19/looking-deeper-innodbs-problem-many-row-versions/I then read this from Mariadb site: https://mariadb.com/kb/en/mariadb/set-transaction/So, Read Commited might give some more performance than Repeatable Read(default).I've added this to my.cnf and confirmed its using it:transaction-isolation = READ-COMMITTEDMariaDB [(none)]> SELECT @@GLOBAL.tx_isolation, @@tx_isolation; +-----------------------+----------------+ | @@GLOBAL.tx_isolation | @@tx_isolation | +-----------------------+----------------+ | READ-COMMITTED | READ-COMMITTED | +-----------------------+----------------+Lets check if i notice some performance improvements.
sobrenome Posted February 24, 2015 Author Posted February 24, 2015 tmpdir on RAM made no difference, it was barely used.When something was there, it was about 16kb for very short time.
sobrenome Posted February 24, 2015 Author Posted February 24, 2015 With the current settings, the first view TTFB gets around 400ms (nice) but the repeated view goes around 1100ms... (much worse than before)
Recommended Posts
Archived
This topic is now archived and is closed to further replies.