LinuxQuestions.org
Share your knowledge at the LQ Wiki.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Server
User Name
Password
Linux - Server This forum is for the discussion of Linux Software used in a server related context.

Notices


Reply
  Search this Thread
Old 01-12-2016, 01:07 PM   #1
robertjinx
Member
 
Registered: Oct 2007
Location: Prague, CZ
Distribution: RedHat / CentOS / Ubuntu / SUSE / Debian
Posts: 749

Rep: Reputation: 73
Tuning mysql server (InnoDB engine)


Hello, I've got a mysql/mariadb setup, on a Xen guest. The machine has 4 cores, 8GB of RAM (8GB of swap space) and about 300GB of disk space (just a info).

I'm running MariaDB 10.1.10, having 6 databases (besides the mariadb system databases), all using InnoDB engine and consuming approx 600MB:

Quote:
+----------------------+-----------+
| Database | Size (MB) |
+----------------------+-----------+
| mydb12 | 585.75 |
| information_schema | 0.17 |
| mysql | 1.01 |
| performance_schema | 0.00 |
| xxxxxxxxxx_blog | 12.86 |
| xxxxxxxxxx_photos | 10.78 |
| mysql_user_db000 | 0.59 |
| mysql_user_db111 | 0.95 |
| mysql_user_db222 | 5.48 |
+----------------------+-----------+
My problem is that when running mysqltuner.pl, it complains about some settings (please ignore the 24h thing in the output, it happens the same even after 10 days of running), but I would like to find the perfect or close to perfect settings to my machine/setup, if possible.

Output of mysqltuner.pl:
Quote:
# mysqltuner.pl --buffers
>> MySQLTuner 1.6.2 - 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.10-MariaDB-buildpkg-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +Aria +CSV +InnoDB +MRG_MyISAM +SEQUENCE
[--] Data in InnoDB tables: 616M (Tables: 202)
[!!] Total fragmented tables: 2

-------- Security Recommendations -------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!

-------- CVE Security Recommendations -------------------------------------------
[--] Skipped due to --cvefile option undefined

-------- Performance Metrics -------------------------------------------------
[--] Up for: 5h 29m 42s (44K q [2.230 qps], 864 conn, TX: 25M, RX: 10M)
[--] Reads / Writes: 18% / 82%
[--] Binary logging is enabled (GTID MODE: OFF)
[--] Total buffers: 6.1G global + 5.9M per thread (20 max threads)
[--] Global Buffers
[--] +-- Key Buffer: 1.0M
[--] +-- Max Tmp Table: 4.0G
[--] Query Cache Buffers
[--] +-- Query Cache: OFF - DISABLED
[--] +-- Query Cache Size: 0B
[--] Per Thread Buffers
[--] +-- Read Buffer: 128.0K
[--] +-- Read RND Buffer: 1.0M
[--] +-- Sort Buffer: 512.0K
[--] +-- Thread stack: 240.0K
[--] +-- Join Buffer: 4.0M
[--] Binlog Cache Buffers
[--] +-- Binlog Cache: 512.0M
[OK] Maximum reached memory usage: 6.2G (79.04% of installed RAM)
[OK] Maximum possible memory usage: 6.3G (80.29% of installed RAM)
[OK] Slow queries: 0% (0/44K)
[OK] Highest usage of available connections: 15% (3/20)
[OK] Aborted connections: 0.00% (0/864)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2K sorts)
[!!] Temporary tables created on disk: 33% (453 on disk / 1K total)
[OK] Thread cache hit rate: 99% (3 created / 864 connections)
[OK] Table cache hit rate: 24% (285 open / 1K opened)
[OK] Open file limit used: 0% (57/81K)
[OK] Table locks acquired immediately: 100% (41K immediate / 41K locks)
[OK] Binlog cache memory access: 100.00% ( 884 Memory / 884 Total)

-------- MyISAM Metrics -----------------------------------------------------
[!!] Key buffer used: 20.8% (218K used / 1M cache)
[OK] Key buffer size / total MyISAM indexes: 1.0M/120.0K
[!!] Read Key buffer hit rate: 90.7% (375 cached / 35 reads)
[!!] Write Key buffer hit rate: 20.9% (215 cached / 170 writes)

-------- InnoDB Metrics -----------------------------------------------------
[--] InnoDB is enabled.
[--] InnoDB Buffers
[--] +-- InnoDB Buffer Pool: 2.0G
[--] +-- InnoDB Buffer Pool Instances: 2
[--] +-- InnoDB Additional Mem Pool: 8.0M
[--] +-- InnoDB Log Buffer: 4.0M
[--] +-- InnoDB Log Buffer Free: 11.3K
[--] +-- InnoDB Log Buffer Used: 128.0K
[OK] InnoDB buffer pool / data size: 2.0G/616.4M
[OK] InnoDB buffer pool instances: 2
[OK] InnoDB Used buffer: 91.18% (119515 used/ 131070 total)
[OK] InnoDB Read buffer efficiency: 99.98% (76324024 hits/ 76336904 total)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
[OK] InnoDB log waits: 0.00% (0 waits / 1046 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
MySQL started within last 24 hours - recommendations may be inaccurate
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)
My current my.cnf used:
Quote:
[client]
port = 3306
socket = /tmp/mysql.sock

[mysqld]
server-id = 1
port = 3306
bind-address = 127.0.0.1
default-storage-engine = InnoDB
socket = /tmp/mysql.sock
tmpdir = /tmp
datadir = /var/lib/mysql
log_error = /var/lib/mysql/mysql.log
pid_file = /var/lib/mysql/mysql.pid
max_connections = 20
character-set-server = utf8
collation-server = utf8_general_ci
skip-external-locking
open_files_limit = 81920
join_buffer_size = 4M
key_buffer_size = 1M
query_cache_size = 0
thread_cache_size = 4
thread_stack = 240k
table_cache = 20480
table_open_cache = 40960
table_definition_cache = 20480
max_allowed_packet = 1M
tmp_table_size = 4G
max_heap_table_size = 4G
sort_buffer_size = 512k
read_buffer_size = 128k
read_rnd_buffer_size = 1M
myisam_sort_buffer_size = 1M
myisam_max_sort_file_size = 1G
myisam_repair_threads = 1
myisam-recover-options
concurrent_insert = 2
sync_binlog = 1
expire_logs_days = 2
log-bin = mysql-bin
binlog_format = mixed
binlog_cache_size = 512M
binlog_direct_non_transactional_updates = true
long_query_time = 3
low_priority_updates = 1
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/mysql-slow.log
wait_timeout = 120
interactive_timeout = 120
symbolic-links = 0
sql_mode = NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER

# InnoDB settings
innodb = on
innodb_data_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /var/lib/mysql
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_buffer_pool_size = 2G
innodb_buffer_pool_instances = 2
innodb_log_file_size = 128M
innodb_log_buffer_size = 4M
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DSYNC
innodb_lock_wait_timeout = 60
innodb_file_per_table = 1
skip-innodb_doublewrite

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# safe-updates

[myisamchk]
key_buffer_size = 32M
sort_buffer_size = 32M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
log_error = /var/lib/mysql/mysql.log
pid_file = /var/lib/mysql/mysql.pid
Can anyone tell me the bestest way to configurate my mysql server for performance but also stability (data safety)?

From all those databases, one of them is for Gallery2, another is for a 'custom' project and rest are wordpress dbs.

P.S. most complains I get from mysqtuner.pl is this:

Quote:
[!!] Temporary tables created on disk: 33% (453 on disk / 1K total)
and no matter to how much I increase tmp_table_size/max_heap_table_size, I always get this. Same happens with open_files_limit/table_open_cache.

Any help is welcomed (I really dont know MariaDB/MySQL).
 
Old 01-14-2016, 12:03 PM   #2
Rinndalir
Member
 
Registered: Sep 2015
Posts: 733

Rep: Reputation: Disabled
First thing to do is say what the problem is and why you think it's a problem. Just a couple of sentences.
 
Old 01-15-2016, 01:56 AM   #3
robertjinx
Member
 
Registered: Oct 2007
Location: Prague, CZ
Distribution: RedHat / CentOS / Ubuntu / SUSE / Debian
Posts: 749

Original Poster
Rep: Reputation: 73
Sorry, I expressed myself badly.

I'm asking first of all, how to tuned my mysql/mariadb installation and second, should I take into account what mysqltuner.pl is telling me?
I can't put open_files to infinity the same with table_cache, don't know why it keeps on increasing the values.

Also, maybe the main question would be, how do I know all is OK?
 
Old 01-15-2016, 01:55 PM   #4
Rinndalir
Member
 
Registered: Sep 2015
Posts: 733

Rep: Reputation: Disabled
You first need to declare what problem you see and the symptoms you see.
 
Old 01-15-2016, 03:49 PM   #5
robertjinx
Member
 
Registered: Oct 2007
Location: Prague, CZ
Distribution: RedHat / CentOS / Ubuntu / SUSE / Debian
Posts: 749

Original Poster
Rep: Reputation: 73
I just wrote you that I don't know if there is a problem. There seems to be a problem according to mysqltuner.pl, but I wouldn't know if mysqltuner.pl is correct or not.

I can see mysqltuner.pl complains about :

Quote:
[!!] Temporary tables created on disk: 33 (453 on disk / 1K total)
[!!] Key buffer used: 20.8% (218K used / 1M cache)
[!!] Read Key buffer hit rate: 90.7% (375 cached / 35 reads)
[!!] Write Key buffer hit rate: 20.9% (215 cached / 170 writes)
[!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total)
As you can see mysqltuner.pl complains about this, but all the values are quite high and I have just 6 db's, which are not so much in use. Why does mysqltuner.pl complains about those issues? How do I fix them? Is mysqltuner.pl correct?
 
Old 01-15-2016, 04:35 PM   #6
Habitual
LQ Veteran
 
Registered: Jan 2011
Location: Abingdon, VA
Distribution: Catalina
Posts: 9,374
Blog Entries: 37

Rep: Reputation: Disabled
Checks performed by MySQLTuner is where I'd check.
 
Old 01-16-2016, 02:03 PM   #7
robertjinx
Member
 
Registered: Oct 2007
Location: Prague, CZ
Distribution: RedHat / CentOS / Ubuntu / SUSE / Debian
Posts: 749

Original Poster
Rep: Reputation: 73
Again, I'm asking if the information from mysqltuner.pl is correct, should I do what? Pointing me to mysqltuner.pl help page, doesn't help. I need to know if I should follow or not what mysqltuner.pl says?!
 
Old 01-16-2016, 02:55 PM   #8
Habitual
LQ Veteran
 
Registered: Jan 2011
Location: Abingdon, VA
Distribution: Catalina
Posts: 9,374
Blog Entries: 37

Rep: Reputation: Disabled
Quote:
Originally Posted by robertjinx View Post
Pointing me to mysqltuner.pl help page, doesn't help. I need to know if I should follow or not what mysqltuner.pl says?!
Do your own damn research.
 
Old 01-16-2016, 03:20 PM   #9
robertjinx
Member
 
Registered: Oct 2007
Location: Prague, CZ
Distribution: RedHat / CentOS / Ubuntu / SUSE / Debian
Posts: 749

Original Poster
Rep: Reputation: 73
Quote:
Originally Posted by Habitual View Post
Do your own damn research.
You can choose to shut up bro (meaning not to write), maybe you should.
 
Old 01-17-2016, 05:20 AM   #10
Habitual
LQ Veteran
 
Registered: Jan 2011
Location: Abingdon, VA
Distribution: Catalina
Posts: 9,374
Blog Entries: 37

Rep: Reputation: Disabled
I'm not your "bro".
My "bros" read documentation.

End Transmission.

Last edited by Habitual; 01-17-2016 at 05:57 AM.
 
Old 01-18-2016, 01:45 AM   #11
robertjinx
Member
 
Registered: Oct 2007
Location: Prague, CZ
Distribution: RedHat / CentOS / Ubuntu / SUSE / Debian
Posts: 749

Original Poster
Rep: Reputation: 73
Quote:
Originally Posted by Habitual View Post
I'm not your "bro".
Thank God and the Holy Spirit for that.

Quote:
Originally Posted by Habitual View Post
My "bros" read documentation.
First you need to have "bros" to read the documentation.
 
Old 01-18-2016, 02:13 AM   #12
sag47
Senior Member
 
Registered: Sep 2009
Location: Raleigh, NC
Distribution: Kubuntu x64, Raspbian, CentOS
Posts: 1,861
Blog Entries: 36

Rep: Reputation: 459Reputation: 459Reputation: 459Reputation: 459Reputation: 459
Seems to me you have a solution in search of a problem. If your websites are performant, and none of your clients (websites) are complaining, then why are you risking destabilizing the system by "tuning"?

Before you start blindly tunng; what metrics are you collecting? If you don't have any solid data on the performance of the system then no amount of tuning will help you. You must understand your problem, and yes, that does tend to mean you have to read documentation.
 
Old 01-18-2016, 02:29 AM   #13
robertjinx
Member
 
Registered: Oct 2007
Location: Prague, CZ
Distribution: RedHat / CentOS / Ubuntu / SUSE / Debian
Posts: 749

Original Poster
Rep: Reputation: 73
Quote:
Originally Posted by sag47 View Post
Seems to me you have a solution in search of a problem. If your websites are performant, and none of your clients (websites) are complaining, then why are you risking destabilizing the system by "tuning"?

Before you start blindly tunng; what metrics are you collecting? If you don't have any solid data on the performance of the system then no amount of tuning will help you. You must understand your problem, and yes, that does tend to mean you have to read documentation.
I never said I have a problem (at least I don't think I have, doesn't look like that), my question was/is if I can rely on what mysqltuner.pl says. How correct it is to use it for tuning the mysql server.
 
Old 01-18-2016, 02:47 AM   #14
sag47
Senior Member
 
Registered: Sep 2009
Location: Raleigh, NC
Distribution: Kubuntu x64, Raspbian, CentOS
Posts: 1,861
Blog Entries: 36

Rep: Reputation: 459Reputation: 459Reputation: 459Reputation: 459Reputation: 459
Quote:
Originally Posted by robertjinx View Post
I never said I have a problem (at least I don't think I have, doesn't look like that), my question was/is if I can rely on what mysqltuner.pl says. How correct it is to use it for tuning the mysql server.
I can't tell you if the script is properly reporting without reading the documentation myself (I'm not going to read it since I'm not using that script). This is the first I've heard of that script. Regardless, you should define what is important to you as an admin and your users; then start collecting metrics. After you get some data then you decide if the performance is in an acceptable range. Without a specific goal for performance or the websites aren't noticibly slow then it likely doesn't need more tuning. A quick web search for "collecting metrics for mysql" should render plenty of results to get you started down that path.

Again, as an admin, "if it ain't broke then don't fix it." That's what I meant by, "a solution in search of a problem." If you can't reliably identify your problem then you can't tune.

For most people MySQL defaults are good enough. If they aren't then you need to collect metrics and figure out why they're not good enough. I've discovered problems as obscure as poor programming of the website and just by changing the query the website "sped up."

Last edited by sag47; 01-18-2016 at 02:51 AM.
 
Old 01-18-2016, 03:12 AM   #15
robertjinx
Member
 
Registered: Oct 2007
Location: Prague, CZ
Distribution: RedHat / CentOS / Ubuntu / SUSE / Debian
Posts: 749

Original Poster
Rep: Reputation: 73
Quote:
Originally Posted by sag47 View Post
I can't tell you if the script is properly reporting without reading the documentation myself (I'm not going to read it since I'm not using that script). This is the first I've heard of that script. Regardless, you should define what is important to you as an admin and your users; then start collecting metrics. After you get some data then you decide if the performance is in an acceptable range. Without a specific goal for performance or the websites aren't noticibly slow then it likely doesn't need more tuning. A quick web search for "collecting metrics for mysql" should render plenty of results to get you started down that path.

Again, as an admin, "if it ain't broke then don't fix it." That's what I meant by, "a solution in search of a problem." If you can't reliably identify your problem then you can't tune.

For most people MySQL defaults are good enough. If they aren't then you need to collect metrics and figure out why they're not good enough. I've discovered problems as obscure as poor programming of the website and just by changing the query the website "sped up."
OK, thanks
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Server Slow : Apache Mysql fine tuning zahirw Linux - Server 2 04-22-2011 01:26 PM
Recover MySQL Innodb from /var/lib/mysql/ backup? eteck Linux - Server 1 01-31-2011 01:36 AM
How to change DATABASE ENGINE to innodb? Stephan_Craft Linux - Software 4 02-21-2010 09:48 PM
innodb engine varun_33 Linux - General 0 05-16-2005 05:45 AM
MySQL & InnoDB riluve Linux - Software 0 03-13-2005 09:19 PM

LinuxQuestions.org > Forums > Linux Forums > Linux - Server

All times are GMT -5. The time now is 02:58 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration