mysql optimization

[Adam Howard]

When it comes to mysql I usually stick to the policy of; if it's not broken, why fix it?! But out of curiosity (or stupidity), I've decided to play around with it a bit. Could use some basic feedback. Server is dedicated, but nothing overly done.

Intel Core2Duo E8300 (2 x 2.83GHz)
4GB DDR2
80GB SATA

This is the default my.conf (currently in use)

[client]
port=3306
socket=/var/run/mysqld/mysqld.sock

[mysqld_safe]
socket=/var/run/mysqld/mysqld.sock

[mysqld]
user=mysql
pid-file=/var/run/mysqld/mysqld.pid
socket=/var/run/mysqld/mysqld.sock
port=3306
basedir=/usr
datadir=/var/lib/mysql
tmpdir=/tmp
lc-messages-dir=/usr/share/mysql
log_error=/var/log/mysql/error.log
max_connections=200
max_user_connections=30
wait_timeout=30
interactive_timeout=50
long_query_time=5
innodb_file_per_table

!includedir /etc/mysql/conf.d/

This is the one I generated (thanks to Percona)

[mysql]

# CLIENT #
port  = 3306
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  = 32M
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  = 32M
max-heap-table-size  = 32M
query-cache-type  = 0
query-cache-size  = 0
max-connections  = 500
thread-cache-size  = 50
open-files-limit  = 65535
table-definition-cache  = 4096
table-open-cache  = 4096

# INNODB #
innodb-flush-method  = O_DIRECT
innodb-log-files-in-group  = 2
innodb-log-file-size  = 128M
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table  = 1
innodb-buffer-pool-size  = 2G

# LOGGING #
log-error  = /var/lib/mysql/mysql-error.log
log-queries-not-using-indexes  = 1
slow-query-log  = 1
slow-query-log-file  = /var/lib/mysql/mysql-slow.log

Thoughts and feedback is always welcome

 

[Floren]

This is not the way to optimize MySQL. Posting a configuration with some values will not help anyone, you have to do it yourself. Did you looked at peak time what is going on for last few days? I'll give the method George (eva2000) uses: For 2 days, he logs everything on server and then he analyses the obtained data. Then he makes changes to MySQL config files and logs again everything for another 2 days. This process is repeated until everything is optimized.

You will have to repeat this process every few months as MySQL is completely dynamic, what you set now as values will not be good anymore in 6 months.

 

[Adam Howard]

This is not the way to optimize MySQL. Posting a configuration with some values will not help anyone, you have to do it yourself. Did you looked at peak time what is going on for last few days? I'll give the method George (eva2000) uses: For 2 days, he logs everything on server and then he analyses the obtained data. Then he makes changes to MySQL config files and logs again everything for another 2 days. This process is repeated until everything is optimized.

You will have to repeat this process every few months as MySQL is completely dynamic, what you set now as values will not be good anymore in 6 months.

Your post is not helpful or productive.

It's almost bate`ish (trollish) in my opinion considering many people have done as I have done (search) and have been given some feedback on their configuration. And yet here you are replying, 'no help for you, you're on your own'.

If you wish not to contribute.... Don't.

 

[Floren]

I'm trying to give you the proper guidelines how to optimize MySQL. If it would that easy to tune MySQL, Percona and other expensive consultants will not exist. Take it the way you like it.

 

[Sheratan]

Optimizing mysql is not a one day job. Like floren said, you had to see the log, siege it, analyses the performance and impact to you database, then see the log again and change again. Do it over and over again to find the best configuration.

Last time, I need to spend a week just to find the right configuration for 512MB RAM VPS

But for some basic optimization, my don't you show us your mysqltuner result?

 

[Adam Howard]

Optimizing mysql is not a one day job. Like floren said, you had to see the log, siege it, analyses the performance and impact to you database, then see the log again and change again. Do it over and over again to find the best configuration.

Last time, I need to spend a week just to find the right configuration for 512MB RAM VPS

But for some basic optimization, my don't you show us your mysqltuner result?

@Sheratan Thank you for your reply and for offering to help and providing some information I can actually use and apply.

Also thank you as well as reminding me about mysqltuner, it's been a while (more than a year) since I've used that script (totally forgot it existed) or attempted to do more depth tweaking of mySQL.

Thank you

This is what I got from that script

>>  MySQLTuner 1.3.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
[OK] Logged in using credentials from debian maintenance account.
[OK] Currently running supported MySQL version 5.6.17-1~dotdeb.1
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 152M (Tables: 6)
[--] Data in InnoDB tables: 824M (Tables: 378)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[--] Data in MEMORY tables: 496K (Tables: 9)
[!!] Total fragmented tables: 44

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 18h 24m 48s (470K q [7.093 qps], 18K conn, TX: 20B, RX: 338M)
[--] Reads / Writes: 86% / 14%
[--] Total buffers: 169.0M global + 1.1M per thread (200 max threads)
[OK] Maximum possible memory usage: 394.0M (10% of installed RAM)
[OK] Slow queries: 0% (14/470K)
[OK] Highest usage of available connections: 3% (7/200)
[OK] Key buffer size / total MyISAM indexes: 8.0M/113.0M
[OK] Key buffer hit rate: 98.5% (172K cached / 2K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (4 temp sorts / 26K sorts)
[!!] Temporary tables created on disk: 35% (4K on disk / 12K total)
[OK] Thread cache hit rate: 99% (7 created / 18K connections)
[!!] Table cache hit rate: 13% (405 open / 3K opened)
[OK] Open file limit used: 1% (11/1K)
[OK] Table locks acquired immediately: 99% (809K immediate / 809K locks)
[!!] InnoDB  buffer pool / data size: 128.0M/824.2M
[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
  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
  Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
  query_cache_type (=1)
  tmp_table_size (> 16M)
  max_heap_table_size (> 16M)
  table_cache (> 407)
  innodb_buffer_pool_size (>= 824M)

Thank you again in advance

 

[Sheratan]

How much your free memory and swap usage in peak time?

 

[Adam Howard]

How much your free memory and swap usage in peak time?

Naturally, I would much prefer to avoid using any swap. This is one of the reasons that make me think maybe I should do some tweaking.

My new dedicated server has been online for only a day, but since I am starting to hit the swap; I figured I needed to do something before reaching a peek usage time. Current swap used is 12m (virtually nothing, but doubt it will stay nothing for long)

 

[Sheratan]

So it's a brand new server, 18h uptime, and no information about peak usage.

I see you are using 2GB for InnoDB buffer and 500 max concurrent, yet mysqltuner result said you are using 128M and 200 max concurrent. Do you use that percona generated conf file?

 

[Floren]

@Adam Howard, that does not mean necessarily MySQL is the source of your swapping.
Use the normal top command and sort the results by swap usage. Or use smem for a detailed output.

# top -M

While viewing top, Shift + O > press P > press Enter > press C to show the executed commands.

 

[Adam Howard]

So it's a brand new server, 18h uptime, and no information about peak usage.

I see you are using 2GB for InnoDB buffer and 500 max concurrent, yet mysqltuner result said you are using 128M and 200 max concurrent. Do you use that percona generated conf file?

No, I have not yet applied the percona generated config. I generated it at the time of this posting (only a short while ago) and was waiting for late at night to apply it.... Just in case something did go wrong and fewer people would notice any downtime (it's 11:40 pm now, so I'll likely being doing so).

I was generally hoping for just a little feedback between what I'm using now vs what percona provided and maybe get an understanding of the values that they seem to be missing between them.

 

[Adam Howard]

@Adam Howard, that does not mean necessarily MySQL is the source of your swapping.
Use the normal top command and sort the results by swap usage. Or use smem for a detailed output.

While viewing top, Shift + O > press P > press Enter > press C to show the executed commands.

With respects, your command do not work.

Help for Interactive Commands - procps-ng version 3.3.3
Window 1:Def: Cumulative mode Off.  System: Delay 3.0 secs; Secure mode Off.

  Z,B  Global: 'Z' change color mappings; 'B' disable/enable bold
  l,t,m  Toggle Summaries: 'l' load avg; 't' task/cpu stats; 'm' mem info
  1,I  Toggle SMP view: '1' single/separate states; 'I' Irix/Solaris mode
  f,F  Manage Fields: add/remove; change order; select sort field

  L,&,<,> . Locate: 'L'/'&' find/again; Move sort column: '<'/'>' left/right
  R,H,V  . Toggle: 'R' norm/rev sort; 'H' show threads; 'V' forest view
  c,i,S  . Toggle: 'c' cmd name/line; 'i' idle tasks; 'S' cumulative time
  x,y  . Toggle highlights: 'x' sort field; 'y' running tasks
  z,b  . Toggle: 'z' color/mono; 'b' bold/reverse (only if 'x' or 'y')
  u,U  . Show: 'u' effective user; 'U' real, saved, file or effective user
  n or #  . Set maximum tasks displayed
  C,...  . Toggle scroll coordinates msg for: up,down,left,right,home,end

  k,r  Manipulate tasks: 'k' kill; 'r' renice
  d or s  Set update interval
  W  Write configuration file
  q  Quit
  ( commands shown with '.' require a visible task display window )
Press 'h' or '?' for help with Windows,
any other key to continue

 

[Floren]

@Tracy Perry, what are the proper commands to get in Debian what I posted earlier related to top?

 

[Sheratan]

No, I have not yet applied the percona generated config. I generated it at the time of this posting (only a short while ago) and was waiting for late at night to apply it.... Just in case something did go wrong and fewer people would notice any downtime (it's 11:40 pm now, so I'll likely being doing so).

I was generally hoping for just a little feedback between what I'm using now vs what percona provided and maybe get an understanding of the values that they seem to be missing between them.

This is a quick suggestion: Try to use that generated configuration, but before that, change:

max-connections from 500 to 200 (500 max concurrent with 2GB buffer pool is "something" in this case - no information about peak)

Watch out with mysql pid and socket.

Restart your mysql, and then log your ram usage, swap usage, mysql connection (it's easy check that in phpMyAdmin > status > max. concurrent connections) and cpu load during peak time for three days (and no mysql restart), better to manually log that every 6 hours.

After that, run mysqltuner again and post the result and the log in here. (That means go back to this thread 3 days from now )

 

[Adam Howard]

This is a quick suggestion: Try to use that generated configuration, but before that, change:

max-connections from 500 to 200 (500 max concurrent with 2GB buffer pool is "something" in this case - no information about peak)

Watch out with mysql pid and socket.

Restart your mysql, and then log your ram usage, swap usage, mysql connection (it's easy check that in phpMyAdmin > status > max. concurrent connections) and cpu load during peak time for three days (and no mysql restart), better to manually log that every 6 hours.

After that, run mysqltuner again and post the result and the log in here. (That means go back to this thread 3 days from now )

I had just applied the one generated, but am using the 500 connections... Did it before seeing this post and was doing my update based upon your previous inquire. So 500 is to much I assume, even with 2GB?

 

[Sheratan]

I had just applied the one generated, but am using the 500 connections... Did it before seeing this post and was doing my update based upon your previous inquire. So 500 is to much I assume, even with 2GB?

Can't say. Depends of your peak usage, how big is you database, how many database you had, how many RAM will your PHP use, etc etc.

But if you are in doubt, you can use 300 connection.

 

[TPerry]

Your post is not helpful or productive.
If you wish not to contribute.... Don't.

Actually @Floren is 100% correct. What may be great today may not be great in 2 weeks. The demand on mysql will change dependent upon many factors and to keep it optimized is a non-stop process.

 

[TPerry]

@Tracy Perry, what are the proper commands to get in Debian what I posted earlier related to top?

F to pull up the menu, scroll down to swap and press lowercase s. The rest should be the same (using C for executed commands).

Fields Management for window 1:Def, whose current sort field is SWAP
   Navigate with Up/Dn, Right selects for move then <Enter> or Left commits,
   'd' or <Space> toggles display, 's' sets sort.  Use 'q' or <Esc> to end!

* PID     = Process Id       
* USER    = Effective User Name
* PR      = Priority         
* NI      = Nice Value       
* VIRT    = Virtual Image (KiB)
* RES     = Resident Size (KiB)
* SHR     = Shared Memory (KiB)
* S       = Process Status   
* %CPU    = CPU Usage        
* %MEM    = Memory Usage (RES)
* TIME+   = CPU Time, hundredths
* COMMAND = Command Name/Line
* PPID    = Parent Process pid
  UID     = Effective User Id
* RUID    = Real User Id     
* RUSER   = Real User Name   
  SUID    = Saved User Id    
  SUSER   = Saved User Name  
  GID     = Group Id         
  GROUP   = Group Name       
  PGRP    = Process Group Id 
  TTY     = Controlling Tty  
  TPGID   = Tty Process Grp Id
  SID     = Session Id       
  nTH     = Number of Threads
  P       = Last Used Cpu (SMP)
  TIME    = CPU Time         
* SWAP    = Swapped Size (KiB)
  CODE    = Code Size (KiB)  
  DATA    = Data+Stack (KiB) 
  nMaj    = Major Page Faults
  nMin    = Minor Page Faults
  nDRT    = Dirty Pages Count
  WCHAN   = Sleeping in Function
  Flags   = Task Flags <sched.h>
  CGROUPS = Control Groups   
  SUPGIDS = Supp Groups IDs  
  SUPGRPS = Supp Groups Names
  TGID    = Thread Group Id

 

[Adam Howard]

Actually @Floren is 100% correct. What may be great today may not be great in 2 weeks. The demand on mysql will change dependent upon many factors and to keep it optimized is a non-stop process.

Which is great if you know what you're looking for or what to do, which at the moment.. I do not. As stated in the 1st post, I usually leave such things 'as is' and do not bother to tweak anything.

And whole point of this thread was to ask for some advise and maybe guidance and someone telling me, 'no help for you, you're on your own' ... Is neither helpful or productive (it's actually quite condescending).

So right now, I am very thankful and greatful that @Sheratan came into this thread and he's earned not only my thanks, but also my respect.

Thank you very much, Sheratan for your common curtsy, feedback, advise, and general willingness to help another member here within XenForo.

 

[TPerry]

That will work as a baseline to start with... but you still will want to tweak it some over time - but for a fully optimized configuration it's ongoing. I honestly don't worry about tweaking mine. I get it where it works well and let it go from there. I'll check it every month or so and see if there might be something I need to tweak but there hasn't been so far.