marklcfc Posted March 13, 2017 Posted March 13, 2017 Since changing from myisam to innodb my forum has been sluggish, the search index is going to take almost 4 days to complete. I tested a simple installation of IPS4 which took 10 minutes and importing a theme took 1 minute. It is also sluggish saving the theme. Clearly something is amiss. My server uses a Intel(R) Xeon(R) CPU X5550 @ 2.67GHz processor with 32GB of ram and ssd hard drives. I've read a post by @Dave MacDonald here https://invisionpower.com/forums/topic/430990-looking-for-mysql-help-happy-to-pay/#comment-2645151 where he gives his my.cnf file which apparently helped his forum. Can someone help me? This is my current my.cnf file. [mysqld] performance-schema=0 innodb_file_per_table=1 max_allowed_packet=268435456 open_files_limit=10000 #general_log local-infile=0 thread_cache_size=4 table_open_cache=1200 query_cache_size=16M key_buffer_size=3G default-storage-engine=innodb innodb_buffer_pool_size=8G innodb_buffer_pool_instances=8 #26/04/2016 tweaks max_connections=1000 interactive_timeout=300 wait_timeout=300 query_cache_type=0 table_open_cache=8500 join_buffer_size=256K #long_query_time = 1 #slow_query_log = 1 #log_queries_not_using_indexes =1 #slow_query_log_file = "/var/lib/mysql/slowquery.log"
Linguica Posted March 13, 2017 Posted March 13, 2017 I'm not a DBA, but for starters, it looks like your innodb_buffer_pool_size is currently 130 megabytes?? Pretty much every MySQL tuning guide will tell you that for a InnoDB database, your buffer pool size is the most important thing. It should ideally be big enough to store your entire database in RAM, and in general can take up to 70 or 80% of your system's memory.
marklcfc Posted March 13, 2017 Author Posted March 13, 2017 Sorry, not a good start. That is actually incorrect. I've corrected my first post now. plus -------- 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: 95.76% (502032 used/ 524280 total) [OK] InnoDB Read buffer efficiency: 100.00% (18051963354 hits/ 18052269657 total) [OK] InnoDB Write log efficiency: 95.95% (109985142 hits/ 114625893 total) [OK] InnoDB log waits: 0.00% (0 waits / 4640751 writes)
RevengeFNF Posted March 13, 2017 Posted March 13, 2017 2 minutes ago, marklcfc said: 10.0.30-MariaDB 7.0.16 PHP Can you upgrade to MariaDB 10.1? Not absolutely necessary but it came with some performance improvements. Try this configuration and tell me how it goes: [mysqld] user=mysql symbolic-links=0 max_connections=500 max_user_connections=500 wait_timeout=300 interactive_timeout=300 skip-name-resolve max-connect-errors=1000000 skip_networking = 1 #InnoDB innodb_file_per_table innodb_flush_method=O_DIRECT innodb_flush_log_at_trx_commit=0 innodb_buffer_pool_size =8G innodb_log_file_size=256M innodb_read_io_threads=4 innodb_write_io_threads=4 innodb_buffer_pool_instances=8 innodb_buffer_pool_dump_at_shutdown = 1 innodb_buffer_pool_load_at_startup = 1 transaction-isolation = READ-COMMITTED innodb_flush_neighbors=0 innodb_file_format=BARRACUDA innodb_large_prefix=1 binlog_format=MIXED innodb_autoinc_lock_mode=2 #MyISAM key_buffer_size=32M aria_pagecache_buffer_size=256M ft_min_word_len=3 #Thread Cache thread_handling = pool-of-threads #Table cache table_open_cache=1000 open_files_limit=2000 #Buffers join_buffer_size=2M read_buffer_size=128K sort_buffer_size=256K #Query Cache query_cache_type=1 query_cache_size=128M query_cache_limit=6M query_cache_strip_comments=1 #Temporary Tables tmp_table_size=256M max_heap_table_size=256M
marklcfc Posted March 13, 2017 Author Posted March 13, 2017 Yes, thanks. I will ask my host to update my.cnf file tomorrow and see what difference it makes, just wanted to check a few things: - Iit was mentioned above that buffer pool size should be big enough to store my database. My database is very close to 8gb now, so it 8g ok for that part? - should my max user connections stay at 1000 as sometimes I can get a huge traffic, a few weeks ago for example I had over 1000 active users within a 30 second period. - should innodb_file_per_table should it have a number as it doesn't have one in your list.
RevengeFNF Posted March 13, 2017 Posted March 13, 2017 Does not need a number. Max connections is diferent from users. Its the max simultaneously connections to mysql. Mysqltuner shows the max it used.
marklcfc Posted March 13, 2017 Author Posted March 13, 2017 5 hours ago, RevengeFNF said: innodb_file_format=BARRACUDA Do you know that this will work with my site?
RevengeFNF Posted March 13, 2017 Posted March 13, 2017 20 minutes ago, marklcfc said: Do you know that this will work with my site? Yes, it will.
marklcfc Posted March 13, 2017 Author Posted March 13, 2017 Ok I will try this tomorrow. One thing I noticed was table_open_cache is 1000 in your file compared to 8500 that my host set it as. I also saw in the next mariadb version it is 2000 as default.
RevengeFNF Posted March 13, 2017 Posted March 13, 2017 Yes, you can increase it. I don't have metrics of your site. Check with mysqltuner ao many open files it uses.
marklcfc Posted March 13, 2017 Author Posted March 13, 2017 Hard for me to make sense of it, hence this topic -------- Performance Metrics ------------------------------------------------- [--] Up for: 3d 7h 6m 58s (63M q [223.584 qps], 1M conn, TX: 229B, RX: 76B) [--] Reads / Writes: 85% / 15% [--] Total buffers: 11.0G global + 544.0K per thread (1000 max threads) [OK] Maximum possible memory usage: 11.6G (36% of installed RAM) [OK] Slow queries: 0% (42/63M) [OK] Highest usage of available connections: 9% (98/1000) [!!] None of your MyISAM tables are indexed - add indexes immediately [OK] Sorts requiring temporary tables: 0% (128 temp sorts / 3M sorts) [!!] Joins performed without indexes: 52628 [!!] Temporary tables created on disk: 31% (438K on disk / 1M total) [OK] Thread cache hit rate: 95% (64K created / 1M connections) [!!] Table cache hit rate: 10% (322 open / 3K opened) Use of uninitialized value $myvar{"table_cache"} in concatenation (.) or string at mysqltuner.pl line 851, <> line 2 (#1) [OK] Open file limit used: 1% (195/18K) [OK] Table locks acquired immediately: 99% (67M immediate / 67M locks) [OK] InnoDB data size / buffer pool: 5.2G/8.0G -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Upgrade MySQL to version 4+ to utilize query caching Adjust your join queries to always utilize indexes When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries without LIMIT clauses Increase table_cache gradually to avoid file descriptor limits Variables to adjust: join_buffer_size (> 256.0K, or always use indexes with joins) tmp_table_size (> 16M) max_heap_table_size (> 16M) table_cache (> )
ASTRAPI Posted March 14, 2017 Posted March 14, 2017 I am recommending you first to upgrade to a newer version of Mysql :-)
Recommended Posts
Archived
This topic is now archived and is closed to further replies.