|
|
|
|
|
we found that when massive insert operations situation met, the problem reproduce instantly. |
|
|
after reviewing code of our test program , we found the problem is that:we should not create too many stmt objects, the usage of stmt is not correct. bad case: for i := 0; i < testCount; i++ { ... stmt, err := db.Prepare(sqlStatement) if err != nil { fmt.Println("prepare sql failed, err->", err, "sql->", sqlStatement) continue } res, err := stmt.Exec() if err != nil { fmt.Println("insert into test failed, err->", err, "res->", res, "sql->", sqlStatement) continue } } good case: sqlStatement := fmt.Sprintf(`INSERT INTO test( name, id, age) VALUES($1,$2,$3)`) stmt, err := db.Prepare(sqlStatement) if err != nil { fmt.Println("prepare sql failed, err->", err, "sql->", sqlStatement) continue } for i := 0; i < testCount; i++ { res, err := stmt.Exec(param1, param2, param3) if err != nil { fmt.Println("insert into test failed, err->", err, "res->", res, "sql->", sqlStatement) continue } } after we revise our code, we found that the cpu load of pgpool is ok. BUT, the problem is still exist, which is that when we create many stmt objects, pgpool child process consume high cpu resource.By the way, when we directly connect postgresql server, even we create many stmt objects, the cpu load is not high. we also using flamegrapgh tools to inspect what happend. pgpool_cpu_high.svg (165,026 bytes) pgpool_cpu_not_high.svg (123,693 bytes) |
|
|
I will confirm that this issue is from Pgpool-II or application? |
|
|
If the application never removes prepared statements in a session, it's not surprising that Pgpool-II consumes much memory because it cannot free memory allocated by the prepared statements until they are removed. Also if there is tons of data to keep track each prepared statement in Pgpool-II, it's not also surprising that Pgpool-II consumes cpu because the search space for prepared statements is huge. In summary I think you need to fix your application program so that it removes prepared statements that are no longer necessary. |
- Anonymous
