Jump to content

MariaDB with XtraDB


sobrenome

Recommended Posts

  • Replies 132
  • Created
  • Last Reply
 >>  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.0.16-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: 1033)
[--] Data in InnoDB tables: 6G (Tables: 679)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[--] Data in MEMORY tables: 0B (Tables: 2)
[!!] Total fragmented tables: 208

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 4h 48m 25s (2M q [27.217 qps], 126K conn, TX: 56B, RX: 4B)
[--] Reads / Writes: 66% / 34%
[--] Total buffers: 6.3G global + 2.8M per thread (252 max threads)
[OK] Maximum possible memory usage: 7.0G (44% of installed RAM)
[OK] Slow queries: 0% (68/2M)
[OK] Highest usage of available connections: 21% (54/252)
[OK] Key buffer size / total MyISAM indexes: 128.0M/2.3G
[OK] Key buffer hit rate: 96.1% (14M cached / 549K reads)
[OK] Query cache efficiency: 26.5% (576K cached / 2M selects)
[!!] Query cache prunes per day: 194846
[OK] Sorts requiring temporary tables: 0% (7 temp sorts / 88K sorts)
[OK] Temporary tables created on disk: 23% (17K on disk / 76K total)
[OK] Thread cache hit rate: 99% (476 created / 126K connections)
[!!] Table cache hit rate: 1% (400 open / 32K opened)
[OK] Open file limit used: 3% (362/10K)
[OK] Table locks acquired immediately: 99% (3M immediate / 3M locks)
[!!] InnoDB  buffer pool / data size: 6.0G/6.7G
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    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 (> 64M)
    table_open_cache (> 400)
    innodb_buffer_pool_size (>= 6G)

 

Link to comment
Share on other sites

I might recommend something like this to use as a base configuration,

# MariaDB database server configuration file.

[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		= 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

I've lowered your maximum connections a bit, since your peak connection usage is only 52 at the moment it would probably be safe to lower this even further but you're not using that much memory per thread at the moment anyways.

I keep query cache disabled, as I don't believe it provides any real benefit for complex applications such as IP.Board (and can even potentially degrade performance.) Specifically, notice how many query cache prunes you have per day? This is because complex applications such as IP.Board can have a lot of complex queries that can't be cached properly and end up needing to be frequently flushed.

I've increased the InnoDB buffer pool to 8GB, which still gives you a little breathing room for scaling at 6.7GB of current usage.

This includes some various general purpose performance tweaks for InnoDB and MariaDB in general (e.g. O_DIRECT flushing and flushing the log once per second instead of at every transaction commit, thread pooling, making sure performance_schema is disabled (I can't remember if this is still enabled by default in the latest stable release of MariaDB but it degrades performance in production and should only be enabled when you're using it)).

Going off your previous MySQLtuner output, I'm assuming you're running on 16GB of total available memory. With this configuration, you'll be dedicated a little over half your available memory pool to MySQL. This should still allow plenty of room for other web services to run as long as everything else is properly configured.

I can't remember if you still need to do this with MariaDB 10 or not, but just to be safe, after updating your configuration please run the following commands to restart MySQL. You generally need to do this any time you change the innodb_log_file_size. (I think MariaDB 10 may have removed this requirement but I honestly can't remember)

mysql -uroot -p -e"SET GLOBAL innodb_fast_shutdown = 0"
service mysql stop
mv /var/lib/mysql/ib_logfile[01] /tmp
service mysql start
Link to comment
Share on other sites

Kirito, in Mariadb 10 we can change the logfile size without any problem.

I also think he should add this to my.cnf

innodb_buffer_pool_instances=8
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1

Instead of having a big 8Gb pool, he will have 8 pools of 1Gb each.
The other two is to when he restart Mariadb, there is no warm up time.

Also i think he should raise the Key Buffer Size to the size of Myisam cache. In this case, 2.3Gb.

Link to comment
Share on other sites

 

innodb_buffer_pool_instances=8
innodb_buffer_pool_dump_at_shutdown = 1
innodb_buffer_pool_load_at_startup = 1

Instead of having a big 8Gb pool, he will have 8 pools of 1Gb each

​The default value for innodb_buffer_pool_instances in MariaDB 10 is actually already 8. I don't mind having to let my forum warm up again after restarting, as restarts are so rare and warm up doesn't take that long anyways, but it's certainly a fair suggestion.

Link to comment
Share on other sites

I had one problem with this:

###############################
# Basic Settings              #
###############################
skip-external-locking
performance_schema	= off
bind-address		= 127.0.0.1

I had to remove this 3 settings for the service restart.

I don't know which one was causing the problem, but as they are the ones that I am not familiar with, I turned all 3 off.

Link to comment
Share on other sites

@Kirito i added thread_handling = pool-of-threads as you suggested because i have read very good thing about it, but now my Thread Cache is not caching. Is this normal with this option?

WORKER THREADS
Current thread_cache_size = 8
Current threads_cached = 0
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

 

PS:
Ok, Mariadb explain this:

Notes

The thread_cache_size system variable is not used when the thread pool is used and theThreads_cached status variable will have a value of 0.

 

So you can remove this from my.cnf, because Mariadb will not use it.

thread_cache_size	= 128
Link to comment
Share on other sites

You have set your innodb_log_file_size to 2G, which means you have 4Gb for logs.

In the Percona Blog they say the best log size is to contain 1 hour of transactions.

I did this for mine:

In the peak hour of your site, run the query: SHOW GLOBAL STATUS

Then search for the value of: innodb_lsn_current

Write the value in some place.

After exacly 1 minute, run again the SHOW GLOBAL STATUS and check the new value of innodb_lsn_current.

Now you need to subtract this last value with the first one, and you will get the amount of transaction to the log file in bytes for 1 minute.

Convert the value in Bytes to Mb. In my case it was 8Mb. Multiple that value with 60, and you get the trabnsaction in 1 hour. In my case its 480Mb. I'll use 512Mb.

So i just set the innodb_log_file_size to 256Mb and i have the log for 1 hour of transactions.

Link to comment
Share on other sites

The only negative impact of having a large buffer pool that I know of is that it makes recovery slower in the event of a crash. Quoting from the MySQL docs,

The size in bytes of each log file in a log group. The combined size of log files (innodb_log_file_size *innodb_log_files_in_group) cannot exceed a maximum value that is slightly less than 4GB. A pair of 2047 MB log files, for example, would allow you to approach the range limit but not exceed it. The default value is 5MB. Sensible values range from 1MB to 1/N-th of the size of the buffer pool, where N is the number of log files in the group. The larger the value, the less checkpoint flush activity is needed in the buffer pool, saving disk I/O. But larger log files also mean that recovery is slower in case of a crash.

32MB may be a perfectly fair value for your current traffic requirements, but keeping burst traffic and scaling in mind I would avoid setting this value too low, and leaving it at 1/4th the buffer pool I think should be fine unless you are constrained on storage and have a really large buffer pool.

Link to comment
Share on other sites

Last mysqltuner recommendations:

 >>  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.0.16-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: 1034)
[--] Data in InnoDB tables: 5G (Tables: 669)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[--] Data in MEMORY tables: 0B (Tables: 2)
[!!] Total fragmented tables: 191

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 21h 40m 43s (3M q [40.513 qps], 116K conn, TX: 44B, RX: 1B)
[--] Reads / Writes: 56% / 44%
[--] Total buffers: 8.2G global + 2.8M per thread (192 max threads)
[OK] Maximum possible memory usage: 8.7G (56% of installed RAM)
[OK] Slow queries: 0% (5/3M)
[OK] Highest usage of available connections: 4% (9/192)
[OK] Key buffer size / total MyISAM indexes: 128.0M/2.3G
[OK] Key buffer hit rate: 99.8% (8M cached / 17K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (31 temp sorts / 87K sorts)
[OK] Temporary tables created on disk: 22% (17K on disk / 77K total)
[OK] Thread cache hit rate: 98% (1K created / 116K connections)
[!!] Table cache hit rate: 2% (400 open / 16K opened)
[OK] Open file limit used: 16% (342/2K)
[OK] Table locks acquired immediately: 100% (5M immediate / 5M locks)
[OK] InnoDB buffer pool / data size: 8.0G/5.9G
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    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)
    table_open_cache (> 400)

 

Link to comment
Share on other sites

You can ignore the suggestion to enable query cache. I've seen mixed information regarding increasing table_open_cache, you can continually try and increase this and mysqltuner will just immediately tell you to increase it further. This is what I currently have configured, and you can try increasing this steadily yourself and see if it makes any noticeable improvements for you,

table_open_cache        = 4096
table_cache             = 4096
table_definition_cache  = 4096
Link to comment
Share on other sites

Raise your table_open_cache to at least 1000.

Also you have 1703 tables, so you need to set your table definition cache to at least 1703.

You have 22% of your temporary tables created on disk. You can create a tmpfs partition of 2GB, and set your mysql tmpdir to that partition. This way, even those 22% of temporary tables are created in your Ram. 

Link to comment
Share on other sites

You can ignore the suggestion to enable query cache. I've seen mixed information regarding increasing table_open_cache, you can continually try and increase this and mysqltuner will just immediately tell you to increase it further. This is what I currently have configured, and you can try increasing this steadily yourself and see if it makes any noticeable improvements for you,

table_open_cache        = 4096
table_cache             = 4096
table_definition_cache  = 4096

 

​Kirito, table_open_cache and table_cache(this one is deprecated) are the same thing ;)
He will not gain anything with the definition cache so high, because he only have 1703 tables. It will cache each .frm file.

Mysqltuner is dumb regarding the Table Caches. He can install Tuning Primer that will show him the real Tables cache opened.

Link to comment
Share on other sites

I have tried to change tmpdir="/mysqltmp" to a RAM directory but MySQL didn't restart.

chown mysql:mysql /mysqltmp
id mysql -> For you to know the id of mysql for the next line. In my example, the id is 27.
nano /etc/fstab
Add -> tmpfs /var/mysqltmp tmpfs rw,gid=27,uid=27,size=2G,nr_inodes=10k,mode=0700 0 0
mount -a
Add to my.cnf -> tmpdir = /mysqltmp

Link to comment
Share on other sites

I was reading the last blog posts from Peter in the Percona blog, and i noticed this two posts from him: 
http://www.percona.com/blog/2015/01/14/mysql-performance-implications-of-innodb-isolation-modes/
http://www.percona.com/blog/2015/01/19/looking-deeper-innodbs-problem-many-row-versions/

I then read this from Mariadb site: https://mariadb.com/kb/en/mariadb/set-transaction/

So, Read Commited might give some more performance than Repeatable Read(default).

I've added this to my.cnf and confirmed its using it:
transaction-isolation = READ-COMMITTED

MariaDB [(none)]> SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
+-----------------------+----------------+
| @@GLOBAL.tx_isolation | @@tx_isolation |
+-----------------------+----------------+
| READ-COMMITTED        | READ-COMMITTED |
+-----------------------+----------------+

Lets check if i notice some performance improvements.

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...