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 <ratheesh.kj@tallysolutions.com> 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,3 92,155,1579,693,1577,509,199,770,1535,78,54,993,59 4,557,132,859,99,1557,645,527,79,181,1520,200,1350 ,1534,1591,545,70,191,1550,189,726,40,228,97,196,8 60,303,1321,394,363,1412,597,1013,1377,1250,1299,3 ,301,756,170,1553,1578,1343,953,593,250,600,1552,4 94,311,146,664,589,631,495,4,1254,678,511,931,1020 ,410,592,822,933,1531,1507,858,453,1257,555,897,35 2,188,546,1544,291,1529,370,765,963,356,1303,1328, 354,414,1581,1030,382,1356,1521,227,396,1333,591,1 249,760,1334,1034,51,80,1276,794,145,295,934,544,1 65,1594,886,929,558,685,880,831,1592,882,320,566,1 74,796,1593,5,1361,1522,435,388,951,1362,369,806,2 0,1336,330,77,907,754,507,1330,1364,1,202,1501,289 ,1296,1378,1061,1500,952,1439,1369,1358,373,1548,2 94,338,30,1351,1575,728,207,1558,406,837,210,970,6 20,387,450,1586,38,1227,460,455,1347,841,386,318,1 30,492,961,590,229,463,284,1380,1580,422,362,1337, 581,1490,568,950,1083,960,1329,825,532,404,936,125 1,552,1089,1585,1225,708,1564,817,260,372,965,305, 456,847,192,1465,962,1523,1590,745,180,1540,753,58 5,890,1537,1099,225,750,230,7,413,1554,578,572,820 ,1549,883,810,1105,1403,423,1524,969,542,286,797,1 582,1301,384,930,308,854,742,1107,1108,1555,1338,1 562,947,673,1506,417,236,798,1576,63,27,1210,371,1 485,82,272,1274,529,1277,381,1342,689,185,1118,235 ,1120,37,598,724,205,946,203,608,405,610,19,958,12 6,307,967,1360,218,954,1525,891,116,135,75,715,154 7,431,1138,879,1498,211,1140,1463,6,1528,344,956,5 95,91,826,1145,1584,1545,1258,443,643,632,1526,267 ,1530,58,945,314,1470,763,1491,1595,968,385,955,28 2,684,179,178,666,409,663,390,1447,1341,1546,1587, 125,358,173,279,957,50,182,840,107,580,807,133,124 8,892,690,513,898,365,821,325,669,121,62,827,106,2 19,1253,633,41,562,1489,162,101,861,561,839,153,15 65,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,94 2,1335,297,445,296,411,525,420,266,102,1559,418,43 8,109,661,804,662,1543,984,1556,1409,522,195,1471, 439,341,1209,878,838,1464,881,271,36,83,1379,857,9 44,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,F LD_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_D ELETED,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_FLA G
> key: FLD_MSG_ID
> key_len: 4
> ref: tallydb.TFMM.FLD_MSG_ID
> rows: 1
> Extra: Using where
>
> ----- Original Message -----
> *From:* Alex Arul <alex.lurthu@gmail.com>
> *To:* Ratheesh K J <ratheesh.kj@tallysolutions.com>
> *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 <ratheesh.kj@tallysolutions.com> 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 whichwere
> > 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_created 21863
> > Threads_cached 1
> > Threads_connected 38
> > Connections 5784350
> >
> >
> > Running a SHOW VARIABLES shows:
> >
> > thread_cache_size 8
> >
> > It is evident that mysqld is creating a lots of threads... Could this be
> > the problem?
> >
> > Thanks,
> >
> > Ratheesh K J
> >
>
>