lifubang
commented
8 years ago 
Closed lifubang closed 2 years ago
lifubang
commented
8 years ago
lifubang
commented
8 years ago I think it the reason of MongoAcquireSampleRows function, after I changed this function like this, the network output bytes changed: `static int MongoAcquireSampleRows(Relation relation, int errorLevel, HeapTuple _sampleRows, int targetRowCount, double totalRowCount, double totalDeadRowCount) { (_totalRowCount) = 100000000;//rowCount; (*totalDeadRowCount) = 0;
return 0;}
//the old MongoAcquireSampleRows function static int MongoAcquireSampleRows1(Relation relation, int errorLevel, HeapTuple sampleRows, int targetRowCount, double totalRowCount, double *totalDeadRowCount) { int sampleRowCount = 0; double rowCount = 0; ....
foreignTableId = RelationGetRelid(relation);
queryDocument = QueryDocument(foreignTableId, NIL); **// no filter**
....
**//get more for the whole table**
for (;;)
{
/* check for user-requested abort or sleep */
vacuum_delay_point();
/* initialize all values for this row to null */
memset(columnValues, 0, columnCount * sizeof(Datum));
memset(columnNulls, true, columnCount * sizeof(bool));
if(MongoCursorNext(mongoCursor, NULL))
{
....}`
lifubang
commented
8 years ago Ah, No, it's not true. the same thing occurs again:
netout 206M now.
how to disable documents full scan?
ahsanhadi
commented
8 years ago You are saying that the mongodb_fdw is scanning the full document even when you use a where clause in your query? Can you share a small example to demonstrate the issue?
lifubang
commented
8 years ago Here is the small example. You need to reference jars as follows: ./lib/commons-dbcp-1.2.1.jar ./lib/commons-pool-1.2.jar ./lib/commons-collections-3.2.jar ./lib/postgresql-9.4.1208.jre7.jar
import org.apache.commons.dbcp.BasicDataSource;
import java.lang.Integer;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;
import java.lang.Thread;
public class dbcp {
public static BasicDataSource dataSource = new BasicDataSource();
public static void main(String args[])
{
dataSource.setPoolPreparedStatements(false);
dataSource.setUrl("jdbc:postgresql://*************/acmcoder");
dataSource.setUsername("postgres");
dataSource.setPassword("***************");
int testcases = 100;
if (args.length > 0) {
testcases = Integer.parseInt(args[0]);
}
System.out.println("testcases: " + testcases);
for (int i=0; i<testcases; i++) {
Thread thread = new TestThread();
thread.start();
}
}
public static boolean getKandsMailState(Long id, String prjId) throws Exception {
Connection conn = null;
PreparedStatement psmt = null;
ResultSet res = null;
boolean flag = false;
try {
String sql = " select \"confirmState\" from \"wb_Cands_Writeable\" where id=? ";
dataSource.setPoolPreparedStatements(false);
conn = dataSource.getConnection();
psmt = conn.prepareStatement(sql);
psmt.setLong(1, id);
res = psmt.executeQuery();
while (res.next()) {
String confirmState = res.getString("confirmState");
System.out.println(confirmState);
if (confirmState != null) {
if (confirmState.equals("40") || confirmState.equals("50") || confirmState.equals("60")) {
flag = true;
}
}
}
} catch (Exception e) {
throw e;
} finally {
if (res != null)
res.close();
if (psmt != null)
psmt.close();
if (conn != null)
conn.close();
}
return flag;
}
}
class TestThread extends Thread {
public void run() {
try {
System.out.println(Thread.currentThread().getName() + " is running.");
System.out.println(Thread.currentThread().getName() + ": " + dbcp.getKandsMailState(4392325L, ""));
} catch (Exception exp) {
System.out.println(exp.getMessage());
exp.printStackTrace();
}
}
}There were about 10 java, mongo and postgresql engineers to examine the problem. We found that: The first 6 or 7 thread are correct, after that, it start to scan the full table. If there are no conn.prepareStatement(sql), and use "select confirmState from wb_Cands_Writeable where id=4392325L", it works normally.
Alvin-Fu
commented
5 years ago 你好，getmore的日志是怎么打印的？
lifubang
commented
5 years ago @AlvinLiFu You need to db.setProfilingLevel(2) first. and then use db.system.profile.find({ns:'****', "op" : "getmore"})
andreasscherbaum
commented
5 years ago Given that the original report was on a no longer supported PostgreSQL version: is this issue still open?
vaibhavdalvi93
commented
2 years ago Hi @lifubang , Are you still facing this issue? If not, can you please close this ticket?
lifubang
commented
2 years ago Given that the original report was on a no longer supported PostgreSQL version
The maintainer said this maybe the reason, but I have no time to upgrade it, so close this, thanks.
When I use mongo_fdw, I find that the mongo network output is too big, the top of it can reach 300M per seconds. I analyses the mongo log, and find that there are so many getmore operations. The log contents is:
mongo日志 query
mongo日志 getmore
I don't use no filter query in pg, but the getmore operation has no filter. by the way, my query in pg have indexes in mongo. how to deal with it?
I guess if you exec a sql in pg, the mongo_fdw will scan the whole foreign table in mongo.