index files of database

index files of database

Post by Toni Schlichtin » Fri, 28 Jun 2002 04:56:48



Hi all,

I have been trying to move away the index files from an Informix SE
database to another device replacing them with symbolic links.

The Informix database doesn't bother and continues its work when it is
started again after this "operation". The only question is:

Can anybody help me to estimate whether this would be a performance gain
or a performance loss if done in a larger scale? (large number of index
files > 1000, few but huge transactions, db will be created "mode ansi")

The goal of moving the index files and others to a number of different
devices would be to reduce concurrency on disk I/O. And the actual
question is: Does it make sense and IF or IF NOT, why?

Thank you all,

Ciao, Toni

 
 
 

index files of database

Post by Barry Margoli » Fri, 28 Jun 2002 08:55:13




Quote:>Hi all,

>I have been trying to move away the index files from an Informix SE
>database to another device replacing them with symbolic links.

>The Informix database doesn't bother and continues its work when it is
>started again after this "operation". The only question is:

>Can anybody help me to estimate whether this would be a performance gain
>or a performance loss if done in a larger scale? (large number of index
>files > 1000, few but huge transactions, db will be created "mode ansi")

I expect that an index file will need to be opened once per query that
makes use of that particular index.  Compared to all the overhead of doing
a huge query, I doubt that the extra overhead of following a few symbolic
links will make any noticeable difference.  If all the links point to files
in the same directory, the kernel's filename cache should cause most of
these to go very quickly.

--

Genuity, Woburn, MA
*** DON'T SEND TECHNICAL QUESTIONS DIRECTLY TO ME, post them to newsgroups.
Please DON'T copy followups to me -- I'll assume it wasn't posted to the group.

 
 
 

index files of database

Post by Andrew Ham » Fri, 28 Jun 2002 09:33:21


Toni Schlichting wrote ...
Quote:

>I have been trying to move away the index files from an Informix SE
>database to another device replacing them with symbolic links.

>The goal of moving the index files and others to a number of different
>devices would be to reduce concurrency on disk I/O. And the actual
>question is: Does it make sense and IF or IF NOT, why?

No worries - good theory and minimal cost. Symlinks are very economical;
they only get in the way as the file is opened for the first time. After
that the *real* work begins... Presumably, the SE client code intelligently
caches the open file handles.
 
 
 

index files of database

Post by Dan » Sun, 30 Jun 2002 16:25:35



Quote:> Hi all,

<snip>

Quote:> The goal of moving the index files and others to a number of different
> devices would be to reduce concurrency on disk I/O. And the actual
> question is: Does it make sense and IF or IF NOT, why?

Way back in time it made a big difference.  Today's hard disk drive (and
bus) speeds make it less of an issue.  Doing it can not hurt anything.  I
would suggest you try some iostat commands to see if it is really the issue.
The amount of workload on the databases is the big factor.  I am just
wondering if this is a admin vs users question.  That is to say, are the
users saying it is slow?  One of the biggest bottlenecks I have run into was
when the users used tables with every column indexed regardless of what they
were looking at.  So, the slowdown was their individual workstations caching
a copy of all the indexes, and of ourse, they all logged on at the same
time.

Good luck!