āĻ
āĻĨāĻŦāĻž āĻāĻāĻā§ āĻĒā§āĻ°āĻ¯āĻŧā§āĻ āĻāĻ°āĻž āĻā§āĻā§āĻ°āĻŋāĻ¸ā§āĻ˛āĻāĻŋāĨ¤
āĻ¨āĻ¤ā§āĻ¨ āĻ¸āĻŦāĻāĻŋāĻā§ āĻĒā§āĻ°āĻžāĻ¨ā§ āĻāĻžāĻ˛ā§āĻāĻžāĻŦā§ āĻā§āĻ˛ā§ āĻā§āĻā§āĨ¤
āĻāĻĒāĻŋāĻā§āĻ°āĻžāĻĢāĨ¤
āĻ¸āĻŽāĻ¸ā§āĻ¯āĻž āĻāĻ āĻ¨
āĻāĻĒāĻ¨āĻžāĻā§ āĻĒāĻ°ā§āĻ¯āĻžāĻ¯āĻŧāĻā§āĻ°āĻŽā§ AWS āĻā§āĻ˛āĻžāĻāĻĄ āĻĨā§āĻā§ āĻāĻĒāĻ¨āĻžāĻ° āĻ¸ā§āĻĨāĻžāĻ¨ā§āĻ¯āĻŧ āĻ˛āĻŋāĻ¨āĻžāĻā§āĻ¸ āĻšā§āĻ¸ā§āĻā§ āĻŦāĻ°ā§āĻ¤āĻŽāĻžāĻ¨ PostgreSQL āĻ˛āĻ āĻĢāĻžāĻāĻ˛ āĻĄāĻžāĻāĻ¨āĻ˛ā§āĻĄ āĻāĻ°āĻ¤ā§ āĻšāĻŦā§āĨ¤ āĻ°āĻŋāĻ¯āĻŧā§āĻ˛ āĻāĻžāĻāĻŽā§ āĻ¨āĻ¯āĻŧ, āĻāĻŋāĻ¨ā§āĻ¤ā§, āĻāĻāĻā§ āĻĻā§āĻ°āĻŋ āĻāĻ°ā§āĻ āĻŦāĻ˛āĻŋāĨ¤
āĻ˛āĻ āĻĢāĻžāĻāĻ˛ āĻāĻĒāĻĄā§āĻ āĻĄāĻžāĻāĻ¨āĻ˛ā§āĻĄā§āĻ° āĻ¸āĻŽāĻ¯āĻŧāĻāĻžāĻ˛ 5 āĻŽāĻŋāĻ¨āĻŋāĻāĨ¤
AWS-āĻ āĻ˛āĻ āĻĢāĻžāĻāĻ˛āĻāĻŋ āĻĒā§āĻ°āĻ¤āĻŋ āĻāĻ¨ā§āĻāĻžāĻ¯āĻŧ āĻā§āĻ°āĻžāĻ¨ā§ āĻšāĻ¯āĻŧāĨ¤
āĻŦā§āĻ¯āĻŦāĻšā§āĻ¤ āĻ¸āĻ°āĻā§āĻāĻžāĻŽ
āĻšā§āĻ¸ā§āĻā§ āĻ˛āĻ āĻĢāĻžāĻāĻ˛ āĻĄāĻžāĻāĻ¨āĻ˛ā§āĻĄ āĻāĻ°āĻ¤ā§, āĻāĻāĻāĻŋ āĻŦā§āĻ¯āĻžāĻļ āĻ¸ā§āĻā§āĻ°āĻŋāĻĒā§āĻ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°āĻž āĻšāĻ¯āĻŧ āĻ¯āĻž AWS API āĻāĻ˛ āĻāĻ°ā§ "
āĻŦāĻŋāĻāĻ˛ā§āĻĒ:
- âdb-āĻāĻ¨āĻ¸ā§āĻā§āĻ¯āĻžāĻ¨ā§āĻ¸-āĻāĻāĻĄā§āĻ¨ā§āĻāĻŋāĻĢāĻžāĻ¯āĻŧāĻžāĻ°: AWS āĻāĻ¨āĻ¸ā§āĻā§āĻ¯āĻžāĻ¨ā§āĻ¸ āĻ¨āĻžāĻŽ;
- --log-file-name: āĻŦāĻ°ā§āĻ¤āĻŽāĻžāĻ¨ā§ āĻ¤ā§āĻ°āĻŋ āĻāĻ°āĻž āĻ˛āĻ āĻĢāĻžāĻāĻ˛ā§āĻ° āĻ¨āĻžāĻŽ
- --max-item: āĻāĻŽāĻžāĻ¨ā§āĻĄ āĻāĻāĻāĻĒā§āĻā§ āĻĢāĻŋāĻ°ā§ āĻāĻ¸āĻž āĻāĻāĻā§āĻŽā§āĻ° āĻŽā§āĻ āĻ¸āĻāĻā§āĻ¯āĻžāĨ¤āĻĄāĻžāĻāĻ¨āĻ˛ā§āĻĄ āĻāĻ°āĻž āĻĢāĻžāĻāĻ˛ā§āĻ° āĻ āĻāĻļā§āĻ° āĻāĻāĻžāĻ°āĨ¤
- --āĻ¸ā§āĻāĻžāĻ°ā§āĻāĻŋāĻ-āĻā§āĻā§āĻ¨: āĻ¸ā§āĻāĻžāĻ°ā§āĻāĻŋāĻ āĻā§āĻā§āĻ¨
āĻāĻŦāĻ āĻāĻāĻŋ āĻ¸āĻšāĻ - āĻāĻžāĻā§āĻ° āĻ¸āĻŽāĻ¯āĻŧ āĻĒā§āĻ°āĻļāĻŋāĻā§āĻˇāĻŖ āĻāĻŦāĻ āĻŦā§āĻāĻŋāĻ¤ā§āĻ°ā§āĻ¯ā§āĻ° āĻāĻ¨ā§āĻ¯ āĻāĻāĻāĻŋ āĻāĻāĻ°ā§āĻˇāĻŖā§āĻ¯āĻŧ āĻāĻžāĻāĨ¤
āĻāĻŽāĻŋ āĻ§āĻ°ā§ āĻ¨ā§āĻŦ āĻ¯ā§ āĻĻā§āĻ¨āĻ¨ā§āĻĻāĻŋāĻ¨ āĻā§āĻŦāĻ¨ā§āĻ° āĻāĻžāĻ°āĻŖā§ āĻ¸āĻŽāĻ¸ā§āĻ¯āĻžāĻāĻŋ āĻāĻ¤āĻŋāĻŽāĻ§ā§āĻ¯ā§ āĻ¸āĻŽāĻžāĻ§āĻžāĻ¨ āĻšāĻ¯āĻŧā§ āĻā§āĻā§āĨ¤ āĻāĻŋāĻ¨ā§āĻ¤ā§ āĻāĻāĻāĻŋ āĻĻā§āĻ°ā§āĻ¤ Google āĻā§āĻ¨ā§ āĻ¸āĻŽāĻžāĻ§āĻžāĻ¨ āĻĒā§āĻ°āĻ¸ā§āĻ¤āĻžāĻŦ āĻāĻ°ā§āĻ¨āĻŋ, āĻāĻŦāĻ āĻāĻŽāĻžāĻ° āĻāĻ°āĻ āĻāĻā§āĻ°ā§ āĻ
āĻ¨ā§āĻ¸āĻ¨ā§āĻ§āĻžāĻ¨ āĻāĻ°āĻžāĻ° āĻā§āĻŦ āĻŦā§āĻļāĻŋ āĻāĻā§āĻāĻž āĻāĻŋāĻ˛ āĻ¨āĻžāĨ¤ āĻ¯ā§āĻāĻžāĻŦā§āĻ āĻšā§āĻ, āĻāĻāĻž āĻāĻāĻāĻž āĻāĻžāĻ˛ā§ āĻāĻ¯āĻŧāĻžāĻ°ā§āĻāĻāĻāĻāĨ¤
āĻāĻžāĻā§āĻ° āĻāĻ¨ā§āĻˇā§āĻ āĻžāĻ¨āĻŋāĻā§āĻāĻ°āĻŖ
āĻā§āĻĄāĻŧāĻžāĻ¨ā§āĻ¤ āĻ˛āĻ āĻĢāĻžāĻāĻ˛ā§ āĻĒāĻ°āĻŋāĻŦāĻ°ā§āĻ¤āĻ¨āĻļā§āĻ˛ āĻĻā§āĻ°ā§āĻā§āĻ¯ā§āĻ° āĻ
āĻ¨ā§āĻ āĻ˛āĻžāĻāĻ¨ āĻĨāĻžāĻā§āĨ¤ āĻā§āĻ°āĻžāĻĢāĻŋāĻāĻāĻžāĻŦā§, āĻ˛āĻ āĻĢāĻžāĻāĻ˛āĻāĻŋ āĻāĻ°āĻāĻŽ āĻāĻŋāĻā§ āĻāĻĒāĻ¸ā§āĻĨāĻžāĻĒāĻ¨ āĻāĻ°āĻž āĻ¯ā§āĻ¤ā§ āĻĒāĻžāĻ°ā§:
āĻāĻāĻž āĻāĻ¤āĻŋāĻŽāĻ§ā§āĻ¯ā§ āĻāĻŋāĻā§ āĻŽāĻ¨ā§ āĻāĻ°āĻŋāĻ¯āĻŧā§ āĻĻā§āĻ¯āĻŧ? āĻā§āĻā§āĻ°āĻŋāĻ¸ āĻāĻ° āĻ¸āĻžāĻĨā§ āĻāĻŋ āĻāĻ°āĻžāĻ° āĻāĻā§? āĻāĻŦāĻ āĻāĻāĻžāĻ¨ā§ āĻāĻāĻž āĻāĻŋ āĻāĻāĻž āĻ¸āĻā§āĻā§ āĻāĻŋ āĻāĻā§.
āĻāĻŽāĻ°āĻž āĻ¯āĻĻāĻŋ āĻĒāĻ°āĻŦāĻ°ā§āĻ¤ā§ āĻĢāĻžāĻāĻ˛āĻāĻŋ āĻā§āĻ°āĻžāĻĢāĻŋāĻāĻāĻžāĻŦā§ āĻ˛ā§āĻĄ āĻāĻ°āĻžāĻ° āĻ¸āĻŽāĻ¯āĻŧ āĻāĻĻā§āĻā§āĻ¤ āĻ¸āĻŽā§āĻāĻžāĻŦā§āĻ¯ āĻŦāĻŋāĻāĻ˛ā§āĻĒāĻā§āĻ˛āĻŋ āĻāĻ˛ā§āĻĒāĻ¨āĻž āĻāĻ°āĻŋ (āĻ¸āĻ°āĻ˛āĻ¤āĻžāĻ° āĻāĻ¨ā§āĻ¯, āĻāĻ āĻā§āĻˇā§āĻ¤ā§āĻ°ā§, āĻ˛āĻžāĻāĻ¨āĻā§āĻ˛āĻŋāĻ° āĻĻā§āĻ°ā§āĻā§āĻ¯ āĻāĻāĻ āĻĨāĻžāĻā§āĻ), āĻāĻŽāĻ°āĻž āĻĒāĻžāĻ āĻ¸ā§āĻā§āĻ¯āĻžāĻ¨ā§āĻĄāĻžāĻ°ā§āĻĄ āĻā§āĻā§āĻ°āĻŋāĻ¸ āĻā§āĻāĻ°āĻž:
1) āĻĢāĻžāĻāĻ˛āĻāĻŋ āĻ¸āĻŽā§āĻĒā§āĻ°ā§āĻŖāĻ°ā§āĻĒā§ āĻĄāĻžāĻāĻ¨āĻ˛ā§āĻĄ āĻāĻ°āĻž āĻšāĻ¯āĻŧā§āĻā§ āĻāĻŦāĻ āĻā§āĻĄāĻŧāĻžāĻ¨ā§āĻ¤āĨ¤ āĻ
āĻāĻļā§āĻ° āĻāĻāĻžāĻ° āĻā§āĻĄāĻŧāĻžāĻ¨ā§āĻ¤ āĻĢāĻžāĻāĻ˛ā§āĻ° āĻāĻāĻžāĻ°ā§āĻ° āĻā§āĻ¯āĻŧā§ āĻŦāĻĄāĻŧ:
2) āĻĢāĻžāĻāĻ˛ āĻāĻ˛āĻ¤ā§ āĻĨāĻžāĻā§āĨ¤ āĻāĻŖā§āĻĄā§āĻ° āĻāĻāĻžāĻ° āĻā§āĻĄāĻŧāĻžāĻ¨ā§āĻ¤ āĻĢāĻžāĻāĻ˛ā§āĻ° āĻāĻāĻžāĻ°ā§āĻ° āĻā§āĻ¯āĻŧā§ āĻā§āĻ:
3) āĻĢāĻžāĻāĻ˛āĻāĻŋ āĻĒā§āĻ°ā§āĻŦāĻŦāĻ°ā§āĻ¤ā§ āĻĢāĻžāĻāĻ˛ā§āĻ° āĻāĻāĻāĻŋ āĻ§āĻžāĻ°āĻžāĻŦāĻžāĻšāĻŋāĻāĻ¤āĻž āĻāĻŦāĻ āĻāĻāĻāĻŋ āĻ§āĻžāĻ°āĻžāĻŦāĻžāĻšāĻŋāĻāĻ¤āĻž āĻ°āĻ¯āĻŧā§āĻā§āĨ¤ āĻāĻŖā§āĻĄā§āĻ° āĻāĻāĻžāĻ° āĻā§āĻĄāĻŧāĻžāĻ¨ā§āĻ¤ āĻĢāĻžāĻāĻ˛ā§āĻ° āĻ
āĻŦāĻļāĻŋāĻˇā§āĻ āĻāĻāĻžāĻ°ā§āĻ° āĻā§āĻ¯āĻŧā§ āĻā§āĻ:
4) āĻĢāĻžāĻāĻ˛āĻāĻŋ āĻĒā§āĻ°ā§āĻŦāĻŦāĻ°ā§āĻ¤ā§ āĻĢāĻžāĻāĻ˛ā§āĻ° āĻāĻāĻāĻŋ āĻ§āĻžāĻ°āĻžāĻŦāĻžāĻšāĻŋāĻāĻ¤āĻž āĻāĻŦāĻ āĻāĻāĻŋ āĻā§āĻĄāĻŧāĻžāĻ¨ā§āĻ¤āĨ¤ āĻāĻŖā§āĻĄā§āĻ° āĻāĻāĻžāĻ° āĻā§āĻĄāĻŧāĻžāĻ¨ā§āĻ¤ āĻĢāĻžāĻāĻ˛ā§āĻ° āĻ
āĻŦāĻļāĻŋāĻˇā§āĻ āĻāĻāĻžāĻ°ā§āĻ° āĻā§āĻ¯āĻŧā§ āĻŦāĻĄāĻŧ:
āĻāĻžāĻ¸ā§āĻ āĻāĻāĻāĻŋ āĻāĻ¯āĻŧāĻ¤āĻā§āĻˇā§āĻ¤ā§āĻ° āĻāĻāĻ¤ā§āĻ°āĻŋāĻ¤ āĻāĻ°āĻž āĻŦāĻž āĻāĻāĻāĻŋ āĻ¨āĻ¤ā§āĻ¨ āĻ¸ā§āĻ¤āĻ°ā§ Tetris āĻā§āĻ˛āĻž āĻšāĻ¯āĻŧ.
āĻ¸āĻŽāĻ¸ā§āĻ¯āĻž āĻ¸āĻŽāĻžāĻ§āĻžāĻ¨ā§āĻ° āĻ¸āĻŽāĻ¯āĻŧ āĻ¯ā§ āĻ¸āĻŽāĻ¸ā§āĻ¯āĻžāĻā§āĻ˛ā§ āĻĻā§āĻāĻž āĻĻā§āĻ¯āĻŧ
1) 2 āĻā§āĻāĻ°āĻž āĻāĻāĻāĻŋ āĻ˛āĻžāĻāĻ¨ āĻāĻ āĻžāĻ˛ā§
āĻ¸āĻžāĻ§āĻžāĻ°āĻŖāĻāĻžāĻŦā§, āĻā§āĻ¨ āĻŦāĻŋāĻļā§āĻˇ āĻ¸āĻŽāĻ¸ā§āĻ¯āĻž āĻāĻŋāĻ˛ āĻ¨āĻžāĨ¤ āĻāĻāĻāĻŋ āĻĒā§āĻ°āĻžāĻĨāĻŽāĻŋāĻ āĻĒā§āĻ°ā§āĻā§āĻ°āĻžāĻŽāĻŋāĻ āĻā§āĻ°ā§āĻ¸ āĻĨā§āĻā§ āĻāĻāĻāĻŋ āĻāĻĻāĻ°ā§āĻļ āĻ¸āĻŽāĻ¸ā§āĻ¯āĻžāĨ¤
āĻ¸āĻ°ā§āĻŦā§āĻ¤ā§āĻ¤āĻŽ āĻĒāĻ°āĻŋāĻŦā§āĻļāĻ¨ āĻāĻāĻžāĻ°
āĻ¤āĻŦā§ āĻāĻāĻŋ āĻāĻāĻā§ āĻŦā§āĻļāĻŋ āĻāĻāĻ°ā§āĻˇāĻŖā§āĻ¯āĻŧāĨ¤
āĻĻā§āĻ°ā§āĻāĻžāĻā§āĻ¯āĻŦāĻļāĻ¤, āĻļā§āĻ°ā§ āĻ
āĻāĻļ āĻ˛ā§āĻŦā§āĻ˛ āĻĒāĻ°ā§ āĻāĻāĻāĻŋ āĻ
āĻĢāĻ¸ā§āĻ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°āĻžāĻ° āĻā§āĻ¨ āĻāĻĒāĻžāĻ¯āĻŧ āĻ¨ā§āĻ:
āĻāĻĒāĻ¨āĻŋ āĻāĻ¤āĻŋāĻŽāĻ§ā§āĻ¯ā§āĻ āĻāĻžāĻ¨ā§āĻ¨ āĻŦāĻŋāĻāĻ˛ā§āĻĒāĻāĻŋâāĻ¸ā§āĻāĻžāĻ°ā§āĻāĻŋāĻ-āĻā§āĻā§āĻ¨ āĻā§āĻĨāĻžāĻ¯āĻŧ āĻĒā§āĻāĻŋāĻ¨ āĻāĻ°āĻž āĻļā§āĻ°ā§ āĻāĻ°āĻ¤ā§ āĻšāĻŦā§ āĻ¤āĻž āĻ¨āĻŋāĻ°ā§āĻĻāĻŋāĻˇā§āĻ āĻāĻ°āĻ¤ā§ āĻŦā§āĻ¯āĻŦāĻšā§āĻ¤ āĻšāĻ¯āĻŧāĨ¤ āĻāĻ āĻŦāĻŋāĻāĻ˛ā§āĻĒāĻāĻŋ āĻ¸ā§āĻā§āĻ°āĻŋāĻ āĻŽāĻžāĻ¨ āĻ¨ā§āĻ¯āĻŧ āĻ¯āĻžāĻ° āĻ āĻ°ā§āĻĨ āĻšāĻ˛ āĻāĻĒāĻ¨āĻŋ āĻ¯āĻĻāĻŋ āĻ¨ā§āĻā§āĻ¸āĻ āĻā§āĻā§āĻ¨ āĻ¸ā§āĻā§āĻ°āĻŋāĻāĻ¯āĻŧā§āĻ° āĻ¸āĻžāĻŽāĻ¨ā§ āĻāĻāĻāĻŋ āĻ āĻĢāĻ¸ā§āĻ āĻŽāĻžāĻ¨ āĻ¯ā§āĻ āĻāĻ°āĻžāĻ° āĻā§āĻˇā§āĻāĻž āĻāĻ°ā§āĻ¨ āĻ¤āĻŦā§ āĻŦāĻŋāĻāĻ˛ā§āĻĒāĻāĻŋāĻā§ āĻ āĻĢāĻ¸ā§āĻ āĻšāĻŋāĻ¸āĻžāĻŦā§ āĻŦāĻŋāĻŦā§āĻāĻ¨āĻž āĻāĻ°āĻž āĻšāĻŦā§ āĻ¨āĻžāĨ¤
āĻāĻŦāĻ āĻ¸ā§āĻāĻāĻ¨ā§āĻ¯, āĻāĻĒāĻ¨āĻžāĻā§ āĻā§āĻāĻ°ā§ āĻā§āĻāĻ°ā§ āĻāĻ°ā§ āĻĒāĻĄāĻŧāĻ¤ā§ āĻšāĻŦā§āĨ¤
āĻāĻĒāĻ¨āĻŋ āĻ¯āĻĻāĻŋ āĻŦāĻĄāĻŧ āĻ
āĻāĻļā§ āĻĒāĻĄāĻŧā§āĻ¨ āĻ¤āĻŦā§ āĻĒāĻĄāĻŧāĻžāĻ° āĻ¸āĻāĻā§āĻ¯āĻž āĻ¸āĻ°ā§āĻŦāĻ¨āĻŋāĻŽā§āĻ¨ āĻšāĻŦā§, āĻ¤āĻŦā§ āĻāĻ˛āĻŋāĻāĻŽ āĻ¸āĻ°ā§āĻŦāĻžāĻ§āĻŋāĻ āĻšāĻŦā§āĨ¤
āĻāĻĒāĻ¨āĻŋ āĻ¯āĻĻāĻŋ āĻā§āĻ āĻ
āĻāĻļā§ āĻĒāĻĄāĻŧā§āĻ¨, āĻ¤āĻŦā§ āĻŦāĻŋāĻĒāĻ°ā§āĻ¤ā§, āĻĒāĻĄāĻŧāĻžāĻ° āĻ¸āĻāĻā§āĻ¯āĻž āĻ¸āĻ°ā§āĻŦāĻžāĻ§āĻŋāĻ āĻšāĻŦā§, āĻ¤āĻŦā§ āĻāĻ˛āĻŋāĻāĻŽāĻāĻŋ āĻ¸āĻ°ā§āĻŦāĻ¨āĻŋāĻŽā§āĻ¨ āĻšāĻŦā§āĨ¤
āĻ
āĻ¤āĻāĻŦ, āĻā§āĻ°ā§āĻ¯āĻžāĻĢāĻŋāĻ āĻāĻŽāĻžāĻ¤ā§ āĻāĻŦāĻ āĻ¸āĻŽāĻžāĻ§āĻžāĻ¨ā§āĻ° āĻ¸āĻžāĻŽāĻā§āĻ°āĻŋāĻ āĻ¸ā§āĻ¨ā§āĻĻāĻ°ā§āĻ¯ā§āĻ° āĻāĻ¨ā§āĻ¯, āĻāĻŽāĻžāĻā§ āĻāĻāĻāĻŋ āĻ¸āĻŽāĻžāĻ§āĻžāĻ¨ āĻ¨āĻŋāĻ¯āĻŧā§ āĻāĻ¸āĻ¤ā§ āĻšāĻ¯āĻŧā§āĻāĻŋāĻ˛, āĻ¯āĻž āĻĻā§āĻ°ā§āĻāĻžāĻā§āĻ¯āĻŦāĻļāĻ¤, āĻāĻŋāĻā§āĻāĻž āĻā§āĻ°āĻžāĻā§āĻ° āĻŽāĻ¤ā§ āĻĻā§āĻāĻžāĻ¯āĻŧāĨ¤
āĻĻā§āĻˇā§āĻāĻžāĻ¨ā§āĻ¤ā§āĻ° āĻāĻ¨ā§āĻ¯, āĻāĻ¸ā§āĻ¨ 2āĻāĻŋ āĻ āĻ¤ā§āĻ¯āĻ¨ā§āĻ¤ āĻ¸āĻ°āĻ˛ā§āĻā§āĻ¤ āĻ¸āĻāĻ¸ā§āĻāĻ°āĻŖā§ āĻāĻāĻāĻŋ āĻ˛āĻ āĻĢāĻžāĻāĻ˛ āĻĄāĻžāĻāĻ¨āĻ˛ā§āĻĄ āĻāĻ°āĻžāĻ° āĻĒā§āĻ°āĻā§āĻ°āĻŋāĻ¯āĻŧāĻž āĻŦāĻŋāĻŦā§āĻāĻ¨āĻž āĻāĻ°āĻž āĻ¯āĻžāĻāĨ¤ āĻāĻāĻ¯āĻŧ āĻā§āĻˇā§āĻ¤ā§āĻ°ā§āĻ āĻĒāĻĄāĻŧāĻžāĻ° āĻ¸āĻāĻā§āĻ¯āĻž āĻ āĻāĻļā§āĻ° āĻāĻāĻžāĻ°ā§āĻ° āĻāĻĒāĻ° āĻ¨āĻŋāĻ°ā§āĻāĻ° āĻāĻ°ā§āĨ¤
1) āĻā§āĻ āĻ
āĻāĻļā§ āĻ˛ā§āĻĄ āĻāĻ°ā§āĻ¨:
2) āĻŦāĻĄāĻŧ āĻ
āĻāĻļā§ āĻ˛ā§āĻĄ āĻāĻ°ā§āĻ¨:
āĻ¯āĻĨāĻžāĻ°ā§āĻ¤āĻŋ, āĻ¸āĻ°ā§āĻŦā§āĻ¤ā§āĻ¤āĻŽ āĻ¸āĻŽāĻžāĻ§āĻžāĻ¨ āĻŽāĻžāĻāĻāĻžāĻ¨ā§.
āĻĒāĻ°āĻŋāĻŦā§āĻļāĻ¨ā§āĻ° āĻāĻāĻžāĻ° āĻ¨ā§āĻ¯ā§āĻ¨āĻ¤āĻŽ, āĻ¤āĻŦā§ āĻĒāĻĄāĻŧāĻžāĻ° āĻĒā§āĻ°āĻā§āĻ°āĻŋāĻ¯āĻŧāĻž āĻāĻ˛āĻžāĻāĻžāĻ˛ā§āĻ¨, āĻĒāĻĄāĻŧāĻžāĻ° āĻ¸āĻāĻā§āĻ¯āĻž āĻāĻŽāĻžāĻ¤ā§ āĻāĻāĻžāĻ° āĻŦāĻžāĻĄāĻŧāĻžāĻ¨ā§ āĻ¯ā§āĻ¤ā§ āĻĒāĻžāĻ°ā§āĨ¤
āĻāĻāĻž āĻāĻ˛ā§āĻ˛ā§āĻ āĻāĻ°āĻž āĻāĻāĻŋāĻ¤ āĻĒāĻ āĻ¨āĻ¯ā§āĻā§āĻ¯ āĻ āĻāĻļā§āĻ° āĻ¸āĻ°ā§āĻŦā§āĻ¤ā§āĻ¤āĻŽ āĻāĻāĻžāĻ° āĻ¨āĻŋāĻ°ā§āĻŦāĻžāĻāĻ¨ āĻāĻ°āĻžāĻ° āĻ¸āĻŽāĻ¸ā§āĻ¯āĻžāĻāĻŋ āĻāĻāĻ¨āĻ āĻ¸āĻŽāĻžāĻ§āĻžāĻ¨ āĻāĻ°āĻž āĻšāĻ¯āĻŧāĻ¨āĻŋ āĻāĻŦāĻ āĻāĻ°āĻ āĻāĻā§āĻ°āĻāĻžāĻŦā§ āĻ āĻ§ā§āĻ¯āĻ¯āĻŧāĻ¨ āĻāĻŦāĻ āĻŦāĻŋāĻļā§āĻ˛ā§āĻˇāĻŖā§āĻ° āĻĒā§āĻ°āĻ¯āĻŧā§āĻāĻ¨āĨ¤ āĻšāĻ¯āĻŧāĻ¤ā§ āĻāĻāĻā§ āĻĒāĻ°ā§āĨ¤
āĻŦāĻžāĻ¸ā§āĻ¤āĻŦāĻžāĻ¯āĻŧāĻ¨ā§āĻ° āĻ¸āĻžāĻ§āĻžāĻ°āĻŖ āĻŦāĻ°ā§āĻŖāĻ¨āĻž
āĻĒāĻ°āĻŋāĻˇā§āĻŦāĻž āĻā§āĻŦāĻŋāĻ˛ āĻŦā§āĻ¯āĻŦāĻšā§āĻ¤
CREATE TABLE endpoint
(
id SERIAL ,
host text
);
TABLE database
(
id SERIAL ,
âĻ
last_aws_log_time text ,
last_aws_nexttoken text ,
aws_max_item_size integer
);
last_aws_log_time â вŅĐĩĐŧĐĩĐŊĐŊĐ°Ņ ĐŧĐĩŅĐēĐ° ĐŋĐžŅĐģĐĩĐ´ĐŊĐĩĐŗĐž СаĐŗŅŅĐļĐĩĐŊĐŊĐžĐŗĐž ĐģĐžĐŗ-ŅĐ°ĐšĐģĐ° в ŅĐžŅĐŧĐ°ŅĐĩ YYYY-MM-DD-HH24.
last_aws_nexttoken â ŅĐĩĐēŅŅОваŅ ĐŧĐĩŅĐēĐ° ĐŋĐžŅĐģĐĩĐ´ĐŊĐĩĐš СаĐŗŅŅĐļĐĩĐŊĐŊОК ĐŋĐžŅŅии.
aws_max_item_size- ŅĐŧĐŋиŅиŅĐĩŅĐēиĐŧ ĐŋŅŅĐĩĐŧ, ĐŋОдОйŅĐ°ĐŊĐŊŅĐš ĐŊĐ°ŅĐ°ĐģŅĐŊŅĐš ŅаСĐŧĐĩŅ ĐŋĐžŅŅии.
āĻ¸āĻŽā§āĻĒā§āĻ°ā§āĻŖ āĻ¸ā§āĻā§āĻ°āĻŋāĻĒā§āĻ āĻĒāĻžāĻ ā§āĻ¯
āĻĄāĻžāĻāĻ¨āĻ˛ā§āĻĄ_aws_piece.sh
#!/bin/bash
#########################################################
# download_aws_piece.sh
# downloan piece of log from AWS
# version HABR
let min_item_size=1024
let max_item_size=1048576
let growth_factor=3
let growth_counter=1
let growth_counter_max=3
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh:''STARTED'
AWS_LOG_TIME=$1
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh:AWS_LOG_TIME='$AWS_LOG_TIME
database_id=$2
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh:database_id='$database_id
RESULT_FILE=$3
endpoint=`psql -h MONITOR_ENDPOINT.rds.amazonaws.com -U USER -d MONITOR_DATABASE_DATABASE -A -t -c "select e.host from endpoint e join database d on e.id = d.endpoint_id where d.id = $database_id "`
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh:endpoint='$endpoint
db_instance=`echo $endpoint | awk -F"." '{print toupper($1)}'`
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh:db_instance='$db_instance
LOG_FILE=$RESULT_FILE'.tmp_log'
TMP_FILE=$LOG_FILE'.tmp'
TMP_MIDDLE=$LOG_FILE'.tmp_mid'
TMP_MIDDLE2=$LOG_FILE'.tmp_mid2'
current_aws_log_time=`psql -h MONITOR_ENDPOINT.rds.amazonaws.com -U USER -d MONITOR_DATABASE -A -t -c "select last_aws_log_time from database where id = $database_id "`
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh:current_aws_log_time='$current_aws_log_time
if [[ $current_aws_log_time != $AWS_LOG_TIME ]];
then
is_new_log='1'
if ! psql -h MONITOR_ENDPOINT.rds.amazonaws.com -U USER -d MONITOR_DATABASE -v ON_ERROR_STOP=1 -A -t -q -c "update database set last_aws_log_time = '$AWS_LOG_TIME' where id = $database_id "
then
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: FATAL_ERROR - update database set last_aws_log_time .'
exit 1
fi
else
is_new_log='0'
fi
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh:is_new_log='$is_new_log
let last_aws_max_item_size=`psql -h MONITOR_ENDPOINT.rds.amazonaws.com -U USER -d MONITOR_DATABASE -A -t -c "select aws_max_item_size from database where id = $database_id "`
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: last_aws_max_item_size='$last_aws_max_item_size
let count=1
if [[ $is_new_log == '1' ]];
then
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: START DOWNLOADING OF NEW AWS LOG'
if ! aws rds download-db-log-file-portion
--max-items $last_aws_max_item_size
--region REGION
--db-instance-identifier $db_instance
--log-file-name error/postgresql.log.$AWS_LOG_TIME > $LOG_FILE
then
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: FATAL_ERROR - Could not get log from AWS .'
exit 2
fi
else
next_token=`psql -h MONITOR_ENDPOINT.rds.amazonaws.com -U USER -d MONITOR_DATABASE -v ON_ERROR_STOP=1 -A -t -c "select last_aws_nexttoken from database where id = $database_id "`
if [[ $next_token == '' ]];
then
next_token='0'
fi
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: CONTINUE DOWNLOADING OF AWS LOG'
if ! aws rds download-db-log-file-portion
--max-items $last_aws_max_item_size
--starting-token $next_token
--region REGION
--db-instance-identifier $db_instance
--log-file-name error/postgresql.log.$AWS_LOG_TIME > $LOG_FILE
then
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: FATAL_ERROR - Could not get log from AWS .'
exit 3
fi
line_count=`cat $LOG_FILE | wc -l`
let lines=$line_count-1
tail -$lines $LOG_FILE > $TMP_MIDDLE
mv -f $TMP_MIDDLE $LOG_FILE
fi
next_token_str=`cat $LOG_FILE | grep NEXTTOKEN`
next_token=`echo $next_token_str | awk -F" " '{ print $2}' `
grep -v NEXTTOKEN $LOG_FILE > $TMP_FILE
if [[ $next_token == '' ]];
then
cp $TMP_FILE $RESULT_FILE
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: NEXTTOKEN NOT FOUND - FINISH '
rm $LOG_FILE
rm $TMP_FILE
rm $TMP_MIDDLE
rm $TMP_MIDDLE2
exit 0
else
psql -h MONITOR_ENDPOINT.rds.amazonaws.com -U USER -d MONITOR_DATABASE -v ON_ERROR_STOP=1 -A -t -q -c "update database set last_aws_nexttoken = '$next_token' where id = $database_id "
fi
first_str=`tail -1 $TMP_FILE`
line_count=`cat $TMP_FILE | wc -l`
let lines=$line_count-1
head -$lines $TMP_FILE > $RESULT_FILE
###############################################
# MAIN CIRCLE
let count=2
while [[ $next_token != '' ]];
do
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: count='$count
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: START DOWNLOADING OF AWS LOG'
if ! aws rds download-db-log-file-portion
--max-items $last_aws_max_item_size
--starting-token $next_token
--region REGION
--db-instance-identifier $db_instance
--log-file-name error/postgresql.log.$AWS_LOG_TIME > $LOG_FILE
then
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: FATAL_ERROR - Could not get log from AWS .'
exit 4
fi
next_token_str=`cat $LOG_FILE | grep NEXTTOKEN`
next_token=`echo $next_token_str | awk -F" " '{ print $2}' `
TMP_FILE=$LOG_FILE'.tmp'
grep -v NEXTTOKEN $LOG_FILE > $TMP_FILE
last_str=`head -1 $TMP_FILE`
if [[ $next_token == '' ]];
then
concat_str=$first_str$last_str
echo $concat_str >> $RESULT_FILE
line_count=`cat $TMP_FILE | wc -l`
let lines=$line_count-1
tail -$lines $TMP_FILE >> $RESULT_FILE
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: NEXTTOKEN NOT FOUND - FINISH '
rm $LOG_FILE
rm $TMP_FILE
rm $TMP_MIDDLE
rm $TMP_MIDDLE2
exit 0
fi
if [[ $next_token != '' ]];
then
let growth_counter=$growth_counter+1
if [[ $growth_counter -gt $growth_counter_max ]];
then
let last_aws_max_item_size=$last_aws_max_item_size*$growth_factor
let growth_counter=1
fi
if [[ $last_aws_max_item_size -gt $max_item_size ]];
then
let last_aws_max_item_size=$max_item_size
fi
psql -h MONITOR_ENDPOINT.rds.amazonaws.com -U USER -d MONITOR_DATABASE -A -t -q -c "update database set last_aws_nexttoken = '$next_token' where id = $database_id "
concat_str=$first_str$last_str
echo $concat_str >> $RESULT_FILE
line_count=`cat $TMP_FILE | wc -l`
let lines=$line_count-1
#############################
#Get middle of file
head -$lines $TMP_FILE > $TMP_MIDDLE
line_count=`cat $TMP_MIDDLE | wc -l`
let lines=$line_count-1
tail -$lines $TMP_MIDDLE > $TMP_MIDDLE2
cat $TMP_MIDDLE2 >> $RESULT_FILE
first_str=`tail -1 $TMP_FILE`
fi
let count=$count+1
done
#
#################################################################
exit 0
āĻāĻŋāĻā§ āĻŦā§āĻ¯āĻžāĻā§āĻ¯āĻž āĻ¸āĻš āĻ¸ā§āĻā§āĻ°āĻŋāĻĒā§āĻ āĻāĻŖā§āĻĄ:
āĻ¸ā§āĻā§āĻ°āĻŋāĻĒā§āĻ āĻāĻ¨āĻĒā§āĻ āĻĒāĻ°āĻžāĻŽāĻŋāĻ¤āĻŋ:
- YYYY-MM-DD-HH24 āĻĢāĻ°āĻŽā§āĻ¯āĻžāĻā§ āĻ˛āĻ āĻĢāĻžāĻāĻ˛ āĻ¨āĻžāĻŽā§āĻ° āĻāĻžāĻāĻŽāĻ¸ā§āĻā§āĻ¯āĻžāĻŽā§āĻĒ: AWS_LOG_TIME=$1
- āĻĄāĻžāĻāĻžāĻŦā§āĻ¸ āĻāĻāĻĄāĻŋ: database_id=$2
- āĻ¸āĻāĻā§āĻšā§āĻ¤ āĻ˛āĻ āĻĢāĻžāĻāĻ˛ā§āĻ° āĻ¨āĻžāĻŽ: RESULT_FILE=$3
āĻļā§āĻˇ āĻ˛ā§āĻĄ āĻāĻ°āĻž āĻ˛āĻ āĻĢāĻžāĻāĻ˛ā§āĻ° āĻāĻžāĻāĻŽāĻ¸ā§āĻā§āĻ¯āĻžāĻŽā§āĻĒ āĻĒāĻžāĻ¨:
current_aws_log_time=`psql -h MONITOR_ENDPOINT.rds.amazonaws.com -U USER -d MONITOR_DATABASE -A -t -c "select last_aws_log_time from database where id = $database_id "`
āĻ¯āĻĻāĻŋ āĻļā§āĻˇ āĻ˛ā§āĻĄ āĻāĻ°āĻž āĻ˛āĻ āĻĢāĻžāĻāĻ˛ā§āĻ° āĻāĻžāĻāĻŽāĻ¸ā§āĻā§āĻ¯āĻžāĻŽā§āĻĒ āĻāĻ¨āĻĒā§āĻ āĻĒā§āĻ¯āĻžāĻ°āĻžāĻŽāĻŋāĻāĻžāĻ°ā§āĻ° āĻ¸āĻžāĻĨā§ āĻŽā§āĻ˛ā§ āĻ¨āĻž, āĻāĻāĻāĻŋ āĻ¨āĻ¤ā§āĻ¨ āĻ˛āĻ āĻĢāĻžāĻāĻ˛ āĻ˛ā§āĻĄ āĻāĻ°āĻž āĻšāĻ¯āĻŧ:
if [[ $current_aws_log_time != $AWS_LOG_TIME ]];
then
is_new_log='1'
if ! psql -h ENDPOINT.rds.amazonaws.com -U USER -d MONITOR_DATABASE -v ON_ERROR_STOP=1 -A -t -c "update database set last_aws_log_time = '$AWS_LOG_TIME' where id = $database_id "
then
echo '***download_aws_piece.sh -FATAL_ERROR - update database set last_aws_log_time .'
exit 1
fi
else
is_new_log='0'
fi
āĻāĻŽāĻ°āĻž āĻĄāĻžāĻāĻ¨āĻ˛ā§āĻĄ āĻāĻ°āĻž āĻĢāĻžāĻāĻ˛ āĻĨā§āĻā§ āĻĒāĻ°āĻŦāĻ°ā§āĻ¤ā§ āĻā§āĻā§āĻ¨ āĻ˛ā§āĻŦā§āĻ˛ā§āĻ° āĻŽāĻžāĻ¨ āĻĒāĻžāĻ:
next_token_str=`cat $LOG_FILE | grep NEXTTOKEN`
next_token=`echo $next_token_str | awk -F" " '{ print $2}' `
āĻāĻāĻāĻŋ āĻāĻžāĻ˛āĻŋ āĻ¨ā§āĻā§āĻ¸āĻāĻā§āĻā§āĻ¨ āĻŽāĻžāĻ¨ āĻĄāĻžāĻāĻ¨āĻ˛ā§āĻĄ āĻļā§āĻˇ āĻšāĻāĻ¯āĻŧāĻžāĻ° āĻāĻŋāĻšā§āĻ¨ āĻšāĻŋāĻ¸āĻžāĻŦā§ āĻāĻžāĻ āĻāĻ°ā§āĨ¤
āĻāĻāĻāĻŋ āĻ˛ā§āĻĒā§, āĻāĻŽāĻ°āĻž āĻĢāĻžāĻāĻ˛ā§āĻ° āĻ
āĻāĻļāĻā§āĻ˛āĻŋ āĻāĻŖāĻ¨āĻž āĻāĻ°āĻŋ, āĻĒāĻĨā§ āĻ˛āĻžāĻāĻ¨āĻā§āĻ˛āĻŋāĻā§ āĻ¸āĻāĻ¯ā§āĻā§āĻ¤ āĻāĻ°āĻŋ āĻāĻŦāĻ āĻ
āĻāĻļā§āĻ° āĻāĻāĻžāĻ° āĻŦāĻžāĻĄāĻŧāĻžāĻ:
āĻĒā§āĻ°āĻ§āĻžāĻ¨ āĻ˛ā§āĻĒ
# MAIN CIRCLE
let count=2
while [[ $next_token != '' ]];
do
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: count='$count
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: START DOWNLOADING OF AWS LOG'
if ! aws rds download-db-log-file-portion
--max-items $last_aws_max_item_size
--starting-token $next_token
--region REGION
--db-instance-identifier $db_instance
--log-file-name error/postgresql.log.$AWS_LOG_TIME > $LOG_FILE
then
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: FATAL_ERROR - Could not get log from AWS .'
exit 4
fi
next_token_str=`cat $LOG_FILE | grep NEXTTOKEN`
next_token=`echo $next_token_str | awk -F" " '{ print $2}' `
TMP_FILE=$LOG_FILE'.tmp'
grep -v NEXTTOKEN $LOG_FILE > $TMP_FILE
last_str=`head -1 $TMP_FILE`
if [[ $next_token == '' ]];
then
concat_str=$first_str$last_str
echo $concat_str >> $RESULT_FILE
line_count=`cat $TMP_FILE | wc -l`
let lines=$line_count-1
tail -$lines $TMP_FILE >> $RESULT_FILE
echo $(date +%Y%m%d%H%M)': download_aws_piece.sh: NEXTTOKEN NOT FOUND - FINISH '
rm $LOG_FILE
rm $TMP_FILE
rm $TMP_MIDDLE
rm $TMP_MIDDLE2
exit 0
fi
if [[ $next_token != '' ]];
then
let growth_counter=$growth_counter+1
if [[ $growth_counter -gt $growth_counter_max ]];
then
let last_aws_max_item_size=$last_aws_max_item_size*$growth_factor
let growth_counter=1
fi
if [[ $last_aws_max_item_size -gt $max_item_size ]];
then
let last_aws_max_item_size=$max_item_size
fi
psql -h MONITOR_ENDPOINT.rds.amazonaws.com -U USER -d MONITOR_DATABASE -A -t -q -c "update database set last_aws_nexttoken = '$next_token' where id = $database_id "
concat_str=$first_str$last_str
echo $concat_str >> $RESULT_FILE
line_count=`cat $TMP_FILE | wc -l`
let lines=$line_count-1
#############################
#Get middle of file
head -$lines $TMP_FILE > $TMP_MIDDLE
line_count=`cat $TMP_MIDDLE | wc -l`
let lines=$line_count-1
tail -$lines $TMP_MIDDLE > $TMP_MIDDLE2
cat $TMP_MIDDLE2 >> $RESULT_FILE
first_str=`tail -1 $TMP_FILE`
fi
let count=$count+1
done
āĻāĻ°āĻĒāĻ° āĻāĻŋ?
āĻ¸ā§āĻ¤āĻ°āĻžāĻ, āĻĒā§āĻ°āĻĨāĻŽ āĻŽāĻ§ā§āĻ¯āĻŦāĻ°ā§āĻ¤ā§ āĻāĻžāĻ - "āĻā§āĻ˛āĻžāĻāĻĄ āĻĨā§āĻā§ āĻāĻāĻāĻŋ āĻ˛āĻ āĻĢāĻžāĻāĻ˛ āĻĄāĻžāĻāĻ¨āĻ˛ā§āĻĄ āĻāĻ°ā§āĻ¨" āĻ¸āĻŽāĻžāĻ§āĻžāĻ¨ āĻāĻ°āĻž āĻšāĻ¯āĻŧā§āĻā§āĨ¤ āĻĄāĻžāĻāĻ¨āĻ˛ā§āĻĄ āĻ˛āĻ āĻĻāĻŋāĻ¯āĻŧā§ āĻāĻŋ āĻāĻ°āĻŦā§āĻ¨?
āĻĒā§āĻ°āĻĨāĻŽā§, āĻāĻĒāĻ¨āĻžāĻā§ āĻ˛āĻ āĻĢāĻžāĻāĻ˛āĻāĻŋ āĻĒāĻžāĻ°ā§āĻ¸ āĻāĻ°āĻ¤ā§ āĻšāĻŦā§ āĻāĻŦāĻ āĻāĻāĻŋ āĻĨā§āĻā§ āĻĒā§āĻ°āĻā§āĻ¤ āĻ
āĻ¨ā§āĻ°ā§āĻ§āĻā§āĻ˛āĻŋ āĻŦā§āĻ° āĻāĻ°āĻ¤ā§ āĻšāĻŦā§āĨ¤
āĻāĻžāĻāĻāĻŋ āĻā§āĻŦ āĻāĻ āĻŋāĻ¨ āĻ¨āĻ¯āĻŧāĨ¤ āĻ¸āĻšāĻāĻ¤āĻŽ āĻŦā§āĻ¯āĻžāĻļ āĻ¸ā§āĻā§āĻ°āĻŋāĻĒā§āĻāĻāĻŋ āĻŦā§āĻļ āĻāĻžāĻ˛ āĻāĻžāĻ āĻāĻ°ā§āĨ¤
upload_log_query.sh
#!/bin/bash
#########################################################
# upload_log_query.sh
# Upload table table from dowloaded aws file
# version HABR
###########################################################
echo 'TIMESTAMP:'$(date +%c)' Upload log_query table '
source_file=$1
echo 'source_file='$source_file
database_id=$2
echo 'database_id='$database_id
beginer=' '
first_line='1'
let "line_count=0"
sql_line=' '
sql_flag=' '
space=' '
cat $source_file | while read line
do
line="$space$line"
if [[ $first_line == "1" ]]; then
beginer=`echo $line | awk -F" " '{ print $1}' `
first_line='0'
fi
current_beginer=`echo $line | awk -F" " '{ print $1}' `
if [[ $current_beginer == $beginer ]]; then
if [[ $sql_flag == '1' ]]; then
sql_flag='0'
log_date=`echo $sql_line | awk -F" " '{ print $1}' `
log_time=`echo $sql_line | awk -F" " '{ print $2}' `
duration=`echo $sql_line | awk -F" " '{ print $5}' `
#replace ' to ''
sql_modline=`echo "$sql_line" | sed 's/'''/''''''/g'`
sql_line=' '
################
#PROCESSING OF THE SQL-SELECT IS HERE
if ! psql -h ENDPOINT.rds.amazonaws.com -U USER -d DATABASE -v ON_ERROR_STOP=1 -A -t -c "select log_query('$ip_port',$database_id , '$log_date' , '$log_time' , '$duration' , '$sql_modline' )"
then
echo 'FATAL_ERROR - log_query '
exit 1
fi
################
fi #if [[ $sql_flag == '1' ]]; then
let "line_count=line_count+1"
check=`echo $line | awk -F" " '{ print $8}' `
check_sql=${check^^}
#echo 'check_sql='$check_sql
if [[ $check_sql == 'SELECT' ]]; then
sql_flag='1'
sql_line="$sql_line$line"
ip_port=`echo $sql_line | awk -F":" '{ print $4}' `
fi
else
if [[ $sql_flag == '1' ]]; then
sql_line="$sql_line$line"
fi
fi #if [[ $current_beginer == $beginer ]]; then
done
āĻāĻāĻ¨ āĻāĻĒāĻ¨āĻŋ āĻ˛āĻ āĻĢāĻžāĻāĻ˛ āĻĨā§āĻā§ āĻ¨āĻŋāĻ°ā§āĻŦāĻžāĻāĻŋāĻ¤ āĻ āĻ¨ā§āĻ°ā§āĻ§ā§āĻ° āĻ¸āĻžāĻĨā§ āĻāĻžāĻ āĻāĻ°āĻ¤ā§ āĻĒāĻžāĻ°ā§āĻ¨āĨ¤
āĻāĻŦāĻ āĻŦā§āĻļ āĻāĻŋāĻā§ āĻĻāĻ°āĻāĻžāĻ°ā§ āĻ¸ā§āĻ¯ā§āĻ āĻāĻ¨ā§āĻŽā§āĻā§āĻ¤āĨ¤
āĻĒāĻžāĻ°ā§āĻ¸ āĻāĻ°āĻž āĻĒā§āĻ°āĻļā§āĻ¨ āĻā§āĻĨāĻžāĻ āĻ¸āĻāĻ°āĻā§āĻˇāĻŖ āĻāĻ°āĻž āĻĒā§āĻ°āĻ¯āĻŧā§āĻāĻ¨. āĻāĻ° āĻāĻ¨ā§āĻ¯ āĻāĻāĻāĻŋ āĻĒāĻ°āĻŋāĻˇā§āĻŦāĻž āĻā§āĻŦāĻŋāĻ˛ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°āĻž āĻšāĻ¯āĻŧ log_query
CREATE TABLE log_query
(
id SERIAL ,
queryid bigint ,
query_md5hash text not null ,
database_id integer not null ,
timepoint timestamp without time zone not null,
duration double precision not null ,
query text not null ,
explained_plan text[],
plan_md5hash text ,
explained_plan_wo_costs text[],
plan_hash_value text ,
baseline_id integer ,
ip text ,
port text
);
ALTER TABLE log_query ADD PRIMARY KEY (id);
ALTER TABLE log_query ADD CONSTRAINT queryid_timepoint_unique_key UNIQUE (queryid, timepoint );
ALTER TABLE log_query ADD CONSTRAINT query_md5hash_timepoint_unique_key UNIQUE (query_md5hash, timepoint );
CREATE INDEX log_query_timepoint_idx ON log_query (timepoint);
CREATE INDEX log_query_queryid_idx ON log_query (queryid);
ALTER TABLE log_query ADD CONSTRAINT database_id_fk FOREIGN KEY (database_id) REFERENCES database (id) ON DELETE CASCADE ;
āĻĒāĻžāĻ°ā§āĻ¸ āĻāĻ°āĻž āĻ
āĻ¨ā§āĻ°ā§āĻ§ āĻĒā§āĻ°āĻā§āĻ°āĻŋāĻ¯āĻŧāĻž āĻāĻ°āĻž āĻšāĻ¯āĻŧ plpgsql āĻĢāĻžāĻāĻļāĻ¨ "log_queryÂģ.
log_query.sql
--log_query.sql
--verison HABR
CREATE OR REPLACE FUNCTION log_query( ip_port text ,log_database_id integer , log_date text , log_time text , duration text , sql_line text ) RETURNS boolean AS $$
DECLARE
result boolean ;
log_timepoint timestamp without time zone ;
log_duration double precision ;
pos integer ;
log_query text ;
activity_string text ;
log_md5hash text ;
log_explain_plan text[] ;
log_planhash text ;
log_plan_wo_costs text[] ;
database_rec record ;
pg_stat_query text ;
test_log_query text ;
log_query_rec record;
found_flag boolean;
pg_stat_history_rec record ;
port_start integer ;
port_end integer ;
client_ip text ;
client_port text ;
log_queryid bigint ;
log_query_text text ;
pg_stat_query_text text ;
BEGIN
result = TRUE ;
RAISE NOTICE '***log_query';
port_start = position('(' in ip_port);
port_end = position(')' in ip_port);
client_ip = substring( ip_port from 1 for port_start-1 );
client_port = substring( ip_port from port_start+1 for port_end-port_start-1 );
SELECT e.host , d.name , d.owner_pwd
INTO database_rec
FROM database d JOIN endpoint e ON e.id = d.endpoint_id
WHERE d.id = log_database_id ;
log_timepoint = to_timestamp(log_date||' '||log_time,'YYYY-MM-DD HH24-MI-SS');
log_duration = duration:: double precision;
pos = position ('SELECT' in UPPER(sql_line) );
log_query = substring( sql_line from pos for LENGTH(sql_line));
log_query = regexp_replace(log_query,' +',' ','g');
log_query = regexp_replace(log_query,';+','','g');
log_query = trim(trailing ' ' from log_query);
log_md5hash = md5( log_query::text );
--Explain execution plan--
EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||database_rec.host||' dbname='||database_rec.name||' user=DATABASE password='||database_rec.owner_pwd||' '')';
log_explain_plan = ARRAY ( SELECT * FROM dblink('LINK1', 'EXPLAIN '||log_query ) AS t (plan text) );
log_plan_wo_costs = ARRAY ( SELECT * FROM dblink('LINK1', 'EXPLAIN ( COSTS FALSE ) '||log_query ) AS t (plan text) );
PERFORM dblink_disconnect('LINK1');
--------------------------
BEGIN
INSERT INTO log_query
(
query_md5hash ,
database_id ,
timepoint ,
duration ,
query ,
explained_plan ,
plan_md5hash ,
explained_plan_wo_costs ,
plan_hash_value ,
ip ,
port
)
VALUES
(
log_md5hash ,
log_database_id ,
log_timepoint ,
log_duration ,
log_query ,
log_explain_plan ,
md5(log_explain_plan::text) ,
log_plan_wo_costs ,
md5(log_plan_wo_costs::text),
client_ip ,
client_port
);
activity_string = 'New query has logged '||
' database_id = '|| log_database_id ||
' query_md5hash='||log_md5hash||
' , timepoint = '||to_char(log_timepoint,'YYYYMMDD HH24:MI:SS');
RAISE NOTICE '%',activity_string;
PERFORM pg_log( log_database_id , 'log_query' , activity_string);
EXCEPTION
WHEN unique_violation THEN
RAISE NOTICE '*** unique_violation *** query already has logged';
END;
SELECT queryid
INTO log_queryid
FROM log_query
WHERE query_md5hash = log_md5hash AND
timepoint = log_timepoint;
IF log_queryid IS NOT NULL
THEN
RAISE NOTICE 'log_query with query_md5hash = % and timepoint = % has already has a QUERYID = %',log_md5hash,log_timepoint , log_queryid ;
RETURN result;
END IF;
------------------------------------------------
RAISE NOTICE 'Update queryid';
SELECT *
INTO log_query_rec
FROM log_query
WHERE query_md5hash = log_md5hash AND timepoint = log_timepoint ;
log_query_rec.query=regexp_replace(log_query_rec.query,';+','','g');
FOR pg_stat_history_rec IN
SELECT
queryid ,
query
FROM
pg_stat_db_queries
WHERE
database_id = log_database_id AND
queryid is not null
LOOP
pg_stat_query = pg_stat_history_rec.query ;
pg_stat_query=regexp_replace(pg_stat_query,'n+',' ','g');
pg_stat_query=regexp_replace(pg_stat_query,'t+',' ','g');
pg_stat_query=regexp_replace(pg_stat_query,' +',' ','g');
pg_stat_query=regexp_replace(pg_stat_query,'$.','%','g');
log_query_text = trim(trailing ' ' from log_query_rec.query);
pg_stat_query_text = pg_stat_query;
--SELECT log_query_rec.query like pg_stat_query INTO found_flag ;
IF (log_query_text LIKE pg_stat_query_text) THEN
found_flag = TRUE ;
ELSE
found_flag = FALSE ;
END IF;
IF found_flag THEN
UPDATE log_query SET queryid = pg_stat_history_rec.queryid WHERE query_md5hash = log_md5hash AND timepoint = log_timepoint ;
activity_string = ' updated queryid = '||pg_stat_history_rec.queryid||
' for log_query with id = '||log_query_rec.id
;
RAISE NOTICE '%',activity_string;
EXIT ;
END IF ;
END LOOP ;
RETURN result ;
END
$$ LANGUAGE plpgsql;
āĻĒā§āĻ°āĻā§āĻ°āĻŋāĻ¯āĻŧāĻžāĻāĻ°āĻŖā§āĻ° āĻ¸āĻŽāĻ¯āĻŧ āĻāĻāĻāĻŋ āĻĒāĻ°āĻŋāĻˇā§āĻŦāĻž āĻā§āĻŦāĻŋāĻ˛ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°āĻž āĻšāĻ¯āĻŧ pg_stat_db_queries, āĻā§āĻŦāĻŋāĻ˛ āĻĨā§āĻā§ āĻŦāĻ°ā§āĻ¤āĻŽāĻžāĻ¨ āĻĒā§āĻ°āĻļā§āĻ¨ā§āĻ° āĻāĻāĻāĻŋ āĻ¸ā§āĻ¨ā§āĻ¯āĻžāĻĒāĻļāĻ āĻ°āĻ¯āĻŧā§āĻā§ pg_stat_history (āĻā§āĻŦāĻŋāĻ˛ā§āĻ° āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻāĻžāĻ¨ā§ āĻŦāĻ°ā§āĻŖāĻ¨āĻž āĻāĻ°āĻž āĻšāĻ¯āĻŧā§āĻā§ -
TABLE pg_stat_db_queries
(
database_id integer,
queryid bigint ,
query text ,
max_time double precision
);
TABLE pg_stat_history
(
âĻ
database_id integer ,
âĻ
queryid bigint ,
âĻ
max_time double precision ,
âĻ
);
āĻĢāĻžāĻāĻļāĻ¨āĻāĻŋ āĻāĻĒāĻ¨āĻžāĻā§ āĻāĻāĻāĻŋ āĻ˛āĻ āĻĢāĻžāĻāĻ˛ āĻĨā§āĻā§ āĻ āĻ¨ā§āĻ°ā§āĻ§ āĻĒā§āĻ°āĻā§āĻ°āĻŋāĻ¯āĻŧāĻžāĻāĻ°āĻŖā§āĻ° āĻāĻ¨ā§āĻ¯ āĻŦā§āĻļ āĻāĻ¯āĻŧā§āĻāĻāĻŋ āĻĻāĻ°āĻāĻžāĻ°ā§ āĻā§āĻˇāĻŽāĻ¤āĻž āĻĒā§āĻ°āĻ¯āĻŧā§āĻ āĻāĻ°āĻ¤ā§ āĻĻā§āĻ¯āĻŧāĨ¤ āĻ¯āĻĨāĻž:
āĻ¸ā§āĻ¯ā§āĻ #1 - āĻā§āĻ¯āĻŧā§āĻ°āĻŋ āĻ¸āĻŽā§āĻĒāĻžāĻĻāĻ¨ā§āĻ° āĻāĻ¤āĻŋāĻšāĻžāĻ¸
āĻāĻāĻāĻŋ āĻāĻ°ā§āĻŽāĻā§āĻˇāĻŽāĻ¤āĻž āĻāĻāĻ¨āĻž āĻ¸āĻŽāĻžāĻ§āĻžāĻ¨ āĻļā§āĻ°ā§ āĻāĻ°āĻžāĻ° āĻāĻ¨ā§āĻ¯ āĻā§āĻŦ āĻĻāĻ°āĻāĻžāĻ°ā§. āĻĒā§āĻ°āĻĨāĻŽāĻ¤, āĻāĻ¤āĻŋāĻšāĻžāĻ¸ā§āĻ° āĻ¸āĻžāĻĨā§ āĻĒāĻ°āĻŋāĻāĻŋāĻ¤ āĻšāĻ¨ - āĻŽāĻ¨ā§āĻĨāĻ°āĻ¤āĻž āĻāĻāĻ¨ āĻļā§āĻ°ā§ āĻšāĻ¯āĻŧā§āĻāĻŋāĻ˛?
āĻ¤āĻžāĻ°āĻĒāĻ°, āĻā§āĻ˛āĻžāĻ¸āĻŋāĻ āĻ
āĻ¨ā§āĻ¸āĻžāĻ°ā§, āĻŦāĻžāĻšā§āĻ¯āĻŋāĻ āĻāĻžāĻ°āĻŖāĻā§āĻ˛āĻŋ āĻ¸āĻ¨ā§āĻ§āĻžāĻ¨ āĻāĻ°ā§āĻ¨āĨ¤ āĻšāĻ¤ā§ āĻĒāĻžāĻ°ā§ āĻĄāĻžāĻāĻžāĻŦā§āĻ¸ āĻ˛ā§āĻĄ āĻā§āĻŦāĻ˛ āĻ¤ā§āĻŦā§āĻ°āĻāĻžāĻŦā§ āĻŦā§āĻĻā§āĻ§āĻŋ āĻĒā§āĻ¯āĻŧā§āĻā§ āĻāĻŦāĻ āĻ¨āĻŋāĻ°ā§āĻĻāĻŋāĻˇā§āĻ āĻ
āĻ¨ā§āĻ°ā§āĻ§ā§āĻ° āĻ¸āĻžāĻĨā§ āĻāĻ° āĻāĻŋāĻā§āĻ āĻāĻ°āĻžāĻ° āĻ¨ā§āĻāĨ¤
log_query āĻā§āĻŦāĻŋāĻ˛ā§ āĻāĻāĻāĻŋ āĻ¨āĻ¤ā§āĻ¨ āĻāĻ¨ā§āĻā§āĻ°āĻŋ āĻ¯ā§āĻ āĻāĻ°ā§āĻ¨
port_start = position('(' in ip_port);
port_end = position(')' in ip_port);
client_ip = substring( ip_port from 1 for port_start-1 );
client_port = substring( ip_port from port_start+1 for port_end-port_start-1 );
SELECT e.host , d.name , d.owner_pwd
INTO database_rec
FROM database d JOIN endpoint e ON e.id = d.endpoint_id
WHERE d.id = log_database_id ;
log_timepoint = to_timestamp(log_date||' '||log_time,'YYYY-MM-DD HH24-MI-SS');
log_duration = to_number(duration,'99999999999999999999D9999999999');
pos = position ('SELECT' in UPPER(sql_line) );
log_query = substring( sql_line from pos for LENGTH(sql_line));
log_query = regexp_replace(log_query,' +',' ','g');
log_query = regexp_replace(log_query,';+','','g');
log_query = trim(trailing ' ' from log_query);
RAISE NOTICE 'log_query=%',log_query ;
log_md5hash = md5( log_query::text );
--Explain execution plan--
EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||database_rec.host||' dbname='||database_rec.name||' user=DATABASE password='||database_rec.owner_pwd||' '')';
log_explain_plan = ARRAY ( SELECT * FROM dblink('LINK1', 'EXPLAIN '||log_query ) AS t (plan text) );
log_plan_wo_costs = ARRAY ( SELECT * FROM dblink('LINK1', 'EXPLAIN ( COSTS FALSE ) '||log_query ) AS t (plan text) );
PERFORM dblink_disconnect('LINK1');
--------------------------
BEGIN
INSERT INTO log_query
(
query_md5hash ,
database_id ,
timepoint ,
duration ,
query ,
explained_plan ,
plan_md5hash ,
explained_plan_wo_costs ,
plan_hash_value ,
ip ,
port
)
VALUES
(
log_md5hash ,
log_database_id ,
log_timepoint ,
log_duration ,
log_query ,
log_explain_plan ,
md5(log_explain_plan::text) ,
log_plan_wo_costs ,
md5(log_plan_wo_costs::text),
client_ip ,
client_port
);
āĻ¸āĻŽā§āĻāĻžāĻŦāĻ¨āĻž #2 - āĻā§āĻ¯ā§āĻ¯āĻŧāĻžāĻ°ā§ āĻāĻā§āĻ¸āĻŋāĻāĻŋāĻāĻļāĻ¨ āĻĒā§āĻ˛ā§āĻ¯āĻžāĻ¨ āĻ¸ā§āĻ āĻāĻ°ā§āĻ¨
āĻāĻ āĻŽā§āĻšā§āĻ°ā§āĻ¤ā§ āĻāĻāĻāĻŋ āĻāĻĒāĻ¤ā§āĻ¤āĻŋ-āĻ¸ā§āĻĒāĻˇā§āĻā§āĻāĻ°āĻŖ-āĻŽāĻ¨ā§āĻ¤āĻŦā§āĻ¯ āĻāĻ āĻ¤ā§ āĻĒāĻžāĻ°ā§: "āĻāĻŋāĻ¨ā§āĻ¤ā§ āĻāĻ¤āĻŋāĻŽāĻ§ā§āĻ¯ā§ āĻ¸ā§āĻŦāĻ¯āĻŧāĻāĻā§āĻ°āĻŋāĻ¯āĻŧ āĻŦā§āĻ¯āĻžāĻā§āĻ¯āĻž āĻāĻā§" āĻšā§āĻ¯āĻžāĻ, āĻāĻāĻž āĻāĻā§, āĻāĻŋāĻ¨ā§āĻ¤ā§ āĻ¯āĻĻāĻŋ āĻāĻā§āĻ¸āĻŋāĻāĻŋāĻāĻļāĻ¨ āĻĒā§āĻ˛ā§āĻ¯āĻžāĻ¨āĻāĻŋ āĻāĻāĻ āĻ˛āĻ āĻĢāĻžāĻāĻ˛ā§ āĻ¸āĻāĻ°āĻā§āĻˇāĻŖ āĻāĻ°āĻž āĻšāĻ¯āĻŧ āĻāĻŦāĻ āĻāĻ°āĻ āĻŦāĻŋāĻļā§āĻ˛ā§āĻˇāĻŖā§āĻ° āĻāĻ¨ā§āĻ¯ āĻāĻāĻŋ āĻ¸āĻāĻ°āĻā§āĻˇāĻŖ āĻāĻ°āĻžāĻ° āĻāĻ¨ā§āĻ¯, āĻāĻĒāĻ¨āĻžāĻā§ āĻ˛āĻ āĻĢāĻžāĻāĻ˛āĻāĻŋ āĻĒāĻžāĻ°ā§āĻ¸ āĻāĻ°āĻ¤ā§ āĻšāĻŦā§ āĻ¤āĻžāĻšāĻ˛ā§ āĻāĻŋ āĻ˛āĻžāĻ?
āĻāĻŽāĻžāĻ° āĻ¯āĻž āĻĻāĻ°āĻāĻžāĻ° āĻāĻŋāĻ˛ āĻ¤āĻž āĻšāĻ˛:
āĻĒā§āĻ°āĻĨāĻŽ: āĻ¨āĻŋāĻ°ā§āĻā§āĻˇāĻŖ āĻĄāĻžāĻāĻžāĻŦā§āĻ¸ā§āĻ° āĻĒāĻ°āĻŋāĻˇā§āĻŦāĻž āĻā§āĻŦāĻŋāĻ˛ā§ āĻāĻžāĻ°ā§āĻ¯āĻāĻ°ā§ āĻĒāĻ°āĻŋāĻāĻ˛ā§āĻĒāĻ¨āĻž āĻ¸āĻāĻ°āĻā§āĻˇāĻŖ āĻāĻ°ā§āĻ¨;
āĻĻā§āĻŦāĻŋāĻ¤ā§āĻ¯āĻŧāĻ¤: āĻā§āĻ¯ā§āĻ¯āĻŧāĻžāĻ°ā§ āĻāĻā§āĻ¸āĻŋāĻāĻŋāĻāĻļāĻ¨ āĻĒā§āĻ˛ā§āĻ¯āĻžāĻ¨ āĻĒāĻ°āĻŋāĻŦāĻ°ā§āĻ¤āĻŋāĻ¤ āĻšāĻ¯āĻŧā§āĻā§ āĻ¤āĻž āĻ
āĻŦāĻŋāĻ˛āĻŽā§āĻŦā§ āĻĻā§āĻāĻžāĻ° āĻāĻ¨ā§āĻ¯ āĻāĻā§ āĻ
āĻĒāĻ°ā§āĻ° āĻ¸āĻžāĻĨā§ āĻāĻā§āĻ¸āĻŋāĻāĻŋāĻāĻļāĻ¨ āĻĒā§āĻ˛ā§āĻ¯āĻžāĻ¨ āĻ¤ā§āĻ˛āĻ¨āĻž āĻāĻ°āĻ¤ā§ āĻ¸āĻā§āĻˇāĻŽ āĻšāĻāĻ¯āĻŧāĻžāĨ¤
āĻ¨āĻŋāĻ°ā§āĻĻāĻŋāĻˇā§āĻ āĻāĻā§āĻ¸āĻŋāĻāĻŋāĻāĻļāĻ¨ āĻĒā§āĻ¯āĻžāĻ°āĻžāĻŽāĻŋāĻāĻžāĻ° āĻ¸āĻš āĻāĻāĻāĻŋ āĻ
āĻ¨ā§āĻ°ā§āĻ§ āĻ°āĻ¯āĻŧā§āĻā§āĨ¤ EXPLAIN āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°ā§ āĻāĻ° āĻāĻžāĻ°ā§āĻ¯āĻāĻ°ā§ āĻĒāĻ°āĻŋāĻāĻ˛ā§āĻĒāĻ¨āĻž āĻĒā§āĻ°āĻžāĻĒā§āĻ¤ āĻāĻ°āĻž āĻāĻŦāĻ āĻ¸āĻāĻ°āĻā§āĻˇāĻŖ āĻāĻ°āĻž āĻāĻāĻāĻŋ āĻĒā§āĻ°āĻžāĻĨāĻŽāĻŋāĻ āĻāĻžāĻāĨ¤
āĻ¤āĻžāĻāĻžāĻĄāĻŧāĻž, EXPLAIN (COSTS FALSE) āĻ
āĻāĻŋāĻŦā§āĻ¯āĻā§āĻ¤āĻŋ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°ā§, āĻāĻĒāĻ¨āĻŋ āĻĒāĻ°āĻŋāĻāĻ˛ā§āĻĒāĻ¨āĻžāĻ° āĻāĻāĻāĻŋ āĻāĻā§āĻāĻžāĻ˛ āĻĒā§āĻ¤ā§ āĻĒāĻžāĻ°ā§āĻ¨, āĻ¯āĻž āĻĒāĻ°āĻŋāĻāĻ˛ā§āĻĒāĻ¨āĻžāĻ° āĻšā§āĻ¯āĻžāĻļ āĻŽāĻžāĻ¨ āĻĒā§āĻ¤ā§ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°āĻž āĻšāĻŦā§, āĻ¯āĻž āĻāĻžāĻ°ā§āĻ¯āĻāĻ°ā§ āĻĒāĻ°āĻŋāĻāĻ˛ā§āĻĒāĻ¨āĻžāĻ° āĻĒāĻ°āĻŋāĻŦāĻ°ā§āĻ¤āĻ¨ā§āĻ° āĻāĻ¤āĻŋāĻšāĻžāĻ¸ā§āĻ° āĻĒāĻ°āĻŦāĻ°ā§āĻ¤ā§ āĻŦāĻŋāĻļā§āĻ˛ā§āĻˇāĻŖā§ āĻ¸āĻšāĻžāĻ¯āĻŧāĻ¤āĻž āĻāĻ°āĻŦā§āĨ¤
āĻāĻā§āĻ¸āĻŋāĻāĻŋāĻāĻļāĻ¨ āĻĒā§āĻ˛ā§āĻ¯āĻžāĻ¨ āĻā§āĻŽāĻĒā§āĻ˛ā§āĻ āĻĒāĻžāĻ¨
--Explain execution plan--
EXECUTE 'SELECT dblink_connect(''LINK1'',''host='||database_rec.host||' dbname='||database_rec.name||' user=DATABASE password='||database_rec.owner_pwd||' '')';
log_explain_plan = ARRAY ( SELECT * FROM dblink('LINK1', 'EXPLAIN '||log_query ) AS t (plan text) );
log_plan_wo_costs = ARRAY ( SELECT * FROM dblink('LINK1', 'EXPLAIN ( COSTS FALSE ) '||log_query ) AS t (plan text) );
PERFORM dblink_disconnect('LINK1');
āĻ¸āĻŽā§āĻāĻžāĻŦāĻ¨āĻž #3 - āĻĒāĻ°ā§āĻ¯āĻŦā§āĻā§āĻˇāĻŖā§āĻ° āĻāĻ¨ā§āĻ¯ āĻā§āĻ¯ā§āĻ¯āĻŧāĻžāĻ°ā§ āĻ˛āĻ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°āĻž
āĻ¯ā§āĻšā§āĻ¤ā§ āĻĒāĻžāĻ°āĻĢāĻ°āĻŽā§āĻ¯āĻžāĻ¨ā§āĻ¸ āĻŽā§āĻā§āĻ°āĻŋāĻāĻā§āĻ˛āĻŋ āĻ
āĻ¨ā§āĻ°ā§āĻ§ā§āĻ° āĻĒāĻžāĻ ā§āĻ¯ā§ āĻ¨āĻ¯āĻŧ, āĻāĻŋāĻ¨ā§āĻ¤ā§ āĻāĻ° āĻāĻāĻĄāĻŋāĻ¤ā§ āĻāĻ¨āĻĢāĻŋāĻāĻžāĻ° āĻāĻ°āĻž āĻšāĻ¯āĻŧā§āĻā§, āĻ¤āĻžāĻ āĻāĻĒāĻ¨āĻžāĻā§ āĻ˛āĻ āĻĢāĻžāĻāĻ˛ āĻĨā§āĻā§ āĻ
āĻ¨ā§āĻ°ā§āĻ§āĻā§āĻ˛āĻŋāĻā§ āĻāĻŽāĻ¨ āĻ
āĻ¨ā§āĻ°ā§āĻ§āĻā§āĻ˛āĻŋāĻ° āĻ¸āĻžāĻĨā§ āĻ¸āĻāĻ¯ā§āĻā§āĻ¤ āĻāĻ°āĻ¤ā§ āĻšāĻŦā§ āĻ¯āĻžāĻ° āĻāĻ¨ā§āĻ¯ āĻĒāĻžāĻ°āĻĢāĻ°āĻŽā§āĻ¯āĻžāĻ¨ā§āĻ¸ āĻŽā§āĻā§āĻ°āĻŋāĻāĻā§āĻ˛āĻŋ āĻāĻ¨āĻĢāĻŋāĻāĻžāĻ° āĻāĻ°āĻž āĻšāĻ¯āĻŧā§āĻā§ā§ˇ
āĻāĻžāĻ˛, āĻ
āĻ¨ā§āĻ¤āĻ¤ āĻ¯āĻžāĻ¤ā§ āĻāĻāĻāĻŋ āĻāĻ°ā§āĻŽāĻā§āĻˇāĻŽāĻ¤āĻž āĻāĻāĻ¨āĻž āĻāĻāĻ¨āĻžāĻ° āĻ¸āĻ āĻŋāĻ āĻ¸āĻŽāĻ¯āĻŧ āĻāĻā§.
āĻāĻāĻāĻžāĻŦā§, āĻ¯āĻāĻ¨ āĻāĻāĻāĻŋ āĻ
āĻ¨ā§āĻ°ā§āĻ§ āĻāĻāĻĄāĻŋāĻ° āĻāĻ¨ā§āĻ¯ āĻāĻāĻāĻŋ āĻĒāĻžāĻ°āĻĢāĻ°āĻŽā§āĻ¯āĻžāĻ¨ā§āĻ¸ āĻāĻāĻ¨āĻž āĻāĻā§, āĻ¤āĻāĻ¨ āĻ¨āĻŋāĻ°ā§āĻĻāĻŋāĻˇā§āĻ āĻĒā§āĻ¯āĻžāĻ°āĻžāĻŽāĻŋāĻāĻžāĻ° āĻŽāĻžāĻ¨ āĻ¸āĻš āĻāĻāĻāĻŋ āĻ¨āĻŋāĻ°ā§āĻĻāĻŋāĻˇā§āĻ āĻ
āĻ¨ā§āĻ°ā§āĻ§ā§āĻ° āĻāĻāĻāĻŋ āĻ˛āĻŋāĻā§āĻ āĻāĻŦāĻ āĻ
āĻ¨ā§āĻ°ā§āĻ§āĻāĻŋāĻ° āĻ¸āĻ āĻŋāĻ āĻ¸āĻŽā§āĻĒāĻžāĻĻāĻ¨ā§āĻ° āĻ¸āĻŽāĻ¯āĻŧ āĻāĻŦāĻ āĻ¸āĻŽāĻ¯āĻŧāĻāĻžāĻ˛ āĻĨāĻžāĻāĻŦā§āĨ¤ āĻļā§āĻ§ā§āĻŽāĻžāĻ¤ā§āĻ° āĻāĻŋāĻ āĻŦā§āĻ¯āĻŦāĻšāĻžāĻ° āĻāĻ°ā§ āĻāĻ āĻ¤āĻĨā§āĻ¯ āĻĒāĻžāĻ¨ pg_stat_statements - āĻāĻāĻž āĻ¨āĻŋāĻˇāĻŋāĻĻā§āĻ§.
āĻ
āĻ¨ā§āĻ°ā§āĻ§ā§āĻ° queryid āĻā§āĻāĻā§āĻ¨ āĻāĻŦāĻ log_query āĻā§āĻŦāĻŋāĻ˛ā§ āĻāĻ¨ā§āĻā§āĻ°āĻŋ āĻāĻĒāĻĄā§āĻ āĻāĻ°ā§āĻ¨
SELECT *
INTO log_query_rec
FROM log_query
WHERE query_md5hash = log_md5hash AND timepoint = log_timepoint ;
log_query_rec.query=regexp_replace(log_query_rec.query,';+','','g');
FOR pg_stat_history_rec IN
SELECT
queryid ,
query
FROM
pg_stat_db_queries
WHERE
database_id = log_database_id AND
queryid is not null
LOOP
pg_stat_query = pg_stat_history_rec.query ;
pg_stat_query=regexp_replace(pg_stat_query,'n+',' ','g');
pg_stat_query=regexp_replace(pg_stat_query,'t+',' ','g');
pg_stat_query=regexp_replace(pg_stat_query,' +',' ','g');
pg_stat_query=regexp_replace(pg_stat_query,'$.','%','g');
log_query_text = trim(trailing ' ' from log_query_rec.query);
pg_stat_query_text = pg_stat_query;
--SELECT log_query_rec.query like pg_stat_query INTO found_flag ;
IF (log_query_text LIKE pg_stat_query_text) THEN
found_flag = TRUE ;
ELSE
found_flag = FALSE ;
END IF;
IF found_flag THEN
UPDATE log_query SET queryid = pg_stat_history_rec.queryid WHERE query_md5hash = log_md5hash AND timepoint = log_timepoint ;
activity_string = ' updated queryid = '||pg_stat_history_rec.queryid||
' for log_query with id = '||log_query_rec.id
;
RAISE NOTICE '%',activity_string;
EXIT ;
END IF ;
END LOOP ;
āĻāĻ¤ā§āĻ¤āĻ°āĻāĻžāĻˇ
āĻŦāĻ°ā§āĻŖāĻŋāĻ¤ āĻā§āĻļāĻ˛āĻāĻŋ āĻ
āĻŦāĻļā§āĻˇā§ āĻĒā§āĻ°āĻ¯āĻŧā§āĻ āĻĒāĻžāĻāĻ¯āĻŧāĻž āĻā§āĻā§
āĻ¯āĻĻāĻŋāĻ, āĻ āĻŦāĻļā§āĻ¯āĻ, āĻāĻŽāĻžāĻ° āĻŦā§āĻ¯āĻā§āĻ¤āĻŋāĻāĻ¤ āĻŽāĻ¤ā§, āĻĄāĻžāĻāĻ¨āĻ˛ā§āĻĄ āĻāĻ°āĻž āĻ āĻāĻļā§āĻ° āĻāĻāĻžāĻ° āĻ¨āĻŋāĻ°ā§āĻŦāĻžāĻāĻ¨ āĻāĻŦāĻ āĻĒāĻ°āĻŋāĻŦāĻ°ā§āĻ¤āĻ¨ āĻāĻ°āĻžāĻ° āĻāĻ¨ā§āĻ¯ āĻ ā§āĻ¯āĻžāĻ˛āĻāĻ°āĻŋāĻĻāĻŽā§ āĻāĻ°āĻ āĻāĻžāĻ āĻāĻ°āĻž āĻĒā§āĻ°āĻ¯āĻŧā§āĻāĻ¨āĨ¤ āĻ¸āĻžāĻ§āĻžāĻ°āĻŖ āĻā§āĻˇā§āĻ¤ā§āĻ°ā§ āĻāĻāĻ¨āĻ āĻ¸āĻŽāĻ¸ā§āĻ¯āĻžāĻ° āĻ¸āĻŽāĻžāĻ§āĻžāĻ¨ āĻšāĻ¯āĻŧāĻ¨āĻŋāĨ¤ āĻāĻāĻž āĻ¸āĻŽā§āĻāĻŦāĻ¤ āĻāĻāĻ°ā§āĻˇāĻŖā§āĻ¯āĻŧ āĻšāĻŦā§.
āĻāĻŋāĻ¨ā§āĻ¤ā§ āĻāĻāĻž āĻ¸āĻŽā§āĻĒā§āĻ°ā§āĻŖ āĻāĻŋāĻ¨ā§āĻ¨ āĻāĻ˛ā§āĻĒ...
āĻāĻ¤ā§āĻ¸: www.habr.com