Best Way to store Hierarchical Data in Mysql

Dear all,

I researched a lot on storing Hierarchical data in mysql tables.
Below links :

http://scvinodkumar.wordpress.com/2011/01/30/managing-hierar chical-data-in-mysql/
http://www.sitepoint.com/hierarchical-data-database-2/

shows different ways to store.

But is there any standard way of doing this fore.g storing twitter
tweets or comments on a link etc.


Thanks

--
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: Best Way to store Hierarchical Data in Mysql

Its Ok, it happen sometimes.



Alex Schaft wrote:
> Apologies. I hid reply to on the wrong message....
>
> On 2011/08/19 12:10 PM, Adarsh Sharma wrote:
>> Dear all,
>>
>> I researched a lot on storing Hierarchical data in mysql tables.
>> Below links :
>>
>> http://scvinodkumar.wordpress.com/2011/01/30/managing-hierar chical-data-in-mysql/
>>
>> http://www.sitepoint.com/hierarchical-data-database-2/
>>
>> shows different ways to store.
>>
>> But is there any standard way of doing this fore.g storing twitter
>> tweets or comments on a link etc.
>>
>>
>> Thanks
>>
>


--
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: Best Way to store Hierarchical Data in Mysql

> I researched a lot on storing Hierarchical data in mysql tables.
> Below links :
>
> http://scvinodkumar.wordpress.com/2011/01/30/managing-hierar chical-data-in-
> mysql/ http://www.sitepoint.com/hierarchical-data-database-2/
>
> shows different ways to store.
>
> But is there any standard way of doing this fore.g storing twitter
> tweets or comments on a link etc.

Most used is de adjacency model, but it depends on how you are going to use
it, and how deep hierarchical data goes / can go. Also a good source of
information:
- http://www.slideshare.net/billkarwin/models-for-hierarchical -data (nice
concise comparison of 4 alternatives)
- http://www.amazon.com/Hierarchies-Smarties-Kaufmann-Manageme nt-
Systems/dp/1558609202/ref=ntt_at_ep_dpt_2
--
Regards,

Rik

--
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: Best Way to store Hierarchical Data in Mysql

On 8/19/2011 5:10 AM, Adarsh Sharma wrote:
> Dear all,
>
> I researched a lot on storing Hierarchical data in mysql tables.
> Below links :
>
> http://scvinodkumar.wordpress.com/2011/01/30/managing-hierar chical-data-in-mysql/
>
> http://www.sitepoint.com/hierarchical-data-database-2/
>
> shows different ways to store.
>
> But is there any standard way of doing this fore.g storing twitter
> tweets or comments on a link etc.
Several ways, see
http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20 .html.

PB

-----

>
>
> Thanks
>

--
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

Reg...My Hung MYSQL instance

Dear, Today suddenly my database went into hung state due to Out of Memory
[ Killed process 1330 (mysqld) ].

Please advise me folks.This happens now often

Shafi




Get your world in your inbox!

Mail, widgets, documents, spreadsheets, organizer and much more with your Sifymail WIYI id!
Log on to http://www.sify.com

********** DISCLAIMER **********
Information contained and transmitted by this E-MAIL is proprietary to
Sify Technologies Limited and is intended for use only by the individual or entity to
which it is addressed, and may contain information that is privileged,
confidential or exempt from disclosure under applicable law. If this is a
forwarded message, the content of this E-MAIL may not have been sent with
the authority of the Company. If you are not the intended recipient, an
agent of the intended recipient or a person responsible for delivering the
information to the named recipient, you are notified that any use,
distribution, transmission, printing, copying or dissemination of this
information in any way or in any manner is strictly prohibited. If you have
received this communication in error, please delete this mail & notify us
immediately at admin@sifycorp.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: Reg...My Hung MYSQL instance

--20cf303bfa0c9b0a5a04ab280fc2
Content-Type: text/plain; charset=ISO-8859-1

It will only do what you let it. If your server ui consuming too much memory
it because you've let it.
On Aug 23, 2011 9:22 AM, "Shafi AHMED" wrote:
> Dear, Today suddenly my database went into hung state due to Out of Memory
> [ Killed process 1330 (mysqld) ].
>
> Please advise me folks.This happens now often
>
> Shafi
>
>
>
>
> Get your world in your inbox!
>
> Mail, widgets, documents, spreadsheets, organizer and much more with your
Sifymail WIYI id!
> Log on to http://www.sify.com
>
> ********** DISCLAIMER **********
> Information contained and transmitted by this E-MAIL is proprietary to
> Sify Technologies Limited and is intended for use only by the individual
or entity to
> which it is addressed, and may contain information that is privileged,
> confidential or exempt from disclosure under applicable law. If this is a
> forwarded message, the content of this E-MAIL may not have been sent with
> the authority of the Company. If you are not the intended recipient, an
> agent of the intended recipient or a person responsible for delivering the

> information to the named recipient, you are notified that any use,
> distribution, transmission, printing, copying or dissemination of this
> information in any way or in any manner is strictly prohibited. If you
have
> received this communication in error, please delete this mail & notify us
> immediately at admin@sifycorp.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=eroomydna@gmail.com
>

--20cf303bfa0c9b0a5a04ab280fc2--

Re: Reg...My Hung MYSQL instance

--20cf307ac8798c910004ab281e07
Content-Type: text/plain; charset=ISO-8859-1

Kill the Mysql process as of now & set the proper buffer parameters as per
the usage. start the mysql instance.



On Tue, Aug 23, 2011 at 2:04 PM, Andrew Moore wrote:

> It will only do what you let it. If your server ui consuming too much
> memory
> it because you've let it.
> On Aug 23, 2011 9:22 AM, "Shafi AHMED" wrote:
> > Dear, Today suddenly my database went into hung state due to Out of
> Memory
> > [ Killed process 1330 (mysqld) ].
> >
> > Please advise me folks.This happens now often
> >
> > Shafi
> >
> >
> >
> >
> > Get your world in your inbox!
> >
> > Mail, widgets, documents, spreadsheets, organizer and much more with your
> Sifymail WIYI id!
> > Log on to http://www.sify.com
> >
> > ********** DISCLAIMER **********
> > Information contained and transmitted by this E-MAIL is proprietary to
> > Sify Technologies Limited and is intended for use only by the individual
> or entity to
> > which it is addressed, and may contain information that is privileged,
> > confidential or exempt from disclosure under applicable law. If this is a
> > forwarded message, the content of this E-MAIL may not have been sent with
> > the authority of the Company. If you are not the intended recipient, an
> > agent of the intended recipient or a person responsible for delivering
> the
>
> > information to the named recipient, you are notified that any use,
> > distribution, transmission, printing, copying or dissemination of this
> > information in any way or in any manner is strictly prohibited. If you
> have
> > received this communication in error, please delete this mail & notify us
> > immediately at admin@sifycorp.com
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/mysql?unsub=eroomydna@gmail.com
> >
>



--
Regards,
Dhaval Jaiswal

--20cf307ac8798c910004ab281e07--

Re: Reg...My Hung MYSQL instance

--20cf30563e9368223a04ab2820f0
Content-Type: text/plain; charset=ISO-8859-1

Hello Shafi,

Can you paste your error log and configuration file with the total memory
you have on the server.

On Tue, Aug 23, 2011 at 2:04 PM, Andrew Moore wrote:

> It will only do what you let it. If your server ui consuming too much
> memory
> it because you've let it.
> On Aug 23, 2011 9:22 AM, "Shafi AHMED" wrote:
> > Dear, Today suddenly my database went into hung state due to Out of
> Memory
> > [ Killed process 1330 (mysqld) ].
> >
> > Please advise me folks.This happens now often
> >
> > Shafi
> >
> >
> >
> >
> > Get your world in your inbox!
> >
> > Mail, widgets, documents, spreadsheets, organizer and much more with your
> Sifymail WIYI id!
> > Log on to http://www.sify.com
> >
> > ********** DISCLAIMER **********
> > Information contained and transmitted by this E-MAIL is proprietary to
> > Sify Technologies Limited and is intended for use only by the individual
> or entity to
> > which it is addressed, and may contain information that is privileged,
> > confidential or exempt from disclosure under applicable law. If this is a
> > forwarded message, the content of this E-MAIL may not have been sent with
> > the authority of the Company. If you are not the intended recipient, an
> > agent of the intended recipient or a person responsible for delivering
> the
>
> > information to the named recipient, you are notified that any use,
> > distribution, transmission, printing, copying or dissemination of this
> > information in any way or in any manner is strictly prohibited. If you
> have
> > received this communication in error, please delete this mail & notify us
> > immediately at admin@sifycorp.com
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe: http://lists.mysql.com/mysql?unsub=eroomydna@gmail.com
> >
>



--
Thanks
Suresh Kuna
MySQL DBA

--20cf30563e9368223a04ab2820f0--

Re: Reg...My Hung MYSQL instance

----- Original Message -----
> From: "Suresh Kuna"
>
> Can you paste your error log and configuration file with the total
> memory you have on the server.

Hey, someone posting something actually useful. You must be new here :-D

Ahmed, do you have more connections than you used to? Some of the memory parameters in the mysql config are allocated per connection instead of globally, so it's quite possible to use more memory than you have if you get a lot of clients.

All of this is well-documented on mysql.com, but if you post your config and some info about your usage and dataset here we can have a brief look, too.

--
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: Reg...My Hung MYSQL instance

Thank you everyone who have responded back...
The issue is fixed now after increasing the max connections param

Shafi M



-----Original Message-----
From: Johan De Meersman [mailto:vegivamp@tuxera.be]
Sent: Tuesday, August 23, 2011 4:06 PM
To: Suresh Kuna
Cc: Shafi AHMED; mysql@lists.mysql.com; Andrew Moore
Subject: Re: Reg...My Hung MYSQL instance

----- Original Message -----
> From: "Suresh Kuna"
>
> Can you paste your error log and configuration file with the total
> memory you have on the server.

Hey, someone posting something actually useful. You must be new here :-D

Ahmed, do you have more connections than you used to? Some of the memory
parameters in the mysql config are allocated per connection instead of
globally, so it's quite possible to use more memory than you have if you get
a lot of clients.

All of this is well-documented on mysql.com, but if you post your config and
some info about your usage and dataset here we can have a brief look, too.

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



Get your world in your inbox!

Mail, widgets, documents, spreadsheets, organizer and much more with your Sifymail WIYI id!
Log on to http://www.sify.com

********** DISCLAIMER **********
Information contained and transmitted by this E-MAIL is proprietary to
Sify Technologies Limited and is intended for use only by the individual or entity to
which it is addressed, and may contain information that is privileged,
confidential or exempt from disclosure under applicable law. If this is a
forwarded message, the content of this E-MAIL may not have been sent with
the authority of the Company. If you are not the intended recipient, an
agent of the intended recipient or a person responsible for delivering the
information to the named recipient, you are notified that any use,
distribution, transmission, printing, copying or dissemination of this
information in any way or in any manner is strictly prohibited. If you have
received this communication in error, please delete this mail & notify us
immediately at admin@sifycorp.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: Reg...My Hung MYSQL instance

----- Original Message -----
> From: "Shafi AHMED"
>
> Thank you everyone who have responded back...
> The issue is fixed now after increasing the max connections param

Glad to hear that, but it seems unlikely, to me.

Certain things, like the query cache, index cache, etc. are allocated once, at startup. Those are fixed memory requirements. Other things, like read buffers, sort buffers and the like get allocated every time a client connects. Those are dynamic memory requirements, and the amount they use increases linearly with the number of concurrent connections you get.

Thus, increasing the max connections can never *reduce* your memory requirements - only potentially allow *more* memory to be allocated. I still suspect that you ran out of memory because you had a sudden influx of connections; and now that you've increased the max connections you'll run out of memory even faster next time that occurs.

If it works now, it works; but keep that in the back of your mind somewhere for next time you see it occur :-)


--
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: Reg...My Hung MYSQL instance

Great, thank you sir!
Appreciate your comprehensive reply

Best Rgs,
Shafi AHMED


-----Original Message-----
From: Johan De Meersman [mailto:vegivamp@tuxera.be]
Sent: Thursday, August 25, 2011 1:30 PM
To: Shafi AHMED
Cc: mysql@lists.mysql.com
Subject: Re: Reg...My Hung MYSQL instance

----- Original Message -----
> From: "Shafi AHMED"
>
> Thank you everyone who have responded back...
> The issue is fixed now after increasing the max connections param

Glad to hear that, but it seems unlikely, to me.

Certain things, like the query cache, index cache, etc. are allocated once,
at startup. Those are fixed memory requirements. Other things, like read
buffers, sort buffers and the like get allocated every time a client
connects. Those are dynamic memory requirements, and the amount they use
increases linearly with the number of concurrent connections you get.

Thus, increasing the max connections can never *reduce* your memory
requirements - only potentially allow *more* memory to be allocated. I still
suspect that you ran out of memory because you had a sudden influx of
connections; and now that you've increased the max connections you'll run
out of memory even faster next time that occurs.

If it works now, it works; but keep that in the back of your mind somewhere
for next time you see it occur :-)


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



Get your world in your inbox!

Mail, widgets, documents, spreadsheets, organizer and much more with your Sifymail WIYI id!
Log on to http://www.sify.com

********** DISCLAIMER **********
Information contained and transmitted by this E-MAIL is proprietary to
Sify Technologies Limited and is intended for use only by the individual or entity to
which it is addressed, and may contain information that is privileged,
confidential or exempt from disclosure under applicable law. If this is a
forwarded message, the content of this E-MAIL may not have been sent with
the authority of the Company. If you are not the intended recipient, an
agent of the intended recipient or a person responsible for delivering the
information to the named recipient, you are notified that any use,
distribution, transmission, printing, copying or dissemination of this
information in any way or in any manner is strictly prohibited. If you have
received this communication in error, please delete this mail & notify us
immediately at admin@sifycorp.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: Reg...My Hung MYSQL instance

Hello Shafi,

On 8/25/2011 02:02, Shafi AHMED wrote:
> Thank you everyone who have responded back...
> The issue is fixed now after increasing the max connections param
>

I disagree. I believe you only reduced the symptom of the problem. The
real problem was you had too many open connections. The solution is to
figure out why each of your connections had been open for so long and
why you needed so many.

* Were those idle connections sitting around doing nothing? - close them
* Were they taking forever to finish their business? - write better
queries or improve your data structures. Then close them.

Allowing more connections to be made at one time can only push your
system harder. Each connection requires some resources to check its
status. There must be buffers for sending and receiving data. Also, if
there are any connection-specific MySQL objects created on a connection
that never closes, then those objects will continue to take up resources
as well (user variables, prepared statements, temporary tables) .

Basically, you need to get your connections under control in order to
solve your problem. Raising the limit was probably a temporary fix, at
best.

--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

--
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