We are trying with IEE trail If it work for us we are ready to buy but we are facing issue “IEE mysql hangs when more than 2 users access web application at the same time”  , We have table with 89 million data , server configuration quad core with 16 GB RAM. 

Is there any configuration changes needs to done for concurrent user access ?

Thanks
Raja

Hi !

A couple of question to clarify more your problem:
- What is the MainHeapSize parameter is brighthouse.ini?
- What other application apart from ICE are running on the server?
- “Hangs” - does the server become simply slow or it gets stuck forever?

Also for clarification,  does the process seem to hold and wait for one query to complete before allowing another user’s query to start?  ICE is not the ideal database for a multi user web application.  IEE is becuase it is designed to support multiple users with multiple connections and concurrency.  You should try IEE, http://www.infobright.com/

We are trying with IEE only, Is there any settings that needs to be done for Multiple user/ Connections in Web Application.

We found there sub query is killing the server , Multiple SELECT in Same query kills server , tried with join not working , Is there any way to re-write the queries in infobright.

SELECT COUNT(1) AS FAMILYTREECOUNT FROM WORLDBASE WHERE GLOBALULTDUNSNO = (SELECT GLOBALULTDUNSNO FROM WORLDBASE WHERE DUNSNO = 600546675 AND GLOBALULTDUNSNO > 0)

SELECT COUNT(1) AS FIELDCOUNT FROM WORLDBASE HD WHERE 1=1 AND (HD.WORLDREGIONCODE IN (1,2,3,5,4,6)) AND (HD.USMETROPOLITANAREACODE IN (40,160,560,680,720,743,760,870,920,1010,4150,4280,6520,6560,6600,6640,6690,6820)) AND (HD.SLS >= 1 ) AND (HD.SIC1DIGIT IN (7,8,9)  ) AND (HD.INTELLECTID IN (SELECT HDKEYS.INTELLECTID FROM INTELLECTKEYNUMBERS HDKEYS WHERE HDKEYS.NETINCOME >= 1)) AND HD.INTELLECTID > 0

Questions:

When you say killing the server?  What exactly does this mean?  Does it throttle the CPU until it returns or does it stop the mysql service all together?  Have you set your ControlMessages = 5 in the brighthouse.ini file?

If so, please run these 2 queries again and send in the bh.err file.

Please find the attached bh.err ,  In the attached file search string “Some pointers may be invalid” you will see all the error messages for subquries

Waiting for your suggestion to fix this issue.

Thanks
Raja

mysqld uses 100% CPU and memory and server hangs after some time it releases some time we need to kill and restart the mysqld service

As per my findings/little knowledge,  when ever there is select query inside IN , it uses normal mysql execution path and it takes lot time to execute , There a option or way to re-write the query in InfoBright.

SELECT COUNT(1) AS FIELDCOUNT FROM WORLDBASE HD WHERE 1=1 AND HD.LOCATIONTYPECODE IN (1,2,0) AND (HD.INTELLECTID IN (SELECT HDSPPLS.INTELLECTID FROM INTELLECTOFFICERS HDSPPLS WHERE HDSPPLS.SALARY >= 5)) AND HD.INTELLECTID > 0

1. Set ControlMessages=5—this will give us more information in the bh.err log file
2. IN Statements shouldn’t cause hanging to occur.  This is a valid and often used operation
3. Nothing given thus far raises any alarms or concerns for IEE.  It seems fairly straightforward.
4. In the bh.err log file, it does give a peculiar error on any new query you execute; however, I’d need to review the full DDL.

It looks as if some of the queries get thrown to the MySQL optimizer.

Hi !

You have said:

quad core with 16 GB RAM.

I see in the logs:

It looks you tell the server to use 20GB on a 16GB machine.

Try

The proper number depends on what other software is running on the server.

Having an incorrect parameter inside of the brighthouse.ini will cause some unexpected results.  Can you please reset them , stop & restart the service then try and run the process again please.