Invision Community 4: SEO, prepare for v5 and dormant account notifications By Matt Monday at 02:04 PM
craigf136 Posted April 7, 2016 Posted April 7, 2016 I'm trying to make things on our site as fast as possible and we are now running pretty fairly quick but the first byte seems to be taken for ever. Our setup is as follows VPS with share of 24 processors (Intel(R) Xeon(R) CPU E5-2630 v2 @ 2.60GHz ) 5.575GB RAM 90GB SSD Nginx memcache pagespeed 10.1.13-MariaDB (mostly InnoDB) changing these over at present. I'm looking for feedback on the our output from sqltuner and webpagespeedtest on what I can do to get the firsty byte down speciflcally and also to tune sql as best as possible. www.callofdutyzombies.com http://www.webpagetest.org/result/160407_7V_10DS/1/details/https://gtmetrix.com/reports/www.callofdutyzombies.com/0rcmSh0o [mysqld] back_log = 75 max_connections = 300 key_buffer_size = 32M myisam_sort_buffer_size = 64M myisam_max_sort_file_size = 4096M join_buffer_size = 256K read_buffer_size = 256K sort_buffer_size = 512k read_rnd_buffer_size = 32M table_definition_cache = 4096 table_open_cache = 2048 thread_cache_size = 64 wait_timeout = 120 connect_timeout = 10 tmp_table_size = 16M max_heap_table_size = 16M max_allowed_packet=268435456 max_seeks_for_key = 1000 open_files_limit = 65535 group_concat_max_len = 1024 max_length_for_sort_data = 2048 net_buffer_length = 16384 max_connect_errors = 100000 concurrent_insert = 2 read_rnd_buffer_size = 512K bulk_insert_buffer_size = 8M query_cache_limit = 64M query_cache_size = 64M query_cache_type = 1 query_cache_min_res_unit = 2K query_prealloc_size = 262144 query_alloc_block_size = 65536 transaction_alloc_block_size = 8192 transaction_prealloc_size = 4096 default-storage-engine = MyISAM slow_query_log=1 slow_query_log_file="/var/log/mysql/mysql-slow.log" long_query_time = 1 log-queries-not-using-indexes innodb_file_per_table=1 >> 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.1.13-MariaDB [!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Aria +CSV +InnoDB +MRG_MyISAM +SEQUENCE [--] Data in MyISAM tables: 5G (Tables: 2217) [--] Data in InnoDB tables: 566M (Tables: 591) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52) [--] Data in MEMORY tables: 0B (Tables: 8) [!!] Total fragmented tables: 53 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 1d 17h 56m 34s (4M q [26.779 qps], 77K conn, TX: 22B, RX: 1B) [--] Reads / Writes: 74% / 26% [--] Total buffers: 264.0M global + 1.8M per thread (300 max threads) [OK] Maximum possible memory usage: 798.7M (13% of installed RAM) [OK] Slow queries: 1% (56K/4M) [OK] Highest usage of available connections: 8% (24/300) [OK] Key buffer size / total MyISAM indexes: 32.0M/2.0G [OK] Key buffer hit rate: 99.7% (97M cached / 330K reads) [OK] Query cache efficiency: 34.1% (1M cached / 3M selects) [!!] Query cache prunes per day: 54527 [OK] Sorts requiring temporary tables: 0% (17 temp sorts / 49K sorts) [!!] Temporary tables created on disk: 68% (325K on disk / 477K total) [OK] Thread cache hit rate: 99% (24 created / 77K connections) [OK] Table cache hit rate: 27% (2K open / 7K opened) [OK] Open file limit used: 3% (2K/65K) [OK] Table locks acquired immediately: 99% (2M immediate / 2M locks) [!!] InnoDB buffer pool / data size: 128.0M/566.8M [OK] InnoDB log waits: 0 -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries without LIMIT clauses Variables to adjust: query_cache_size (> 64M) tmp_table_size (> 16M) max_heap_table_size (> 16M) innodb_buffer_pool_size (>= 566M)
RevengeFNF Posted April 7, 2016 Posted April 7, 2016 Yu are using an outdated version of mysqltuner. The last one is 1.6.9. You should update it. You also need to set the innodb buffer pool, you are only using 13% of your Ram available.
ASTRAPI Posted April 7, 2016 Posted April 7, 2016 First issue: [!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM also you are missing a few Innodb optimization important entries like capacity and threads and your buffer also is very small.... You need an optimization to fix also the Query cache prunes per day but if you convert all tables to Innodb it is better to disable it.... Using the latest mysqltuner as Revenge FNF recommend you, you will see that recommendation as an official one
craigf136 Posted April 7, 2016 Author Posted April 7, 2016 I just made the switch to MariaDB 2 days ago and I've started switching but some of the tables keys which are too big. What's the best way to update? I presume I'll need to go through host to organise the update, which might require a change to a new VPS with centos7 which is only 64 bit? Warning: #1071 Specified key was too long; max key length is 767 bytes Most recent sqltuner report (latest version). >> MySQLTuner 1.6.9 - 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.1.13-MariaDB [!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE [--] Data in MyISAM tables: 7G (Tables: 2216) [--] Data in InnoDB tables: 692M (Tables: 592) [--] Data in MEMORY tables: 0B (Tables: 8) [!!] Total fragmented tables: 45 -------- Security Recommendations ------------------------------------------- [OK] There are no anonymous accounts for any database users [OK] All database users have passwords assigned [--] There are 605 basic passwords in the list. -------- CVE Security Recommendations --------------------------------------- [--] Skipped due to --cvefile option undefined -------- Performance Metrics ------------------------------------------------- [--] Up for: 1d 22h 3m 23s (4M q [26.447 qps], 85K conn, TX: 22G, RX: 1G) [--] Reads / Writes: 74% / 26% [--] Binary logging is disabled [--] Total buffers: 392.0M global + 1.8M per thread (300 max threads) [--] P_S Max memory usage: 0B [--] Galera GCache Max memory usage: 0B [OK] Maximum reached memory usage: 434.8M (7.38% of installed RAM) [OK] Maximum possible memory usage: 926.7M (15.74% of installed RAM) [OK] Slow queries: 1% (61K/4M) [OK] Highest usage of available connections: 8% (24/300) [OK] Aborted connections: 0.00% (2/85981) [!!] Query cache should be disabled by default due to mutex contention. [OK] Sorts requiring temporary tables: 0% (17 temp sorts / 55K sorts) [!!] Temporary tables created on disk: 68% (357K on disk / 523K total) [OK] Thread cache hit rate: 99% (24 created / 85K connections) [!!] Table cache hit rate: 4% (2K open / 42K opened) [OK] Open file limit used: 4% (3K/65K) [OK] Table locks acquired immediately: 99% (2M immediate / 2M locks) -------- ThreadPool Metrics -------------------------------------------------- [--] ThreadPool stat is enabled. [--] Thread Pool Size: 24 thread(s). [--] Using default value is good enougth for your version (10.1.13-MariaDB) -------- Performance schema -------------------------------------------------- [--] Performance schema is disabled. [--] Memory used by P_S: 0B -------- MyISAM Metrics ------------------------------------------------------ [OK] Key buffer used: 100.0% (33M used / 33M cache) [OK] Key buffer size / total MyISAM indexes: 32.0M/1.9G [OK] Read Key buffer hit rate: 99.5% (105M cached / 575K reads) [!!] Write Key buffer hit rate: 1.8% (14M cached / 13M writes) -------- AriaDB Metrics ------------------------------------------------------ [--] AriaDB is enabled. [OK] Aria pagecache size / total Aria indexes: 128.0M/1B [!!] Aria pagecache hit rate: 26.2% (483K cached / 356K reads) -------- InnoDB Metrics ------------------------------------------------------ [--] InnoDB is enabled. [!!] InnoDB buffer pool / data size: 128.0M/692.7M [!!] InnoDB buffer pool <= 1G and innodb_buffer_pool_instances(!=1). [OK] InnoDB Used buffer: 87.50% (7167 used/ 8191 total) [OK] InnoDB Read buffer efficiency: 99.85% (161231026 hits/ 161472622 total) [OK] InnoDB Write log efficiency: 97.61% (7546197 hits/ 7730872 total) [OK] InnoDB log waits: 0.00% (0 waits / 184675 writes) -------- TokuDB Metrics ------------------------------------------------------ [--] TokuDB is disabled. -------- Galera Metrics ------------------------------------------------------ [--] Galera is disabled. -------- Replication Metrics ------------------------------------------------- [--] Galera Synchronous replication: NO [--] No replication slave(s) for this server. [--] This is a standalone server. -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries which have no LIMIT clause Increase table_open_cache gradually to avoid file descriptor limits Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C Beware that open_files_limit (65535) variable should be greater than table_open_cache ( 2048) Variables to adjust: query_cache_type (=0) tmp_table_size (> 16M) max_heap_table_size (> 16M) table_open_cache (> 2048) innodb_buffer_pool_size (>= 692M) if possible. innodb_buffer_pool_instances (=1)
ASTRAPI Posted April 7, 2016 Posted April 7, 2016 Edit your my.cnf and add: innodb_large_prefix = 1 then just restart mysql and you are good to go
RevengeFNF Posted April 7, 2016 Posted April 7, 2016 First thing first. Like Astrapi said, you need to format your server and install a 64 Bit OS. Your server has 6Gb of Ram and you can only use half of that. From the size of your database, its very important for you to be able to use the 6Gb, so make a favor to yourself and reinstall a 64 bit version. You have way too many temp tables created on disk, which is slow. You should rise tmp_table_size and max_heap_table_size. I use 256Mb in my IPS4 site. You need to rise innodb_buffer_pool. When you are using a 64Bit OS and all your tables are in Innodb, set it to 4Gb.
craigf136 Posted April 7, 2016 Author Posted April 7, 2016 9 minutes ago, ASTRAPI said: Edit your my.cnf and add: innodb_large_prefix = 1 then just restart mysql and you are good to go Thanks, did that now got the following, any suggestions? I assume I need to add something else to my.cnf but would appreciate recommendation. #1709 - Index column size too large. The maximum column size is 767 bytes.
ASTRAPI Posted April 7, 2016 Posted April 7, 2016 Add also: innodb-file-format=barracuda restart mysql and see
RevengeFNF Posted April 7, 2016 Posted April 7, 2016 innodb_large_prefix will only work if the file format is set to barracuda and using dynamic or compressed. If you are using the default value, then the file format is Antelope.
craigf136 Posted April 8, 2016 Author Posted April 8, 2016 Thank's @ASTRAPI @RevengeFNF, I have bought a new package running centos7 which we will be migrating too over the next day or so, once we have migrated I will then run sqltuner and carry out a few tweaks. Any recommendations on the above (my.cnf) or any recommendations on nginx adjustments?
ASTRAPI Posted April 8, 2016 Posted April 8, 2016 Every server needs another settings so run it on the new server so we can provide recommendations. But there are a lot more to optimize than mysqltunner's output I can't provide more details as i am offering it as a service but i am sure many members will help you after posting here
craigf136 Posted April 8, 2016 Author Posted April 8, 2016 44 minutes ago, ASTRAPI said: I can't provide more details as i am offering it as a service but i am sure many members will help you after posting here I'll drop you a PM, I might get some advise/help for optimising on the new setup - obviously we'll pay for the assistance.
craigf136 Posted April 14, 2016 Author Posted April 14, 2016 Well we have done a few optimising aspects after switching to the new server, unfortunately when the hosts processed the migration they forgot to migrate the MySQL users resulting in a little downtime but hey ho. Anyway, we have applied the relevant changes, I have redone our my.cnf file and we have had it running now for little over a day (couple of restarts since we migrated, or it would be longer). [mysql] # CLIENT # port = **removed** socket = /var/lib/mysql/mysql.sock [mysqld] # GENERAL # user = mysql default-storage-engine = InnoDB socket = /var/lib/mysql/mysql.sock pid-file = /var/lib/mysql/mysql.pid # MyISAM # key-buffer-size = 1024M myisam-recover = FORCE,BACKUP # SAFETY # max-allowed-packet = 16M max-connect-errors = 1000000 # DATA STORAGE # datadir = /var/lib/mysql/ # BINARY LOGGING # log-bin = /var/lib/mysql/mysql-bin expire-logs-days = 14 sync-binlog = 1 # CACHES AND LIMITS # tmp-table-size = 512M max-heap-table-size = 768M query-cache-type = 0 query-cache-size = 4M max-connections = 30 thread-cache-size = 50 open-files-limit = 65535 table-definition-cache = 4096 table-open-cache = 10240 # INNODB # innodb-flush-method = O_DIRECT innodb-log-files-in-group = 2 innodb-log-file-size = 256M innodb-flush-log-at-trx-commit = 1 innodb-file-per-table = 1 innodb-buffer-pool-size = 4G # LOGGING # log-error = /var/lib/mysql/mysql-error.log log-queries-not-using-indexes = 1 slow-query-log = off slow-query-log-file = /var/lib/mysql/mysql-slow.log long_query_time = 10 max_allowed_packet =268435456 open_files_limit =10000 This is the most recent result from MySQL Tuning Primer -- MYSQL PERFORMANCE TUNING PRIMER -- - By: Matthew Montgomery - MySQL Version 5.6.29-log x86_64 Uptime = 1 days 9 hrs 29 min 11 sec Avg. qps = 28 Total Questions = 3394648 Threads Connected = 2 Warning: Server has not been running for at least 48hrs. It may not be safe to use these recommendations SLOW QUERIES The slow query log is NOT enabled. Current long_query_time = 10.000000 sec. You have 702552 out of 3394670 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 enabled WORKER THREADS Current thread_cache_size = 50 Current threads_cached = 14 Current threads_per_sec = 0 Historic threads_per_sec = 0 Your thread_cache_size is fine MAX CONNECTIONS Current max_connections = 30 Current threads_connected = 2 Historic max_used_connections = 16 The number of used connections is 53% of the configured maximum. Your max_connections variable seems to be fine. INNODB STATUS Current InnoDB index space = 377 M Current InnoDB data space = 1.35 G Current InnoDB buffer pool free = 83 % Current innodb_buffer_pool_size = 4.00 G 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 : 4.05 G Configured Max Per-thread Buffers : 34 M Configured Max Global Buffers : 4.03 G Configured Max Memory Limit : 4.06 G Physical Memory : 5.75 G Max memory limit seem to be within acceptable norms KEY BUFFER Current MyISAM index space = 1.84 G Current key_buffer_size = 16 M Key cache miss rate is 1 : 409 Key buffer free ratio = 0 % You could increase key_buffer_size It is safe to raise this up to 1/4 of total system memory; assuming this is a dedicated database server. QUERY CACHE Query cache is enabled Current query_cache_size = 4 M Current query_cache_used = 16 K Current query_cache_limit = 1 M Current Query cache Memory fill ratio = .41 % 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 = 256 K Current read_rnd_buffer_size = 256 K Sort buffer seems to be fine JOINS Current join_buffer_size = 260.00 K You have had 2311 queries where a join could not use an index properly You should enable "log-queries-not-using-indexes" Then look for non indexed joins in the slow query log. If you are unable to optimize your queries you may want to increase your join_buffer_size to accommodate larger joins in one pass. Note! This script will still suggest raising the join_buffer_size when ANY joins not using indexes are found. OPEN FILES LIMIT Current open_files_limit = 65536 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 = 10240 tables Current table_definition_cache = 4096 tables You have a total of 3247 tables You have 3302 open tables. The table_cache value seems to be fine TEMP TABLES Current max_heap_table_size = 388 M Current tmp_table_size = 256 M Of 775075 temp tables, 43% were created on disk Perhaps you should increase your tmp_table_size and/or max_heap_table_size to reduce the number of disk-based temporary tables Note! BLOB and TEXT columns are not allow in memory tables. If you are using these columns raising these values might not impact your ratio of on disk temp tables. TABLE SCANS Current read_buffer_size = 128 K Current table scan ratio = 104 : 1 read_buffer_size seems to be fine TABLE LOCKING Current Lock Wait ratio = 1 : 508180 Your table locking seems to be fine I haven't enabled cloudflare yet but we are better, I need to try and reduce the first byte reponse but that will be next after looking at optimizing Nginx and will be looking at the post above by Makoto. I just thought I would let people see what we are doing, what changes have implemented and where we are now. Our First Byte is reported as the following by http://www.bytecheck.com/ Your Time To First Byte is: 0.329 seconds Extended Information: Field Data Url Effective HTTP://callofdutyzombies.com Http Code 301 Connect Time 000 Total Time 0.329 Time Namelookup 0.154 Time Connect 0.199 Time Pretransfer 0.199 Time Redirect 0.000 Time To First Byte 0.329 Size Header 440 Size Request 85 Speed Download 0.000 Speed Upload 0.000 Content Type ;charset=UTF-8 Num Connects 1 GT Metrix https://gtmetrix.com/reports/www.callofdutyzombies.com/Yo01pmt0 Webpagetest http://www.webpagetest.org/result/160414_89_K1D/ Overall I'm a lot happier now, but I'm still looking to get even faster which comes with using a CDN and further optimising and nginx set up for php-fpm
Recommended Posts
Archived
This topic is now archived and is closed to further replies.