Re: Urgent - MySQL 5 - mysqld using a lot of memory consistently
Hi, Here, threads_connected is considerable and below the preset value. The threads_connected and threads_running are the good indicators to see how loaded the server is. In your case it is good numbers. So use 'iostat'/relavant utility to monitor the DB activity. Also threads_created is more, which should be low. so to average it increase the thread_cache size to some 64 or more. Ref: http://www.mysql.com/news-and-events/newsletter/2004-01/a000301.html Thanks ViSolve DB Team - Original Message - From: "Ratheesh K J" <[EMAIL PROTECTED]> To: Sent: Tuesday, January 23, 2007 11:59 AM Subject: Urgent - MySQL 5 - mysqld using a lot of memory consistently Hello all, Recently we upgraded from MySQL 4.1.11 to MySQL 5.0.22. The queries are taking a lot of time to execute in the newer version. The queries which were executing within 10 secs are now taking more than 100 secs. Running an expalin on the queries showed that an index_merge optimization is being used which is a new concept in MySQL 5. My initial doubt was on this but now when I checked top it shows that mysqld is consistently using 59% of Memory and 25% of cpu even when there is no load. the SHOW STATUS command in mysql shows: Threads_created21863 Threads_cached1 Threads_connected38 Connections5784350 Running a SHOW VARIABLES shows: thread_cache_size8 It is evident that mysqld is creating a lots of threads... Could this be the problem? Thanks, Ratheesh K J -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Urgent - MySQL 5 - mysqld using a lot of memory consistently
CLOSED` (`FLD_ISSUE_CLOSED`), KEY `FLD_REPLY_FLAG` (`FLD_REPLY_FLAG`), KEY `FLD_SUBJECT` (`FLD_SUBJECT`), KEY `FLD_WORKFLOW_TYPE` (`FLD_WORKFLOW_TYPE`), KEY `FLD_PRODUCT_ID` (`FLD_PRODUCT_ID`), KEY `FLD_SUB_CONTACT_ID` (`FLD_SUB_CONTACT_ID`), KEY `FLD_ESCALATED_FLAG` (`FLD_ESCALATED_FLAG`), KEY `FLD_BOUNCED_MAIL_FLAG` (`FLD_BOUNCED_MAIL_FLAG`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 - Original Message - From: Alex Arul To: Ratheesh K J Cc: mysql@lists.mysql.com Sent: Tuesday, January 23, 2007 12:20 PM Subject: Re: Urgent - MySQL 5 - mysqld using a lot of memory consistently the monitor the threads-connected variable over a period of time and calcuate the value. you can even look at max used connections status variable and allot accordingly. BTW, is you application using demand based connections or connection pooling ? If it is using connection pooling then bumping thread-cache might not help. Please provide create table statement of the tables in question also. Thanks Alex On 1/23/07, Ratheesh K J <[EMAIL PROTECTED]> wrote: Thanks, By how much should I be increasing the thread_cache? currently it is 8... Currently I can provide the EXPLAIN result of a query using index_merge on MySQL 5.0.22. EXPLAIN SELECT IFNULL(TAEM.FLD_FULL_NAME, TFMM.FLD_ASSIGNED_TO) AS ELE1 , TFMM.FLD_ASSIGNED_TO AS KEY_ID, SUM(1) AS ELE2, SUM( IF(TFMM.FLD_ESCALATED_FLAG= 0 , 1 , 0) ) AS ELE3, SUM( IF(TFMM.FLD_ESCALATED_FLAG = 0 AND ( TFMM.FLD_ISSUE_CLOSED IN ( 2, 4,5,1 ) ) , 1, 0) ) AS ELE4, SUM( IF(TFMM.FLD_ESCALATED_FLAG = 0 AND TFMM.FLD_ISSUE_CLOSED = 3, 1, 0) ) AS ELE5, SUM( IF(TFMM.FLD_ESCALATED_FLAG = 1 , 1, 0) ) AS ELE6,SUM( IF(TFMM.FLD_ESCALATED_FLAG = 1 AND ( TFMM.FLD_ISSUE_CLOSED IN ( 2, 4,5,1 ) ) , 1, 0) ) AS ELE7, SUM( IF(TFMM.FLD_ESCALATED_FLAG = 1 AND TFMM.FLD_ISSUE_CLOSED = 3, 1, 0) ) AS ELE8, ROUND(( SUM( IF(TFMM.FLD_ESCALATED_FLAG = 1 , 1, 0) )/SUM(1)) * 100 ,2 ) AS ELE9 FROM TBL_FORUMS_MSG_MAIN TFMM LEFT JOIN TBL_ADMIN_EMP_MASTER TAEM ON TAEM.FLD_EMP_ID = TFMM.FLD_ASSIGNED_TO INNER JOIN TBL_FORUMS_MSG_OP_TRACK TFMOT ON ( TFMM.FLD_MSG_ID=TFMOT.FLD_MSG_ID AND TFMOT.FLD_OP_ID=15 AND TFMOT.FLD_LAST_FLAG=1 ) WHERE TFMM.FLD_ACC_ID IN ( 6, 375 ) AND TFMM.FLD_MARK_AS_DELETED = 0 AND TFMM.FLD_BOUNCED_MAIL_FLAG = 0 AND TFMM.FLD_BLOCK_STATE = 0 AND TFMM.FLD_PARENT_ID = 0 AND TFMM.FLD_ASSIGNED_TO IN ( 935,805,563,543,1352,670,571,530,655,577,355,885,392,155,1579,693,1577,509,199,770,1535,78,54,993,594,557,132,859,99,1557,645,527,79,181,1520,200,1350,1534,1591,545,70,191,1550,189,726,40,228,97,196,860,303,1321,394,363,1412,597,1013,1377,1250,1299,3,301,756,170,1553,1578,1343,953,593,250,600,1552,494,311,146,664,589,631,495,4,1254,678,511,931,1020,410,592,822,933,1531,1507,858,453,1257,555,897,352,188,546,1544,291,1529,370,765,963,356,1303,1328,354,414,1581,1030,382,1356,1521,227,396,1333,591,1249,760,1334,1034,51,80,1276,794,145,295,934,544,165,1594,886,929,558,685,880,831,1592,882,320,566,174,796,1593,5,1361,1522,435,388,951,1362,369,806,20,1336,330,77,907,754,507,1330,1364,1,202,1501,289,1296,1378,1061,1500,952,1439,1369,1358,373,1548,294,338,30,1351,1575,728,207,1558,406,837,210,970,620,387,450,1586,38,1227,460,455,1347,841,386,318,130,492,961,590,229,463,284,1380,1580,422,362,1337,581,1490,568,950,1083,960,1329,825,532,404,936,1251,552,1089,1585,1225,708,1564,817,260,372,965,305,456,847,192,1465,962,1523,1590,745,180,1540,753,585,890,1537,1099,225,750,230,7,413,1554,578,572,820,1549,883,810,1105,1403,423,1524,969,542,286,797,1582,1301,384,930,308,854,742,1107,1108,1555,1338,1562,947,673,1506,417,236,798,1576,63,27,1210,371,1485,82,272,1274,529,1277,381,1342,689,185,1118,235,1120,37,598,724,205,946,203,608,405,610,19,958,126,307,967,1360,218,954,1525,891,116,135,75,715,1547,431,1138,879,1498,211,1140,1463,6,1528,344,956,595,91,826,1145,1584,1545,1258,443,643,632,1526,267,1530,58,945,314,1470,763,1491,1595,968,385,955,282,684,179,178,666,409,663,390,1447,1341,1546,1587,125,358,173,279,957,50,182,840,107,580,807,133,1248,892,690,513,898,365,821,325,669,121,62,827,106,219,1253,633,41,562,1489,162,101,861,561,839,153,1565,1583,395,447,217,1551,888,1574,1176,743,446,556,787,263,949,1178,1331,209,134,1505,1354,55,306,31,964,1348,850,1252,862,966,100,521,175,709,1542,942,1335,297,445,296,411,525,420,266,102,1559,418,438,109,661,804,662,1543,984,1556,1409,522,195,1471,439,341,1209,878,838,1464,881,271,36,83,1379,857,944,656,959,538,2,764,1588,672,520,503,531,462,729,528,204,201,93,677,564,426,606,855,234,676,889) AND TFMOT.FLD_OP_DATE_TIME BETWEEN '2007-01-19 00:00:00' AND '2007-01-23 23:59:59' AND TFMM.FLD_MEDIUM IN ( 1 ) AND TFMM.FLD_MSG_ID > 0 GROUP BY IFNULL(TAEM.FLD_FULL_NAME, TFMM.FLD_ASSIGNED_TO) ORDER BY IFNULL(TAEM.FLD_FULL_NAME, TFMM.FLD_ASSIGNED_TO): *** row 1 *** table: TFMM type: index_merge possible_keys: PRIMARY,FLD_MEDIUM,FLD_PAR
Re: Urgent - MySQL 5 - mysqld using a lot of memory consistently
type: ref possible_keys: FLD_MSG_ID,FLD_OP_ID,FLD_OP_DATE_TIME,FLD_LAST_FLAG key: FLD_MSG_ID key_len: 4 ref: tallydb.TFMM.FLD_MSG_ID rows: 1 Extra: Using where - Original Message - *From:* Alex Arul <[EMAIL PROTECTED]> *To:* Ratheesh K J <[EMAIL PROTECTED]> *Sent:* Tuesday, January 23, 2007 11:57 AM *Subject:* Re: Urgent - MySQL 5 - mysqld using a lot of memory consistently you threads connected is 38 but your thread cache has only 8. So please do bump it up. Also generate explain plan on both versions of mysql and provide create table statement of the tables and the query. FYI, index_merge_optimization is used when more than one index can be used for execution. Thanks Alex On 1/23/07, Ratheesh K J <[EMAIL PROTECTED]> wrote: > > Hello all, > > Recently we upgraded from MySQL 4.1.11 to MySQL 5.0.22. The queries are > taking a lot of time to execute in the newer version. The queries which were > executing within 10 secs are now taking more than 100 secs. > > Running an expalin on the queries showed that an index_merge > optimization is being used which is a new concept in MySQL 5. My initial > doubt was on this but now when I checked top it shows that mysqld is > consistently using 59% of Memory and 25% of cpu even when there is no load. > > the SHOW STATUS command in mysql shows: > > Threads_created21863 > Threads_cached1 > Threads_connected38 > Connections5784350 > > > Running a SHOW VARIABLES shows: > > thread_cache_size8 > > It is evident that mysqld is creating a lots of threads... Could this be > the problem? > > Thanks, > > Ratheesh K J >
Re: Urgent - MySQL 5 - mysqld using a lot of memory consistently
Thanks, By how much should I be increasing the thread_cache? currently it is 8... Currently I can provide the EXPLAIN result of a query using index_merge on MySQL 5.0.22. EXPLAIN SELECT IFNULL(TAEM.FLD_FULL_NAME, TFMM.FLD_ASSIGNED_TO) AS ELE1 , TFMM.FLD_ASSIGNED_TO AS KEY_ID, SUM(1) AS ELE2, SUM( IF(TFMM.FLD_ESCALATED_FLAG= 0 , 1 , 0) ) AS ELE3, SUM( IF(TFMM.FLD_ESCALATED_FLAG = 0 AND ( TFMM.FLD_ISSUE_CLOSED IN ( 2, 4,5,1 ) ) , 1, 0) ) AS ELE4, SUM( IF(TFMM.FLD_ESCALATED_FLAG = 0 AND TFMM.FLD_ISSUE_CLOSED = 3, 1, 0) ) AS ELE5, SUM( IF(TFMM.FLD_ESCALATED_FLAG = 1 , 1, 0) ) AS ELE6,SUM( IF(TFMM.FLD_ESCALATED_FLAG = 1 AND ( TFMM.FLD_ISSUE_CLOSED IN ( 2, 4,5,1 ) ) , 1, 0) ) AS ELE7, SUM( IF(TFMM.FLD_ESCALATED_FLAG = 1 AND TFMM.FLD_ISSUE_CLOSED = 3, 1, 0) ) AS ELE8, ROUND(( SUM( IF(TFMM.FLD_ESCALATED_FLAG = 1 , 1, 0) )/SUM(1)) * 100 ,2 ) AS ELE9 FROM TBL_FORUMS_MSG_MAIN TFMM LEFT JOIN TBL_ADMIN_EMP_MASTER TAEM ON TAEM.FLD_EMP_ID = TFMM.FLD_ASSIGNED_TO INNER JOIN TBL_FORUMS_MSG_OP_TRACK TFMOT ON ( TFMM.FLD_MSG_ID=TFMOT.FLD_MSG_ID AND TFMOT.FLD_OP_ID=15 AND TFMOT.FLD_LAST_FLAG=1 ) WHERE TFMM.FLD_ACC_ID IN ( 6, 375 ) AND TFMM.FLD_MARK_AS_DELETED = 0 AND TFMM.FLD_BOUNCED_MAIL_FLAG = 0 AND TFMM.FLD_BLOCK_STATE = 0 AND TFMM.FLD_PARENT_ID = 0 AND TFMM.FLD_ASSIGNED_TO IN ( 935,805,563,543,1352,670,571,530,655,577,355,885,392,155,1579,693,1577,509,199,770,1535,78,54,993,594,557,132,859,99,1557,645,527,79,181,1520,200,1350,1534,1591,545,70,191,1550,189,726,40,228,97,196,860,303,1321,394,363,1412,597,1013,1377,1250,1299,3,301,756,170,1553,1578,1343,953,593,250,600,1552,494,311,146,664,589,631,495,4,1254,678,511,931,1020,410,592,822,933,1531,1507,858,453,1257,555,897,352,188,546,1544,291,1529,370,765,963,356,1303,1328,354,414,1581,1030,382,1356,1521,227,396,1333,591,1249,760,1334,1034,51,80,1276,794,145,295,934,544,165,1594,886,929,558,685,880,831,1592,882,320,566,174,796,1593,5,1361,1522,435,388,951,1362,369,806,20,1336,330,77,907,754,507,1330,1364,1,202,1501,289,1296,1378,1061,1500,952,1439,1369,1358,373,1548,294,338,30,1351,1575,728,207,1558,406,837,210,970,620,387,450,1586,38,1227,460,455,1347,841,386,318,130,492,961,590,229,463,284,1380,1580,422,362,1337,581,1490,568,950,1083,960,1329,825,532,404,936,1251,552,1089,1585,1225,708,1564,817,260,372,965,305,456,847,192,1465,962,1523,1590,745,180,1540,753,585,890,1537,1099,225,750,230,7,413,1554,578,572,820,1549,883,810,1105,1403,423,1524,969,542,286,797,1582,1301,384,930,308,854,742,1107,1108,1555,1338,1562,947,673,1506,417,236,798,1576,63,27,1210,371,1485,82,272,1274,529,1277,381,1342,689,185,1118,235,1120,37,598,724,205,946,203,608,405,610,19,958,126,307,967,1360,218,954,1525,891,116,135,75,715,1547,431,1138,879,1498,211,1140,1463,6,1528,344,956,595,91,826,1145,1584,1545,1258,443,643,632,1526,267,1530,58,945,314,1470,763,1491,1595,968,385,955,282,684,179,178,666,409,663,390,1447,1341,1546,1587,125,358,173,279,957,50,182,840,107,580,807,133,1248,892,690,513,898,365,821,325,669,121,62,827,106,219,1253,633,41,562,1489,162,101,861,561,839,153,1565,1583,395,447,217,1551,888,1574,1176,743,446,556,787,263,949,1178,1331,209,134,1505,1354,55,306,31,964,1348,850,1252,862,966,100,521,175,709,1542,942,1335,297,445,296,411,525,420,266,102,1559,418,438,109,661,804,662,1543,984,1556,1409,522,195,1471,439,341,1209,878,838,1464,881,271,36,83,1379,857,944,656,959,538,2,764,1588,672,520,503,531,462,729,528,204,201,93,677,564,426,606,855,234,676,889) AND TFMOT.FLD_OP_DATE_TIME BETWEEN '2007-01-19 00:00:00' AND '2007-01-23 23:59:59' AND TFMM.FLD_MEDIUM IN ( 1 ) AND TFMM.FLD_MSG_ID > 0 GROUP BY IFNULL(TAEM.FLD_FULL_NAME, TFMM.FLD_ASSIGNED_TO) ORDER BY IFNULL(TAEM.FLD_FULL_NAME, TFMM.FLD_ASSIGNED_TO): *** row 1 *** table: TFMM type: index_merge possible_keys: PRIMARY,FLD_MEDIUM,FLD_PARENT_ID,FLD_ASSIGNED_TO,FLD_MARK_AS_DELETED,FLD_ACC_ID,FLD_BLOCK_STATE,FLD_BOUNCED_MAIL_FLAG key: FLD_BLOCK_STATE,FLD_MEDIUM,FLD_MARK_AS_DELETED,FLD_BOUNCED_MAIL_FLAG,FLD_PARENT_ID key_len: 2,1,2,2,4 ref: NULL rows: 34468 Extra: Using intersect(FLD_BLOCK_STATE,FLD_MEDIUM,FLD_MARK_AS_DELETED,FLD_BOUNCED_MAIL_FLAG,FLD_PARENT_ID); Using where; Using temporary; Using filesort *** row 2 *** table: TAEM type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: tallydb.TFMM.FLD_ASSIGNED_TO rows: 1 Extra: NULL *** row 3 *** table: TFMOT type: ref possible_keys: FLD_MSG_ID,FLD_OP_ID,FLD_OP_DATE_TIME,FLD_LAST_FLAG key: FLD_MSG_ID key_len: 4 ref: tallydb.TFMM.FLD_MSG_ID rows: 1 Extra: Using where - Original Message - From: Alex Arul To: Ratheesh K J Sent: Tuesday, January 23, 2007 11:57 AM Subject: Re: Urgent - MySQL 5 - mysqld using a lot of memory consistently you threads connected is 38 bu
