sobrenome Posted September 17, 2015 Author Share Posted September 17, 2015 5 hours ago, RevengeFNF said: Why did you reduce the buffer size? Install mysqltuner 1.6 to check if the bug with ram disappears. I have run the "free" command and I was running out of memory. So I reduced the buffer. I will install the 1.6 and update here. Thanks my friend. My website behaves very strangely on webpagetest.org on TTFB. Sometimes it takes more than 2 seconds on first view. On repeated view it takes 100 ms... I think its related to database configuration. Let´s see. Thanks again. Link to comment Share on other sites More sharing options...
sobrenome Posted September 18, 2015 Author Share Posted September 18, 2015 Here are the results: >> MySQLTuner 1.6.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 [--] Skipped version check for MySQLTuner script [OK] Currently running supported 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: 793M (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: 1d 22h 32m 21s (5M q [32.410 qps], 336K conn, TX: 41B, RX: 2B) [--] Reads / Writes: 90% / 10% [--] Binary logging is disabled [--] Total buffers: 4.7G global + 2.9M per thread (192 max threads) [OK] Maximum reached memory usage: 4.7G (30.45% of installed RAM) [OK] Maximum possible memory usage: 5.2G (33.50% of installed RAM) [OK] Slow queries: 0% (0/5M) [OK] Highest usage of available connections: 13% (25/192) [OK] Aborted connections: 0.00% (13/336886) [!!] Query cache is disabled [OK] Sorts requiring temporary tables: 0% (541 temp sorts / 459K sorts) [!!] Joins performed without indexes: 1584 [!!] Temporary tables created on disk: 33% (18K on disk / 53K total) [OK] Thread cache hit rate: 99% (3K created / 336K connections) [OK] Table cache hit rate: 108% (820 open / 759 opened) [OK] Open file limit used: 3% (302/10K) [OK] Table locks acquired immediately: 99% (6M immediate / 6M locks) -------- MyISAM Metrics ----------------------------------------------------- [OK] Key buffer used: 100.0% (134M used / 134M cache) [!!] Key buffer size / total MyISAM indexes: 128.0M/146.8M [!!] Read Key buffer hit rate: 91.4% (1M cached / 154K reads) [!!] Write Key buffer hit rate: 28.7% (353K cached / 251K writes) -------- 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% (252916 used/ 262134 total) [OK] InnoDB Read buffer efficiency: 99.99% (3902854432 hits/ 3903257123 total) [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total) [OK] InnoDB log waits: 0.00% (0 waits / 126188 writes) -------- AriaDB Metrics ----------------------------------------------------- [--] AriaDB is disabled. -------- 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 Temporary table size is already large - reduce result set size Reduce your SELECT DISTINCT queries without LIMIT clauses Variables to adjust: query_cache_size (>= 8M) join_buffer_size (> 2.0M, or always use indexes with joins) key_buffer_size (> 146.8M) 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 24, 2015 Author Share Posted September 24, 2015 Latest recommendations: >> MySQLTuner 1.6.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 [--] Skipped version check for MySQLTuner script [OK] Currently running supported 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: 37M (Tables: 108) [--] Data in InnoDB tables: 2G (Tables: 177) [--] Data in MEMORY tables: 0B (Tables: 2) [!!] Total fragmented tables: 42 -------- 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: 1d 1h 27m 36s (25M q [280.956 qps], 2M conn, TX: 1190B, RX: 9B) [--] Reads / Writes: 96% / 4% [--] Binary logging is disabled [--] Total buffers: 7.7G global + 8.9M per thread (192 max threads) [OK] Maximum reached memory usage: 8.2G (26.30% of installed RAM) [OK] Maximum possible memory usage: 9.3G (29.83% of installed RAM) [OK] Slow queries: 0% (0/25M) [OK] Highest usage of available connections: 33% (65/192) [OK] Aborted connections: 0.00% (6/2343867) [OK] Query cache efficiency: 43.9% (15M cached / 36M selects) [!!] Query cache prunes per day: 770446 [OK] Sorts requiring temporary tables: 0% (268 temp sorts / 92K sorts) [!!] Joins performed without indexes: 705 [OK] Temporary tables created on disk: 3% (738 on disk / 24K total) [OK] Thread cache hit rate: 99% (6K created / 2M connections) [OK] Table cache hit rate: 104% (612 open / 583 opened) [OK] Open file limit used: 2% (294/10K) [OK] Table locks acquired immediately: 100% (5M immediate / 5M locks) -------- MyISAM Metrics ----------------------------------------------------- [!!] Key buffer used: 22.2% (29M used / 134M cache) [OK] Key buffer size / total MyISAM indexes: 128.0M/6.6M [OK] Read Key buffer hit rate: 99.2% (345K cached / 2K reads) [!!] Write Key buffer hit rate: 1.6% (107K cached / 105K writes) -------- InnoDB Metrics ----------------------------------------------------- [--] InnoDB is enabled. [OK] InnoDB buffer pool / data size: 7.0G/2.1G [OK] InnoDB buffer pool instances: 7 [!!] InnoDB Used buffer: 40.15% (184207 used/ 458745 total) [OK] InnoDB Read buffer efficiency: 99.99% (2950889491 hits/ 2951068025 total) [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total) [OK] InnoDB log waits: 0.00% (0 waits / 81467 writes) -------- AriaDB Metrics ----------------------------------------------------- [--] AriaDB is disabled. -------- 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 Variables to adjust: query_cache_size (> 10M) join_buffer_size (> 8.0M, or always use indexes with joins) join_buffer_size isn't too big already? Current settings: [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 = 256M max_heap_table_size = 256M thread_handling = pool-of-threads ############################### # MyISAM # ############################### myisam_recover = BACKUP concurrent_insert = 2 ############################### # Query Cache Configuration # ############################### query_cache_limit = 128K query_cache_size = 10M query_cache_type = 1 ############################### # 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 = 7G 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=10000 ############################### # New fine tunning # ############################### symbolic-links=0 skip-name-resolve max-connect-errors=1000000 innodb_buffer_pool_instances=7 innodb_buffer_pool_dump_at_shutdown = 1 innodb_buffer_pool_load_at_startup = 1 innodb_flush_neighbors=0 aria_pagecache_buffer_size=256M table_open_cache=1000 join_buffer_size =8M read_buffer_size=128K sort_buffer_size=256K Link to comment Share on other sites More sharing options...
RevengeFNF Posted September 24, 2015 Share Posted September 24, 2015 I even think that 8Mb is to high, I use 2Mb. Link to comment Share on other sites More sharing options...
sobrenome Posted September 24, 2015 Author Share Posted September 24, 2015 Ok, I have reduced to 2 MB also. I guess there is no other tuning to do. Should I increase query_cache_size? Link to comment Share on other sites More sharing options...
h-y-b-r-i-d Posted November 16, 2015 Share Posted November 16, 2015 I have the same TTFB issues, do you fix them in the end? Link to comment Share on other sites More sharing options...
sobrenome Posted November 17, 2015 Author Share Posted November 17, 2015 Seems to be ok now. I have to change php from fastcgi to DSO and MPM Event to MPM Prefork. But if I could, I would test MPM Event with php-fpm. Link to comment Share on other sites More sharing options...
sobrenome Posted April 27, 2016 Author Share Posted April 27, 2016 Have you updated the configuration file to set aria_pagecache_buffer_size? https://mariadb.com/kb/en/mariadb/configuring-mariadb-for-optimal-performance/ Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.