+

Last commit for pg_optimizations.py: 9b49ec00decb3065d759f4d0c07a2142e7358da5

Initial commit

Benjamin Renard [2015-11-20 10:29:34]
Initial commit
  1. #!/usr/bin/python
  2.  
  3. import subprocess
  4.  
  5. p = subprocess.Popen(['getconf','PAGE_SIZE'], stdout=subprocess.PIPE, stderr=subprocess.PIPE)
  6. out, err = p.communicate()
  7.  
  8. page_size=int(out.strip())
  9.  
  10. p = subprocess.Popen(['getconf','_PHYS_PAGES'], stdout=subprocess.PIPE, stderr=subprocess.PIPE)
  11. out, err = p.communicate()
  12.  
  13. phys_pages=int(out.strip())
  14.  
  15. all_mem=phys_pages*page_size
  16.  
  17. def pretty_size(size):
  18. size=float(size)
  19. for x in ['B', 'KB', 'MB']:
  20. if size < 1024.0:
  21. return "%i%s" % (size, x)
  22. size /= 1024.0
  23. return "%i%s" % (size*1024, x)
  24.  
  25. print "Total server memory : %s (Pages : %s / Page size : %sB" % (pretty_size(all_mem),phys_pages,page_size)
  26.  
  27. pg_ratio=None
  28. while pg_ratio is None:
  29. pg_ratio=int(raw_input("How many for PG (0-95) ? "))
  30. if pg_ratio<1 or pg_ratio>95:
  31. print "Invalid value"
  32. pg_ratio=None
  33.  
  34. print "Memory allocated to PG : %s" % pretty_size(all_mem*pg_ratio/100)
  35.  
  36. pg_max_con=None
  37. while pg_max_con is None:
  38. pg_max_con=int(raw_input("How many simultaneous connections to PG ? "))
  39. if pg_max_con<=0:
  40. print "Invalid value"
  41. pg_max_con=None
  42.  
  43. shmall=int((phys_pages*pg_ratio/100)*1.2)
  44. if shmall>all_mem:
  45. shmall=all_mem
  46. shmmax=int(shmall*page_size)
  47.  
  48. print "\nTo put in /etc/sysctl.d/30-postgresql-shm.conf :\n"
  49. print "\tkernel.shmall = %s" % shmall
  50. print "\tkernel.shmmax = %s" % shmmax
  51. print "\nAnd restart procps service to apply (on Debian) :\n\n\tservice procps restart"
  52.  
  53. pg_mem=int(all_mem*pg_ratio/100)
  54. shared_buffers=int(pg_mem/4)
  55. work_mem=int(pg_mem / (pg_max_con + (pg_max_con * 0.1)))
  56. effective_cache_size=pg_mem
  57.  
  58. maintenance_work_mem=0.9*pg_mem
  59.  
  60. print "\nTo put in postgresql.conf :\n"
  61. print "\t# Memory related parameters"
  62. print "\t# PG allocated memory : %s" % pretty_size(pg_mem)
  63. print "\tmax_connections = %s" % pg_max_con
  64. print "\t# shared_buffers = 25% of PG allocated memory"
  65. print "\tshared_buffers = %s" % pretty_size(shared_buffers)
  66. print "\t# Memory used for sorting result for each connection"
  67. print "\t# work_mem = PG allocated memory / (max_connexions + 10%)"
  68. print "\twork_mem = %s" % pretty_size(work_mem)
  69. print "\t# Memory used for vacuum, create index, ..."
  70. print "\t# maintenance_work_mem = 90% of PG allocated memory"
  71. print "\tmaintenance_work_mem = %s " % pretty_size(maintenance_work_mem)
  72. print "\t# Available memory for PG (use for internal calculation)"
  73. print "\teffective_cache_size = %s" % pretty_size(effective_cache_size)
  74. print "\n\t# Checkpoints management"
  75. print "\tcheckpoint_segments = 8"
  76. print "\tcheckpoint_timeout = 5min"
  77. print "\tcheckpoint_completion_target = 0.7"
  78. print "\n\t# Log all request > 1sec"
  79. print "\tlog_min_duration_statement = 1000"
  80.  
  81. print "\nAnd restart PG service to apply :\n\n\tservice postgresql restart"