API tools faq
paste
Guest User

Untitled

a guest
Oct 30th, 2017
138
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 11.08 KB | None | 0 0
raw download clone embed print report
  1. My accountSign outWorking hours
  2. Logged in as vlad
  3. HomeMy pageProjectsStatus updatesStuff To DoTimesheetHelp
  4. Search:
  5. Aeye: Servers
  6. Overview
  7. Activity
  8. Issues
  9. New issue
  10. News
  11. Documents
  12. Wiki
  13. Files
  14. Repository
  15. Status updates
  16. Settings
  17. Wiki
  18.  
  19. Start page
  20. Index by title
  21. Index by date
  22. Edit Delete History
  23. Slony-Pgpool
  24. Worklog for creating benchmark environment
  25.  
  26. [root@vm1 ~]# cd /etc/pgpool-II/
  27. [root@vm1 pgpool-II]# ls
  28. pcp.conf pgpool.conf pool_hba.conf
  29. [root@vm1 pgpool-II]# vim pgpool.conf
  30. [root@vm1 pgpool-II]# ls
  31. pcp.conf pgpool.conf pool_hba.conf
  32. [root@vm1 pgpool-II]# vim pcp.conf
  33. [root@vm1 pgpool-II]# vim pgpool.conf
  34. [root@vm1 pgpool-II]# service pgpool start
  35. Starting pgpool service: [ OK ]
  36. [root@vm1 pgpool-II]# tail -f /var/log/messages
  37. Nov 7 04:02:02 vm1 syslogd 1.4.1: restart.
  38. Nov 7 11:09:19 vm1 dhclient: DHCPREQUEST on eth0 to 192.168.2.1 port 67
  39. Nov 7 11:09:19 vm1 dhclient: DHCPACK from 192.168.2.1
  40. Nov 7 11:09:19 vm1 dhclient: bound to 192.168.2.129 -- renewal in 23948 seconds.
  41.  
  42. [root@vm1 pgpool-II]# tail -f /var/log/
  43. acpid cron.1 messages.1 secure.1
  44. anaconda.log cron.2 messages.2 secure.2
  45. anaconda.syslog cups/ pgpool.log slony
  46. audit/ dmesg pgsql spooler
  47. boot.log faillog pm/ spooler.1
  48. boot.log.1 iptraf/ ppp/ spooler.2
  49. boot.log.2 lastlog prelink/ tallylog
  50. brcm-iscsi.log mail/ rpmpkgs wtmp
  51. btmp maillog rpmpkgs.1 yum.log
  52. conman/ maillog.1 rpmpkgs.2
  53. conman.old/ maillog.2 samba/
  54. cron messages secure
  55. [root@vm1 pgpool-II]# tail -f /var/log/p
  56. pgpool.log pgsql pm/ ppp/ prelink/
  57. [root@vm1 pgpool-II]# tail -f /var/log/pgpool.log
  58. 2010-11-07 15:55:13 LOG: pid 20182: Backend status file /var/log/pgpool/pgpool_status does not exist
  59. 2010-11-07 15:55:13 LOG: pid 20182: pgpool successfully started
  60.  
  61. [root@vm1 pgpool-II]# vim pgpool.conf
  62. [root@vm1 pgpool-II]# service pgpool restart
  63. Stopping pgpool service: [ OK ]
  64. Starting pgpool service: [ OK ]
  65. [root@vm1 pgpool-II]# tail -f /var/log/pgpool.log
  66. 2010-11-07 15:55:13 LOG: pid 20182: Backend status file /var/log/pgpool/pgpool_status does not exist
  67. 2010-11-07 15:55:13 LOG: pid 20182: pgpool successfully started
  68. 2010-11-07 15:56:14 LOG: pid 20182: received smart shutdown request
  69. 2010-11-07 15:56:14 ERROR: pid 20182: Could not open status file /var/log/pgpool/pgpool_status
  70. 2010-11-07 15:56:14 LOG: pid 20282: Backend status file /var/log/pgpool/pgpool_status does not exist
  71. 2010-11-07 15:56:14 LOG: pid 20282: pgpool successfully started
  72.  
  73. [root@vm1 pgpool-II]# createdb -p 9999 bench_replication
  74. createdb: could not connect to database postgres: FATAL: role "root" does not exist
  75. [root@vm1 pgpool-II]# createdb -p 9999 bench_replication -U postgres
  76. CREATE DATABASE
  77. [root@vm1 pgpool-II]# pgbench -i -p 9999 bench_replication -U postgres
  78. creating tables...
  79. 10000 tuples done.
  80. 20000 tuples done.
  81. 30000 tuples done.
  82. 40000 tuples done.
  83. 50000 tuples done.
  84. 60000 tuples done.
  85. 70000 tuples done.
  86. 80000 tuples done.
  87. 90000 tuples done.
  88. 100000 tuples done.
  89. set primary key...
  90. NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "branches_pkey" for table "branches"
  91. NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "tellers_pkey" for table "tellers"
  92. NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "accounts_pkey" for table "accounts"
  93. vacuum...done.
  94. [root@vm1 pgpool-II]# screen -rRD
  95. [screen is terminating]
  96. [root@vm1 pgpool-II]# pgbench -i -p 9999 bench_replication -U postgres -C 1000
  97. creating tables...
  98. 10000 tuples done.
  99. 20000 tuples done.
  100. 30000 tuples done.
  101. 40000 tuples done.
  102. 50000 tuples done.
  103. 60000 tuples done.
  104. 70000 tuples done.
  105. 80000 tuples done.
  106. 90000 tuples done.
  107. 100000 tuples done.
  108. set primary key...
  109. NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "branches_pkey" for table "branches"
  110. NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "tellers_pkey" for table "tellers"
  111. NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index "accounts_pkey" for table "accounts"
  112. vacuum...done.
  113. [root@vm1 pgpool-II]# service pgpool restart
  114. [root@vm1 pgpool-II]# tail -f /var/log/pgpool.log
  115. 2010-11-07 15:55:13 LOG: pid 20182: Backend status file /var/log/pgpool/pgpool_status does not exist
  116. 2010-11-07 15:55:13 LOG: pid 20182: pgpool successfully started
  117. 2010-11-07 15:56:14 LOG: pid 20182: received smart shutdown request
  118. 2010-11-07 15:56:14 ERROR: pid 20182: Could not open status file /var/log/pgpool/pgpool_status
  119. 2010-11-07 15:56:14 LOG: pid 20282: Backend status file /var/log/pgpool/pgpool_status does not exist
  120. 2010-11-07 15:56:14 LOG: pid 20282: pgpool successfully started
  121. q
  122. [root@vm1 pgpool-II]# createuser -p 5432 pgpool -U postgres
  123. Shall the new role be a superuser? (y/n) y
  124. CREATE ROLE
  125. [root@vm1 pgpool-II]# createdb -p 5432 -O pgpool pgpool
  126. createdb: could not connect to database postgres: FATAL: role "root" does not exist
  127. [root@vm1 pgpool-II]# createdb -p 5432 -O pgpool pgpool -U postgres
  128. CREATE DATABASE
  129. [root@vm1 pgpool-II]# psql -f /usr/share/pg
  130. pgpool-II/ pgsql/
  131. [root@vm1 pgpool-II]# psql -f /usr/share/pgsql/con
  132. contrib/ conversion_create.sql
  133. [root@vm1 pgpool-II]# psql -f /usr/share/pgsql/contrib/dblink.sql -p 5432 pgpool -U pgpool
  134. CREATE FUNCTION
  135. CREATE FUNCTION
  136. CREATE FUNCTION
  137. CREATE FUNCTION
  138. REVOKE
  139. REVOKE
  140. CREATE FUNCTION
  141. CREATE FUNCTION
  142. CREATE FUNCTION
  143. CREATE FUNCTION
  144. CREATE FUNCTION
  145. CREATE FUNCTION
  146. CREATE FUNCTION
  147. CREATE FUNCTION
  148. CREATE FUNCTION
  149. CREATE FUNCTION
  150. CREATE FUNCTION
  151. CREATE FUNCTION
  152. CREATE FUNCTION
  153. CREATE FUNCTION
  154. CREATE FUNCTION
  155. CREATE FUNCTION
  156. CREATE FUNCTION
  157. CREATE FUNCTION
  158. CREATE FUNCTION
  159. CREATE FUNCTION
  160. CREATE FUNCTION
  161. CREATE FUNCTION
  162. CREATE TYPE
  163. CREATE FUNCTION
  164. CREATE FUNCTION
  165. CREATE FUNCTION
  166. CREATE FUNCTION
  167. CREATE FUNCTION
  168. [root@vm1 pgpool-II]# psql -f /usr/share/pgsql/s -p 5432 pgpool -U pgpoolslony1_base.sql slony1_base.v81.sql slony1_funcs.v80.sql
  169. slony1_base.v73.sql slony1_funcs.sql slony1_funcs.v81.sql
  170. slony1_base.v74.sql slony1_funcs.v73.sql sql_features.txt
  171. slony1_base.v80.sql slony1_funcs.v74.sql system_views.sql
  172. [root@vm1 pgpool-II]# psql -f /usr/share/pgsql/ -p 5432 pgpool -U pgpool[root@vm1 pgpool-II]# find /usr -type f -name system_db.sql
  173. /usr/share/pgpool-II/system_db.sql
  174.  
  175. [root@vm1 pgpool-II]# psql -f /usr/share/pgpool-II/system_db.sql -p 5432 pgpool -U pgpool
  176. psql:/usr/share/pgpool-II/system_db.sql:3: ERROR: schema "pgpool_catalog" does not exist
  177. psql:/usr/share/pgpool-II/system_db.sql:4: ERROR: schema "pgpool_catalog" does not exist
  178. psql:/usr/share/pgpool-II/system_db.sql:5: ERROR: schema "pgpool_catalog" does not exist
  179. psql:/usr/share/pgpool-II/system_db.sql:6: ERROR: schema "pgpool_catalog" does not exist
  180. CREATE SCHEMA
  181. psql:/usr/share/pgpool-II/system_db.sql:20: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "dist_def_pkey" for table "dist_def"
  182. CREATE TABLE
  183. psql:/usr/share/pgpool-II/system_db.sql:29: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "replicate_def_pkey" for table "replicate_def"
  184. CREATE TABLE
  185. psql:/usr/share/pgpool-II/system_db.sql:38: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "query_cache_pkey" for table "query_cache"
  186. CREATE TABLE
  187. [root@vm1 pgpool-II]# psql -f /usr/share/pgpool-II/system_db.sql -p 5432 pgpool -U postgres
  188. DROP TABLE
  189. DROP TABLE
  190. DROP TABLE
  191. DROP SCHEMA
  192. CREATE SCHEMA
  193. psql:/usr/share/pgpool-II/system_db.sql:20: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "dist_def_pkey" for table "dist_def"
  194. CREATE TABLE
  195. psql:/usr/share/pgpool-II/system_db.sql:29: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "replicate_def_pkey" for table "replicate_def"
  196. CREATE TABLE
  197. psql:/usr/share/pgpool-II/system_db.sql:38: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "query_cache_pkey" for table "query_cache"
  198. CREATE TABLE
  199. [root@vm1 pgpool-II]# vim pgpool.conf
  200. [root@vm1 pgpool-II]# cat pgpool.conf | grep -Ev '^#|^$'
  201. listen_addresses = '*'
  202. port = 9999
  203. pcp_port = 9898
  204. socket_dir = '/tmp'
  205. pcp_socket_dir = '/tmp'
  206. backend_socket_dir = '/var/run/pgpool'
  207. pcp_timeout = 10
  208. num_init_children = 32
  209. max_pool = 4
  210. child_life_time = 300
  211. connection_life_time = 0
  212. child_max_connections = 0
  213. client_idle_limit = 0
  214. authentication_timeout = 60
  215. logdir = '/var/log/pgpool'
  216. pid_file_name = '/var/run/pgpool/pgpool.pid'
  217. replication_mode = false
  218. load_balance_mode = true
  219. replication_stop_on_mismatch = false
  220. replicate_select = false
  221. reset_query_list = 'ABORT; DISCARD ALL'
  222. print_timestamp = true
  223. master_slave_mode = false
  224. connection_cache = true
  225. health_check_timeout = 20
  226. health_check_period = 0
  227. health_check_user = 'nobody'
  228. failover_command = ''
  229. failback_command = ''
  230. fail_over_on_backend_error = true
  231. insert_lock = true
  232. ignore_leading_white_space = true
  233. log_statement = false
  234. log_per_node_statement = false
  235. log_connections = false
  236. log_hostname = false
  237. parallel_mode = true
  238. enable_query_cache = false
  239. pgpool2_hostname = 'vm1'
  240. system_db_hostname = 'localhost'
  241. system_db_port = 5432
  242. system_db_dbname = 'pgpool'
  243. system_db_schema = 'pgpool_catalog'
  244. system_db_user = 'pgpool'
  245. system_db_password = ''
  246. backend_hostname0 = 'vm1'
  247. backend_port0 = 5432
  248. backend_weight0 = 1
  249. backend_hostname1 = 'vm2'
  250. backend_port1 = 5432
  251. backend_weight1 = 1
  252. enable_pool_hba = false
  253. recovery_user = 'postgres'
  254. recovery_password = ''
  255. recovery_1st_stage_command = ''
  256. recovery_2nd_stage_command = ''
  257. recovery_timeout = 90
  258. client_idle_limit_in_recovery =
  259. [root@vm1 pgpool-II]# service pgpool restart
  260. Stopping pgpool service: [ OK ]
  261. Starting pgpool service: [ OK ]
  262. [root@vm1 pgpool-II]# cd
  263. [root@vm1 ~]# vim dist_def_pgbench.sql #create SQL-files acordingly tutorial
  264. [root@vm1 ~]# vim replicate_def_pgbench.sql
  265. [root@vm1 ~]# psql -f ./dist_def_pgbench.sql -p 5432 pgpool -U postgres
  266. INSERT 0 1
  267. [root@vm1 ~]# psql -f replicate_def_pgbench.sql -U postgres pgpool
  268. INSERT 0 1
  269. INSERT 0 1
  270. [root@vm1 ~]# createdb -p 9999 bench_parallel -U postgres
  271. CREATE DATABASE
  272.  
  273. pgpool-check-load.sh code:
  274. #!/bin/sh
  275.  
  276. while true;
  277. do
  278. echo 'vm1:5432'
  279. psql -c "SELECT min(aid), max(aid) FROM accounts" -p 5432 -h vm1 -U postgres bench_parallel
  280. echo 'vm2:5432'
  281. psql -c "SELECT min(aid), max(aid) FROM accounts" -p 5432 -h vm2 -U postgres bench_parallel
  282. echo 'pgpool'
  283. psql -c "SELECT min(aid), max(aid) FROM accounts" -p 9999 -h vm1 -U postgres bench_parallel
  284. sleep 3
  285. done
  286. Load was generated by comand:
  287. pgbench -h vm1 -p 9999 -U postgres -i -s 30 bench_parallel
  288.  
  289. pgpool-check-load.sh output
  290.  
  291. slony-pgpool-pgbench.graph.jpg (built by Office tools )
  292.  
  293. New file
  294.  
  295. Also available in: HTML TXT
  296.  
  297. Powered by Nexus
Add Comment
Please, Sign In to add comment
Public Pastes
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!