Joinutility seperatorLogin utility separator Infobright.com
   
 
Crash on multithreaded / multicolumn with OR operator queries
 
Jay Ox
Posted: 08 July 2010 12:00 PM   [ Ignore ]  
Newbie
Rank
Total Posts:  3
Joined  2009-11-11

Hello,

We’re running Infobright with default settings for a 16G server (10000, 1000, 800).
Our queries are always like this

INSERT INTO memorytable{$i} SELECT commonfieldID, field0 FROM table{$i}_1 INNER JOIN table{$i}_2 ON commonfieldID WHERE field1 AND field2 AND field3 AND (field4 OR field5 OR field6)

- {$i} is 1 to 8
- table{$i}_1 and table{$i}_2 have the same number of rows and joined on a unique id
- the tables have about 3 million rows each.

We run all the 8 queries at the same time.

The problem is that for high number of hits (>1 million) for field4, field5, field6 (the ones in the group with OR operator) the server crashes.

I’ve checked bh.err and the memory size that the engine tries to allocate is available on the server. We’ve tried adjusting the settings in /etc/my-ib.cnf but with no success.

Can you tell me if we’re doing something wrong here?

Thanks,
Justin

[ Edited: 08 July 2010 12:09 PM by Jay Ox]
Profile
 
Janusz Borkowski
Posted: 09 July 2010 05:43 AM   [ Ignore ]   [ # 1 ]  
Super Duper Member
Avatar
RankRankRankRankRank
Total Posts:  914
Joined  2008-08-18

Hi !

Do the queries complete successfully if run sequentially?
Can you try watching the memory usage by IB (mysqld process) during the queries using e.g. “top” ? How much memory does it use as reported by “top”

Profile
 
Jay Ox
Posted: 12 July 2010 08:03 AM   [ Ignore ]   [ # 2 ]  
Newbie
Rank
Total Posts:  3
Joined  2009-11-11

I’ve tried running the queries sequentially but i got the same error.
“HY000 2013 Lost connection to MySQL server during query”.

This is the strace log for mysqld during the query:

Process 23087 attached - interrupt to quit
select(6, [4 5], NULL, NULL, NULL)      = 1 (in [4])
fcntl(4, F_SETFL, O_RDWR|O_NONBLOCK)    = 0
accept(4, {sa_family=AF_INET, sin_port=htons(46060), sin_addr=inet_addr("127.0.0.1")}, [16]) = 22
fcntl(4, F_SETFL, O_RDWR)               = 0
getsockname(22, {sa_family=AF_INET, sin_port=htons(5029), sin_addr=inet_addr("127.0.0.1")}, [16]) = 0
fcntl(22, F_SETFL, O_RDONLY)            = 0
fcntl(22, F_GETFL)                      = 0x2 (flags O_RDWR)
fcntl(22, F_SETFL, O_RDWR|O_NONBLOCK)   = 0
setsockopt(22, SOL_IP, IP_TOS, [8], 4)  = 0
setsockopt(22, SOL_TCP, TCP_NODELAY, [1], 4) = 0
mmap(NULL, 266240, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS|MAP_STACK, -1, 0) = 0x7fbae17a0000
mprotect(0x7fbae17a0000, 4096, PROT_NONE) = 0
clone(child_stack=0x7fbae17e00c0, flags=CLONE_VM|CLONE_FS|CLONE_FILES|CLONE_SIGHAND|CLONE_THREAD|CLONE_SYSVSEM|CLONE_SETTLS|CLONE_PARENT_SETTID|CLONE_CHILD_CLEARTID, parent_tidptr=0x7fbae17e09e0, tls=0x7fbae17e0950, child_tidptr=0x7fbae17e09e0) = 23278
select(6, [4 5], NULL, NULL, NULLPANIC: attached pid 23087 exited with 1
 <unfinished ... exit status 1> 

Any ideas?

Thanks.

Profile
 
Jay Ox
Posted: 15 July 2010 08:02 AM   [ Ignore ]   [ # 3 ]  
Newbie
Rank
Total Posts:  3
Joined  2009-11-11

Hello,

I found out what was wrong with the queries.
I was not writing correct the expressions:
- i was writing only flagA instead of flagA > 0 (or flagA IS NOT NULL)
- i didn’t get an SQL error so I supposed the query was correct.

Thanks,
Jay

Profile
 
Piotr Synak
Posted: 15 July 2010 08:43 AM   [ Ignore ]   [ # 4 ]  
Member
Avatar
RankRankRank
Total Posts:  191
Joined  2008-08-18

Hi,

‘flagA’ should be automatically converted to ‘flagA <> 0’, assuming it is not a string column.

Thanks,
Piotr

Profile
 
   
 
 
‹‹ unexpected segfault      outfile to mount folder can not read... ››