Jump to content

InnoDB mysql server configuration


Recommended Posts

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"

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 (> )

 

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...