Thanks for the improved query.The indexing didn't help much and still the
main problem is it locking all updates to the tables while it executes...
even if I am executing it on a copy of the tables in a different database
--
Dave
2008/11/27 Chandru <[EMAIL PROTECTED]>
> Hi David,
> please create index on games_sessions_levels table on the column startTime
> and the query can be rewritten as
> "gSL.starttime between unix_timestamp('2008-11-26') and
> unix_timestamp('2008-11-26') and gSL.endTime > 0"
>
> gSL.startTime > 0 is invalid since already the value that you verify is
> between unix_timestamp('2008-11-26') and unix_timestamp('2008-11-26')
>
>
> gSL.starttime > unix_timestamp('2008-11-26')
> AND gSL.startTime > 0 AND gSL.endTime > 0
> AND gSL.starttime < unix_timestamp('2008-11-26')
>
> Regards,
> Chandru
> www.mafiree.com
> On Thu, Nov 27, 2008 at 5:50 PM, David Scott <
> [EMAIL PROTECTED]> wrote:
>
>> Ah yes, sorry, here it is:
>> SELECT gS.gameid, g.gamename, COUNT(DISTINCT(gS.sessionid)) 'sessions',
>> SUM(gSL.endTime - gSL.startTime)/COUNT(DISTINCT(gS.sessionid))/60 'average
>> SESSION time',
>> SUM(gSL.totalTime)/COUNT(DISTINCT(gS.sessionid))/60 'average PLAY time',
>> SUM(gSL.totalTime)/60
>> FROM databaseX.games_sessions_levels gSL
>> JOIN databaseX.games_sessions gS ON gS.sessionid = gSL.sessionid
>> JOIN databaseX.games g ON g.id = gS.gameid
>> WHERE gSL.starttime > unix_timestamp('2008-11-26')
>> AND gSL.startTime > 0 AND gSL.endTime > 0
>> AND gSL.starttime < unix_timestamp('2008-11-27')
>> GROUP BY gS.gameid
>> ORDER BY SUM(gSL.totalTime) DESC
>>
>>
>>
>> CREATE TABLE `databaseX `.`Xgames_sessions` (
>> `sessionid` int(99) NOT NULL auto_increment,
>> `playerid` varchar(32) NOT NULL,
>> `gameid` int(99) NOT NULL,
>> `starttime` int(20) NOT NULL,
>> `zone` varchar(255) NOT NULL,
>> `host` varchar(255) NOT NULL,
>> `loadref` varchar(50) NOT NULL,
>> PRIMARY KEY (`sessionid`)
>> ) ENGINE=InnoDB AUTO_INCREMENT=604907 DEFAULT CHARSET=latin1
>> ROW_FORMAT=DYNAMIC;
>>
>>
>> CREATE TABLE `databaseX`.`Xgames_sessions_levels` (
>> `id` int(99) NOT NULL auto_increment,
>> `sessionid` int(99) NOT NULL,
>> `levelnumber` int(99) NOT NULL,
>> `levelname` varchar(50) default NULL,
>> `starttime` int(20) NOT NULL,
>> `endtime` int(20) NOT NULL,
>> `totaltime` int(20) NOT NULL default '0',
>> `info` int(11) NOT NULL,
>> `score` int(99) NOT NULL,
>> `done` tinyint(1) NOT NULL default '0',
>> `zone` varchar(50) NOT NULL,
>> PRIMARY KEY (`id`)
>> ) ENGINE=InnoDB AUTO_INCREMENT=4213995 DEFAULT CHARSET=latin1
>> ROW_FORMAT=DYNAMIC;
>>
>> --
>> Dave
>>
>> 2008/11/27 Chandru <[EMAIL PROTECTED]>
>>
>>> Hai david,
>>> without seeing the query i cant comment if the index that is created
>>> is efficient or not. Please send the query if you need more help.
>>>
>>> Regards,
>>> Chandru
>>> www.mafiree.com
>>>
>>> On Thu, Nov 27, 2008 at 5:36 PM, David Scott <
>>> [EMAIL PROTECTED]> wrote:
>>>
>>>> We only indexed the id on the table as it has many many updates and very
>>>> rarely do we select, we assumed this would be more efficient? --
>>>> Dave
>>>>
>>>> 2008/11/27 Chandru <[EMAIL PROTECTED]>
>>>>
>>>> Hi David,
>>>>> I find that the query is going for a full table scan. i think u need
>>>>> to optimize the query. Can you please send the original query and also the
>>>>> output of " show index from <tablename>;"
>>>>>
>>>>> Regards,
>>>>> Chandru.
>>>>> www.mafiree.com
>>>>>
>>>>> On Thu, Nov 27, 2008 at 5:18 PM, David Scott <
>>>>> [EMAIL PROTECTED]> wrote:
>>>>>
>>>>>> We are using innodb.
>>>>>> EXPLAIN on the select:
>>>>>> 1, 'SIMPLE', 'gSL', 'ALL', '', '', '', '', 4210688, 'Using where;
>>>>>> Using temporary; Using filesort'
>>>>>> 1, 'SIMPLE', 'gS', 'eq_ref', 'PRIMARY', 'PRIMARY', '4',
>>>>>> 'databaseX.gSL.sessionid', 1, ''
>>>>>> 1, 'SIMPLE', 'g', 'eq_ref', 'PRIMARY', 'PRIMARY', '4',
>>>>>> 'databaseX.gS.gameid', 1, ''
>>>>>>
>>>>>> We are baffled by how doing it on one DB can lock updates in another
>>>>>> --
>>>>>> Dave
>>>>>>
>>>>>>
>>>>>> 2008/11/27 Pradeep Chandru <[EMAIL PROTECTED]>
>>>>>>
>>>>>> Hi David,
>>>>>>> can you please let me know what is the select query and the update
>>>>>>> query along with the explain plan of the same.
>>>>>>> can you please let me know if you are using innodb storage engine?
>>>>>>>
>>>>>>> Regards,
>>>>>>> Chandru
>>>>>>> www.mafiree.com
>>>>>>>
>>>>>>> On Thu, Nov 27, 2008 at 4:45 PM, David Scott <
>>>>>>> [EMAIL PROTECTED]> wrote:
>>>>>>>
>>>>>>>> show full processlist
>>>>>>>> userX is the user the site is using to connect
>>>>>>>> databaseX is the database in question
>>>>>>>>
>>>>>>>> 1976156, 'userX', 'localhost', 'databaseX', 'Sleep', 13, '', ''
>>>>>>>> 13508974, 'dave', 'IPX:29212', 'databaseX', 'Sleep', 0, '', ''
>>>>>>>> 13759139, 'sen', '1 IPX:32775', '', 'Sleep', 160, '', ''
>>>>>>>> 13775621, 'sen', 'IPX:43603', 'databaseX', 'Sleep', 0, '', ''
>>>>>>>> 13821806, 'dave', 'IPX:55885', '', 'Query', 0, '', 'show full
>>>>>>>> processlist'
>>>>>>>> 13821878, 'userX', 'localhost', 'databaseX', 'Sleep', 0, '', ''
>>>>>>>>
>>>>>>>> Show innodb status:
>>>>>>>>
>>>>>>>> '
>>>>>>>> =====================================
>>>>>>>> 081127 11:12:38 INNODB MONITOR OUTPUT
>>>>>>>> =====================================
>>>>>>>> Per second averages calculated from the last 1 seconds
>>>>>>>> ----------
>>>>>>>> SEMAPHORES
>>>>>>>> ----------
>>>>>>>> OS WAIT ARRAY INFO: reservation count 164975, signal count 155476
>>>>>>>> Mutex spin waits 0, rounds 7441650, OS waits 120688
>>>>>>>> RW-shared spins 37873, OS waits 17328; RW-excl spins 24776, OS waits
>>>>>>>> 4966
>>>>>>>> ------------
>>>>>>>> TRANSACTIONS
>>>>>>>> ------------
>>>>>>>> Trx id counter 0 25118320
>>>>>>>> Purge done for trx's n:o < 0 25118124 undo n:o < 0 0
>>>>>>>> History list length 89
>>>>>>>> Total number of lock structs in row lock hash table 0
>>>>>>>> LIST OF TRANSACTIONS FOR EACH SESSION:
>>>>>>>> ---TRANSACTION 0 0, not started, process no 19166, OS thread id
>>>>>>>> 1096026448
>>>>>>>> MySQL thread id 13833472, query id 57762790 IPX dave
>>>>>>>> SHOW INNODB STATUS
>>>>>>>> ---TRANSACTION 0 25063765, not started, process no 19166, OS thread
>>>>>>>> id
>>>>>>>> 1182529872
>>>>>>>> MySQL thread id 13508974, query id 57762327 IPX dave
>>>>>>>> ---TRANSACTION 0 0, not started, process no 19166, OS thread id
>>>>>>>> 1176140112
>>>>>>>> MySQL thread id 13775621, query id 57762659 IPX sen
>>>>>>>> ---TRANSACTION 0 0, not started, process no 19166, OS thread id
>>>>>>>> 1181997392
>>>>>>>> MySQL thread id 13759139, query id 57665031 IPX sen
>>>>>>>> ---TRANSACTION 0 25117242, not started, process no 19166, OS thread
>>>>>>>> id
>>>>>>>> 1188387152
>>>>>>>> MySQL thread id 1976156, query id 57760643 localhost beta_cc
>>>>>>>> --------
>>>>>>>> FILE I/O
>>>>>>>> --------
>>>>>>>> I/O thread 0 state: waiting for i/o request (insert buffer thread)
>>>>>>>> I/O thread 1 state: waiting for i/o request (log thread)
>>>>>>>> I/O thread 2 state: waiting for i/o request (read thread)
>>>>>>>> I/O thread 3 state: waiting for i/o request (write thread)
>>>>>>>> Pending normal aio reads: 0, aio writes: 0,
>>>>>>>> ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
>>>>>>>> Pending flushes (fsync) log: 0; buffer pool: 0
>>>>>>>> 28 OS file reads, 12527564 OS file writes, 12361532 OS fsyncs
>>>>>>>> 0.00 reads/s, 0 avg bytes/read, 79.92 writes/s, 79.92 fsyncs/s
>>>>>>>> -------------------------------------
>>>>>>>> INSERT BUFFER AND ADAPTIVE HASH INDEX
>>>>>>>> -------------------------------------
>>>>>>>> Ibuf: size 1, free list len 0, seg size 2,
>>>>>>>> 0 inserts, 0 merged recs, 0 merges
>>>>>>>> Hash table size 2310107, used cells 513846, node heap has 859
>>>>>>>> buffer(s)
>>>>>>>> 34.97 hash searches/s, 99.90 non-hash searches/s
>>>>>>>> ---
>>>>>>>> LOG
>>>>>>>> ---
>>>>>>>> Log sequence number 1 501773721
>>>>>>>> Log flushed up to 1 501773721
>>>>>>>> Last checkpoint at 1 500074343
>>>>>>>> 0 pending log writes, 0 pending chkp writes
>>>>>>>> 12329746 log i/o's done, 79.92 log i/o's/second
>>>>>>>> ----------------------
>>>>>>>> BUFFER POOL AND MEMORY
>>>>>>>> ----------------------
>>>>>>>> Total memory allocated 1201497898; in additional pool allocated
>>>>>>>> 558592
>>>>>>>> Buffer pool size 64000
>>>>>>>> Free buffers 3328
>>>>>>>> Database pages 59813
>>>>>>>> Modified db pages 253
>>>>>>>> Pending reads 0
>>>>>>>> Pending writes: LRU 0, flush list 0, single page 0
>>>>>>>> Pages read 65, created 59748, written 573841
>>>>>>>> 0.00 reads/s, 0.00 creates/s, 0.00 writes/s
>>>>>>>> Buffer pool hit rate 1000 / 1000
>>>>>>>> --------------
>>>>>>>> ROW OPERATIONS
>>>>>>>> --------------
>>>>>>>> 0 queries inside InnoDB, 0 queries in queue
>>>>>>>> 1 read views open inside InnoDB
>>>>>>>> Main thread process no. 19166, id 1171347792, state: sleeping
>>>>>>>> Number of rows inserted 17853779, updated 10095603, deleted 18, read
>>>>>>>> 948444635
>>>>>>>> 13.99 inserts/s, 65.93 updates/s, 0.00 deletes/s, 98.90 reads/s
>>>>>>>> ----------------------------
>>>>>>>> END OF INNODB MONITOR OUTPUT
>>>>>>>> ============================
>>>>>>>> '
>>>>>>>>
>>>>>>>>
>>>>>>>> I hope that helps--
>>>>>>>> Dave
>>>>>>>>
>>>>>>>>
>>>>>>>> 2008/11/27 Ananda Kumar <[EMAIL PROTECTED]>
>>>>>>>>
>>>>>>>> > can u please do "show full processlist" when the update is
>>>>>>>> happening, and
>>>>>>>> > if its innodb
>>>>>>>> >
>>>>>>>> > please do "SHOW INNODB STATUS", which will give complete activity
>>>>>>>> on innodb
>>>>>>>> > engine, including lock information.
>>>>>>>> >
>>>>>>>> > Please show use the output of these.
>>>>>>>> >
>>>>>>>> > regards
>>>>>>>> > anandkl
>>>>>>>> >
>>>>>>>> >
>>>>>>>> > On 11/27/08, David Scott <[EMAIL PROTECTED]> wrote:
>>>>>>>> >>
>>>>>>>> >> Hi list.We have 2 tables, both have a few inserts, many updates
>>>>>>>> and the
>>>>>>>> >> occasional select.
>>>>>>>> >> When running a select joining the 2 tables (which can take upto
>>>>>>>> 20 seconds
>>>>>>>> >> to complete, they are large tables) all updates are blocked and
>>>>>>>> the
>>>>>>>> >> maxconnections is quickly reached.
>>>>>>>> >>
>>>>>>>> >> We tried copying the data to a 2nd database (in the same MySQL
>>>>>>>> install) to
>>>>>>>> >> run the select on that but for some reason that still prevents
>>>>>>>> the updates
>>>>>>>> >> on the original database, we watch the connections and we see
>>>>>>>> them build
>>>>>>>> >> up,
>>>>>>>> >> when the select finishes they quickly clear.
>>>>>>>> >>
>>>>>>>> >> My question is how can we prevent this backing up of updates when
>>>>>>>> running
>>>>>>>> >> a
>>>>>>>> >> select and why would doing a select on one database cause
>>>>>>>> connections on
>>>>>>>> >> another to back up?
>>>>>>>> >>
>>>>>>>> >> Thanks
>>>>>>>> >> --
>>>>>>>> >> David
>>>>>>>> >>
>>>>>>>> >
>>>>>>>> >
>>>>>>>>
>>>>>>>
>>>>>>>
>>>>>>
>>>>>
>>>>
>>>
>>
>
