Re: EXPLAIN says DEPENDENT SUBQUERY despite no free variables
On Thu, Feb 25, 2010 at 8:48 AM, Dan Nelson dnel...@allantgroup.com wrote: IN is fine ( for example ... WHERE field1 IN (1,2,3,4,5,6) is extremely efficient); it's subqueries in general that are killers. If the dependent subquery is nothing but index lookups, it's still blazingly fast, though :) I just optimized one like that: select nid from search_total left join search_index on search_total.nid = search_index.nid where search_index.nid is null; got optimized to select nid from search_total where nid not in (select nid from search_index); This shaved 3 seconds off a 10-second query (field is indexed in both tables, plenty of room in the key cache). Now, if there was a way to tell MySQL that the subquery isn't dependant, it should turn into a near-zero query. I also tested a *not exists* construct, which turned out to be about a hundreth of a second slower. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Remove - Foreign key constraint in innodb
Hi, I tried to remove foreign key constraint in innodb table. I tried with different ways; but i am unable to drop the constraints. http://lists.mysql.com/mysql/113053 It says that, droping the foreign key constraint it is not possible in innodb engine. Is it so? or any other possibilities? I am using mysql 5.1.32 Please, Can any one you help me? Thank you VIKRAM A The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/
Re: Remove - Foreign key constraint in innodb
Yes - you can drop a foreign key constraint, use the 'alter table ... drop foreign key ...' command. If you get an error message, post the error message. On Thu, Feb 25, 2010 at 6:03 AM, Vikram A vikkiatb...@yahoo.in wrote: Hi, I tried to remove foreign key constraint in innodb table. I tried with different ways; but i am unable to drop the constraints. http://lists.mysql.com/mysql/113053 It says that, droping the foreign key constraint it is not possible in innodb engine. Is it so? or any other possibilities? I am using mysql 5.1.32 Please, Can any one you help me? Thank you VIKRAM A The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/ -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: Remove - Foreign key constraint in innodb
An example how to delete a foreign key from an InnoDB table: test CREATE TABLE table_1 (id int unsigned NOT NULL auto_increment PRIMARY KEY) ENGINE=InnoDB; Query OK, 0 rows affected (0.56 sec) test CREATE TABLE table_2 (table1_id int unsigned NOT NULL, FOREIGN KEY (table1_id) REFERENCES table_1 (id)) ENGINE=InnoDB; Query OK, 0 rows affected (0.11 sec) test SHOW CREATE TABLE table_2\G *** 1. row *** Table: table_2 Create Table: CREATE TABLE `table_2` ( `table1_id` int(10) unsigned NOT NULL, KEY `table1_id` (`table1_id`), CONSTRAINT `table_2_ibfk_1` FOREIGN KEY (`table1_id`) REFERENCES `table_1` (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.47 sec) test ALTER TABLE table_2 DROP FOREIGN KEY table_2_ibfk_1; Query OK, 0 rows affected (0.11 sec) Records: 0 Duplicates: 0 Warnings: 0 test SHOW CREATE TABLE table_2\G *** 1. row *** Table: table_2 Create Table: CREATE TABLE `table_2` ( `table1_id` int(10) unsigned NOT NULL, KEY `table1_id` (`table1_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 1 row in set (0.00 sec) On 25/02/2010, at 11:33 PM, Jim Lyons wrote: Yes - you can drop a foreign key constraint, use the 'alter table ... drop foreign key ...' command. If you get an error message, post the error message. On Thu, Feb 25, 2010 at 6:03 AM, Vikram A vikkiatb...@yahoo.in wrote: Hi, I tried to remove foreign key constraint in innodb table. I tried with different ways; but i am unable to drop the constraints. http://lists.mysql.com/mysql/113053 It says that, droping the foreign key constraint it is not possible in innodb engine. Is it so? or any other possibilities? I am using mysql 5.1.32 Please, Can any one you help me? Thank you VIKRAM A The INTERNET now has a personality. YOURS! See your Yahoo! Homepage. http://in.yahoo.com/ -- Jim Lyons Web developer / Database administrator http://www.weblyons.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: EXPLAIN says DEPENDENT SUBQUERY despite no free variables
On Thu, Feb 25, 2010 at 2:48 AM, Dan Nelson dnel...@allantgroup.com wrote: IN is fine ( for example ... WHERE field1 IN (1,2,3,4,5,6) is extremely efficient); Yes, I meant to say IN/NOT IN subqueries, not value lists. it's subqueries in general that are killers. Subqueries in the FROM clause (aka derived tables) work pretty well, acting as an in-line temp table. Other subqueries perform poorly, as you say. - Perrin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: EXPLAIN says DEPENDENT SUBQUERY despite no free variables
Hello, On Wed, Feb 24, 2010 at 10:11 AM, Yang Zhang yanghates...@gmail.com wrote: I have the following query. Note that the nested query has no dependencies on the outer one, yet mysql reports it as dependent. Do an EXPLAIN EXTENDED followed by SHOW WARNINGS. You will see the optimization that mysqld applies to the subquery, to try to help it by adding a dependency on the outer query. There's nothing you can do about this :-( You have to use a JOIN in most cases. BTW, the general log is itself a performance killer when logged to tables. If I were you I'd use the slow query log and mk-query-digest from Maatkit. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Index analyser
Bryan, On Tue, Feb 23, 2010 at 6:09 PM, Cantwell, Bryan bcantw...@firescope.com wrote: Ya, that one is helpful... just trying to land on a solution like I've seen in other DB's that have index-advisor that listens and creates what it thinks is the perfect indexes ... but thx... I know exactly what you are thinking of. I used to live and breathe SQL Server. Nothing similar exists for MySQL to the best of my (reasonably extensive) knowledge. But it's a great idea for a future Maatkit tool, or a plug-in for mk-query-digest. - Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
What is unusually high for the # of connections to MySQL?
I was wondering what would be considered unusually high for the # of connections to a MySQL Server? Also, if a high number of these are in sleep mode,does it make a difference? We have a web site (a few, actually) and MySQL (Version 5.0.67-community-nt-log) running on a WS08 server, and several times now, we have basically had the web site crash on us. One tech thought that it may be the # of connections. I have seen between 100 to 125 connections or so at one time 98% of them all from the same user. This is from our asp.net web application that we're using for testing. The app basically becomes unresponsive, but I'm not 100% convinced that this is a MySQL problem. The site does not even seem to be serving up pages when it gets into this mode. Also, there are other web sites on this same server (not being used a lot at all), and these sites all seem to come up just fine. There are no connection issues with the pages or with the data in those applications. My main questio is this. Is 100 to 125 unusually high? I have implemented a connection pool into my connection string in hopes that this will resolve the problem. Here is that string: uid=usernamer;password=password;Server=127.0.0.1;port=3306;Database=mydatabase;Allow Zero Datetime=true;pooling=true; max pool size=10; min pool size=3 Someone else suggested this string, but after implementing it and re-starting the server, we still had the same problem. My plan is to move the app to a WS03 server tonight in hopes that the issue is the O/S. Can anyone fill me in? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: What is unusually high for the # of connections to MySQL?
It depends, but 100 is not strange at all, particularly if you have sleeping connections (usually due to slow page loading (ajax?) and/or persistent connections from the app) and any number of connections cannot crash a server, can make it slow or unusable, but not crash it. Watch the app, you could have for loops banging the database, a not optimized app can kill cause a D.O.S.(=bad) of MySQL. Anyway the point is another. I think you cant afford guessing, it will take a huge amount of effort to try to guess why it crashes. Find the more information you can enabling all the logging possible, put server parameters under graphing, the more information you have on the crash, the less you will need to guess. Watch, cpu(load, context switches), ram(usage,swapping), IO. Guess less, know more. Claudio 2010/2/26 Jesse j...@msdlg.com I was wondering what would be considered unusually high for the # of connections to a MySQL Server? Also, if a high number of these are in sleep mode,does it make a difference? We have a web site (a few, actually) and MySQL (Version 5.0.67-community-nt-log) running on a WS08 server, and several times now, we have basically had the web site crash on us. One tech thought that it may be the # of connections. I have seen between 100 to 125 connections or so at one time 98% of them all from the same user. This is from our asp.net web application that we're using for testing. The app basically becomes unresponsive, but I'm not 100% convinced that this is a MySQL problem. The site does not even seem to be serving up pages when it gets into this mode. Also, there are other web sites on this same server (not being used a lot at all), and these sites all seem to come up just fine. There are no connection issues with the pages or with the data in those applications. My main questio is this. Is 100 to 125 unusually high? I have implemented a connection pool into my connection string in hopes that this will resolve the problem. Here is that string: uid=usernamer;password=password;Server=127.0.0.1;port=3306;Database=mydatabase;Allow Zero Datetime=true;pooling=true; max pool size=10; min pool size=3 Someone else suggested this string, but after implementing it and re-starting the server, we still had the same problem. My plan is to move the app to a WS03 server tonight in hopes that the issue is the O/S. Can anyone fill me in? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com -- Claudio
Re: What is unusually high for the # of connections to MySQL?
Thank you so much for the reply. I think we may have stepped outside of the MySQL realm now, but here is what I know: * At least a couple times, recycling the application pool started things right up, but that did not always work. * When this is going on, I cannot even get to a page itself, let alone execute a function that runs a query. * One time when this happened, we moved the entire app to an OLD WS03 server. It had only 2 GB, I believe, and it ran like champ after that. Due to circumstances beyond our control, we had to move it back to the WS08 server, and here we are again with the same problem. * I can log on to the server, no problem. I can also log on to MySQL and run queries. I would think that if the database server were the problem, I would not be able to do that. * Do do frequently get errors when this is occurring. These are asp.net errors. here are a few of those: MySql.Data.MySqlClient.MySqlException: error connecting: Timeout expired System.IndexOutOfRangeException: Could not find specified column in results Object reference not set to an instance of an object System.IO.IOException: Unable to write data to the transport connection: An existing connection was forcibly closed by the remote host 42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''SHOW VARIABLE'' Key cannot be null The list goes on. As you can see, the errors are all over the board. Some make sense, some do not. For instance, the you have an error in your sql does not, because this same area of code works perfectly Many times throughout the day, and I or no one else has changed it. Plus, the one stating ''SHOW VARIABLE'' makes no sense at all. I have not executed such a function in my code. Thanks, Jesse - Original Message - From: Claudio Nanni To: Jesse Cc: mysql@lists.mysql.com Sent: Thursday, February 25, 2010 6:28 PM Subject: Re: What is unusually high for the # of connections to MySQL? It depends, but 100 is not strange at all, particularly if you have sleeping connections (usually due to slow page loading (ajax?) and/or persistent connections from the app) and any number of connections cannot crash a server, can make it slow or unusable, but not crash it. Watch the app, you could have for loops banging the database, a not optimized app can kill cause a D.O.S.(=bad) of MySQL. Anyway the point is another. I think you cant afford guessing, it will take a huge amount of effort to try to guess why it crashes. Find the more information you can enabling all the logging possible, put server parameters under graphing, the more information you have on the crash, the less you will need to guess. Watch, cpu(load, context switches), ram(usage,swapping), IO. Guess less, know more. Claudio 2010/2/26 Jesse j...@msdlg.com I was wondering what would be considered unusually high for the # of connections to a MySQL Server? Also, if a high number of these are in sleep mode,does it make a difference? We have a web site (a few, actually) and MySQL (Version 5.0.67-community-nt-log) running on a WS08 server, and several times now, we have basically had the web site crash on us. One tech thought that it may be the # of connections. I have seen between 100 to 125 connections or so at one time 98% of them all from the same user. This is from our asp.net web application that we're using for testing. The app basically becomes unresponsive, but I'm not 100% convinced that this is a MySQL problem. The site does not even seem to be serving up pages when it gets into this mode. Also, there are other web sites on this same server (not being used a lot at all), and these sites all seem to come up just fine. There are no connection issues with the pages or with the data in those applications. My main questio is this. Is 100 to 125 unusually high? I have implemented a connection pool into my connection string in hopes that this will resolve the problem. Here is that string: uid=usernamer;password=password;Server=127.0.0.1;port=3306;Database=mydatabase;Allow Zero Datetime=true;pooling=true; max pool size=10; min pool size=3 Someone else suggested this string, but after implementing it and re-starting the server, we still had the same problem. My plan is to move the app to a WS03 server tonight in hopes that the issue is the O/S. Can anyone fill me in? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com -- Claudio
Incorrect key file for table '...'; try to repair it on Innodb table
I'm getting Incorrect key file for table 'stock'; try to repair it for alter table stock add constraint pk_stock primary key (s_w_id, s_i_id);. I can't do repair table on this since it's an innodb table. Googling doesn't turn up any clear answers. Any way to recover from this, or is mysqldump + load data the way to go? -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Using symlinks for database creation in mysql
Hi, On Mon, Feb 22, 2010 at 10:40 AM, Johan De Meersman vegiv...@tuxera.be 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=arch...@jab.org
Re: Any faster building primary/unique indexes after Load Data Infile?
Hi, On Sun, Feb 21, 2010 at 1:42 PM, mos mo...@fastmail.fm wrote: I am loading 35 million rows of data into an empty MyISAM table. This table has 1 primary key (AutoInc) and 1 unique index and 2 non-unique indexes. Is it going to be any faster if I remove the indexes from the table before loading the data, load the data, then do an Alter Table .. add index for all of the indexes? Or is it faster to just leave the indexes in place prior to loading the data. I know if the table is empty and optimized, the non-unique indexes will be built AFTER the data is loaded using Load Data Infile, but the unique and primary indexes will be built as the data is being loaded and this is going to slow down the import. There is no point doing a Disable Indexes on the table because this only affects non-unique indexes and that is already taken care of since the table is already empty and optimized. But if I remove the indexes from the empty table then load the data, then execute the Alter Table Add Index ... for all 4 indexes at one time, isn't the Alter Table going to create a copy of the table so it is just going to reload the data all over again? Yes. It is going to create a new table, copy the rows into it, and then delete the old one. Is there any way to add a primary or unique index without copy the data all over again? Create Index ... can't be used to create a primary index. Dirty hacks with .frm files and REPAIR TABLE have sometimes been known to help in cases like this. But it's not for the faint of heart. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Re: What is unusually high for the # of connections to MySQL?
Hi Jesse, as you can see the most relevant is connection timed out, you could focus on this, this problem is typical of ODBC. this can happen because you use the persistent connection pool in your DSN (ODBC). So I would start focusing on the connection time out. I could say raise the timeout time but you could always meet the problem again, you have to see if you can set the driver (odbc) to refresh the connection automatically when it expires. Also in the code if you trap the error you can refresh the connection from the code. This is just where I would start. let me know Claudio Il giorno 26/feb/2010 00.38, Jesse j...@msdlg.com ha scritto: Thank you so much for the reply. I think we may have stepped outside of the MySQL realm now, but here is what I know: * At least a couple times, recycling the application pool started things right up, but that did not always work. * When this is going on, I cannot even get to a page itself, let alone execute a function that runs a query. * One time when this happened, we moved the entire app to an OLD WS03 server. It had only 2 GB, I believe, and it ran like champ after that. Due to circumstances beyond our control, we had to move it back to the WS08 server, and here we are again with the same problem. * I can log on to the server, no problem. I can also log on to MySQL and run queries. I would think that if the database server were the problem, I would not be able to do that. * Do do frequently get errors when this is occurring. These are asp.net errors. here are a few of those: MySql.Data.MySqlClient.MySqlException: error connecting: Timeout expired System.IndexOutOfRangeException: Could not find specified column in results Object reference not set to an instance of an object System.IO.IOException: Unable to write data to the transport connection: An existing connection was forcibly closed by the remote host 42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''SHOW VARIABLE'' Key cannot be null The list goes on. As you can see, the errors are all over the board. Some make sense, some do not. For instance, the you have an error in your sql does not, because this same area of code works perfectly Many times throughout the day, and I or no one else has changed it. Plus, the one stating ''SHOW VARIABLE'' makes no sense at all. I have not executed such a function in my code. Thanks, Jesse - Original Message - From: Claudio Nanni To: Jesse Cc: mysql@lists.mysql.com Sent: Thursday, February 25, 2010 6:28 PM Subject: Re: What is unusually high for the # of connections to MySQL? It depends, but 100 is not strange at all, particularly if you have sleeping connections (usually due to slow page loading (ajax?) and/or persistent connections from the app) and any number of connections cannot crash a server, can make it slow or unusable, but not crash it. Watch the app, you could have for loops banging the database, a not optimized app can kill cause a DOS(=bad) of MySQL. Anyway the point is another. I think you cant afford guessing, it will take a huge amount of effort to try to guess why it crashes. Find the more information you can enabling all the logging possible, put server parameters under graphing, the more information you have on the crash, the less you will need to guess. Watch, cpu(load, context switches), ram(usage,swapping), IO. Guess less, know more. Claudio 2010/2/26 Jesse j...@msdlg.com I was wondering what would be considered unusually high for the # of connections to a MySQL Server? Also, if a high number of these are in sleep mode,does it make a difference? We have a web site (a few, actually) and MySQL (Version 5.0.67-community-nt-log) running on a WS08 server, and several times now, we have basically had the web site crash on us. One tech thought that it may be the # of connections. I have seen between 100 to 125 connections or so at one time 98% of them all from the same user. This is from our asp.net web application that we're using for testing. The app basically becomes unresponsive, but I'm not 100% convinced that this is a MySQL problem. The site does not even seem to be serving up pages when it gets into this mode. Also, there are other web sites on this same server (not being used a lot at all), and these sites all seem to come up just fine. There are no connection issues with the pages or with the data in those applications. My main questio is this. Is 100 to 125 unusually high? I have implemented a connection pool into my connection string in hopes that this will resolve the problem. Here is that string: uid=usernamer;password=password;Server=127.0.0.1;port=3306;Database=mydatabase;Allow Zero Datetime=true;pooling=true; max pool size=10; min pool size=3 Someone else
Re: Any faster building primary/unique indexes after Load Data Infile?
Then before loading alter table table_name disable keys; load data alter table table enable keys; This will enable faster data load and faster index rebuild. regards anandkl On Fri, Feb 26, 2010 at 8:03 AM, Baron Schwartz ba...@xaprb.com wrote: Hi, On Sun, Feb 21, 2010 at 1:42 PM, mos mo...@fastmail.fm wrote: I am loading 35 million rows of data into an empty MyISAM table. This table has 1 primary key (AutoInc) and 1 unique index and 2 non-unique indexes. Is it going to be any faster if I remove the indexes from the table before loading the data, load the data, then do an Alter Table .. add index for all of the indexes? Or is it faster to just leave the indexes in place prior to loading the data. I know if the table is empty and optimized, the non-unique indexes will be built AFTER the data is loaded using Load Data Infile, but the unique and primary indexes will be built as the data is being loaded and this is going to slow down the import. There is no point doing a Disable Indexes on the table because this only affects non-unique indexes and that is already taken care of since the table is already empty and optimized. But if I remove the indexes from the empty table then load the data, then execute the Alter Table Add Index ... for all 4 indexes at one time, isn't the Alter Table going to create a copy of the table so it is just going to reload the data all over again? Yes. It is going to create a new table, copy the rows into it, and then delete the old one. Is there any way to add a primary or unique index without copy the data all over again? Create Index ... can't be used to create a primary index. Dirty hacks with .frm files and REPAIR TABLE have sometimes been known to help in cases like this. But it's not for the faint of heart. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com
Re: Re: What is unusually high for the # of connections to MySQL?
I think its more on the app side that is causing the problem. Check if the connections are getting closed on the app side after the job is done. Also u might to check on slow or general query log to know what is happening on the db side. Also check the load on the db server. We have db's running with close to 600 connections without any issues. regards anandkl On Fri, Feb 26, 2010 at 12:53 PM, claudio.na...@gmail.com wrote: Hi Jesse, as you can see the most relevant is connection timed out, you could focus on this, this problem is typical of ODBC. this can happen because you use the persistent connection pool in your DSN (ODBC). So I would start focusing on the connection time out. I could say raise the timeout time but you could always meet the problem again, you have to see if you can set the driver (odbc) to refresh the connection automatically when it expires. Also in the code if you trap the error you can refresh the connection from the code. This is just where I would start. let me know Claudio Il giorno 26/feb/2010 00.38, Jesse j...@msdlg.com ha scritto: Thank you so much for the reply. I think we may have stepped outside of the MySQL realm now, but here is what I know: * At least a couple times, recycling the application pool started things right up, but that did not always work. * When this is going on, I cannot even get to a page itself, let alone execute a function that runs a query. * One time when this happened, we moved the entire app to an OLD WS03 server. It had only 2 GB, I believe, and it ran like champ after that. Due to circumstances beyond our control, we had to move it back to the WS08 server, and here we are again with the same problem. * I can log on to the server, no problem. I can also log on to MySQL and run queries. I would think that if the database server were the problem, I would not be able to do that. * Do do frequently get errors when this is occurring. These are asp.net errors. here are a few of those: MySql.Data.MySqlClient.MySqlException: error connecting: Timeout expired System.IndexOutOfRangeException: Could not find specified column in results Object reference not set to an instance of an object System.IO.IOException: Unable to write data to the transport connection: An existing connection was forcibly closed by the remote host 42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''SHOW VARIABLE'' Key cannot be null The list goes on. As you can see, the errors are all over the board. Some make sense, some do not. For instance, the you have an error in your sql does not, because this same area of code works perfectly Many times throughout the day, and I or no one else has changed it. Plus, the one stating ''SHOW VARIABLE'' makes no sense at all. I have not executed such a function in my code. Thanks, Jesse - Original Message - From: Claudio Nanni To: Jesse Cc: mysql@lists.mysql.com Sent: Thursday, February 25, 2010 6:28 PM Subject: Re: What is unusually high for the # of connections to MySQL? It depends, but 100 is not strange at all, particularly if you have sleeping connections (usually due to slow page loading (ajax?) and/or persistent connections from the app) and any number of connections cannot crash a server, can make it slow or unusable, but not crash it. Watch the app, you could have for loops banging the database, a not optimized app can kill cause a DOS(=bad) of MySQL. Anyway the point is another. I think you cant afford guessing, it will take a huge amount of effort to try to guess why it crashes. Find the more information you can enabling all the logging possible, put server parameters under graphing, the more information you have on the crash, the less you will need to guess. Watch, cpu(load, context switches), ram(usage,swapping), IO. Guess less, know more. Claudio 2010/2/26 Jesse j...@msdlg.com I was wondering what would be considered unusually high for the # of connections to a MySQL Server? Also, if a high number of these are in sleep mode,does it make a difference? We have a web site (a few, actually) and MySQL (Version 5.0.67-community-nt-log) running on a WS08 server, and several times now, we have basically had the web site crash on us. One tech thought that it may be the # of connections. I have seen between 100 to 125 connections or so at one time 98% of them all from the same user. This is from our asp.net web application that we're using for testing. The app basically becomes unresponsive, but I'm not 100% convinced that this is a MySQL problem. The site does not even seem to be serving up pages when it gets into this mode. Also, there are other web sites on this same server (not being used a lot at all), and these sites all
Re: Incorrect key file for table '...'; try to repair it on Innodb table
This is due to less space in your /tmp file system. Change tmpdir parameter to point to a file system having more space and then try the adding the constraint. regards anandkl On Fri, Feb 26, 2010 at 5:14 AM, Yang Zhang yanghates...@gmail.com wrote: I'm getting Incorrect key file for table 'stock'; try to repair it for alter table stock add constraint pk_stock primary key (s_w_id, s_i_id);. I can't do repair table on this since it's an innodb table. Googling doesn't turn up any clear answers. Any way to recover from this, or is mysqldump + load data the way to go? -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com
