Postgres up CPU to 340%


#1

I just setup Skygear server with docker-compose and Postgres.
when I use chat (send a message or typing ) Postgres is using up to 340% CPU.

CONTAINER ID        NAME                   CPU %               MEM USAGE / LIMIT     MEM %               NET I/O             BLOCK I/O           PIDS
be8aff734e96        chatfinal_plugin_1     3.69%               36.38MiB / 6.643GiB   0.53%               6.46MB / 5.52MB     0B / 0B             14
ac31de845c9a        chatfinal_app_1        7.73%               28.73MiB / 6.643GiB   0.42%               21MB / 20.1MB       2.27MB / 0B         12
8855b0f55e1e        chatfinal_jsplugin_1   0.00%               22.02MiB / 6.643GiB   0.32%               126kB / 43.6kB      451kB / 0B          11
08f4417fd8ca        chatfinal_db_1         280.15%             29.4MiB / 6.643GiB    0.43%               14.3MB / 16.6MB     131kB / 12.7MB      15

Below is my compose file :

version: '2'

services:
db:
image: mdillon/postgis:9.5
ports:
- "5432:5432"
volumes:
- db_data:/var/lib/postgresql/data

app:
image: quay.io/skygeario/skygear-server:latest
ports:
- "3001:3000"
volumes:
- app_data:/go/src/app/data
links:
- db
command: skygear-server
environment:
DATABASE_URL: postgresql://postgres:@db/postgres?sslmode=disable
APP_NAME: giftedChat
API_KEY: larsa
MASTER_KEY: larsa_comp
TOKEN_STORE: jwt
TOKEN_STORE_SECRET: jwt_secret
PLUGINS: JS,CHAT
JS_TRANSPORT: http
JS_PATH: http://jsplugin:9000
CHAT_TRANSPORT: zmq
CHAT_PATH: chat
CHAT_ARGS: tcp://0.0.0.0:5555
ASSET_STORE: fs
ASSET_STORE_URL_PREFIX: http://localhost:3000/files
ASSET_STORE_SECRET: khaled_ald_gifted
APNS_ENABLE: "YES"
APNS_ENV: sandbox
APNS_TYPE: token
APNS_KEY_ID: xxxxxxxxxxxx
APNS_TEAM_ID: xxxxxxxxxxxxx
APNS_TOKEN_KEY_PATH: /app/data/cert/khaled.p8
CUSTOM_TOKEN_SECRET: xxxxxxxxxxxxx

plugin:
build:
context: .
links:
- app
- db
volumes:
- ./:/usr/src/app
command: py-skygear chat
environment:
DATABASE_URL: postgresql://postgres:@db/postgres?sslmode=disable
PUBSUB_URL: ws://app:3000/pubsub
SKYGEAR_ADDRESS: tcp://app:5555
SKYGEAR_ENDPOINT: http://app:3000
APP_NAME: giftedChat
API_KEY: larsa
MASTER_KEY: larsa_comp
TOKEN_STORE_SECRET: jwt_secret
ASSET_STORE_URL_PREFIX: http://localhost:3000/
ASSET_STORE: fs
ASSET_STORE_SECRET: khaled_ald_gifted
ZMQ_MAX_BOUNCE: 1

jsplugin:
image: quay.io/skygeario/skygear-node:canary
volumes:
- .:/usr/src/app
environment:
DATABASE_URL: postgres://postgres@db/postgres?sslmode=disable
PUBSUB_URL: PUBSUB_URL=ws://app:3000/pubsub
SKYGEAR_ENDPOINT: http://app:3000
API_KEY: larsa
MASTER_KEY: larsa_comp
APP_NAME: giftedChat
ASSET_STORE: fs
ASSET_STORE_SECRET: khaled_ald_gifted
ASSET_STORE_URL_PREFIX: http://localhost:3000/

volumes:
db_data:
driver: local
app_data:
driver: local

Any Suggestion ???


#2

Hi @KhaledAld

Did you try it on a clean Skygear installation? (E.g. if you remove the JS plugin does it still happens)?

You may want to look at the postgresql and see which query is using the high CPU. You may want to reference from here: https://dba.stackexchange.com/questions/44084/troubleshooting-high-cpu-usage-from-postgres-and-postmaster-services

If you find the problematic query that is using high CPU, you may post it there so we can look into it too.


#3

Hi @bencheng
I try it with out JSPlugins and it’s still happen.
this problem is appear when using chat (send message , typing).


#4

Ic. Would be great if you can follow the troubleshoot above to share the result of which query is causing the high cpu usage, with the OS and config of postgresql, so that we can look into it.

Alternatively you might want to use our hosted services at skygear.io if you have problems setting up the opensource version.


#5

@bencheng
if I want to use skygear.io, What is the difference between opensource version and your hosted services ???
and it provides end to end encryption ??