Here is the CREATE TABLE Statement for the table on which the index_merge is
being applied. There will be atleast 10 queries always running on this table
with an index_merge optimization.
We have the max_connections variable set to 100. Also repeatedly checking the
Threads_Connected status variable shows varying b/w 16 to 40..
So I guess increasing the thread_cahce_size from 8 to 40 will help... Or shld
it be even less?
CREATE TABLE
CREATE TABLE `TBL_FORUMS_MSG_MAIN` (
`FLD_MSG_ID` int(10) unsigned NOT NULL default '0',
`FLD_ACC_ID` smallint(5) unsigned NOT NULL default '0',
`FLD_DOMAIN_ID` tinyint(4) NOT NULL default '0',
`FLD_TICKET_NUM` varchar(32) NOT NULL default '',
`FLD_ADD_BY` int(10) unsigned NOT NULL default '0',
`FLD_ADD_DATE_TIME` datetime NOT NULL default '0000-00-00 00:00:00',
`FLD_UPDATE_BY` int(10) unsigned default NULL,
`FLD_UPDATE_DATE_TIME` datetime default NULL,
`FLD_MSG_DATE_TIME` datetime default '0000-00-00 00:00:00',
`FLD_THREAD_ID` int(10) unsigned NOT NULL default '0',
`FLD_PARENT_ID` int(10) unsigned NOT NULL default '0',
`FLD_TREE_POS` int(10) unsigned NOT NULL default '0',
`FLD_TREE_LEVEL` int(10) unsigned NOT NULL default '0',
`FLD_RESTORE_THREAD_ID` int(10) unsigned default '0',
`FLD_WORKFLOW_TYPE` tinyint(1) unsigned NOT NULL default '0',
`FLD_MEDIUM` tinyint(1) unsigned NOT NULL default '0',
`FLD_DIRECTION` tinyint(3) unsigned NOT NULL default '0',
`FLD_ISSUE_TYPE` tinyint(3) unsigned NOT NULL default '0',
`FLD_ZONE` int(11) unsigned default '0',
`FLD_COMPANY_ID` int(11) unsigned default '0',
`FLD_PRODUCT_ID` tinyint(4) unsigned default '0',
`FLD_ASSIGNED_TO` int(10) unsigned default '0',
`FLD_MSG_TYPE` tinyint(3) unsigned default '0',
`FLD_MSG_INFO_ONLY_STATE` tinyint(1) unsigned default '0',
`FLD_ATTACHMENT_FLAG` tinyint(1) unsigned default '0',
`FLD_COUNTRY_TYPE` tinyint(1) unsigned default '0',
`FLD_NO_SUPPORT_FLAG` tinyint(1) unsigned default '0',
`FLD_CONTACT_PID` int(9) unsigned zerofill default NULL,
`FLD_SUB_CONTACT_ID` int(10) unsigned default NULL,
`FLD_BLOCK_STATE` tinyint(1) unsigned default '0',
`FLD_MARK_AS_DELETED` tinyint(1) unsigned default '0',
`FLD_SEEN_FLAG` tinyint(1) unsigned default '0',
`FLD_REPLY_FLAG` tinyint(1) unsigned default '0',
`FLD_FWD_FLAG` tinyint(1) unsigned default '0',
`FLD_USR_SEEN_FLAG` tinyint(1) unsigned default '0',
`FLD_USR_REPLY_FLAG` tinyint(1) unsigned default '0',
`FLD_USR_FWD_FLAG` tinyint(1) unsigned default '0',
`FLD_SUBM_OP_ID` tinyint(1) unsigned default '0',
`FLD_ISSUE_CLOSED` tinyint(1) unsigned NOT NULL default '0',
`FLD_TASK_STATE` tinyint(1) unsigned default '0',
`FLD_ESCALATED_FLAG` tinyint(1) unsigned default '0',
`FLD_BOUNCED_MAIL_FLAG` tinyint(1) unsigned default '0',
`FLD_LEAD_ID` int(11) unsigned default '0',
`FLD_BUG_ID` int(11) unsigned default '0',
`FLD_EMAIL_IP_ADDRESS` varchar(15) default NULL,
`FLD_EMAIL_FROM` varchar(150) default NULL,
`FLD_EMAIL_TO` text,
`FLD_EMAIL_CC_TO` text,
`FLD_EMAIL_BCC_TO` text,
`FLD_SUBJECT` varchar(100) default NULL,
`FLD_PRIORITY` tinyint(1) unsigned default NULL,
`FLD_TELCALL_FROMTO_NAME` varchar(128) default NULL,
`FLD_TELCALL_FROMTO_DESC` varchar(64) default NULL,
`FLD_CHAT_FROM_NAME` varchar(32) default NULL,
`FLD_CHAT_FROM_DESC` varchar(64) default NULL,
`FLD_CHAT_START_DATE_TIME` datetime default '0000-00-00 00:00:00',
`FLD_CHAT_END_DATE_TIME` datetime default '0000-00-00 00:00:00',
`FLD_CHAT_SESSION_ID` int(11) default '0',
`FLD_CSS_INTERACTION_TYPE` tinyint(1) unsigned default '0',
`FLD_CSS_ISSUE_CATEGORY` tinyint(1) unsigned default '0',
`FLD_CSS_ISSUE_TAT_QTY` tinyint(1) unsigned default '0',
`FLD_CSS_ISSUE_TAT_UNIT` varchar(6) default NULL,
`FLD_CSS_ISSUE_SLA_QTY` tinyint(1) unsigned default '0',
`FLD_CSS_ISSUE_SLA_UNIT` varchar(6) default NULL,
PRIMARY KEY (`FLD_MSG_ID`),
KEY `TREE_POS` (`FLD_TREE_POS`),
KEY `FLD_MEDIUM` (`FLD_MEDIUM`),
KEY `FLD_MSG_TYPE` (`FLD_MSG_TYPE`),
KEY `FLD_PARENT_ID` (`FLD_PARENT_ID`),
KEY `FLD_ADD_DATE_TIME` (`FLD_ADD_DATE_TIME`),
KEY `FLD_CONTACT_PID` (`FLD_CONTACT_PID`),
KEY `FLD_ASSIGNED_TO` (`FLD_ASSIGNED_TO`),
KEY `FLD_THREAD_ID` (`FLD_THREAD_ID`),
KEY `FLD_EMAIL_FROM` (`FLD_EMAIL_FROM`),
KEY `FLD_TICKET_NUM` (`FLD_TICKET_NUM`),
KEY `FLD_MARK_AS_DELETED` (`FLD_MARK_AS_DELETED`),
KEY `FLD_ACC_ID` (`FLD_ACC_ID`),
KEY `FLD_BLOCK_STATE` (`FLD_BLOCK_STATE`),
KEY `FLD_FWD_FLAG` (`FLD_FWD_FLAG`),
KEY `FLD_ISSUE_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_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 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_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
