SHARE
TWEET

Untitled

a guest Jul 22nd, 2019 71 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. --- /etc/my.cnf.d/server.cnf    2019-07-19 15:57:17.981964885 +0000
  2. +++ /root/server.cnf    2019-07-22 09:21:20.881126875 +0000
  3. @@ -1,12 +1,41 @@
  4.  [server]
  5.  [mysqld]
  6. -query_cache_size = 64M
  7. -join_buffer_size = 256K
  8. -tmp_table_size = 64M
  9. -max_heap_table_size = 64M
  10. -thread_cache_size = 10
  11. -innodb_buffer_pool_size = 32G
  12. -innodb_buffer_pool_instances = 10
  13. -max_connections=1024
  14. -max_allowed_packet=1024M
  15. -sql_mode = ""
  16. +
  17. +explicit_defaults_for_timestamp = 1    # Fix deprecation warning
  18. +
  19. +# Exclude lost+found from db list
  20. +ignore-db-dir=lost+found       #If Mysql data stored on separate partion, we need to exclude lost+found directory,
  21. +                   # otherwise Mysql will fail to start and think lost+found is a database.     
  22. +
  23. +thread_cache_size = 50             #Monitor Mysql Threads and increase if Threads_created >> Threads_cached
  24. +innodb_buffer_pool_size = 20G      #We cannot set all system memory pool size
  25. +innodb_log_file_size = 256M        #Good starting value
  26. +innodb_buffer_pool_instances = 8   # 8 is a default
  27. +innodb_file_per_table = 1      #Default in Mysql 5.7
  28. +innodb_flush_method = O_DIRECT         #InnoDB has internal i/o caching we must avoid double caching — OS and Mysql
  29. +innodb_flush_log_at_trx_commit = 2     #Compromise value beatween speed and stability
  30. +query_cache_type = 0           #It is better to disable Query caching and adjust if only if needed.
  31. +query_cache_size = 0           #It is better to disable Query caching and adjust if only if needed.
  32. +key_buffer_size  = 32K             #Default is 128K — We do not use MyISAM
  33. +max_connect_errors = 1000000       #Avoid connection errors spam
  34. +max_connections=1024           #Not changed, to high in my opinion.
  35. +max_allowed_packet=1024M       #Same as max_connections
  36. +
  37. +# Query logs               # Query logging usefull for SQL query analyze and fix.
  38. +slow-query-log = 1
  39. +slow-query-log-file = /var/log/slow-query.log
  40. +long_query_time = 1
  41. +
  42. +### General recommendations ####
  43. +# Databases uses different SQL engines — InnoDB and MyISAM, for example, shop-tradersyard.
  44. +# There is no reason to do this, use one SQL engine for all databases. With Mysql 5.7 InnoDB
  45. +# or Barracuda will be a wise choise.
  46. +#
  47. +# Developers must study query logs and fix SQL queries, add indexes, etc if needed.
  48. +
  49. +########### Non Mysql fixes (need to investigate first) ###########
  50. +# Mysql running in lxc-container, thus all clients connected via TCP
  51. +# That means tcp parameters, like local port range and backbuffer size need to be changed
  52. +# to optimal values. Unfortunately, this is not easy with lxc-containers. We need to  test
  53. +# in testing  ENV first.
  54. +
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top