myapit

mysql-config-1

Nov 24th, 2020 (edited)
522
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1.  SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
  2.      (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
  3.      FROM information_schema.tables WHERE engine='InnoDB') A;
  4. +-------+
  5. | RIBPS |
  6. +-------+
  7. |     8 |
  8. +-------+
  9. 1 row in set (4.31 sec)
  10.  
  11. mysql>
  12. With this output, you would set the following in /etc/my.cnf
  13.  
  14. [mysqld]
  15. innodb_buffer_pool_size=8G
  16.  
  17.  
  18. After the restart, run MySQL for a week or two. Then, run this query:
  19.  
  20. SELECT (PagesData*PageSize)/POWER(1024,3) DataGB FROM
  21. (SELECT variable_value PagesData
  22. FROM information_schema.global_status
  23. WHERE variable_name='Innodb_buffer_pool_pages_data') A,
  24. (SELECT variable_value PageSize
  25. FROM information_schema.global_status
  26. WHERE variable_name='Innodb_page_size') B;
  27.  
  28. This will give you how many actual GB of memory is in use by InnoDB Data in the InnoDB Buffer Pool at this moment.
  29.  
  30. This value DataGB more closely resembles how big the InnoDB Buffer Pool should be + (percentage specified in innodb_change_buffer_max_size). I am sure this will be far less than the 20000M you have reserved right now. The savings in RAM can be used for tuning other things like
  31.  
  32. join_buffer_size
  33. sort_buffer_size
  34. read_buffer_size
  35. read_rnd_buffer_size
  36. max_connection
  37.  
  38.  
  39. SELECT CONCAT(CEILING(RIBPS/POWER(1024,pw)),SUBSTR(' KMGT',pw+1,1))
  40. Recommended_InnoDB_Buffer_Pool_Size FROM
  41. (
  42.     SELECT RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw
  43.     FROM
  44.     (
  45.         SELECT SUM(data_length+index_length)*1.1*growth RIBPS
  46.         FROM information_schema.tables AAA,
  47.         (SELECT 1.25 growth) BBB
  48.         WHERE ENGINE='InnoDB'
  49.     ) AA
  50. ) A;
  51.  
  52.  
  53. ===================
  54. Something like this? Using SHOW VARIABLES and SHOW GLOBAL STATUS:
  55.  
  56. Expression: innodb_buffer_pool_size / _ram
  57. Meaning: % of RAM used for InnoDB buffer_pool
  58. Recommended range: 60~80%
  59.  
  60. Expression: Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests
  61. Meaning: Read requests that had to hit disk
  62. Recommended range: 0-2%
  63. What to do if out of range: Increase innodb_buffer_pool_size if you have enough RAM.
  64.  
  65. Expression: Innodb_pages_read / Innodb_buffer_pool_read_requests
  66. Meaning: Read requests that had to hit disk
  67. Recommended range: 0-2%
  68. What to do if out of range: Increase innodb_buffer_pool_size if you have enough RAM.
  69.  
  70. Expression: Innodb_pages_written / Innodb_buffer_pool_write_requests
  71. Meaning: Write requests that had to hit disk
  72. Recommended range: 0-15%
  73. What to do if out of range: Check innodb_buffer_pool_size
  74.  
  75. Expression: Innodb_buffer_pool_reads / Uptime
  76. Meaning: Reads
  77. Recommended range: 0-100/sec.
  78. What to do if out of range: Increase innodb_buffer_pool_size?
  79.  
  80. Expression: (Innodb_buffer_pool_reads + Innodb_buffer_pool_pages_flushed)  / Uptime
  81. Meaning: InnoDB I/O
  82. Recommended range: 0-100/sec.
  83. What to do if out of range: Increase innodb_buffer_pool_size?
  84.  
  85. Expression: Innodb_buffer_pool_pages_flushed / Uptime
  86. Meaning: Writes (flushes)
  87. Recommended range: 0-100/sec.
  88. What to do if out of range: Increase innodb_buffer_pool_size?
  89.  
  90. Expression: Innodb_buffer_pool_wait_free / Uptime
  91. Meaning: Counter for when there are no free pages in buffer_pool. That is, all pages are dirty.
  92. Recommended range: 0-1/sec.
  93. What to do if out of range: First be sure innodb_buffer_pool_size is set reasonably; if still trouble, decrease innodb_max_dirty_pages_pct
RAW Paste Data