1

I have 3 queries that are running very slow. All of the are doing the same in general : select from a view in oracle (viw oracle_fdw extension). All the views look like that : select /parallel (table_Name,4)/ column1,column2,replace(column 3,' ',null),...,replace(...),column8 from table_name.

*I have more than 40 columns for every table so I just mentioned the format of the query.

the selects i run in postgresql look like that: 

select * from oracle_table1_view;

  • table1 size is 10G with 100,000,000 records.

  • table2 size is 1.3G with 6,000,000 records.

  • table3 size is 8G with 75,000,000 records.

All this happens as part of a big function that gets data from the oracle db. Before Importing the data to the local tables in postgresql I delete the indexes and the constraints of the local tables and after the import of the data I create them.

Some info about my server :

  • I have 5 GB RAM on server and 4 are free.

  • I have 2 cpus

Some info about my Postgresql instance:

Currently I have only 1 db on the instance.

shared_buffers = 1000MB
effective_cache_size = 2GB
autovacuum = on
work_mem = 4MB

Moreover, I have a lot of selects * from foreign_Table. All of them taking some time but those 3 are taking too much. Please help improve performance of those 3 and if you can of all my selects.

JeyJ
  • 3,582
  • 4
  • 35
  • 83
  • Could you post an extract of your PostgreSQL server log file ? It might be usefull to see if you're using a lot of temporary files for example... – Arkhena Jul 27 '17 at 13:02
  • Will it be helpfull If I will put all the content of the log after I run the query ? If yes : 2017-07-27 16:05:13 IDT u postdbSTATEMENT: select * from foreign_table; I prefer not to upload the whole server log. Can you guide me to search for a specific info ? – JeyJ Jul 27 '17 at 13:07
  • I understand. First logging settings: I think you need to put checkpoint logging on, all temporary files (size min 0), lock_wait on. Then I'd like to now if you have errors or warnings in the log file when you run the query (such as checkpoints performing to frequently) and if (and then how many and which size) you created temporary files. – Arkhena Jul 27 '17 at 13:21
  • Temporary files | Size of temporary files -----------------+------------------------- 0 | 0 0 | 0 0 | 0 99 | 51190833152 17 | 3451628 – JeyJ Jul 27 '17 at 13:23
  • 2017-07-27 16:31:12 IDT u LOG: parameter "log_connections" changed to "on" 2017-07-27 16:31:12 IDT u LOG: parameter "log_lock_waits" changed to "on" 2017-07-27 16:31:12 IDT u LOG: parameter "log_statement" changed to "all" 2017-07-27 16:31:12 IDT u LOG: parameter "log_temp_files" changed to "0" 2017-07-27 16:31:14 IDT u [unknown]LOG: connection received: host=[local] 2017-07-27 16:32:01 IDT u postdbLOG: statement: select * from foreign_table; – JeyJ Jul 27 '17 at 13:33
  • You haven't mentioned how long these take, nor whether you're issuing multiple queries at the same time, nor how long the queries that run faster take or how much data is involved. – David Aldridge Jul 27 '17 at 13:37
  • I specified the size of the tables - I select * from them. I`m running the queries one after another which means when one finishes I run the second. – JeyJ Jul 27 '17 at 13:42

1 Answers1

2

Do the queries run fast when you execute them with sqlplus?

If not, you have to solve the problem on the Orace side.

To see the Oracle execution plan used by oracle_fdw, run

EXPLAIN (VERBOSE) SELECT * FROM oracle_table1_view;

Check if that matches the plan when you run from sqlplus. If not, try to spot the difference and figure out why.

If the plan looks the same, but the execution time is different, it could be that you select some LOB columns. Row prefetching does not work if such columns are involved, so there will be one round trip from PostgreSQL to Oracle for each selected row, which can make things really slow.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263
  • The queries take time in the oracle side also but they arent killed. When I run those selects in psql they end and I see Killed the prompt. – JeyJ Jul 30 '17 at 08:08
  • What is "Killed the prompt"? Can you explain in more detail? Who kills what? – Laurenz Albe Jul 30 '17 at 20:10
  • server:[~] : psql -d mydb -U user psql (9.5.7) Type "help" for help. mydb=> select * from remote_Table; Killed – JeyJ Jul 31 '17 at 14:26
  • I checked the server log and I see : 2017-07-31 17:19:41 IDT u mydbLOG: statement: select * from remote_Table; 2017-07-31 17:25:42 IDT u mydbLOG: could not send data to client: Broken pipe 2017-07-31 17:25:42 IDT u mydbSTATEMENT: select * from remote_table; 2017-07-31 17:25:42 IDT u mydbFATAL: connection to client lost – JeyJ Jul 31 '17 at 14:33
  • It seems that when I select large tables in oracle My session get killed. – JeyJ Jul 31 '17 at 14:34
  • 1
    I bet that is the OOM killer on Linux that kills `psql` because your system is running out of memory. Check the kernel log to confirm. The problem is `psql`, which stores the entire query result in memory and gags. Try `\set FETCH_COUNT 1000` or similar in `psql`. – Laurenz Albe Jul 31 '17 at 14:47
  • I checked in /var/log/messages and i saw that the kernel killed it because i was runnig out of memory as you mentioned. What does this command do ? – JeyJ Jul 31 '17 at 14:49
  • By default, `psql` caches the whole result set. With `FETCH_COUNT` set, it fetches the rows in batches. You might also want to disable the pager for a large result set. See the [manual page for `psql`](https://www.postgresql.org/docs/current/static/app-psql.html) for all that. – Laurenz Albe Aug 01 '17 at 06:30
  • Laurenz, regarding the performance issue - I`m dropping the indexes and the constraints for those tables and after that i import the data. When the import finishes I create the constraints and the indexes again. I should run analyze on them after I create the index and the constraint or before ? and should it be vacuum analyze or just leave it as analyze ? – JeyJ Aug 01 '17 at 06:33
  • 1
    This is drifting off... It looks like you want to create local tables by selecting from the foreign tables. It does not matter if you run `ANALYZE` before or after you create indexes and constraints, unless there is an index based on an expression rather than a column. The only difference between `ANALYZE` and `VACUUM (ANALYZE)` for a newly created table is that the latter will set hint bits for all table rows and thus might speed up the first `SELECT`. – Laurenz Albe Aug 01 '17 at 06:55