Run Out Of Virtual Memory

[flixer]

Hi ive just ran the following query across three tables:

select region1titles.DVDTitle, ProductionYear, Genre, Rating

from region1titles, ukdvdtitles, imdbratings

where region1titles.DVDTitle = ukdvdtitles.DVDTitle And ProductionYear > 1900

I received the following error:

Error Code : 2008

MySQL client ran out of memory

(7681 ms taken)

I have 512mb ddr system memory and virtual memory is at 1000mb.

Im running windows xp, sqlyog v5.18 and mysql 5.0.24a.

Is there a way to avoid this issue without adding more ram, or is it inevitable?

tia

[peterlaursen]

MySQL client ran out of memory

(7681 ms taken)

it should normally take much more time than 7-8 sec's to fill that memory.

You are sure that your JOIN-query dos not return a 'astronomic' set of data?

How many rows would you expect to be returned here?

Try attach the 'Create statement' for the tables.

[flixer]

peterlaursen wrote on Sep 24 2006, 06:13 PM:

MySQL client ran out of memory

(7681 ms taken)

it should normally take much more time than 7-8 sec's to fill that memory.

You are sure that your JOIN-query dos not return a 'astronomic' set of data?

How many rows would you expect to be returned here?

Try attach the 'Create statement' for the tables.

It would only be between 17,000 to 20,000 rows. Several columns i had to edit because “DVD Title” and “Production Year” wouldnt work in the query, but after altering the columns to “DVDTitle” and “ProductionYear” the query ran (but then came across this memory issue).

Below is the whole history log for the past 1hr, 15minutes.

/*[17:14:26][ 370 ms]*/ show variables like '%character%'

/*[17:14:26][ 70 ms]*/ Set character_set_connection=latin1

/*[17:14:26][ 0 ms]*/ Set character_set_results=latin1

/*[17:14:26][ 0 ms]*/ Set character_set_client=latin1

/*[17:14:26][ 0 ms]*/ set sql_mode=''

/*[17:14:26][ 20 ms]*/ show databases

/*[17:14:29][ 0 ms]*/ use `uk-dvd`

/*[17:14:29][ 231 ms]*/ select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA` = 'uk-dvd' and `TABLE_TYPE` = 'BASE TABLE'

/*[17:17:57][ 110 ms]*/ create table `uk-dvd`.`Region1Titles` ( `DVD Title` varchar (60) NOT NULL , `Production Year` date NOT NULL , `Genre` varchar (25) NOT NULL )

/*[17:17:59][ 10 ms]*/ show databases

/*[17:17:59][ 40 ms]*/ select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA` = 'uk-dvd' and `TABLE_TYPE` = 'BASE TABLE'

/*[17:18:04][ 60 ms]*/ select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA` = 'uk-dvd' and `TABLE_TYPE` = 'BASE TABLE'

/*[17:18:04][ 20 ms]*/ show full fields from `uk-dvd`.`region1titles`

/*[17:18:48][1081 ms]*/ load data local infile 'C:/………….allregion1.csv' into table `uk-dvd`.`region1titles` fields escaped by '\' terminated by ',' enclosed by '”' lines terminated by 'rn' ( `DVD Title`, `Production Year`, `Genre` )

/*[17:18:56][ 0 ms]*/ show full fields from `uk-dvd`.`region1titles`

/*[17:18:56][ 0 ms]*/ show keys from `uk-dvd`.`region1titles`

/*[17:18:56][ 10 ms]*/ select * from `uk-dvd`.`region1titles` limit 0, 1000

/*[17:19:03][ 0 ms]*/ show full fields from `uk-dvd`.`ukdvdtitles`

/*[17:19:03][ 0 ms]*/ show keys from `uk-dvd`.`ukdvdtitles`

/*[17:19:03][ 10 ms]*/ select * from `uk-dvd`.`ukdvdtitles` limit 0, 1000

/*[17:19:04][ 0 ms]*/ show full fields from `uk-dvd`.`region1titles`

/*[17:19:04][ 0 ms]*/ show keys from `uk-dvd`.`region1titles`

/*[17:19:04][ 0 ms]*/ select * from `uk-dvd`.`region1titles` limit 0, 1000

/*[17:19:11][ 60 ms]*/ truncate table `uk-dvd`.`region1titles`

/*[17:19:16][ 0 ms]*/ show full fields from `uk-dvd`.`region1titles`

/*[17:19:16][ 10 ms]*/ show keys from `uk-dvd`.`region1titles`

/*[17:19:16][ 0 ms]*/ select * from `uk-dvd`.`region1titles` limit 0, 1000

/*[17:19:19][ 0 ms]*/ show full fields from `uk-dvd`.`region1titles`

/*[17:19:19][ 0 ms]*/ show keys from `uk-dvd`.`region1titles`

/*[17:19:19][ 0 ms]*/ show table status from `uk-dvd`

/*[17:19:37][ 140 ms]*/ alter table `uk-dvd`.`region1titles` change `Production Year` `Production Year` numeric NOT NULL

/*[17:19:38][ 0 ms]*/ show databases

/*[17:19:38][ 0 ms]*/ select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA` = 'uk-dvd' and `TABLE_TYPE` = 'BASE TABLE'

/*[17:19:44][ 20 ms]*/ select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA` = 'uk-dvd' and `TABLE_TYPE` = 'BASE TABLE'

/*[17:19:44][ 0 ms]*/ show full fields from `uk-dvd`.`region1titles`

/*[17:19:55][1022 ms]*/ load data local infile 'C:/……allregion1.csv' into table `uk-dvd`.`region1titles` fields escaped by '\' terminated by ',' enclosed by '”' lines terminated by 'rn' ( `DVD Title`, `Production Year`, `Genre` )

/*[17:19:59][ 0 ms]*/ show full fields from `uk-dvd`.`region1titles`

/*[17:19:59][ 0 ms]*/ show keys from `uk-dvd`.`region1titles`

/*[17:19:59][ 10 ms]*/ select * from `uk-dvd`.`region1titles` limit 0, 1000

/*[17:21:12][ 0 ms]*/ show full fields from `uk-dvd`.`averagematchedratings`

/*[17:21:12][ 40 ms]*/ show keys from `uk-dvd`.`averagematchedratings`

/*[17:21:12][ 0 ms]*/ select * from `uk-dvd`.`averagematchedratings` limit 0, 1000

/*[17:21:25][ 0 ms]*/ show full fields from `uk-dvd`.`averagematchedratings`

/*[17:21:25][ 20 ms]*/ show keys from `uk-dvd`.`averagematchedratings`

/*[17:21:25][ 40 ms]*/ show table status from `uk-dvd`

/*[17:21:39][ 60 ms]*/ rename table `uk-dvd`.`averagematchedratings` to `uk-dvd`.`avgmatchedratings`

/*[17:22:53][ 0 ms]*/ show full fields from `uk-dvd`.`region1titles`

/*[17:22:53][ 0 ms]*/ show keys from `uk-dvd`.`region1titles`

/*[17:22:53][ 0 ms]*/ select * from `uk-dvd`.`region1titles` limit 0, 1000

/*[17:23:29][ 20 ms]*/ select DVD Title, Production Year, Genre, Rating from region1titles, avgmatchedratings where region1titles.DVD Title = avgmatchedratings.Title

/*[17:23:53][ 0 ms]*/ select DVD Title, Production Year, Genre, Rating from region1titles, avgmatchedratings where region1titles.”DVD Title” = avgmatchedratings.Title

/*[17:24:05][ 0 ms]*/ select DVD Title, Production Year, Genre, Rating from region1titles, avgmatchedratings where “region1titles.DVD Title” = avgmatchedratings.Title

/*[17:24:19][ 0 ms]*/ select DVD Title, Production Year, Genre, Rating from region1titles, avgmatchedratings where region1titles.DVD Title = avgmatchedratings.Title

/*[17:24:32][ 10 ms]*/ show full fields from `uk-dvd`.`region1titles`

/*[17:24:32][ 0 ms]*/ show keys from `uk-dvd`.`region1titles`

/*[17:24:32][ 40 ms]*/ show table status from `uk-dvd`

/*[17:24:39][ 0 ms]*/ show databases

/*[17:24:39][ 0 ms]*/ select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA` = 'uk-dvd' and `TABLE_TYPE` = 'BASE TABLE'

/*[17:24:41][ 0 ms]*/ show full fields from `uk-dvd`.`region1titles`

/*[17:24:41][ 0 ms]*/ show keys from `uk-dvd`.`region1titles`

/*[17:24:41][ 40 ms]*/ show table status from `uk-dvd`

/*[17:24:49][1912 ms]*/ alter table `uk-dvd`.`region1titles` change `DVD Title` `DVD-Title` varchar (60) NOT NULL COLLATE latin1_swedish_ci

/*[17:24:50][ 0 ms]*/ show databases

/*[17:24:50][ 90 ms]*/ select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA` = 'uk-dvd' and `TABLE_TYPE` = 'BASE TABLE'

/*[17:25:01][ 10 ms]*/ select DVD-Title, Production Year, Genre, Rating from region1titles, avgmatchedratings where region1titles.DVD-Title = avgmatchedratings.Title

/*[17:25:27][ 0 ms]*/ show full fields from `uk-dvd`.`region1titles`

/*[17:25:27][ 30 ms]*/ show keys from `uk-dvd`.`region1titles`

/*[17:25:27][ 0 ms]*/ select * from `uk-dvd`.`region1titles` limit 0, 1000

/*[17:25:40][ 0 ms]*/ show full fields from `uk-dvd`.`region1titles`

/*[17:25:41][ 30 ms]*/ show keys from `uk-dvd`.`region1titles`

/*[17:25:41][ 80 ms]*/ show table status from `uk-dvd`

/*[17:25:47][1943 ms]*/ alter table `uk-dvd`.`region1titles` change `DVD-Title` `DVDTitle` varchar (60) NOT NULL COLLATE latin1_swedish_ci

/*[17:25:48][ 0 ms]*/ show databases

/*[17:25:48][ 90 ms]*/ select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA` = 'uk-dvd' and `TABLE_TYPE` = 'BASE TABLE'

/*[17:25:58][ 0 ms]*/ select DVDTitle, Production Year, Genre, Rating from region1titles, avgmatchedratings where region1titles.DVDTitle = avgmatchedratings.Title

/*[17:26:07][ 0 ms]*/ show full fields from `uk-dvd`.`region1titles`

/*[17:26:07][ 20 ms]*/ show keys from `uk-dvd`.`region1titles`

/*[17:26:07][ 70 ms]*/ show table status from `uk-dvd`

/*[17:26:19][2234 ms]*/ alter table `uk-dvd`.`region1titles` change `Production Year` `ProductionYear` decimal (10,0) NOT NULL

/*[17:26:20][ 0 ms]*/ show databases

/*[17:26:20][ 100 ms]*/ select `TABLE_NAME` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_SCHEMA` = 'uk-dvd' and `TABLE_TYPE` = 'BASE TABLE'

/*[17:26:30][2373 ms]*/ select DVDTitle, ProductionYear, Genre, Rating from region1titles, avgmatchedratings where region1titles.DVDTitle = avgmatchedratings.Title

/*[17:31:01][ 10 ms]*/ show full fields from `uk-dvd`.`avgmatchedratings`

/*[17:31:01][ 0 ms]*/ show keys from `uk-dvd`.`avgmatchedratings`

/*[17:31:01][ 0 ms]*/ select * from `uk-dvd`.`avgmatchedratings` limit 0, 1000

/*[17:34:40][ 0 ms]*/ select DVDTitle, ProductionYear, Genre, Rating from region1titles, avgmatchedratings where region1titles.DVDTitle = avgmatchedratings.Title And ProductionYear = notnull

/*[17:35:40][3235 ms]*/ select DVDTitle, ProductionYear, Genre, Rating from region1titles, avgmatchedratings where region1titles.DVDTitle = avgmatchedratings.Title And ProductionYear > 1900

/*[17:38:30][ 0 ms]*/ describe `uk-dvd`.`ukdvdtitles`

/*[17:38:30][ 40 ms]*/ show index from `uk-dvd`.`ukdvdtitles`

/*[17:39:04][ 0 ms]*/ show full fields from `uk-dvd`.`ukdvdtitles`

/*[17:39:04][ 30 ms]*/ show keys from `uk-dvd`.`ukdvdtitles`

/*[17:39:04][ 0 ms]*/ select * from `uk-dvd`.`ukdvdtitles` limit 0, 1000

/*[17:39:29][ 0 ms]*/ show full fields from `uk-dvd`.`ukdvdtitles`

/*[17:39:29][ 40 ms]*/ show keys from `uk-dvd`.`ukdvdtitles`

/*[17:39:29][ 0 ms]*/ select * from `uk-dvd`.`ukdvdtitles`

/*[17:39:36][ 0 ms]*/ select DVDTitle, ProductionYear, Genre, Rating from region1titles, ukdvdtitles where region1titles.DVDTitle = ukdvdtitles.DVDTitle And ProductionYear > 1900

/*[17:40:40][ 0 ms]*/ select region1titles.DVDTitle, ProductionYear, Genre, Rating from region1titles, ukdvdtitles where region1titles.DVDTitle = ukdvdtitles.DVDTitle And ProductionYear > 1900

/*[17:41:28][7681 ms]*/ select region1titles.DVDTitle, ProductionYear, Genre, Rating from region1titles, ukdvdtitles, imdbratings where region1titles.DVDTitle = ukdvdtitles.DVDTitle And ProductionYear > 1900

/*[18:01:41][ 150 ms]*/ show variables like '%character%'

/*[18:01:41][ 70 ms]*/ Set character_set_connection=latin1

/*[18:01:41][ 0 ms]*/ Set character_set_results=latin1

/*[18:01:41][ 0 ms]*/ Set character_set_client=latin1

/*[18:01:41][ 0 ms]*/ set sql_mode=''

/*[18:01:41][ 90 ms]*/ show full fields from `uk-dvd`.`ukdvdtitles`

/*[18:01:42][ 160 ms]*/ show keys from `uk-dvd`.`ukdvdtitles`

/*[18:01:42][ 51 ms]*/ select * from `uk-dvd`.`ukdvdtitles`

[peterlaursen]

I asked for the 'create statement for the tables' . the log does not tell anything here.

'create statement for the table' in on the OBJECTS tab – or execute 'show create table tablename' …

What makes me suspicious is that you have THREE tables, but you only use TWO of them in the where?

what about

Code:

where region1titles.DVDTitle = ukdvdtitles.DVDTitle And region1titles.DVDTitle = ratings.DVDTitle And ProductionYear > 1900

.. but to discuss the query in detail I need to understand your tables first!

[flixer]

peterlaursen wrote on Sep 24 2006, 06:38 PM:

I asked for the 'create statement for the tables' . the log does not tell anything here.

'create statement for the table' in on the OBJECTS tab – or execute 'show create table tablename' …

What makes me suspicious is that you have THREE tables, but you only use TWO of them in the where?

what about

Code:

where region1titles.DVDTitle = ukdvdtitles.DVDTitle And region1titles.DVDTitle = ratings.DVDTitle And ProductionYear > 1900

.. but to discuss the query in detail I need to understand your tables first!

Hi Peter,

I just ran the query u suggested and this time i didnt have the memory issue, however it did take 473440ms to execute!

But i am getting some duplicate results, so i think i need to add a GROUP BY or AVG statement.

Thanks again for your valuable assistance, and i hope you dont mind me asking you so many questions.

[Author]

Posts