Re: Max connections being used every 10-12 day.
Hi, My original query was a UNION query :) and that one is really fast. The problem i had was that every 8-12 day mysql sad that all of my max_connections was in use. I think i will stick with my UNION query it seems faster. // Fredrik. Bill Easton wrote: Fredrik, I haven't read all of the earlier messages, but it looks like your problem is that a query such as the following takes more than a minute to return 11 rows. Select A.id, A.parent from art A inner join art B using (id) where A.id=560685 or B.parent=560685; Why? Well, your explain says that no key is used, but the entire table is scanned (type='ALL'). In particular, MySQL is reading every record of A and looking to see if either A.id=560685 or B.parent=560685. Not good. Your query is equivalent to the following: Select id from art where id=560685 or parent=560685; I'd be surprised if the simpler query weren't slightly faster than the original--MySQL has more work to do on the original. It appears that, with the more complex query, you are trying to fool MySQL into using indexes for both parts of the OR. It didn't work. In an earlier mail, your explain had type='index' and key='id_3'. In that case, you evidently had an index, id_3, that contained both id and parent. So, MySQL could get all of the information form the id_3 index; therefore, it read the index instead of the table. It still read the entire index, looking to see if either A.id=560685 or B.parent=560685. Better than reading the full table, but still not good. What to do? Well, you can get the information you want in TWO queries: Select id from art where id=560685; Select id from art where parent=560685; In each of these, MySQL will surely use the appropriate index and return the result in a few milliseconds. You should be able to combine them into one query and get the same behavior: Select id from art where id=560685 UNION ALL Select id from art where parent=560685; I'd be surprised if MySQL didn't do fine on this. (You may have tried this and failed--as I said, I didn't try to read all of the earlier mail. But I'd be astonished if it weren't fast, though I suppose MySQL might try to do something fancy, knowing that the two queries are on the same table. In any event, check the two individual queries. If necessary, use a temporary table.) Then, you get to add your ORDER BY clause; presumably, it will still be fast. There was some mention in earlier mail of joins being faster than unions. That may be, but the difference should be too small to notice, unless different indexes are used. In your query above, with the inner join, MySQL is going to first consider the join and then consider the use of indexes for the where clause--so it ends up with the full table scan. HTH, Bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max connections being used every 10-12 day.
Fredrik, I haven't read all of the earlier messages, but it looks like your problem is that a query such as the following takes more than a minute to return 11 rows. Select A.id, A.parent from art A inner join art B using (id) where A.id=560685 or B.parent=560685; Why? Well, your explain says that no key is used, but the entire table is scanned (type='ALL'). In particular, MySQL is reading every record of A and looking to see if either A.id=560685 or B.parent=560685. Not good. Your query is equivalent to the following: Select id from art where id=560685 or parent=560685; I'd be surprised if the simpler query weren't slightly faster than the original--MySQL has more work to do on the original. It appears that, with the more complex query, you are trying to fool MySQL into using indexes for both parts of the OR. It didn't work. In an earlier mail, your explain had type='index' and key='id_3'. In that case, you evidently had an index, id_3, that contained both id and parent. So, MySQL could get all of the information form the id_3 index; therefore, it read the index instead of the table. It still read the entire index, looking to see if either A.id=560685 or B.parent=560685. Better than reading the full table, but still not good. What to do? Well, you can get the information you want in TWO queries: Select id from art where id=560685; Select id from art where parent=560685; In each of these, MySQL will surely use the appropriate index and return the result in a few milliseconds. You should be able to combine them into one query and get the same behavior: Select id from art where id=560685 UNION ALL Select id from art where parent=560685; I'd be surprised if MySQL didn't do fine on this. (You may have tried this and failed--as I said, I didn't try to read all of the earlier mail. But I'd be astonished if it weren't fast, though I suppose MySQL might try to do something fancy, knowing that the two queries are on the same table. In any event, check the two individual queries. If necessary, use a temporary table.) Then, you get to add your ORDER BY clause; presumably, it will still be fast. There was some mention in earlier mail of joins being faster than unions. That may be, but the difference should be too small to notice, unless different indexes are used. In your query above, with the inner join, MySQL is going to first consider the join and then consider the use of indexes for the where clause--so it ends up with the full table scan. HTH, Bill = original message follows = From: Fredrik Carlsson <[EMAIL PROTECTED]> To: Donny Simonton <[EMAIL PROTECTED]> CC: mysql@lists.mysql.com Subject: Re: Max connections being used every 10-12 day. I really appreciate your help :) I did some cleanup of my indexes(there are a couple of them left to clean out but it takes so long time): mysql> show index from art; +++--+--+-+- --+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +++--+--+-+- --+-+--++--++-+ | art | 0 | PRIMARY |1 | id | A | 542437 | NULL | NULL | | BTREE | | | art | 1 | date |1 | date| A | 542437 | NULL | NULL | YES | BTREE | | | art | 1 | lst |1 | lst| A | 216 | NULL | NULL | YES | BTREE | | | art | 1 | batch|1 | batch | A | 183 | NULL | NULL | YES | BTREE | | | art | 1 | batch|2 | lst| A |1802 | NULL | NULL | YES | BTREE | | | art | 1 | lst_3 |1 | lst| A | 216 | NULL | NULL | YES | BTREE | | | art | 1 | lst_3 |2 | parent | A | 90406 | NULL | NULL | YES | BTREE | | | art | 1 | lst_3 |3 | batch | A | 90406 | NULL | NULL | YES | BTREE | | | art | 1 | parent |1 | parent | A | 90406 | NULL | NULL | YES | BTREE | | | art | 1 | mid|1 | mid | A | 542437 | NULL | NULL | YES | BTREE | | | art | 1 | date_2 |1 | date| A | 542437 | NULL | NULL | YES | BTREE | | | art | 1 | subc |1 | subc | A | 54243 | NULL | NULL | YES | FULLTEXT | | | art | 1 |
Re: Max connections being used every 10-12 day.
I really appreciate your help :) I did some cleanup of my indexes(there are a couple of them left to clean out but it takes so long time): mysql> show index from art; +++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +++--+--+-+---+-+--++--++-+ | art | 0 | PRIMARY |1 | id | A | 542437 | NULL | NULL | | BTREE | | | art | 1 | date |1 | date| A | 542437 | NULL | NULL | YES | BTREE | | | art | 1 | lst |1 | lst| A | 216 | NULL | NULL | YES | BTREE | | | art | 1 | batch|1 | batch | A | 183 | NULL | NULL | YES | BTREE | | | art | 1 | batch|2 | lst| A |1802 | NULL | NULL | YES | BTREE | | | art | 1 | lst_3 |1 | lst| A | 216 | NULL | NULL | YES | BTREE | | | art | 1 | lst_3 |2 | parent | A | 90406 | NULL | NULL | YES | BTREE | | | art | 1 | lst_3 |3 | batch | A | 90406 | NULL | NULL | YES | BTREE | | | art | 1 | parent |1 | parent | A | 90406 | NULL | NULL | YES | BTREE | | | art | 1 | mid|1 | mid | A | 542437 | NULL | NULL | YES | BTREE | | | art | 1 | date_2 |1 | date| A | 542437 | NULL | NULL | YES | BTREE | | | art | 1 | subc |1 | subc | A | 54243 | NULL | NULL | YES | FULLTEXT | | | art | 1 | mf|1 | mf | A | 54243 | NULL | NULL | YES | FULLTEXT | | +++--+--+-+---+-+--++--++-+ 13 rows in set (0.00 sec) mysql> Select A.id, A.parent from art A inner join art B using (id) where A.id=560685 or B.parent=560685 order by A.date; +++ | id | parent | +++ | 560685 | 0 | | 560707 | 560685 | | 560714 | 560685 | | 560780 | 560685 | | 560783 | 560685 | | 560802 | 560685 | | 560810 | 560685 | | 560851 | 560685 | | 560855 | 560685 | | 561056 | 560685 | | 561104 | 560685 | +++ 11 rows in set (1 min 12.45 sec) mysql> explain Select A.id, A.parent from art A inner join art B using (id) where A.id=560685 or B.parent=560685 order by A.date; +---+++-+-+---+++ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+++-+-+---+++ | A | ALL| PRIMARY| NULL|NULL | NULL | 542437 | Using filesort | | B | eq_ref | PRIMARY,parent | PRIMARY | 4 | mail_archive.A.id | 1 | Using where| +---+++-+-+---+++ 2 rows in set (0.00 sec) // Fredrik Donny Simonton wrote: Frederik, I may be losing my mind, but I don't think I am according to your show index, you have multiple indexes on the same fields which is absolutely worthless and actually makes things slower. For example, id, which you have as primary should not have any other indexes on it, but with the explain you have PRIMARY,id,id_2,id_3 Get rid of id, id_2, and id_3. You need to do this for everything that you have duplicates of. Next according to your original query, the real query you should try and run should look like this: Select A.id, A.parent from art A inner join art B using (id) where A.id=60 or B.parent=60 order by A.date Donny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Max connections being used every 10-12 day.
There are some unnecessary indexes though. These include id_2 and 1st. This is because id_3 is an index on (id,parent,date) and id_2 is and index on (id,parent). The way mysql uses indexes, id_3 will work for (id), (id,parent), and (id,parent,date). The same is true of the '1st' index. This is covered by 1st_2 or 1st_3. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Tom Crimmins Sent: Monday, January 03, 2005 2:49 PM To: Donny Simonton; 'Fredrik Carlsson' Cc: mysql@lists.mysql.com Subject: RE: Max connections being used every 10-12 day. [snip] I may be losing my mind, but I don't think I am according to your show index, you have multiple indexes on the same fields which is absolutely worthless and actually makes things slower. For example, id, which you have as primary should not have any other indexes on it, but with the explain you have PRIMARY,id,id_2,id_3 [/snip] id_2 and id_3 are composite indexes. As the explain shows, mysql is using id_3 because it is the most specific to the query. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Donny Simonton Sent: Monday, January 03, 2005 1:12 PM To: 'Fredrik Carlsson' Cc: mysql@lists.mysql.com Subject: RE: Max connections being used every 10-12 day. Frederik, I may be losing my mind, but I don't think I am according to your show index, you have multiple indexes on the same fields which is absolutely worthless and actually makes things slower. For example, id, which you have as primary should not have any other indexes on it, but with the explain you have PRIMARY,id,id_2,id_3 Get rid of id, id_2, and id_3. You need to do this for everything that you have duplicates of. Next according to your original query, the real query you should try and run should look like this: Select A.id, A.parent from art A inner join art B using (id) where A.id=60 or B.parent=60 order by A.date Donny > -Original Message- > From: Fredrik Carlsson > Sent: Monday, January 03, 2005 11:08 AM > To: Donny Simonton > Cc: mysql@lists.mysql.com > Subject: Re: Max connections being used every 10-12 day. > > mysql> describe art; > +-+--+--+-+-++ > | Field | Type | Null | Key | Default | Extra | > +-+--+--+-+-++ > | id | int(11) | | PRI | NULL| auto_increment | > | parent | int(11) | YES | MUL | NULL|| > | bodyid | int(11) | YES | | NULL|| > | lst| varchar(80) | YES | MUL | NULL|| > | mf | varchar(80) | YES | | NULL|| > | mt | varchar(80) | YES | | NULL|| > | subc | varchar(200) | YES | MUL | NULL|| > | sdate | varchar(45) | YES | | NULL|| > | batch | varchar(80) | YES | MUL | NULL|| > | mgid | varchar(90) | YES | | NULL|| > | date| datetime | YES | MUL | NULL|| > +-+--+--+-+-++ > 11 rows in set (0.12 sec) > > > mysql> show index from art; > +++--+--+-+--- > +-+--++--++-+ > | Table | Non_unique | Key_name | Seq_in_index | Column_name | > Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment > | > +++--+--+-+--- > +-+--++--++-+ > | art | 0 | PRIMARY |1 | id | A > | 405011 | NULL | NULL | | BTREE | | > | art | 1 | id |1 | id | > A | 405011 | NULL | NULL | | BTREE | > | > | art | 1 | date |1 | date| > A | 405011 | NULL | NULL | YES | BTREE | > | > | art | 1 | lst |1 | lst| > A | 213 | NULL | NULL | YES | BTREE | > | > | art | 1 | id_2 |1 | id | > A | 405011 | NULL | NULL | | BTREE | > | > | art | 1 | id_2 |2 | parent | > A | 405011 | NULL | NULL | YES | BTREE | > | > | art | 1 | lst_2 |1 | lst| > A | 213 | NULL | NULL | YES | BTREE | > | > | art | 1 | lst_2 |
RE: Max connections being used every 10-12 day.
[snip] I may be losing my mind, but I don't think I am according to your show index, you have multiple indexes on the same fields which is absolutely worthless and actually makes things slower. For example, id, which you have as primary should not have any other indexes on it, but with the explain you have PRIMARY,id,id_2,id_3 [/snip] id_2 and id_3 are composite indexes. As the explain shows, mysql is using id_3 because it is the most specific to the query. --- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -Original Message- From: Donny Simonton Sent: Monday, January 03, 2005 1:12 PM To: 'Fredrik Carlsson' Cc: mysql@lists.mysql.com Subject: RE: Max connections being used every 10-12 day. Frederik, I may be losing my mind, but I don't think I am according to your show index, you have multiple indexes on the same fields which is absolutely worthless and actually makes things slower. For example, id, which you have as primary should not have any other indexes on it, but with the explain you have PRIMARY,id,id_2,id_3 Get rid of id, id_2, and id_3. You need to do this for everything that you have duplicates of. Next according to your original query, the real query you should try and run should look like this: Select A.id, A.parent from art A inner join art B using (id) where A.id=60 or B.parent=60 order by A.date Donny > -Original Message- > From: Fredrik Carlsson > Sent: Monday, January 03, 2005 11:08 AM > To: Donny Simonton > Cc: mysql@lists.mysql.com > Subject: Re: Max connections being used every 10-12 day. > > mysql> describe art; > +-+--+--+-+-++ > | Field | Type | Null | Key | Default | Extra | > +-+--+--+-+-++ > | id | int(11) | | PRI | NULL| auto_increment | > | parent | int(11) | YES | MUL | NULL|| > | bodyid | int(11) | YES | | NULL|| > | lst| varchar(80) | YES | MUL | NULL|| > | mf | varchar(80) | YES | | NULL|| > | mt | varchar(80) | YES | | NULL|| > | subc | varchar(200) | YES | MUL | NULL|| > | sdate | varchar(45) | YES | | NULL|| > | batch | varchar(80) | YES | MUL | NULL|| > | mgid | varchar(90) | YES | | NULL|| > | date| datetime | YES | MUL | NULL|| > +-+--+--+-+-++ > 11 rows in set (0.12 sec) > > > mysql> show index from art; > +++--+--+-+--- > +-+--++--++-+ > | Table | Non_unique | Key_name | Seq_in_index | Column_name | > Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment > | > +++--+--+-+--- > +-+--++--++-+ > | art | 0 | PRIMARY |1 | id | A > | 405011 | NULL | NULL | | BTREE | | > | art | 1 | id |1 | id | > A | 405011 | NULL | NULL | | BTREE | > | > | art | 1 | date |1 | date| > A | 405011 | NULL | NULL | YES | BTREE | > | > | art | 1 | lst |1 | lst| > A | 213 | NULL | NULL | YES | BTREE | > | > | art | 1 | id_2 |1 | id | > A | 405011 | NULL | NULL | | BTREE | > | > | art | 1 | id_2 |2 | parent | > A | 405011 | NULL | NULL | YES | BTREE | > | > | art | 1 | lst_2 |1 | lst| > A | 213 | NULL | NULL | YES | BTREE | > | > | art | 1 | lst_2 |2 | parent | > A | 57858 | NULL | NULL | YES | BTREE | > | > | art | 1 | lst_2 |3 | date| > A | 405011 | NULL | NULL | YES | BTREE | > | > | art | 1 | batch|1 | batch | > A | 141 | NULL | NULL | YES | BTREE | > | > | art | 1 | batch|2 | lst| > A |1177 | NULL | NULL | YES | BTREE | > | > | art | 1 | lst_3 |1 | lst
RE: Max connections being used every 10-12 day.
Frederik, I may be losing my mind, but I don't think I am according to your show index, you have multiple indexes on the same fields which is absolutely worthless and actually makes things slower. For example, id, which you have as primary should not have any other indexes on it, but with the explain you have PRIMARY,id,id_2,id_3 Get rid of id, id_2, and id_3. You need to do this for everything that you have duplicates of. Next according to your original query, the real query you should try and run should look like this: Select A.id, A.parent from art A inner join art B using (id) where A.id=60 or B.parent=60 order by A.date Donny > -Original Message- > From: Fredrik Carlsson [mailto:[EMAIL PROTECTED] > Sent: Monday, January 03, 2005 11:08 AM > To: Donny Simonton > Cc: mysql@lists.mysql.com > Subject: Re: Max connections being used every 10-12 day. > > mysql> describe art; > +-+--+--+-+-++ > | Field | Type | Null | Key | Default | Extra | > +-+--+--+-+-++ > | id | int(11) | | PRI | NULL| auto_increment | > | parent | int(11) | YES | MUL | NULL|| > | bodyid | int(11) | YES | | NULL|| > | lst| varchar(80) | YES | MUL | NULL|| > | mf | varchar(80) | YES | | NULL|| > | mt | varchar(80) | YES | | NULL|| > | subc | varchar(200) | YES | MUL | NULL|| > | sdate | varchar(45) | YES | | NULL|| > | batch | varchar(80) | YES | MUL | NULL|| > | mgid | varchar(90) | YES | | NULL|| > | date| datetime | YES | MUL | NULL|| > +-+--+--+-+-++ > 11 rows in set (0.12 sec) > > > mysql> show index from art; > +++--+--+-+--- > +-+--++--++-+ > | Table | Non_unique | Key_name | Seq_in_index | Column_name | > Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment > | > +++--+--+-+--- > +-+--++--++-+ > | art | 0 | PRIMARY |1 | id | A > | 405011 | NULL | NULL | | BTREE | | > | art | 1 | id |1 | id | > A | 405011 | NULL | NULL | | BTREE | > | > | art | 1 | date |1 | date| > A | 405011 | NULL | NULL | YES | BTREE | > | > | art | 1 | lst |1 | lst| > A | 213 | NULL | NULL | YES | BTREE | > | > | art | 1 | id_2 |1 | id | > A | 405011 | NULL | NULL | | BTREE | > | > | art | 1 | id_2 |2 | parent | > A | 405011 | NULL | NULL | YES | BTREE | > | > | art | 1 | lst_2 |1 | lst| > A | 213 | NULL | NULL | YES | BTREE | > | > | art | 1 | lst_2 |2 | parent | > A | 57858 | NULL | NULL | YES | BTREE | > | > | art | 1 | lst_2 |3 | date| > A | 405011 | NULL | NULL | YES | BTREE | > | > | art | 1 | batch|1 | batch | > A | 141 | NULL | NULL | YES | BTREE | > | > | art | 1 | batch|2 | lst| > A |1177 | NULL | NULL | YES | BTREE | > | > | art | 1 | lst_3 |1 | lst| > A | 213 | NULL | NULL | YES | BTREE | > | > | art | 1 | lst_3 |2 | parent | > A | 57858 | NULL | NULL | YES | BTREE | > | > | art | 1 | lst_3 |3 | batch | > A | 67501 | NULL | NULL | YES | BTREE | > | > | art | 1 | id_3 |1 | id | > A | 405011 | NULL | NULL | | BTREE | > | > | art | 1 | id_3 |2 | parent | > A | 405011 | NULL | NULL | YES | BTREE | > | > | art |
Re: Max connections being used every 10-12 day.
mysql> describe art; +-+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-++ | id | int(11) | | PRI | NULL| auto_increment | | parent | int(11) | YES | MUL | NULL|| | bodyid | int(11) | YES | | NULL|| | lst| varchar(80) | YES | MUL | NULL|| | mf | varchar(80) | YES | | NULL|| | mt | varchar(80) | YES | | NULL|| | subc | varchar(200) | YES | MUL | NULL|| | sdate | varchar(45) | YES | | NULL|| | batch | varchar(80) | YES | MUL | NULL|| | mgid | varchar(90) | YES | | NULL|| | date| datetime | YES | MUL | NULL|| +-+--+--+-+-++ 11 rows in set (0.12 sec) mysql> show index from art; +++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +++--+--+-+---+-+--++--++-+ | art | 0 | PRIMARY |1 | id | A | 405011 | NULL | NULL | | BTREE | | | art | 1 | id |1 | id | A | 405011 | NULL | NULL | | BTREE | | | art | 1 | date |1 | date| A | 405011 | NULL | NULL | YES | BTREE | | | art | 1 | lst |1 | lst| A | 213 | NULL | NULL | YES | BTREE | | | art | 1 | id_2 |1 | id | A | 405011 | NULL | NULL | | BTREE | | | art | 1 | id_2 |2 | parent | A | 405011 | NULL | NULL | YES | BTREE | | | art | 1 | lst_2 |1 | lst| A | 213 | NULL | NULL | YES | BTREE | | | art | 1 | lst_2 |2 | parent | A | 57858 | NULL | NULL | YES | BTREE | | | art | 1 | lst_2 |3 | date| A | 405011 | NULL | NULL | YES | BTREE | | | art | 1 | batch|1 | batch | A | 141 | NULL | NULL | YES | BTREE | | | art | 1 | batch|2 | lst| A |1177 | NULL | NULL | YES | BTREE | | | art | 1 | lst_3 |1 | lst| A | 213 | NULL | NULL | YES | BTREE | | | art | 1 | lst_3 |2 | parent | A | 57858 | NULL | NULL | YES | BTREE | | | art | 1 | lst_3 |3 | batch | A | 67501 | NULL | NULL | YES | BTREE | | | art | 1 | id_3 |1 | id | A | 405011 | NULL | NULL | | BTREE | | | art | 1 | id_3 |2 | parent | A | 405011 | NULL | NULL | YES | BTREE | | | art | 1 | id_3 |3 | date| A | 405011 | NULL | NULL | YES | BTREE | | | art | 1 | parent |1 | parent | A | 57858 | NULL | NULL | YES | BTREE | | | art | 1 | subc |1 | subc | A | 40501 | NULL | NULL | YES | FULLTEXT | | +++--+--+-+---+-+--++--++-+ 19 rows in set (0.04 sec) The query: Select A.id, A.parent, B.id, B.parent from art A inner join art B using (id) order by A.date; Would return several hundered thousend answers so i added a where option, i dont know if this was what you had in mind. The way i used your query it should be the same thing as: Select A.id, A.parent from art A where A.id=60 or A.parent=60 order by A.date; mysql> explain Select A.id, A.parent from art A inner join art B using (id) where A.id=60 or A.parent=60 order by A.date; +---++--+-+-+---
RE: Max connections being used every 10-12 day.
The inner join should ALWAYS return a faster result than the union, if you have the indexes correctly. Can you send me the explain of the inner join version and also the full table structure and indexes on the table? This should be fairly easy to solve. > -Original Message- > From: Fredrik Carlsson [mailto:[EMAIL PROTECTED] > Sent: Sunday, January 02, 2005 4:45 PM > To: mysql@lists.mysql.com > Subject: Re: Max connections being used every 10-12 day. > > The inner join statement returned the same stuff but it was not as fast > as the union is and the inner join seems to use more cpu resources. > > Could these union queries really be the problem behind my occasional > lock ups and that 200 connections being used? i mean the server is not > that loaded and the http logs show amazeingly low http traffic the night > of the lock up. > > I upgraded mysql to 4.0.22 and tuned down thread_concurency to 2 to se > if that helps. > > When these lock ups occures it dont seems like mysql is freeing the > connections, > for exampel the last time it happend was around 02:00 a couple of days > ago and when i checked the server 14 hours later (16:00) it still said > that max_connections was full and mysqld was idling using 0% of the CPU, > if the queries was queued up would'nt mysql at least show some activity? > > // Fredrik > > Donny Simonton wrote: > > >Frederick, > >What exactly are you trying to accomplish? Personally, I don't recommend > >using union unless absolutely necessary, since most people don't really > >understand when it should be used. And I think it shouldn't be used in > this > >case either. > > > >Select A.id, A.parent, B.id, B.parent > >from art A inner join art B using (id) > >order by A.date; > > > >See if that gives you the same results as the original query and then > >explain it to see if you get anything differently. > > > >Also what is the table structure including indexes of the table? > > > >Donny > > > > > > > > -- > 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]
Re: Max connections being used every 10-12 day.
The inner join statement returned the same stuff but it was not as fast as the union is and the inner join seems to use more cpu resources. Could these union queries really be the problem behind my occasional lock ups and that 200 connections being used? i mean the server is not that loaded and the http logs show amazeingly low http traffic the night of the lock up. I upgraded mysql to 4.0.22 and tuned down thread_concurency to 2 to se if that helps. When these lock ups occures it dont seems like mysql is freeing the connections, for exampel the last time it happend was around 02:00 a couple of days ago and when i checked the server 14 hours later (16:00) it still said that max_connections was full and mysqld was idling using 0% of the CPU, if the queries was queued up would'nt mysql at least show some activity? // Fredrik Donny Simonton wrote: Frederick, What exactly are you trying to accomplish? Personally, I don't recommend using union unless absolutely necessary, since most people don't really understand when it should be used. And I think it shouldn't be used in this case either. Select A.id, A.parent, B.id, B.parent from art A inner join art B using (id) order by A.date; See if that gives you the same results as the original query and then explain it to see if you get anything differently. Also what is the table structure including indexes of the table? Donny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Max connections being used every 10-12 day.
Frederick, What exactly are you trying to accomplish? Personally, I don't recommend using union unless absolutely necessary, since most people don't really understand when it should be used. And I think it shouldn't be used in this case either. Select A.id, A.parent, B.id, B.parent from art A inner join art B using (id) order by A.date; See if that gives you the same results as the original query and then explain it to see if you get anything differently. Also what is the table structure including indexes of the table? Donny > -Original Message- > From: Fredrik Carlsson [mailto:[EMAIL PROTECTED] > Sent: Sunday, January 02, 2005 5:18 AM > To: Donny Simonton > Cc: mysql@lists.mysql.com > Subject: Re: Max connections being used every 10-12 day. > > It is a single PIII 500MHz, so i just changed thread_concurrency to 2 > :), thanks > > The slow query log don't show that many slow queries, but they did show > alot of queries that was'nt using any index, can these queries cause > some kind of occasional lock up? > > Is there a faster way to perform this query? > (SELECT id,parent FROM art WHERE id=495098 ORDER BY date) UNION > (SELECT id,parent FROM art WHERE > parent=495098 ORDER BY date); > > explain show the following > > ++---+--+-+-+---+- > -+-+ > | table | type | possible_keys| key | key_len | ref > | rows | Extra | > ++---+--+-+-+---+- > -+-+ > | art | const | PRIMARY,id,id_2,id_3 | PRIMARY | 4 | const |1 > | | > | art | ref | parent | parent | 5 | const |2 > | Using where; Using filesort | > ++---+--+-+-+---+- > -+-+ > > // Fredrik Carlsson > > Donny Simonton wrote: > > >What kind of box is this? According to you're my.cnf it looks like it's > a > >either a dual with hyperthreading or a quad box. > > > >I don't see that you have your slow query log turned on, this should be > the > >first thing you should do in my opinion. This is what mine looks like. > > > >### Slow Query Information ### > >log-long-format > >log-slow-queries > >log-queries-not-using-indexes > >set-variable= long_query_time=3 > > > >Then go in and fix all of those that are showing up in the slow query > log. > > > >With 4-5 queries per second, you should NEVER fill up the 200 connections > >unless you just have some awful queries or you have some tables that are > >getting corrupted and are being repaired during that time. > > > >Donny > > > > > > > > > -- > 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]
Re: Max connections being used every 10-12 day.
Fredrik Carlsson wrote: It is a single PIII 500MHz, so i just changed thread_concurrency to 2 :), thanks The slow query log don't show that many slow queries, but they did show alot of queries that was'nt using any index, can these queries cause some kind of occasional lock up? Is there a faster way to perform this query? (SELECT id,parent FROM art WHERE id=495098 ORDER BY date) UNION (SELECT id,parent FROM art WHERE parent=495098 ORDER BY date); explain show the following ++---+--+-+-+---+--+-+ | table | type | possible_keys| key | key_len | ref | rows | Extra | ++---+--+-+-+---+--+-+ | art | const | PRIMARY,id,id_2,id_3 | PRIMARY | 4 | const | 1 | | | art | ref | parent | parent | 5 | const | 2 | Using where; Using filesort | ++---+--+-+-+---+--+-+ Have you tried: SELECT id,parent FROM ((SELECT id,parent FROM art WHERE id=495098) UNION (SELECT id,parent FROM art WHERE parent=495098)) ORDER BY date; You could also try adding an index on (parent, id, date) to speed up you second query. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max connections being used every 10-12 day.
It is a single PIII 500MHz, so i just changed thread_concurrency to 2 :), thanks The slow query log don't show that many slow queries, but they did show alot of queries that was'nt using any index, can these queries cause some kind of occasional lock up? Is there a faster way to perform this query? (SELECT id,parent FROM art WHERE id=495098 ORDER BY date) UNION (SELECT id,parent FROM art WHERE parent=495098 ORDER BY date); explain show the following ++---+--+-+-+---+--+-+ | table | type | possible_keys| key | key_len | ref | rows | Extra | ++---+--+-+-+---+--+-+ | art | const | PRIMARY,id,id_2,id_3 | PRIMARY | 4 | const |1 | | | art | ref | parent | parent | 5 | const |2 | Using where; Using filesort | ++---+--+-+-+---+--+-+ // Fredrik Carlsson Donny Simonton wrote: What kind of box is this? According to you're my.cnf it looks like it's a either a dual with hyperthreading or a quad box. I don't see that you have your slow query log turned on, this should be the first thing you should do in my opinion. This is what mine looks like. ### Slow Query Information ### log-long-format log-slow-queries log-queries-not-using-indexes set-variable= long_query_time=3 Then go in and fix all of those that are showing up in the slow query log. With 4-5 queries per second, you should NEVER fill up the 200 connections unless you just have some awful queries or you have some tables that are getting corrupted and are being repaired during that time. Donny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Max connections being used every 10-12 day.
What kind of box is this? According to you're my.cnf it looks like it's a either a dual with hyperthreading or a quad box. I don't see that you have your slow query log turned on, this should be the first thing you should do in my opinion. This is what mine looks like. ### Slow Query Information ### log-long-format log-slow-queries log-queries-not-using-indexes set-variable= long_query_time=3 Then go in and fix all of those that are showing up in the slow query log. With 4-5 queries per second, you should NEVER fill up the 200 connections unless you just have some awful queries or you have some tables that are getting corrupted and are being repaired during that time. Donny > -Original Message- > From: Fredrik Carlsson [mailto:[EMAIL PROTECTED] > Sent: Saturday, January 01, 2005 4:37 AM > To: mysql@lists.mysql.com > Subject: Max connections being used every 10-12 day. > > Hi list, > > I have a rather serious problem that i really dont know how to solve, > > Every 8-12 day my mysql server stops responding and i get the error code > that indicates that max_connections are full, the problem is that i have > checked all of > my code over and over again to se that every connections are closed > properly and they are. No persistent connections are being used and the > max connections error allways occures at night 02:00->03:00, my httpd > logs shows no unnormal amount of traffic at the time. > > The last time this happend i tuned the wait_timeout down to 15 seconds > to se if that helped, but no effect :( > > The server is running NetBSD 1.6.2 and mysql 4.0.21 > > I really need help on this one because i dont know what is causing > max_connections to be used all at once or how to reproduce the error, i > only know that it happens very periodicly and 'show full processlist' > hardly ever shows any connections not even the day/hours before the > error. The server has about 4-5 queries / seconds. > > According to the manual the max_connections have one connection reserved > for the superuser but i have never been able to use that extra > connection to se which user that is eating upp all the connections. > > > // Fredrik Carlsson > > > # The MySQL server > [mysqld] > port= 3306 > socket= /tmp/mysql.sock > skip-locking > key_buffer = 280M > max_allowed_packet = 32M > table_cache = 512 > sort_buffer_size = 2M > read_buffer_size = 2M > myisam_sort_buffer_size = 64M > thread_cache = 8 > query_cache_size = 64M > # Try number of CPU's*2 for thread_concurrency > thread_concurrency = 8 > max_connections = 200 > wait_timeout = 15 > connect_timeout = 5 > > [mysqldump] > quick > max_allowed_packet = 16M > > [mysql] > no-auto-rehash > # Remove the next comment character if you are not familiar with SQL > #safe-updates > > [isamchk] > key_buffer = 256M > sort_buffer_size = 256M > read_buffer = 2M > write_buffer = 2M > > [myisamchk] > key_buffer = 256M > sort_buffer_size = 256M > read_buffer = 2M > write_buffer = 2M > > [mysqlhotcopy] > interactive-timeout > > > > > > -- > 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]
Re: Max connections being used every 10-12 day.
mysql has about 50GB of temp space to work with so thats not the problem. Its strange because the server is not that loaded and around 12-04 at night no cronjobs that affects mysql or general server performance are being run. Is is possible to se how many queued up questions mysql has at the moment? Peter Lovatt wrote: Hi there a are a couple of things that I have found cause occasional lock ups. running out of temp space - MySql builds temp files on bigger queries and if it runs out of temp disk space it grinds to a halt, which causes all the following queries to queue up until max_connections is exceeded. the second cause is one massive query - big tables, lots of joins etc - that takes so much processing power that there is nothing left. MySql and perhaps the server slows down to the point that it cannot process any more queries, and again the queue builds until max_connections is exceeded. if the lockup occurs overnight this might be the time the server is doing housekeeping - apache log analysis for example - so it may be the server is loaded too heavily to manage MySql queries too, so again the queue builds up. Just a few thoughts - hope it helps. Peter -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Max connections being used every 10-12 day.
Hi there a are a couple of things that I have found cause occasional lock ups. running out of temp space - MySql builds temp files on bigger queries and if it runs out of temp disk space it grinds to a halt, which causes all the following queries to queue up until max_connections is exceeded. the second cause is one massive query - big tables, lots of joins etc - that takes so much processing power that there is nothing left. MySql and perhaps the server slows down to the point that it cannot process any more queries, and again the queue builds until max_connections is exceeded. if the lockup occurs overnight this might be the time the server is doing housekeeping - apache log analysis for example - so it may be the server is loaded too heavily to manage MySql queries too, so again the queue builds up. Just a few thoughts - hope it helps. Peter > -Original Message- > From: Fredrik Carlsson [mailto:[EMAIL PROTECTED] > Sent: 01 January 2005 10:37 > To: mysql@lists.mysql.com > Subject: Max connections being used every 10-12 day. > > > Hi list, > > I have a rather serious problem that i really dont know how to solve, > > Every 8-12 day my mysql server stops responding and i get the error code > that indicates that max_connections are full, the problem is that i have > checked all of > my code over and over again to se that every connections are closed > properly and they are. No persistent connections are being used and the > max connections error allways occures at night 02:00->03:00, my httpd > logs shows no unnormal amount of traffic at the time. > > The last time this happend i tuned the wait_timeout down to 15 seconds > to se if that helped, but no effect :( > > The server is running NetBSD 1.6.2 and mysql 4.0.21 > > I really need help on this one because i dont know what is causing > max_connections to be used all at once or how to reproduce the error, i > only know that it happens very periodicly and 'show full processlist' > hardly ever shows any connections not even the day/hours before the > error. The server has about 4-5 queries / seconds. > > According to the manual the max_connections have one connection reserved > for the superuser but i have never been able to use that extra > connection to se which user that is eating upp all the connections. > > > // Fredrik Carlsson > > > # The MySQL server > [mysqld] > port= 3306 > socket= /tmp/mysql.sock > skip-locking > key_buffer = 280M > max_allowed_packet = 32M > table_cache = 512 > sort_buffer_size = 2M > read_buffer_size = 2M > myisam_sort_buffer_size = 64M > thread_cache = 8 > query_cache_size = 64M > # Try number of CPU's*2 for thread_concurrency > thread_concurrency = 8 > max_connections = 200 > wait_timeout = 15 > connect_timeout = 5 > > [mysqldump] > quick > max_allowed_packet = 16M > > [mysql] > no-auto-rehash > # Remove the next comment character if you are not familiar with SQL > #safe-updates > > [isamchk] > key_buffer = 256M > sort_buffer_size = 256M > read_buffer = 2M > write_buffer = 2M > > [myisamchk] > key_buffer = 256M > sort_buffer_size = 256M > read_buffer = 2M > write_buffer = 2M > > [mysqlhotcopy] > interactive-timeout > > > > > > -- > 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]
Max connections being used every 10-12 day.
Hi list, I have a rather serious problem that i really dont know how to solve, Every 8-12 day my mysql server stops responding and i get the error code that indicates that max_connections are full, the problem is that i have checked all of my code over and over again to se that every connections are closed properly and they are. No persistent connections are being used and the max connections error allways occures at night 02:00->03:00, my httpd logs shows no unnormal amount of traffic at the time. The last time this happend i tuned the wait_timeout down to 15 seconds to se if that helped, but no effect :( The server is running NetBSD 1.6.2 and mysql 4.0.21 I really need help on this one because i dont know what is causing max_connections to be used all at once or how to reproduce the error, i only know that it happens very periodicly and 'show full processlist' hardly ever shows any connections not even the day/hours before the error. The server has about 4-5 queries / seconds. According to the manual the max_connections have one connection reserved for the superuser but i have never been able to use that extra connection to se which user that is eating upp all the connections. // Fredrik Carlsson # The MySQL server [mysqld] port= 3306 socket= /tmp/mysql.sock skip-locking key_buffer = 280M max_allowed_packet = 32M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 64M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 max_connections = 200 wait_timeout = 15 connect_timeout = 5 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
