Query with > 500000 entities in in where clause crashes server
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