[GE users] ARCo and Oracle DB

chambon chambon at cc.in2p3.fr
Wed Nov 3 08:25:07 GMT 2010


    [ The following text is in the "utf-8" character set. ]
    [ Your display is set for the "ISO-8859-10" character set.  ]
    [ Some characters may be displayed incorrectly. ]

Hello,

I am continuing with GE stress test, now using ARCo.
I encountred a slowness with Oracle DB,  that was  not the case with Mysql DB
(on GE master the reporting files were consumed very slowly using Oracle DB)

After looking at tables and requests we saw that the following request was slow  :

SELECT * FROM sge_job WHERE j_job_number = 6866047 AND j_job_number = -1 AND j_pe_taskid = 'NONE' ORDER BY j_submission_time DESC

due to missing index on columns j_job_number and cô.

SQL> select INDEX_NAME, COLUMN_NAME  from USER_IND_COLUMNS where table_name='SGE_JOB'; 
INDEX_NAME	COLUMN_NAME
SYS_C0073644	J_ID
SGE_JOB_IDX1	J_SUBMISSION_TIME
SGE_JOB_IDX2	J_OWNER

After creating the ad-hoc index on j_job_number, j_job_number, j_pe_taskid,  the speed increase to an acceptable value

In dbwriter.log file the processing increase from 133 lines/s to 900 lines/s

02/11/2010 15:15:11|...|le.FileParser.createStatistics|I|Processed 162,884 lines in 1216.82s (133.86 lines/s)
...
02/11/2010 15:33:50|...|le.FileParser.createStatistics|I|Processed 31,595 lines in 33.93s (931.15 lines/s)

To be exhautive, on Mysql DB the index exist (that why everything works fine)

mysql> show create table sge_job \G
*************************** 1. row ***************************
       Table: sge_job
Create Table: CREATE TABLE `sge_job` (
  `j_id` decimal(38,0) NOT NULL DEFAULT '0',
  `j_job_number` int(11) DEFAULT NULL,
...
  PRIMARY KEY (`j_id`),
  KEY `sge_job_idx0` (`j_job_number`,`j_task_number`,`j_pe_taskid`),


Do I miss something (configuration) ? or is there an installation problem with DBwriter using Oracle DB ?

(I didn't make test with PostgresSQL)

Best regards
Bernard

------------------------------------------------------
http://gridengine.sunsource.net/ds/viewMessage.do?dsForumId=38&dsMessageId=292343

To unsubscribe from this discussion, e-mail: [users-unsubscribe at gridengine.sunsource.net].



More information about the gridengine-users mailing list