MySQL Bugs: #56485: Big querys block the data base

Bug #56485	Big querys block the data base
Submitted:	2 Sep 2010 8:31	Modified:	25 Jul 2011 6:43
Reporter:	rdfd fsdfsdf	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.1.30-community	OS:	Windows (XP and VISTA)
Assigned to:		CPU Architecture:	Any

Description:
I have developped a real time software that gets data from some external devices and inserts them into the database. At the same moment we have a window where these data are shown and refreshed every second. The rate of insert is about 400 rows per second and every second one select is performed with 400 rows retrieved.

This mechanism works well... but the problem arrives when I like to paint these data in a graphic. To retrieve the data for a device for example I perform a query between two dates filtered by external device and for example in 8 hours of execution the number of rows is 30.000.000 and the number data retrieved is 82.000 rows. This query takes 15 minutes and during this select the rest of insert and select (performed by others threads in java) cannot performed theirs tasks or they are performed perhaps once a minute.

If during the big select, I try to open an explorer to access the data base throw phpadmin the data base is blocked and not only the table concerned and perhaps after 2 minutes I am able to access the database and the querys are very slow until the big query finishes.

I have tested the same java code against an Oracle 11g Enterprise data base and the table is not blocked.

Should it be possible that this performance works in another version of mysql or in commercial version?

How to repeat:
This is the table

CREATE TABLE IF NOT EXISTS `sensor_measurements` (
`FK_SENSOR` int(11) NOT NULL,
`VALUE` decimal(15,3) NOT NULL COMMENT 'Decimal value of the group of bits received',
`CALCULATED_VALUE` varchar(50) DEFAULT NULL,
`MILLIS_MEASUREMENT` bigint(14) NOT NULL,
`FK_EXPERIMENT` int(11) DEFAULT NULL,
KEY `FK_SENSOR` (`FK_SENSOR`),
KEY `MILLIS_INDEX` (`MILLIS_MEASUREMENT`),
KEY `FK_EXPERIMENT` (`FK_EXPERIMENT`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

inset into this table 30.000.000 of rows and then performed a query where
FK_SENSOR=? and MILLIS_INDEX between ? and ?

During this execution you try to select and insert others rows systematically.

Please, send the results of EXPLAIN for your problematic query with some usual values of parameters substituted.

I have no usual values... for example inserting values from 1271937272453 to 1271965333375 incrementing this value by 1000 each time and inserting FK_SENSOR from 1 to 400 you'll have 30.000.000 of rows, then you execute the query with FK_SENSOR=100 and from 1271946626093 to 1271955979733

During this execution (it can takes some minutes) if you try to insert or select something else to this table, the table is not blocked completely but it blocks for seconds or minutes...
Thanks

Excuse-me I did not unsderstand correctly your post.
Here you are the EXPLAIN of the query:
explain select millis_measurement, calculated_value from sensor_measurements sm  where sm.FK_EXPERIMENT = 19 and sm.FK_SENSOR in (100);

id  select_type  table  type  possible_keys            key       
1   SIMPLE       sm     ref   FK_SENSOR,FK_EXPERIMENT  FK_SENSOR 

key_len  ref     rows    Extra  
4        const   97920   Using where 

the table has 90.000.000 of rows and the first filter (FK_EXPERIMENT) filters 30.000.000 and the second one 97920.

Thanks in advance

Please, send the results of

show full processlist;

when this "blocking" happens.

Here you are:

 Id  User  Host            db         Command  Time  State  
  1  root  localhost:55397 egse_gaia  Query    2     NULL  
  2  root  localhost:55473 egse_gaia  Query    29    Sending data  
  5  pma   localhost:55494 NULL       Sleep    0      
  6  root  localhost:55495 egse_gaia  Query    0     NULL 

Info  
SHOW KEYS FROM `sensor_measurements` FROM `egse_ga...
select ( round (millis_measurement/1000) + 7200)  ...
NULL
show full processlist 

Thanks

Here you are:

 Id  User  Host            db         Command  Time  State  
  1  root  localhost:55397 egse_gaia  Query    2     NULL  
  2  root  localhost:55473 egse_gaia  Query    29    Sending data  
  5  pma   localhost:55494 NULL       Sleep    0      
  6  root  localhost:55495 egse_gaia  Query    0     NULL 

Info  
SHOW KEYS FROM `sensor_measurements` FROM `egse_ga...
select ( round (millis_measurement/1000) + 7200)  ...
NULL
show full processlist 

Thanks

Please provide an example for your select and your insert statement.

Insert:
each second 400 inserts are performed with different data, for example:
insert into TABLE (FK_SENSOR,VALUE,MILLIS_MEASUREMENT,CALCULATED_VALUE) values (1,10,1234567890,12)

Select:
select millis_measurement, calculated_value from TABLE where millis_measurement between ? and ?

where millis_measurement is the number of millis from 1970 and represents the current date.

thanks

Just that I understand you right:

You wrote:
1271937272453 to 1271965333375
incrementing this value by 1000

Your column is INTEGER not BIGINT.

The range of INTEGER just is between:
-2147483648 and 2147483647

INTEGER is 4 Bytes.

You also can find it here:
http://dev.mysql.com/doc/refman/5.1/en/numeric-types.html

The numbers you try to insert are much higher.

You would need BIGINT for that instead.

Maybe this is already your problem.

I'm using a bitint (14)... but my problem is not related to the insert but with the select when inserting...
If I only insert data (400 inserts per second) the performance of the database is more or less acceptable but when during these inserts, I perform a select between two dates and the database has for example 100 Millions of registers, the query takes a long time to get the result and the second problem is that the table and the instance is blocked, not completely but perhaps the inserts are executed every 10 seconds instead of every second...
Thanks

Presumably your query is using index merge. We also experienced performance issues with our query (using index merge) over a table with 10,000,000 rows. Our workaround was to hint query optimizer to ignore index.

Hello... thanks for the answer.. I have tried with IGNORE INDEX but the query takes much more time than before... In a 500.000.000 rows table retrieving 15 rows the query does not finish...

But my problem is not only related to the table selected but with all tables in my database instance:

For example select * from TABLE1 where pk = ? and date between ? and ? ; (TABLE1 has 100.000.000 rows and the query returns 1000 rows (it takes a very long time)

If during this execution, I try to execute:
insert into TABLE2 ...
or 
insert into TABLE1 ...
or another select
select * from TABLE2

the result is the same and the inserts or the new select are delayed until the big query has finished.

Thanks

Please, check if the same problem still happens with a recent version, 5.1.57. If it does, please, send your my.cnf file content.

Current my.cnf for version 5.1.30-community mysql

Attachment: my.cnf (application/octet-stream, text), 5.17 KiB.

Hello,

I haven't tested a new version but I have attached the my.cnf configuration file for 5.1.30-community that is my current version of mysql.

Thanks in advance

We do not fix bugs in old versions, so we need to know if the problem is still repeatable with current one, 5.1.57 at the moment.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".