MySQL Forums :: General :: Query with > 500000 entities in in where clause crashes server


Advanced Search

Query with > 500000 entities in in where clause crashes server
Posted by: Jens Bijell ()
Date: June 10, 2010 05:50AM

Hello Dear MySQL Users,

since around 6 weeks we have a user building reports which uses over 500000 entities in the where clause in his query adressing only one keytable. We executed it while watching the systems. Immediately after starting it theThis query is obviously starving out the server. The only way out is then to reboot the whole server. The Server has 2x Quad-Core Intel XeonProcessor X5355 , 16 GB RAM. No errors in demon log nor in os log. No core dumps. The os server limits:
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 139264
max locked memory (kbytes, -l) 32
max memory size (kbytes, -m) unlimited
open files (-n) 1024
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 10240
cpu time (seconds, -t) unlimited
max user processes (-u) unlimited
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited


I really would be happy if someone could give me some useful feedback on the following questions:
1.) Do we hit potential mysql server limits?
2.) Where can I read/find out about mysql server limits?
3.) What alternatives do we have here with regards to syntax? Would using temporary table(s) storing referenced entities for joins be an alternative?

I know that there should be some inserts to reproduce and so on posted but the table contains 735803633 rows though it is primary a resource issue in my opinion.
I'am trying to give info here which should help understanding the problem more closely.

Problem: select crashes server

select WebsiteId, CustomerId, CampaignId, PlacementId from NetworkData a
where
# 185800 Entities
a.PlacementId in (333964,333961,140719,... 345942,333967 )
# 374000 Entities
and a.CampaignId in (509578,509576,509569,509566,...,509598,304301 )
and a.CustomerId = 13393
#2700 entities
and a.PlacementId in (1047968,1047969,...3,919424,919425,986459 )
and a.CampaignId > 0
and ( a.Date >= 20100430 and a.Date <= 20100430)
and a.NetworkPlacementId = 5111
and a.SubNetworkPlacementId = 1
group by WebsiteId, CustomerId, CampaignId, PlacementId
;


Table: NetworkData
+-----------------------+---------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------------+---------------------+------+-----+---------+-------+
| Date | int(11) | NO | PRI | 0 | |
| NetworkPlacementId | smallint(6) | NO | MUL | 0 | |
| SubNetworkPlacementId | tinyint(4) unsigned | NO | | 0 | |
| WebSiteId | int(11) | NO | MUL | 0 | |
| PlacementId | int(11) | NO | PRI | 0 | |
| CustomerId | int(11) | NO | MUL | 0 | |
| MasterCampaignId | int(11) | NO | | 0 | |
| CampaignId | int(11) | NO | PRI | 0 | |
| BannerId | mediumint(6) | NO | PRI | 0 | |
| BannerNumber | int(11) | NO | | 0 | |
| Impressions | bigint(20) | NO | | 0 | |
| Clicks | bigint(20) | NO | | 0 | |
| Views | bigint(20) | NO | | 0 | |
+-----------------------+---------------------+------+-----+---------+-------+
13 rows in set (0.03 sec)


CREATE TABLE `NetworkData` (
`Date` int(11) NOT NULL default '0',
`NetworkPlacementId` smallint(6) NOT NULL default '0',
`SubNetworkPlacementId` tinyint(4) unsigned NOT NULL default '0',
`WebSiteId` int(11) NOT NULL default '0',
`PlacementId` int(11) NOT NULL default '0',
`CustomerId` int(11) NOT NULL default '0',
`MasterCampaignId` int(11) NOT NULL default '0',
`CampaignId` int(11) NOT NULL default '0',
`BannerId` mediumint(6) NOT NULL default '0',
`BannerNumber` int(11) NOT NULL default '0',
`Impressions` bigint(20) NOT NULL default '0',
`Clicks` bigint(20) NOT NULL default '0',
`Views` bigint(20) NOT NULL default '0',
PRIMARY KEY (`Date`,`CampaignId`,`BannerId`,`PlacementId`),
KEY `IDX_CustomerId` (`CustomerId`),
KEY `IDX_WebSiteId` (`WebSiteId`),
KEY `IDX_PlacementId` (`PlacementId`),
KEY `CampaignId_Date` (`CampaignId`,`Date`),
KEY `NetworkPlacement_Date` (`NetworkPlacementId`,`SubNetworkPlacementId`,`Date`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
;

Any suggestion or opinion appreciated.

Best Regards,
Jens

Options: ReplyQuote


Subject Written By Posted
Query with > 500000 entities in in where clause crashes server Jens Bijell 06/10/2010 05:50AM
Re: Query with > 500000 entities in in where clause crashes server Rick James 06/11/2010 08:53AM
Re: Query with > 500000 entities in in where clause crashes server Jens Bijell 06/14/2010 09:07AM
Re: Query with > 500000 entities in in where clause crashes server Rick James 06/14/2010 08:02PM
Re: Query with > 500000 entities in in where clause crashes server Jens Bijell 06/17/2010 05:43AM
Re: Query with > 500000 entities in in where clause crashes server Rick James 06/17/2010 10:19AM


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.