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:vegiv...@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 sureshkumar...@gmail.com 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 ad...@sifycorp.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Reg...My Hung MYSQL instance
- Original Message - From: Shafi AHMED shafi.ah...@sifycorp.com 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=arch...@jab.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:vegiv...@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 shafi.ah...@sifycorp.com 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 ad...@sifycorp.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.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=arch...@jab.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 ad...@sifycorp.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Reg...My Hung MYSQL instance
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 shafi.ah...@sifycorp.com 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 ad...@sifycorp.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=eroomy...@gmail.com
Re: Reg...My Hung MYSQL instance
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 eroomy...@gmail.com 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 shafi.ah...@sifycorp.com 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 ad...@sifycorp.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=eroomy...@gmail.com -- Regards, Dhaval Jaiswal
Re: Reg...My Hung MYSQL instance
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 eroomy...@gmail.com 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 shafi.ah...@sifycorp.com 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 ad...@sifycorp.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=eroomy...@gmail.com -- Thanks Suresh Kuna MySQL DBA
Re: Reg...My Hung MYSQL instance
- Original Message - From: Suresh Kuna sureshkumar...@gmail.com 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=arch...@jab.org