Using symlinks for database creation in mysql

--=_22gu8q9p71c0
Content-Type: text/plain;
charset=UTF-8
Content-Description: Plaintext Version of Message
Content-Disposition: inline
Content-Transfer-Encoding: 7bit


Hi All,

I am creating symlinks as database.
I have mysql data directory created on /var/lib/mysql/databasename.

on the same path I am creating /var/lib/mydatabase
and creating symlink from /var/lib/mydatabase to /var/lib/mysql/databasename

will there any performance issues as there will be quite a few folders and symlinks on the same path?

Thanks in advance.

--
Regards,
Manasi Save


--=_22gu8q9p71c0
Content-Type: multipart/related;
boundary="=_2b1r1ji2c8e8";
start="2b2ml9jcfekg@mail.artificialmachines.com"
Content-Transfer-Encoding: 7bit

This message is in MIME format.

--=_2b1r1ji2c8e8
Content-Type: text/html;
charset=UTF-8
Content-Description: HTML Version of Message
Content-Disposition: inline
Content-Transfer-Encoding: 7bit
Content-ID: 2b2ml9jcfekg@mail.artificialmachines.com

Hi All,

I am creating symlinks as database. 

I
have mysql data directory created on /var/lib/mysql/databasename.

/>

on the same path I am creating /var/lib/mydatabase

and creating
symlink from /var/lib/mydatabase to
 /var/lib/mysql/databasename

will there any performance
issues as there will be quite a few folders and symlinks on the same
path?

Thanks in advance.

Re: Using symlinks for database creation in mysql

--001636e0ac567fbf16047e47f832
Content-Type: text/plain; charset=ISO-8859-1

Not a problem as you are doing it from a whole data directory.

Thanks
Suresh Kuna
MySQL DBA

On Fri, Jan 29, 2010 at 11:56 AM, Manasi Save <
manasi.save@artificialmachines.com> wrote:

> Hi All,
>
>
> I am creating symlinks as database.
>
> I have mysql data directory created on /var/lib/mysql/databasename.
>
>
> on the same path I am creating /var/lib/mydatabase
>
> and creating symlink from /var/lib/mydatabase to
> /var/lib/mysql/databasename
>
>
> will there any performance issues as there will be quite a few folders and
> symlinks on the same path?
>
>
> Thanks in advance.
>
>
> --
> Regards,
> Manasi Save
>
>


--
Thanks
Suresh Kuna
MySQL DBA

--001636e0ac567fbf16047e47f832--

Re: Using symlinks for database creation in mysql

--0016368e233d909ce5047e480ebd
Content-Type: text/plain; charset=ISO-8859-1

I don't agree, Don't have any specific documents but seems to make a bit of
sense to me.

Opening a file that is a symbolic link takes more time than one that isn't
as you need to do more operations. Search the directory for the symbolic
link, open it (probably, though an inode look up might be enough), find the
file it points to and then open that.

Furthermore, I believe that an application that opened a symbolic link is
only ever going to reference that file via that link (as that's the file
handle it got when it opened the thing). This means that all io to and from
that file probably needs to go through a longer path.

Where you have hundreds of these going on, those additional system calls are
going to add up to a degradation in performance.

On Fri, Jan 29, 2010 at 12:14 PM, Suresh Kuna wrote:

> Not a problem as you are doing it from a whole data directory.
>
> Thanks
> Suresh Kuna
> MySQL DBA
>
> On Fri, Jan 29, 2010 at 11:56 AM, Manasi Save <
> manasi.save@artificialmachines.com> wrote:
>
> > Hi All,
> >
> >
> > I am creating symlinks as database.
> >
> > I have mysql data directory created on /var/lib/mysql/databasename.
> >
> >
> > on the same path I am creating /var/lib/mydatabase
> >
> > and creating symlink from /var/lib/mydatabase to
> > /var/lib/mysql/databasename
> >
> >
> > will there any performance issues as there will be quite a few folders
> and
> > symlinks on the same path?
> >
> >
> > Thanks in advance.
> >
> >
> > --
> > Regards,
> > Manasi Save
> >
> >
>
>
> --
> Thanks
> Suresh Kuna
> MySQL DBA
>



--
Best Regards,

Prabhat Kumar
MySQL DBA
Datavail-India Mumbai
Mobile : 91-9987681929
www.datavail.com

My Blog: http://adminlinux.blogspot.com
My LinkedIn: http://www.linkedin.com/in/profileprabhat

--0016368e233d909ce5047e480ebd--

Re: Using symlinks for database creation in mysql

--001636e1f85d657217047e48186b
Content-Type: text/plain; charset=ISO-8859-1

And, IF the application uses the file a lot, but opens it infrequently, for
example an MySQL data file, then the incremental cost is truly neglectable.

IF the symlink is looked up and followed frequently then caches will make it
neglectable.

IF the application touches a wide range (hundreds of thousands) of symlinks
frequently and performs very little actual access work (small files), then
there will be a potential significant overhead.


On Fri, Jan 29, 2010 at 12:20 PM, prabhat kumar wrote:

> I don't agree, Don't have any specific documents but seems to make a bit
> of sense to me.
>
> Opening a file that is a symbolic link takes more time than one that isn't
> as you need to do more operations. Search the directory for the symbolic
> link, open it (probably, though an inode look up might be enough), find the
> file it points to and then open that.
>
> Furthermore, I believe that an application that opened a symbolic link is
> only ever going to reference that file via that link (as that's the file
> handle it got when it opened the thing). This means that all io to and from
> that file probably needs to go through a longer path.
>
> Where you have hundreds of these going on, those additional system calls
> are going to add up to a degradation in performance.
>
>
> On Fri, Jan 29, 2010 at 12:14 PM, Suresh Kuna wrote:
>
>> Not a problem as you are doing it from a whole data directory.
>>
>> Thanks
>> Suresh Kuna
>> MySQL DBA
>>
>> On Fri, Jan 29, 2010 at 11:56 AM, Manasi Save <
>> manasi.save@artificialmachines.com> wrote:
>>
>> > Hi All,
>> >
>> >
>> > I am creating symlinks as database.
>> >
>> > I have mysql data directory created on /var/lib/mysql/databasename.
>> >
>> >
>> > on the same path I am creating /var/lib/mydatabase
>> >
>> > and creating symlink from /var/lib/mydatabase to
>> > /var/lib/mysql/databasename
>> >
>> >
>> > will there any performance issues as there will be quite a few folders
>> and
>> > symlinks on the same path?
>> >
>> >
>> > Thanks in advance.
>> >
>> >
>> > --
>> > Regards,
>> > Manasi Save
>> >
>> >
>>
>>
>> --
>> Thanks
>> Suresh Kuna
>> MySQL DBA
>>
>
>
>
> --
> Best Regards,
>
> Prabhat Kumar
> MySQL DBA
> Datavail-India Mumbai
> Mobile : 91-9987681929
> www.datavail.com
>
> My Blog: http://adminlinux.blogspot.com
> My LinkedIn: http://www.linkedin.com/in/profileprabhat
>



--
Best Regards,

Prabhat Kumar
MySQL DBA
Datavail-India Mumbai
Mobile : 91-9987681929
www.datavail.com

My Blog: http://adminlinux.blogspot.com
My LinkedIn: http://www.linkedin.com/in/profileprabhat

--001636e1f85d657217047e48186b--

Re: Using symlinks for database creation in mysql

--0050450142cad7a8d6047e4c4387
Content-Type: text/plain; charset=ISO-8859-1

Given that you're talking about "quite a few" folders, I think it'd be good
to check up on the theoretical and practical limits for your filesystem of
choice, though - ext2 for example starts getting noticeably slower when you
have a lot more than 10.000 entries in the same directory. One way of
getting around that would be segmenting your database names in blocks of
three or so (so "database" would be stored under dat/aba/se/).
Takes a bit of management, but then you already have something in place to
create the symlinks anyway.

Also, as a matter of manageability, I'd not put your real database dirs
somwhere where there is other stuff - make a separate directory and keep
them all there.

Another point of note, but I suppose you already know that, is that this
will not work with InnoDB unless you have the file-per-table option enabled.



--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--0050450142cad7a8d6047e4c4387--

Re: Using symlinks for database creation in mysql

Hi Johan,

Some time back you have solved my problem of creating symlink as
database was fixed. But now When I am putting special characters in
symlinks like "." it is not readable as database. If I am creating
database with special characters from mysql command line I can create
it but an symlink with special character is not readable.

Is it possible that I can read a symplink with special characters as databse.

Thanks in advance.

--
Regards,
Manasi Save



Quoting Johan De Meersman :
> Given that you're talking about "quite a few" folders, I think it'd be good
> to check up on the theoretical and practical limits for your filesystem of
> choice, though - ext2 for example starts getting noticeably slower when you
> have a lot more than 10.000 entries in the same directory. One way of
> getting around that would be segmenting your database names in blocks of
> three or so (so "database" would be stored under dat/aba/se/).
> Takes a bit of management, but then you already have something in place to
> create the symlinks anyway.
>
> Also, as a matter of manageability, I'd not put your real database dirs
> somwhere where there is other stuff - make a separate directory and keep
> them all there.
>
> Another point of note, but I suppose you already know that, is that this
> will not work with InnoDB unless you have the file-per-table option enabled.
>
>
>
> -- Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Using symlinks for database creation in mysql

Hi All,

When I am creating a symlink with a special character in database name
it appears in mysql as somthing like this
#mysql50#dbname.

For Ex:- I have created a symlink in mysql data directory named
"User.Name" which appeared to me in mysql command line client as
"#mysql50#User.Name

Can anyone help me on how to resolve this error.

Please Help.
Thanks in advance.

--
Regards,
Manasi Save


Quoting Manasi Save :
>
> Hi Johan,
>
> Some time back you have solved my problem of creating symlink as
> database was fixed. But now When I am putting special characters in
> symlinks like "." it is not readable as database. If I am creating
> database with special characters from mysql command line I can create
> it but an symlink with special character is not readable.
>
> Is it possible that I can read a symplink with special characters as databse.
>
> Thanks in advance.
>
> --
> Regards,
> Manasi Save
>
>
>
> Quoting Johan De Meersman :
> > Given that you're talking about "quite a few" folders, I think it'd be good
> > to check up on the theoretical and practical limits for your filesystem of
> > choice, though - ext2 for example starts getting noticeably slower when you
> > have a lot more than 10.000 entries in the same directory. One way of
> > getting around that would be segmenting your database names in blocks of
> > three or so (so "database" would be stored under dat/aba/se/).
> > Takes a bit of management, but then you already have something in place to
> > create the symlinks anyway.
> >
> > Also, as a matter of manageability, I'd not put your real database dirs
> > somwhere where there is other stuff - make a separate directory and keep
> > them all there.
> >
> > Another point of note, but I suppose you already know that, is that this
> > will not work with InnoDB unless you have the file-per-table option
> enabled.
> >
> >
> >
> > -- Bier met grenadyn
> > Is als mosterd by den wyn
> > Sy die't drinkt, is eene kwezel
> > Hy die't drinkt, is ras een ezel
>
>
> -- MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=manasi.save@artificialmac hines.com
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

RE: Using symlinks for database creation in mysql

Why are you using special characters!? You're just asking for trouble.

Just use letters, numbers, underscores for database names, table names and
column names.
In fact, you should probably use lowercase as well.

http://www.learn-mysql-tutorial.com/Identifiers.cfm
http://dev.mysql.com/doc/refman/5.0/en/innodb-restrictions.h tml

> -----Original Message-----
> From: Manasi Save [mailto:manasi.save@artificialmachines.com]
> Sent: Friday, February 19, 2010 4:20 AM
> To: mysql@lists.mysql.com
> Subject: Re: Using symlinks for database creation in mysql
>
> Hi All,
>
> When I am creating a symlink with a special character in
> database name
> it appears in mysql as somthing like this
> #mysql50#dbname.
>
> For Ex:- I have created a symlink in mysql data directory named
> "User.Name" which appeared to me in mysql command line client as
> "#mysql50#User.Name
>
> Can anyone help me on how to resolve this error.
>
> Please Help.
> Thanks in advance.
>
> --
> Regards,
> Manasi Save
>
>
> Quoting Manasi Save :
> >
> > Hi Johan,
> >
> > Some time back you have solved my problem of creating symlink as
> > database was fixed. But now When I am putting special characters in
> > symlinks like "." it is not readable as database. If I am creating
> > database with special characters from mysql command line I
> can create
> > it but an symlink with special character is not readable.
> >
> > Is it possible that I can read a symplink with special
> characters as databse.
> >
> > Thanks in advance.
> >
> > --
> > Regards,
> > Manasi Save
> >
> >
> >
> > Quoting Johan De Meersman :
> > > Given that you're talking about "quite a few" folders, I
> think it'd be good
> > > to check up on the theoretical and practical limits for
> your filesystem of
> > > choice, though - ext2 for example starts getting
> noticeably slower when you
> > > have a lot more than 10.000 entries in the same
> directory. One way of
> > > getting around that would be segmenting your database
> names in blocks of
> > > three or so (so "database" would be stored under
> dat/aba/se/).
> > > Takes a bit of management, but then you already have
> something in place to
> > > create the symlinks anyway.
> > >
> > > Also, as a matter of manageability, I'd not put your real
> database dirs
> > > somwhere where there is other stuff - make a separate
> directory and keep
> > > them all there.
> > >
> > > Another point of note, but I suppose you already know
> that, is that this
> > > will not work with InnoDB unless you have the
> file-per-table option
> > enabled.
> > >
> > >
> > >
> > > -- Bier met grenadyn
> > > Is als mosterd by den wyn
> > > Sy die't drinkt, is eene kwezel
> > > Hy die't drinkt, is ras een ezel
> >
> >
> > -- MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> >
> http://lists.mysql.com/mysql?unsub=manasi.save@artificialmac hines.com
> >
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=daevid@daevid.com
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Using symlinks for database creation in mysql

--00504502f68153fbc104803242d7
Content-Type: text/plain; charset=ISO-8859-1

I don't think using a dot is a good idea - that's the table.field separator.


On Fri, Feb 19, 2010 at 12:41 PM, Manasi Save <
manasi.save@artificialmachines.com> wrote:

>
> Hi Johan,
>
> Some time back you have solved my problem of creating symlink as database
> was fixed. But now When I am putting special characters in symlinks like "."
> it is not readable as database. If I am creating database with special
> characters from mysql command line I can create it but an symlink with
> special character is not readable.
> Is it possible that I can read a symplink with special characters as
> databse.
> Thanks in advance.
> --
> Regards,
> Manasi Save
>
>
>
> Quoting Johan De Meersman :
>
>> Given that you're talking about "quite a few" folders, I think it'd be
>> good
>> to check up on the theoretical and practical limits for your filesystem of
>> choice, though - ext2 for example starts getting noticeably slower when
>> you
>> have a lot more than 10.000 entries in the same directory. One way of
>> getting around that would be segmenting your database names in blocks of
>> three or so (so "database" would be stored under dat/aba/se/).
>> Takes a bit of management, but then you already have something in place to
>> create the symlinks anyway.
>> Also, as a matter of manageability, I'd not put your real database dirs
>> somwhere where there is other stuff - make a separate directory and keep
>> them all there.
>> Another point of note, but I suppose you already know that, is that this
>> will not work with InnoDB unless you have the file-per-table option
>> enabled.
>>
>>
>> -- Bier met grenadyn
>> Is als mosterd by den wyn
>> Sy die't drinkt, is eene kwezel
>> Hy die't drinkt, is ras een ezel
>>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=vegivamp@tuxera.be
>
>


--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--00504502f68153fbc104803242d7--

Re: Using symlinks for database creation in mysql

Hi,

On Mon, Feb 22, 2010 at 10:40 AM, Johan De Meersman wrote:
> I don't think using a dot is a good idea - that's the table.field separator.

Right. Even if mysqld didn't complain about the directory name, just
try using a '.' character in an identifier. It's a syntax error, no
matter how it's quoted, as I recall. (Shockingly, I don't have a
server available to test this assertion on at the moment.)

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org