Something wrong with new RequestDB?

[KrzysztofCiba]

Hi,

One more issue on integration machine. I have put ther ~2000 test 'transfer' requests, most of them are in 'Waiting' state:

mysql> select count(*) from SubRequests where Status='Waiting';
+----------+
| count(*) |
+----------+
|     1949 |
+----------+
1 row in set (0.00 sec)

but RequestClient is unable to serve them to TransferAgent:

2012-04-24 08:37:18 UTC DataManagement/TransferAgent/RequestManagement/RequestClient  INFO: RequestDBClient.getRequest: Attempting to get request. dips://localhost:9143/RequestManagement/RequestManager transfer
2012-04-24 08:37:19 UTC DataManagement/TransferAgent/RequestManagement/RequestClient  INFO: Found no 'transfer' requests on RequestDB (dips://localhost:9143/RequestManagement/RequestManager) 
2012-04-24 08:37:19 UTC DataManagement/TransferAgent  INFO: execute: no more 'Waiting' requests found in RequestDB 

??? How's that possible?

[atsareg]

I still think that the solution that we have is not optimal. We choose the oldest Requests having either Waiting or Assigned subrequests of requested type with eligible execution order and then limit the list to 10. If we have some subrequests stuck in Assigned for some transient problem and there are new requests coming in, the system will be blocked until the Assigned requests are cleared up just becaused the Assigned subrequests are the oldest ones. So, we ore obliged to wait until cleanup which is far not immediate. The solution should be to choose Requests with the oldest Waiting subrequests. I insist that getting requests is not the way to cope with "blocked in Assigned" subrequests as Ricardo maintains. This is dealt with by the cleaning agent. We should not mix it together.

[graciani]

I have set the limit to 100.

Some corrections:
We chose the Requests that have the oldest "SubRequests" in Waiting or Assigned. For that we only considered a maximum number of SubRequests. I wonder should the order be rather "LastUpdate, ExecutionOrder" instead of "ExecutionOrder,LastUpdate", if everything is done as it should, for a given request there can not be any SubRequest with higher execution order than any Assigned or Waiting SubRequests of lower execution in the same request.

We need to consider Assigned SubRequests, in any case (even if we decide to remove the limit) because we need to know if there are Assigned Requests with a smaller ExecutionOrder. The only question here is not ( "Assigned", "Waiting" ) vs ("Waiting"), but rather LIMIT X vs no LIMIT.

For reference, we have running with:

    req = "SELECT RequestID,SubRequestID FROM SubRequests WHERE Status = 'Waiting' AND RequestType = '%s' ORDER BY LastUpdate ASC LIMIT 50;" % requestType

for ages. It was returning "SubRequest" with the wrong execution order (so even more chances to get stuck) and we never saw any problem.

The main difference between running with a limit or not running with a limit is that in the first case, if N (where N is the limit we set) "older" SubRequests get stuck in "Assigned" and new SubRequest arrive, they will not be considered until the Assigned ones are set back to Waiting by the Cleaner. And of course, that if we run without limit, the query might become huge. This is always a bad idea.

Before any further discussion, we should take something into account:

0.- It will be highly desirable that as soon as the Executors are tested, the whole Request mechanism is rewritten in the same spirit.

So,
1.- the current code is more efficient and well behaved than the old one.
2.- From my point of view it is safer to run with a limit in an otherwise potentially huge nested query.
3.- We can discussed if the limit is 10, or 100, or 1000.
4.- And we need to understand if the order is "LastUpdate, ExecutionOrder" instead of "ExecutionOrder,LastUpdate". I think that the first option is the right one.

In any case given 1.-, and 0.- I think we have already spent too much time in this discussion.

[graciani]

We can reopen at a later stage if we see any problem.

[fstagni]

Hi, I asked to re-open this issue because, even if the issue I am reporting is a bit different from the original one, after putting in production v6r4p12 version of DIRAC, we have seen that the new code is less efficient with respect to the old one. This forced us, after many hours of investigation, to hotfix by putting in the production machine the old version of RequestDBMySQL.py, the one found in DIRAC v6r3p18. Without that, the RequestDB was totally unusable: response times were going up to 600 seconds for single query. With the old code in, the response time is instead reasonable.

The reason being that the v6r4 version produces query (in getRequest()) like the following:

SELECT A.RequestID, A.SubRequestID, A.Operation, A.Arguments, A.ExecutionOrder, A.SourceSE, A.TargetSE, A.Catalogue, A.CreationTime, A.SubmissionTime, A.LastUpdate FROM SubRequests AS A, ( SELECT RequestID, ExecutionOrder FROM ( SELECT * from ( SELECT * from SubRequests WHERE Status IN ( 'Waiting', 'Assigned' ) ORDER BY ExecutionOrder, LastUpdate ) as T1 GROUP BY RequestID ) as T2 WHERE RequestType = 'diset' ORDER BY LastUpdate limit 100 ) AS B WHERE A.RequestID = B.RequestID AND A.ExecutionOrder = B.ExecutionOrder AND A.Status = 'Waiting' AND A.RequestType = 'diset';

This query is way too slow. I believe one of the main reason is because it is using IN(...) when selecting on indexed fields, and this kills the performance of any SELECT query.

The fact that since few days we are hammering the RequestDB with Accounting requests is obviously not helping.

[graciani]

The select must be like this. The main problem is that we have now over half million in:

SELECT * from SubRequests WHERE Status IN ( 'Waiting', 'Assigned' ) ORDER BY ExecutionOrder, LastUpdate ) as T1 GROUP BY RequestID

If the fields where not indexed, it will be impossible to make the query.

This needs to create a temporary table on disk, what can be done to speed things up it to change the configuration so that tmp tables are created not in /tmp but on

tmpfs on /dev/shm type tmpfs (rw)

that we can link from /opt/dirac/tmp as we have done on the new machines. In this way, temporary tables will be created on a memory filesystem and will be much faster. This requires a change in /opt/dirac/mysql/.my.cnf, create the link and restart mysql.

As you say the fact that we have > 500.000 Accounting records is what it is causing the problem, the old code was much slower.

[fstagni]

Hi Ricardo,
what you say about moving from /tmp to /opt/dirac/tmp (or whatever else) is what we have done about 20 hours ago, exactly after we installed the new version, and we started seeing all the indexes being rebuilt.

You say that the new code is faster than the old one. What I reply is what I have already written: we migrated from v6r3 to v6r4 yesterday at 11am. No problems were registered up to that point. All problems started AFTER it. Then, as soon as we switched back to old version (v6r3), the problems went away. I hope it is clear enough on which version is faster.

[atsareg]

The fixes made these queries faster. In the reworked RequestManagementSystem this will be reworked completely