MySQL Forums
Forum List  »  General

Re: Query with > 500000 entities in in where clause crashes server
Posted by: Rick James
Date: June 11, 2010 08:53AM

open files (-n) 1024 -- This could be raised (probably did not cause the issue at hand)

1.) Do we hit potential mysql server limits? -- well, could you show us the MySQL limits you have; especially
SHOW VARIABLES LIKE '%buffer%';
SHOW VARIABLES LIKE '%size';

This index should help:
INDEX(CustomerId, NetworkPlacementId, SubNetworkPlacementId, Date, PlacementId)

And this could possibly be removed (since the above index handles the case):
KEY `IDX_CustomerId` (`CustomerId`),

and ( a.Date >= 20100430 and a.Date <= 20100430)
-->
and ( a.Date = 20100430 )
so that my suggested index will work better. You can do that by conditionally building that part of the WHERE clause in your programming language.

group by WebsiteId, CustomerId, CampaignId, PlacementId -- Why do you have this? You don't have any aggregates; you could use DISTINCT instead; you don't have an ORDER BY.

`PlacementId` int(11) NOT NULL default '0', -- are there potentially 2 billion "placements"? (Or do I not understand what a placement is?) Consider a TINYINT UNSIGNED (1 byte, max of 255) or SMALLINT UNSIGNED (2 bytes, max of 65K) instead of a 4-byte INT.

"only way out is then to reboot the whole server" -- No. You could connect via the commandline 'mysql', do SHOW PROCESSLIST, spot the 'pid', then execute 'kill' with that pid.

Other info that would help:
* SHOW TABLE STATUS LIKE 'NetworkData'\G -- size
* EXPLAIN SELECT ...\G -- clues of inefficiencies
and surround them with [ code ] and [ / code ]

Are you running 64-bit OS? 64-bit MySQL?

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.