sobrenome Posted July 29, 2015 Author Share Posted July 29, 2015 Are you running IPS 4 already? Is there any specific tuning on MariaDB for the IPS 4? Link to comment Share on other sites More sharing options...
RevengeFNF Posted July 29, 2015 Share Posted July 29, 2015 Post here your my.cnf and the result of mysqltuner 1.5.0PS: Your server have HDD or SSD? Link to comment Share on other sites More sharing options...
sobrenome Posted July 29, 2015 Author Share Posted July 29, 2015 I am stuck trying to upgrade mysqltuner in my channel box.I have followed this tutorial http://kb.enterprisevpssolutions.com/how-to-update-mysqltuner-pl-to-latest-version/And now the result is:-bash: ./mysqltuner.pl: No such file or directoryTried to reinstall again, but no success. I am using SSD now. Link to comment Share on other sites More sharing options...
RevengeFNF Posted July 29, 2015 Share Posted July 29, 2015 @sobrenome Do this: wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl Then just type:perl mysqltuner.pl Link to comment Share on other sites More sharing options...
sobrenome Posted July 30, 2015 Author Share Posted July 30, 2015 Thanks @RevengeFNF!!! Here is the result: >> MySQLTuner 1.5.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.20-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: 129) [--] Data in InnoDB tables: 7G (Tables: 674) [--] Data in MEMORY tables: 0B (Tables: 2) [!!] Total fragmented tables: 153 -------- Security Recommendations ------------------------------------------- [OK] There is no anonymous account in all database users [OK] All database users have passwords assigned [!!] There is not basic password file list ! -------- Performance Metrics ------------------------------------------------- [--] Up for: 22d 16h 14m 37s (87M q [44.419 qps], 2M conn, TX: 927B, RX: 51B) [--] Reads / Writes: 70% / 30% [--] Binary logging is disabled [--] Total buffers: 8.2G global + 2.8M per thread (192 max threads) [OK] Maximum reached memory usage: 8.4G (53.85% of installed RAM) [OK] Maximum possible memory usage: 8.7G (56.26% of installed RAM) [OK] Slow queries: 0% (89/87M) [OK] Highest usage of available connections: 28% (54/192) [OK] Aborted connections: 1.62% (46729/2875817) [!!] Key buffer used: 32.0% (42M used / 134M cache) [OK] Key buffer size / total MyISAM indexes: 128.0M/2.0G [OK] Read Key buffer hit rate: 99.7% (1B cached / 4M reads) [!!] Write Key buffer hit rate: 21.8% (186M cached / 146M writes) [!!] Query cache is disabled [OK] Sorts requiring temporary tables: 0% (3K temp sorts / 1M sorts) [!!] Joins performed without indexes: 8862 [OK] Temporary tables created on disk: 2% (219K on disk / 7M total) [OK] Thread cache hit rate: 98% (37K created / 2M connections) [!!] Table cache hit rate: 0% (400 open / 176K opened) [OK] Open file limit used: 2% (56/2K) [OK] Table locks acquired immediately: 99% (98M immediate / 98M locks) -------- InnoDB Metrics ----------------------------------------------------- [--] InnoDB is enabled. [OK] InnoDB buffer pool / data size: 8.0G/7.0G [OK] InnoDB buffer pool instances: 8 [OK] InnoDB Used buffer: 98.44% (516078 used/ 524280 total) [OK] InnoDB Read buffer efficiency: 100.00% (88229038442 hits/ 88229676769 total) [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total) [OK] InnoDB log waits: 0.00% (0 waits / 1582445 writes) -------- Replication Metrics ------------------------------------------------- [--] No replication slave(s) for this server. [--] This is a standalone server.. -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Adjust your join queries to always utilize indexes 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) join_buffer_size (> 128.0K, or always use indexes with joins) table_open_cache (> 400) New Relic data: Mysql my.cnf: [mysqld] ############################### # Basic Settings # ############################### skip-external-locking performance_schema = off #bind-address = 127.0.0.1 tmpdir = /var/mysqltmp ############################### # 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 = 0 transaction-isolation = READ-COMMITTED open_files_limit=2048 Link to comment Share on other sites More sharing options...
RevengeFNF Posted July 30, 2015 Share Posted July 30, 2015 @sobrenomeMy suggestions:Change the following values:innodb_buffer_pool_size =9Gtmp_table_size=256Mmax_heap_table_size=256MAdd the following lines:symbolic-links=0skip-name-resolvemax-connect-errors=1000000innodb_buffer_pool_instances=9innodb_buffer_pool_dump_at_shutdown = 1innodb_buffer_pool_load_at_startup = 1innodb_flush_neighbors=0aria_pagecache_buffer_size=256Mtable_open_cache=1000join_buffer_size =2Mread_buffer_size=128Ksort_buffer_size=256K Link to comment Share on other sites More sharing options...
sobrenome Posted July 31, 2015 Author Share Posted July 31, 2015 Thanks @RevengeFNF! I have followed your suggestions! In the next days I will post the new mysqltuner and new relic data.Do you know where can I check the tpmdir usage?I have addressed 1GB RAM for it, but when I check its use on Cpanel it's always a few kbytes. I would like to reduce the tpmdir size to the smallest safe size, I guess about 1MB, to use the RAM with memcache. Link to comment Share on other sites More sharing options...
RevengeFNF Posted July 31, 2015 Share Posted July 31, 2015 I don't know if I'm following... You are using the tmpdir in the ram with tmpfs? Link to comment Share on other sites More sharing options...
sobrenome Posted July 31, 2015 Author Share Posted July 31, 2015 Yes! That's it! Link to comment Share on other sites More sharing options...
RevengeFNF Posted July 31, 2015 Share Posted July 31, 2015 If you use SSD, i recommend you to use the disk for the temp instead of the TMPFS partition., Link to comment Share on other sites More sharing options...
sobrenome Posted July 31, 2015 Author Share Posted July 31, 2015 I will change then!What about memcache? I have set 4 GB of RAM to it. And it's full already. Is there a safe limit to memchace (software limitation performance) or the limit is based on the RAM installed on my BOX? 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 = /mysqltmpWhat's the simplest way to undo this configuration? Link to comment Share on other sites More sharing options...
RevengeFNF Posted July 31, 2015 Share Posted July 31, 2015 You probably are using the page save for guests. So it will save every page it can for guests. The much memory you reserve, more pages it will save. I have only set it up to 512Mb. I thinks its more than enough. You just need to remove the tmpdir = /mysqltmp from my.conf. Link to comment Share on other sites More sharing options...
sobrenome Posted July 31, 2015 Author Share Posted July 31, 2015 You probably are using the page save for guests. So it will save every page it can for guests. The much memory you reserve, more pages it will save. I have only set it up to 512Mb. I thinks its more than enough.You just need to remove the tmpdir = /mysqltmp from my.conf.I will add more RAM to the BOX the get the max from memcache.And how to unmount the RAM folder safely? Link to comment Share on other sites More sharing options...
RevengeFNF Posted July 31, 2015 Share Posted July 31, 2015 I will add more RAM to the BOX the get the max from memcache.And how to unmount the RAM folder safely?You don't need to unmount, because it will not use any Ram. And its better to leave it there, cause you might need it again.I don't think you will gain much for raising even more in memcache. Link to comment Share on other sites More sharing options...
sobrenome Posted July 31, 2015 Author Share Posted July 31, 2015 I thought that mounting the tmpdir to the RAM would "reserve exclusively" 1 GB of the RAM to the folder tmpdir, avoiding the use by other software.Memcache deletes old cache files to include new ones based on the latest requests?Navigating through phpMyAdmin I have noticed that all my databases from Pages are in MyISAM (ms_custom_database_1, cms_custom_database_2, etc) and the table core_search_index.I have checked my database of 3.4.7 and saw that every table was InnoDB.Why the upgrade changed the databases tables to MyISAM?And can I change the new core_search_index to InnoDB? Link to comment Share on other sites More sharing options...
Lukeroge Posted August 1, 2015 Share Posted August 1, 2015 It seems like a waste of RAM putting the tmpdir in ram... the space could probably be better used for actual caches and buffers. Link to comment Share on other sites More sharing options...
RevengeFNF Posted August 1, 2015 Share Posted August 1, 2015 I thought that mounting the tmpdir to the RAM would "reserve exclusively" 1 GB of the RAM to the folder tmpdir, avoiding the use by other software.Memcache deletes old cache files to include new ones based on the latest requests?Navigating through phpMyAdmin I have noticed that all my databases from Pages are in MyISAM (ms_custom_database_1, cms_custom_database_2, etc) and the table core_search_index.I have checked my database of 3.4.7 and saw that every table was InnoDB.Why the upgrade changed the databases tables to MyISAM?And can I change the new core_search_index to InnoDB?No, tmpfs doesn't reserve the ram. Only uses it if it needs.Memcached deletes old cached but don't removes them from memory, its on the wasted area. It will only start to remove them when the memory is full.You can change the core_search_index to Innodb. I prefer to use Sphinx and that table will not be used. Link to comment Share on other sites More sharing options...
sobrenome Posted August 1, 2015 Author Share Posted August 1, 2015 The tables related to Pages databases can also be converted to InnoDB?Thanks for you help.soon I will post the latest reports from mysqltuner and new relic. Link to comment Share on other sites More sharing options...
RevengeFNF Posted August 1, 2015 Share Posted August 1, 2015 Yep, you can change everything to InnoDB. Don't forget to change the buffer pool if you do that. Link to comment Share on other sites More sharing options...
sobrenome Posted August 1, 2015 Author Share Posted August 1, 2015 Will I have to change innodb_buffer_pool_instances? Link to comment Share on other sites More sharing options...
RevengeFNF Posted August 1, 2015 Share Posted August 1, 2015 Will I have to change innodb_buffer_pool_instances?Yes. 1 instance per Gb. Link to comment Share on other sites More sharing options...
sobrenome Posted September 15, 2015 Author Share Posted September 15, 2015 Something is wrong: >> MySQLTuner 1.5.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.21-MariaDB [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MyISAM [--] Data in MyISAM tables: 779M (Tables: 118) [--] Data in InnoDB tables: 7G (Tables: 513) [--] Data in MEMORY tables: 0B (Tables: 2) [!!] Total fragmented tables: 125 -------- Security Recommendations ------------------------------------------- [OK] There is no anonymous account in all database users [OK] All database users have passwords assigned [!!] There is not basic password file list ! -------- Performance Metrics ------------------------------------------------- [--] Up for: 39d 2h 37m 18s (162M q [47.977 qps], 8M conn, TX: 4624B, RX: 67B) [--] Reads / Writes: 91% / 9% [--] Binary logging is disabled [--] Total buffers: 9.4G global + 2.9M per thread (192 max threads) [!!] Maximum reached memory usage: 9.7G (623.91% of installed RAM) [!!] Maximum possible memory usage: 10.0G (640.52% of installed RAM) [OK] Slow queries: 0% (2K/162M) [OK] Highest usage of available connections: 52% (101/192) [OK] Aborted connections: 0.01% (1312/8891241) [OK] Key buffer used: 100.0% (134M used / 134M cache) [OK] Key buffer size / total MyISAM indexes: 128.0M/144.3M [OK] Read Key buffer hit rate: 98.7% (440M cached / 5M reads) [!!] Write Key buffer hit rate: 3.3% (46M cached / 44M writes) [!!] Query cache is disabled [OK] Sorts requiring temporary tables: 0% (102K temp sorts / 10M sorts) [!!] Joins performed without indexes: 38750 [!!] Temporary tables created on disk: 26% (292K on disk / 1M total) [OK] Thread cache hit rate: 99% (70K created / 8M connections) [!!] Table cache hit rate: 0% (1K open / 111K opened) [OK] Open file limit used: 3% (305/10K) [OK] Table locks acquired immediately: 99% (174M immediate / 174M locks) -------- InnoDB Metrics ----------------------------------------------------- [--] InnoDB is enabled. [OK] InnoDB buffer pool / data size: 9.0G/7.3G [OK] InnoDB buffer pool instances: 9 [OK] InnoDB Used buffer: 98.44% (580595 used/ 589815 total) [OK] InnoDB Read buffer efficiency: 100.00% (145422058604 hits/ 145422389503 total) [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total) [OK] InnoDB log waits: 0.00% (0 waits / 2734735 writes) -------- Replication Metrics ------------------------------------------------- [--] No replication slave(s) for this server. [--] This is a standalone server.. -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Reduce your overall MySQL memory footprint for system stability Adjust your join queries to always utilize indexes Temporary table size is already large - reduce result set size Reduce your SELECT DISTINCT queries without LIMIT clauses 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: *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** query_cache_size (>= 8M) join_buffer_size (> 2.0M, or always use indexes with joins) table_open_cache (> 1000)My system has 16 GB of RAM, so why this warning?[!!] Maximum reached memory usage: 9.7G (623.91% of installed RAM) [!!] Maximum possible memory usage: 10.0G (640.52% of installed RAM) Link to comment Share on other sites More sharing options...
sobrenome Posted September 16, 2015 Author Share Posted September 16, 2015 Could help @RevengeFNF! My TTFB on webpagetest.org is usually very high in the first view and very good in the repeated view. I think that this issue is related to database configuration. I have reduced the buffer from 8GB to 4GB: >> MySQLTuner 1.5.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.21-MariaDB [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MyISAM [--] Data in MyISAM tables: 789M (Tables: 118) [--] Data in InnoDB tables: 7G (Tables: 513) [--] Data in MEMORY tables: 0B (Tables: 2) [!!] Total fragmented tables: 126 -------- Security Recommendations ------------------------------------------- [OK] There is no anonymous account in all database users [OK] All database users have passwords assigned [!!] There is not basic password file list ! -------- Performance Metrics ------------------------------------------------- [--] Up for: 21h 17m 18s (2M q [32.740 qps], 155K conn, TX: 19B, RX: 977M) [--] Reads / Writes: 90% / 10% [--] Binary logging is disabled [--] Total buffers: 4.4G global + 2.9M per thread (192 max threads) [!!] Maximum reached memory usage: 4.5G (288.28% of installed RAM) [!!] Maximum possible memory usage: 5.0G (318.94% of installed RAM) [OK] Slow queries: 0% (0/2M) [OK] Highest usage of available connections: 12% (24/192) [OK] Aborted connections: 0.00% (7/155935) [!!] Key buffer used: 26.8% (35M used / 134M cache) [OK] Key buffer size / total MyISAM indexes: 128.0M/146.2M [OK] Read Key buffer hit rate: 98.8% (878K cached / 10K reads) [!!] Write Key buffer hit rate: 29.1% (190K cached / 135K writes) [!!] Query cache is disabled [OK] Sorts requiring temporary tables: 0% (270 temp sorts / 213K sorts) [!!] Joins performed without indexes: 704 [!!] Temporary tables created on disk: 33% (8K on disk / 24K total) [OK] Thread cache hit rate: 99% (1K created / 155K connections) [OK] Table cache hit rate: 108% (801 open / 738 opened) [OK] Open file limit used: 2% (297/10K) [OK] Table locks acquired immediately: 99% (2M immediate / 2M locks) -------- InnoDB Metrics ----------------------------------------------------- [--] InnoDB is enabled. [!!] InnoDB buffer pool / data size: 4.0G/7.3G [!!] InnoDB buffer pool instances: 9 [OK] InnoDB Used buffer: 96.48% (252915 used/ 262134 total) [OK] InnoDB Read buffer efficiency: 99.98% (1814113352 hits/ 1814514239 total) [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total) [OK] InnoDB log waits: 0.00% (0 waits / 59269 writes) -------- Replication Metrics ------------------------------------------------- [--] No replication slave(s) for this server. [--] This is a standalone server.. -------- 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 Adjust your join queries to always utilize indexes Temporary table size is already large - reduce result set size Reduce your SELECT DISTINCT queries without LIMIT clauses Variables to adjust: *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** query_cache_size (>= 8M) join_buffer_size (> 2.0M, or always use indexes with joins) innodb_buffer_pool_size (>= 7G) if possible. innodb_buffer_pool_instances(=4) Link to comment Share on other sites More sharing options...
sobrenome Posted September 16, 2015 Author Share Posted September 16, 2015 total used free shared buffers cached Mem: 16303540 13699324 2604216 169616 852040 5574628 -/+ buffers/cache: 7272656 9030884 Swap: 11767608 287472 11480136 Link to comment Share on other sites More sharing options...
RevengeFNF Posted September 17, 2015 Share Posted September 17, 2015 Why did you reduce the buffer size? Install mysqltuner 1.6 to check if the bug with ram disappears. Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.