_sqlserv2 process somehow fills up disk space?

[Ryszard Musielak]

Hello,

I would like have some advice, maybe others have come across this problem. We have a Red Hat Linux server with a Progress database. I have a user running a report using SQL that links data from the Progress DB and and external Oracle DB. When this runs there is a process that creates an "unlinked" file, this can grow exponentially and can be seen using the lsof command:
# lsof | grep deleted | grep "_sqlsrv2" | nl
<file size>
1 _sqlsrv2 12661 root 27u REG 8,1 5371854848 297918 /usr/wrk/DBI1082624320rSmtLE (deleted)
1 _sqlsrv2 12661 root 27u REG 8,1 5407113216 297918 /usr/wrk/DBI1082624320rSmtLE (deleted)
1 _sqlsrv2 12661 root 27u REG 8,1 5425004544 297918 /usr/wrk/DBI1082624320rSmtLE (deleted)
1 _sqlsrv2 12661 root 27u REG 8,1 5439160320 297918 /usr/wrk/DBI1082624320rSmtLE (deleted)
1 _sqlsrv2 12661 root 27u REG 8,1 5473959936 297918 /usr/wrk/DBI1082624320rSmtLE (deleted)
1 _sqlsrv2 12661 root 27u REG 8,1 5481365504 297918 /usr/wrk/DBI1082624320rSmtLE (deleted)
1 _sqlsrv2 12661 root 27u REG 8,1 5490343936 297918 /usr/wrk/DBI1082624320rSmtLE (deleted)
1 _sqlsrv2 12661 root 27u REG 8,1 5538775040 297918 /usr/wrk/DBI1082624320rSmtLE (deleted)
1 _sqlsrv2 12661 root 27u REG 8,1 5597954048 297918 /usr/wrk/DBI1082624320rSmtLE (deleted)
1 _sqlsrv2 12661 root 27u REG 8,1 5657001984 297918 /usr/wrk/DBI1082624320rSmtLE (deleted)


You can see it's using over 5GB of space. The problem I've got is that it uses the /usr/wrk folder which is on the Root file system that is filling up and the space sometimes does not get released. After 3 months space usage has gone from 25% to now 67%. We had to reboot the server 3 months ago as we finally reached a point when / was filled up. That that cleared the space.

When I kill the process space goes back from 90% to 67%, so it doesn't seem like a problem, but we are sure this is the root cause of our problems. I suspect the process may sometimes crash and leave a zombie file in the system that is held up by Progress. Maybe there is a way to identify such a process?
The "top" command shows "0 zombie". The "promon" program doesn't offer any help.

Can anyone offer any advice or confirm that the _sqlsrv2 process is causing this issue? We will see if there is another way of running those reports to eliminate this problem.

This doesn't look like an OS problem, so Red Hat won't help.

Thanks,
Richard

 

[TomBascom]

What version of Progress?

Those are temp table overflow files. You can use -T to point them at a different file system.

Have you ever updated statistics?

 

[Ryszard Musielak]

Progress 11.7

I know I can point to another file system, but that doesn't solve the problem, it just moves it to a different place.

I have never ran statistics in Progress, is this for running SQL, or the whole database?

Sorry, still learning .

Thanks

 

[Ryszard Musielak]

You mean something like this?

Progress Customer Community

knowledgebase.progress.com

When I look at the code:
OUTPUT TO "UpdateStatistics.sql".
FOR EACH _File NO-LOCK WHERE _Tbl-Type = "T":
PUT UNFORMATTED "UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS FOR PUB." _File-Name ";" SKIP.
END.
PUT UNFORMATTED "COMMIT WORK;".
OUTPUT CLOSE.


I would like to ask, how do I run it in a UNIX session? We use "sqlexp", but this looks like 4GL to me, so I am not familiar with it very much. Sorry, our third party maintains our application.

 

[TomBascom]

Yes, that is 4gl code that will create a SQL script that you can then run with sqlexp.

Put the 4gl code in file called "updsql.p" and run it from UNIX like so:

mpro dbname -p updsql.p

That will create "UpdateStatistics.sql".

You then run the result with sqlexp.

 

[TomBascom]

If you have tables that have "-" in the name the kbase code doesn't quote them. This is a better way to output the core SQL command:

PUT UNFORMATTED 'UPDATE TABLE STATISTICS AND INDEX STATISTICS AND ALL COLUMN STATISTICS FOR PUB."' _File-Name '";' SKIP.

(I replaced the original fat quotes with skinny quotes and added fat quotes around the table name.)

 

[TomBascom]

You did say: "The problem I've got is that it uses the /usr/wrk folder ..." so it seemed reasonable to think that you might want to know that you can move the destination

 

[Ryszard Musielak]

You did say: "The problem I've got is that it uses the /usr/wrk folder ..." so it seemed reasonable to think that you might want to know that you can move the destination

Of course... no problem...

 

[TomBascom]

If it turns out that you cannot run that mpro command due to lack of a compiler license you could always download ProTop 11.7 r-code and use the ^u command to generate the script.

 

[Ryszard Musielak]

Yes, that is 4gl code that will create a SQL script that you can then run with sqlexp.

Put the 4gl code in file called "updsql.p" and run it from UNIX like so:

mpro dbname -p updsql.p

That will create "UpdateStatistics.sql".

You then run the result with sqlexp.

Ah, excellent, just what I needed.

I found this blog, albeit a nice one, but absolutely doesn't tell how to run a program.p.

progress-4gl Tutorial => Getting started with progress-4gl

Learn progress-4gl - ABL (Advanced Business Language). Earlier known as Progress 4GL.Progress ABL is a programming language tied to the Progress OpenEdge...

riptutorial.com

 

[Ryszard Musielak]

Oooops....

This version of PROGRESS does not allow compiles. (471)
R-code file not located for "UpdateStatistics.p". (473)
Press space bar to continue.

Is there an option I need to install?

 

[TomBascom]

If there are development licenses somewhere else in your company you could connect with one of those.

Or you could download and install ProTop. Progress OpenEdge Monitoring with ProTop - White Star Software

 

[Ryszard Musielak]

If there are development licenses somewhere else in your company you could connect with one of those.

Or you could download and install ProTop. Progress OpenEdge Monitoring with ProTop - White Star Software

Thank you again. I'll check the Live sever, I suspect this was not meant to be available in our licence.

 

[Ryszard Musielak]

Ok, I may find another way to generate the SQL script. However, is it safe to run it against the whole database which may have system data/tables?
Also, how would this help in resolving my space usage problem? If we make the queries more efficient, less TEMP space would be used?
(sorry for the simplistic jargon )

 

[TomBascom]

Yes, the idea is that more efficient queries should mean less temp space being used. Of course I don't know for sure what your queries look like so I am kind of guessing. But it seems like a pretty reasonable guess.

Regarding safety -- yes, it is safe. Having said that - someone is bound to mention that in certain very old releases there were bugs related to updating statistics. You are on 11.7 and it has been a long time since those bugs were relevant. None the less, in the extremely unlikely event that a problem arises you can just delete the statistics. (There is a kbase for that too.)

 

[TomBascom]

The 4gl script only updates statistics for application tables (that's what the "T" does in the WHERE clause). It does not update statistics for any system tables.

 

[Rob Fitzpatrick]

I have never ran statistics in Progress, is this for running SQL, or the whole database?

This is for OpenEdge SQL only.

Those are temp table overflow files. You can use -T to point them at a different file system.

The docs aren't clear on this point, as they say that -T is a client startup parameter. It is, but it can also be specified with a SQL broker. So if you wanted to change where your SQL servers' temp files go, you would add -T /some/dir to your SQL broker startup parameter list.

Also, how would this help in resolving my space usage problem? If we make the queries more efficient, less TEMP space would be used?

As you know, the OpenEdge RDBMS has two query engines, 4GL and SQL, and they operate differently. In a given OpenEdge release, a given 4GL query run against a given schema will always construct the same query plan. It uses a rules-based approach to determine index selection and bracketing; the data in the tables is not considered at all.

By constrast, OpenEdge SQL uses a cost-based query optimizer. The OpenEdge database contains a set of system tables that can contain meta-data that describe the data in your application tables. You populate these meta-data tables by running UPDATE STATISTICS SQL commands against your tables, indexes, and columns. (There is now the option in 12.x of having the database do this for you.)

A "cost-based optimizer" means that when the SQL query engine is evaluating a query, it attempts to take a data-driven approach to make informed decisions about how best to construct its query plan for the query. However if the statistics tables are empty, it must construct a naive query plan that does not consider the amount or distribution of data in the tables. If the statistics are stale, i.e. they contain very old meta-data that no longer accurately describes your data, again the query plan could be non-optimal and the end result could be an unoptimized query. In other words: in a given OpenEdge release, a given SQL query run against a given schema won't always construct the same query plan.

One side-effect of poorly-optimized queries could be that they use the wrong index(es). As you may have seen in 4GL code, using the wrong index can cause excess reads, and in general a poorly-constructed query can cause excess work like client-side sorting of a result list. Consider the following examples from the sports database:

// query 1
for each customer where cust-num > 80
  use-index cust-num
  by cust-num:
    display cust-num name.
end.

// query 2
for each customer where cust-num > 80
  use-index name                       // note the difference
  by cust-num:
    display cust-num name.
end.

The customer table contains 83 records and four of them satisfy the WHERE clause. Both queries return the same data; four records. Query 1 reads four records and does no client-side sorting. Query 2 reads 87 records and then has to do client-side sorting to order the data, since the desired order (by cust-num) doesn't match the index order (by name). To do this it must construct a local result list of sort data and associated rowids, sort the list, and then re-fetch the ordered data.

This is a trivial example, a simple query from one tiny table. Imagine a complicated multi-table join on tables with millions of records each. If the wrong indexes were selected or if joins were done in the wrong way, the numbers could really add up quickly. I'm not shocked that you see a multi-gigabyte DBI file. A well-tuned query might use considerably less space for result lists and as an added bonus might perform better and might even help other clients perform better (due to lower resource utilization, less "pollution" of the database buffer pool, etc.). As Tom says, we have no way of predicting without knowing anything about your queries. But this is the theory behind how things could happen. (It is also a little lesson on the dangers of using USE-INDEX in 4GL code but that wasn't the point I was trying to make. ).

I suspect the process may sometimes crash and leave a zombie file in the system that is held up by Progress.

Can anyone offer any advice or confirm that the _sqlsrv2 process is causing this issue?

This is why Progress creates temp files (including client temp files) as unlinked files in Unix. You can see that they are unlinked as lsof reports them as "(deleted)". If the process crashes, there is no file left the in the file system that you need to clean up.

Without knowing what else uses that file system, I can't say definitively that _sqlsrv2 processes are to blame for utilization. But they certainly could be. It is also certain that you shouldn't have application files written to the root file system if you can avoid it, especially when they have the potential to grow very large very quickly. And you can avoid it.

Sorry, our third party maintains our application.

This doesn't look like an OS problem, so Red Hat won't help.

I understand Red Hat not helping. But IMHO the third party definitely should.

However, is it safe to run it against the whole database which may have system data/tables?

Safe? Yes (but see below). Free? No. Updating statistics does have to read all of your data to construct the meta-data, so it will add load to your system (and likely clear out the contents of the buffer pool as well). I suggest running this at an off-peak time.

I also suggest testing it first in a non-production environment with production-like schema and data and OpenEdge release. This will help you to understand how long it will take, that your scripting is correct, and that you aren't going to hit any unexpected OpenEdge bugs. Many of the bugs of the past have been fixed, but they illustrate that bugs are possible.

I ran into an issue at a client site not too long ago (11.6.0 on AIX) where running UPDATE STATISTICS on a copy of prod caused a lock table overflow. My testing was done with production-like data and schema but on a different platform, so I learned that lesson the hard way. More details on that issue:

Lock table overflow during Update Statistics
Progress KB - Running UPDATE STATISTICS on all tables columns indexes overflows lock table