1

I'm running postgresql server on Windows Server 2008, which has 6GB of ram. My dataset is something near 1,5GB big.

I have problems, when I run specific SQLs in table, which has 51mio of records. The fields which I use in SQLs are indexed (alarm, id_elektrarna, id_razsmernik). Below is the ANALYZE SQL, which causes problems. Do you have any suggestions what to do?

UPDATE:

When I run my query, I see how memory of the system where database is running is increasing, while some scripts that are using database just "pause" working. When I stop SQL from running, everything works again.

There are additional data: #work_mem = 1MB (commented), shared_buffers = 32MB,

index definition, table definition, SQL that is the issue: http://pastebin.com/4iMtpD4r

The database server is using the RAM, not the client application. This is the SQL with EXPLAIN (ANALYZE,BUFFERS) that executes in less than 1sec and do not cause problems. Same SQL, different UNIT in database (different ID) takes more than 15-30minutes: http://explain.depesz.com/s/OdqU.

  • 1
    Please provide the table and index definitions and the output of EXPLAIN (ANALYZE, BUFFERS). Also, the settings shared_buffers and work_mem can be of interest, probably a few others, too. – dezso Mar 4 '14 at 12:02
  • Also show the SQL statement that's the issue, and describe what client you are using when you run the SQL. Is it the database server using the RAM, or the client application? – Craig Ringer Mar 4 '14 at 23:33
  • That 15-30 minutes is under 1 second, as seen from the plan you linked. Returning 20 rows, so it cannot be that the client munches on your data so long. So it looks like some important piece of information is missing here... – dezso Mar 5 '14 at 13:35
  • Yes. That condition alarm!=0 is true only rarely. With specific "id_elektrarna and id_razsmernik" I can find these parameter to be true in very nearly past (as in example provided). But on many cases, to get the condition alarm!=0, SQL must search at 51mio-record table for more than 1 year back. Such cases are causing problems. I can additionaly provide SQL analyze for statement, which takes more than half of hour to execute, but I tried to avoid it, because database uses all available RAM space in such cases. – nejcb1 Mar 5 '14 at 14:31
  • +1 for explain.depesz.com. I did not know such a site existed. – Alexandros Mar 6 '14 at 4:22
0

The main problem is with the shared_buffers = 32MB parameter. It is 2014 not 2000. You cannot expect any kind of decent performance on a 6Gb machine, if you do not increase that to at least 512Mb. I believe that you are also using PostgreSQL 64bit (if not make the change now). Also, you say that your dataset = 1.5 GB. Are you sure? 50 million records fit on 1.5GB on disk? Have you run SELECT pg_database_size('myDbName'); Does it still show 1.5GB?

I also see you do not have an index at status_razsmernika field and you do a join on that, which leads to a sequential scan. You also have (razsmernik.status_razsmernika!=6) and (razsmernik.status_razsmernika !=0). So, you really need to add a b-tree index to status_razsmernika.

Also you are using the "updated" field for order in descending value. If this is always the case, specify the order in the related index as well. http://www.postgresql.org/docs/9.3/static/sql-createindex.html

  • Yes, you are right. 32MB is default value, when you install database, so it remain unchanged until now. I will increase it to 512Mb, as you suggested. Yes, my PostgreSQL is 64bit. You are also right with the size of the database. When I export my database for backup, its about 1,5GB. But running pg_database_size SQL returns 11183874872, which means 10.4GB. Much more, than I said. So you think increasing shared_buffer will solve my problem? Thanks for help. – nejcb1 Mar 6 '14 at 7:16
  • DB back-ups are usually compressed, so they cannot be used to estimate the actual DB size. – Alexandros Mar 6 '14 at 8:52
  • thank you very much for your input. I will add b-tree index, and other things as you suggested. – nejcb1 Mar 6 '14 at 15:36
  • I would, but I do not have enough reputitation, as I just joined. – nejcb1 Mar 7 '14 at 14:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.