Re: Issues with MySQL x86_64 crashing
On 12/7/06, David Sparks [EMAIL PROTECTED] wrote: Kevin Old wrote: Hello everyone, We have a 4 CPU master server running the 5.0.27 RPM x86_64 version of MySQL with a mix of InnoDB and MyISAM tables. We normally run at 1500 queries/per second and lately, the server will all of a sudden lock up and we are forced to restart mysql. That isn't a crash. Can you still connect to the db? If so output of 'show full processlist\G', etc would be useful. Sorry, I shouldn't have worded it as a crash. Basically what happens is that queries start stacking up and if we don't start killing queries, we have to restart mysql before it crashes/restarts itself. We have spotted a couple of queries that we think might be causing the problems, but even after adjusting the queries and the tables (converting from InnoDB to MyISAM) we're still seeing the spike in queries (not due to actual load). Here are links to our my.cnf and the output of show innodb status and of show variables: http://kold.homelinux.com/bj/my.cnf http://kold.homelinux.com/bj/innodbstatus.txt http://kold.homelinux.com/bj/mysqlstats.txt Maybe some of our settings could be causing the problem. Thanks for any help, Kevin -- Kevin Old [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Issues with MySQL x86_64 crashing
We have 16GB total, but are only using 8GB (according to mysql and our dbadmin). Kevin On 12/8/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, How much memory do you have on your system ? (the current setting in your my.cnf could eat *a lot* of memory) Thanks, Jocelyn Fournier www.mesdiscussions.net On 12/7/06, David Sparks [EMAIL PROTECTED] wrote: Kevin Old wrote: Hello everyone, We have a 4 CPU master server running the 5.0.27 RPM x86_64 version of MySQL with a mix of InnoDB and MyISAM tables. We normally run at 1500 queries/per second and lately, the server will all of a sudden lock up and we are forced to restart mysql. That isn't a crash. Can you still connect to the db? If so output of 'show full processlist\G', etc would be useful. Sorry, I shouldn't have worded it as a crash. Basically what happens is that queries start stacking up and if we don't start killing queries, we have to restart mysql before it crashes/restarts itself. We have spotted a couple of queries that we think might be causing the problems, but even after adjusting the queries and the tables (converting from InnoDB to MyISAM) we're still seeing the spike in queries (not due to actual load). Here are links to our my.cnf and the output of show innodb status and of show variables: http://kold.homelinux.com/bj/my.cnf http://kold.homelinux.com/bj/innodbstatus.txt http://kold.homelinux.com/bj/mysqlstats.txt Maybe some of our settings could be causing the problem. Thanks for any help, Kevin -- Kevin Old [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Kevin Old [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Issues with MySQL x86_64 crashing
On 12/8/06, Philip Mather [EMAIL PROTECTED] wrote: So something like 15G, that's not that bad. I'd run mtop as someone suggested and see if some query is hammering it, maybe some other process on the machine is hogging or going IO bound? Thanks. We are watching the queries. The pattern we're seeing now is any large query that takes more than a few seconds to execute causes incoming queries to stack up and not execute, which causes the mysql load to go higher. We've seen a few times where mysql recovered after a large query started other queries to stack up. Keep in mind that we've been running some of these queries that are now having problems for over a year. We were running on the same hardware with the 386 version of mysql and performance was awesome only using 2GB RAM (the max mysql would allow us to use). Only after the switch to the x86_64 version are we seeing these problems. Thanks for your help, Kevin -- Kevin Old [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Issues with MySQL x86_64 crashing
Hello everyone, We have a 4 CPU master server running the 5.0.27 RPM x86_64 version of MySQL with a mix of InnoDB and MyISAM tables. We normally run at 1500 queries/per second and lately, the server will all of a sudden lock up and we are forced to restart mysql. This is not related to higher load, as it frequently happens during our slowest load period (early AM). This is not related to any significant code changes. We have recently converted about 20 of our largest and most frequently used tables from MyISAM to InnoDB. The size of our InnoDB tables collectively is about 95GB. I can provide my configuration if it is necessary, but was just wondering if someone else might have experienced this and could possibly point me in the right direction as to what might be causing this. Thanks, Kevin -- Kevin Old [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: Issues with MySQL x86_64 crashing
No backups (like innodb-backup) running during these times. We have 16GB of RAM and are currently using about 8GB of it. We think we might have narrowed it down to a few hellish queries that are hitting a few tables that we recently converted to InnoDB from MyISAM. We're gonna convert them back to MyISAM tonight and see if that fixes it. Thanks for your help, Kevin On 12/7/06, Michael Bacarella [EMAIL PROTECTED] wrote: RAM too, how are you on RAM? Obviously 100GB+ probably isn't going to fit in cache, but the usage pattern during slower periods might be causing killer thrashing. On Thu, Dec 07, 2006 at 12:43:17PM -0600, Dan Buettner wrote: Another thought is - do you have backups running during this time? Something that might be attempting to backup live the InnoDB files? We had similar problems with MySQL and backup software a while ago, though we used all-MyISAM. Dan On 12/7/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, Do you use MySQL with a NPTL or a linuxthread glibc configuration ? Regards, Jocelyn Fournier www.mesdiscussions.net Hello everyone, We have a 4 CPU master server running the 5.0.27 RPM x86_64 version of MySQL with a mix of InnoDB and MyISAM tables. We normally run at 1500 queries/per second and lately, the server will all of a sudden lock up and we are forced to restart mysql. This is not related to higher load, as it frequently happens during our slowest load period (early AM). This is not related to any significant code changes. We have recently converted about 20 of our largest and most frequently used tables from MyISAM to InnoDB. The size of our InnoDB tables collectively is about 95GB. I can provide my configuration if it is necessary, but was just wondering if someone else might have experienced this and could possibly point me in the right direction as to what might be causing this. Thanks, Kevin -- Kevin Old [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Michael Bacarella [EMAIL PROTECTED] 1-646-641-8662 (cell) 545 Eighth Avenue * Suite 401 New York, NY 10018 http://michael.bacarella.com/ http://netgraft.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Kevin Old [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Disaster with dash on mysql cli interface
Hello everyone, I had a horrible thing happen to me this morning and wanted to make it known to the community. I needed to delete a record from a very large table (yes, it was backed up) and like the cli interface of mysql. I ran this query: delete from tablename where id - 12345; Notice that I accidentally hit the dash (-) instead of the equal (=). It proved to be disasterous as it deleted all the records from that table. Lucky for me I had a backup from last night and not too many records were added since then and I was able to restore. For the record, I am aware of the select before delete method, but didn't use it in this one instance and it meant a few hours restoring data. Just wanted to throw this out and see if others had possible solutions for working with the mysql cli interface for maybe setting up rules for it to cancel a query if it contains a certain character (like the dash). Fat chance there is, but I thought I'd ask. Hope this helps someone, Kevin -- Kevin Old [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Disaster with dash on mysql cli interface
On 6/21/06, Barry [EMAIL PROTECTED] wrote: Kevin Old schrieb: Hello everyone, I had a horrible thing happen to me this morning and wanted to make it known to the community. I needed to delete a record from a very large table (yes, it was backed up) and like the cli interface of mysql. I ran this query: delete from tablename where id - 12345; Notice that I accidentally hit the dash (-) instead of the equal (=). It proved to be disasterous as it deleted all the records from that table. Lucky for me I had a backup from last night and not too many records were added since then and I was able to restore. For the record, I am aware of the select before delete method, but didn't use it in this one instance and it meant a few hours restoring data. Just wanted to throw this out and see if others had possible solutions for working with the mysql cli interface for maybe setting up rules for it to cancel a query if it contains a certain character (like the dash). Fat chance there is, but I thought I'd ask. Hope this helps someone, Kevin On this one use LIMIT. If you want to delete specific rows alway use LIMIT. even if you f**k up you just have deleted one row. If you are luck it is an old one and easy restoreable. Hi Barry, So if I understand you correctly, I'd do the following: delete from tablename where id - 12345 limit 1; Is that correct? -- Kevin Old [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL with SSL enabled
On Wed, 2003-12-17 at 09:39, rmck wrote: Hello, I am in the process of installing Mysql 4.0.17. I need to have Mysql with SSL enabled. Does this feature come enabled with the binary download? Or do I need to download the source and compile it? Any good links ot setting up MySQL with SSL enabled? Hi Rob, I'm not sure about SSL capabilities of MySQL, but there's a SSL wrapper called Stunnel at http://www.stunnel.org that will wrap any ports connection in SSL with OpenSSL and SSLeay libaries. Very cool for securing email connections with regular POP3 accounts. Should be just as easy with any other port. HTH, Kevin -- Kevin Old [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL with SSL enabled
On Wed, 2003-12-17 at 09:39, rmck wrote: Hello, I am in the process of installing Mysql 4.0.17. I need to have Mysql with SSL enabled. Does this feature come enabled with the binary download? Or do I need to download the source and compile it? Any good links ot setting up MySQL with SSL enabled? Thanks Rob Also, this article about 11 SSH Tricks might help. http://www.linuxjournal.com/article.php?sid=6602 HTH, Kevin -- Kevin Old [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Max number of merged tables?
Hello all, I am currently creating a table per hour for some data I have. Each table has 502 fields (yeah, I knownothing I can do about it, this is a very special situation) and about 100,000 records. A few hours worth of data creates a 2GB table, and I don't really want to increase the size of the tables and risk something happening to *all* the data if something becomes corrupt. I'm using the MERGE feature and all has worked well so far. I am using MySQL version 3.23.52. My question is, what is the maximium number of tables I can merge together? Like I said, I create one every hour. 24 a day, 168 a week and 672 a month. I would like to keep as much data available in the merged table. I'm using a merge table so that I can query one table rather than generating a transaction of queries. Any ideas? Thanks, Kevin [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
