Sqlyog Disappears In Taskmanager When Viewing A 'large' Table

[jan marco alkema]

I have a table of 648000 records.

When I Open Table (F11) and I alter “No. of Rows” to 500000 records the program “SQLYog.exe” 'disappears'. If I alter “No. of Rows” = 400000 records SQLyog works good.

I see no (good) error message or program crash (when swap file =tempory file >2.3 Gb). I think it is releated to the amount of avaliable memory and/or the amount of avalialable windows 'swap' space. See the includes Task Manager performance screendump also. It disappears at the moment “wisselbestandgebruik=tempory file” value goes down very fast.

On my Vista Pc SQLyog.exe does not 'disappear' (on screen) when I do the same thing, I think because it has more memory (4096 Mb).

I know that I test the limits of the SQLyog programma.

Environment:

– standard 32 bits XP (1024 Mb Memory, num. of processors = 1, number of cores per processor = 2) on Vmware (operating on a 64 bits Ubuntu 64 bits Linux, i7, 12 Gb memory)

– MySQL 5.1.42-community.

[peterlaursen]

Yes – it happne because of meory exhaustion.  You will have to raise Virtual Memory setting currently.  SQLyog is a C++ program and does not depend on any other runtime environment than the OS/the Windows API itself (as opposite to JAVA and .Net programs for instance where the runtime has some options that the API has not) and the MySQL client API ('libmysql'). When a query is sent the control is taken over by the MySQL API compiled into SQLyog and it does nothing else than fetching the rows. It simply assumes that the result set can be handled by the OS. I am not sure if the MySQL API is more intelligent/aware on *nix (after all MySQL – server and client – are basically Unix programs).

We have discussed this a lot of times before but we did not find a better solution. But let us discuss again.

In around 2 weeks we will provide an option to display query results in RESULT TAB 'pagewise' similar to the DATA TAB. Only one page will be in memory at a time. 

[jan marco alkema]

>When a query is sent the control is taken over by the MySQL API compiled into SQLyog and it does nothing else than fetching the rows. It simply assumes that the result set can be handled by the OS.

Conclusion: The application: SQLyog should manage the request!

I am not expert on this matter, but this are my suggestions, without al lot of study on this subject:

1) Make an estimation of the memory what is needed to show the table. You can get the average field length of a table in MySQL.

2) Compute the available amount of memory on the host. A ‘little bit’ old project for example http://www.jfitz.com/software/RAMpage/index could possibly be used. Maybe there are recent project with same functionality.

If (estimated memory < available memory) { Show records in Grid. } else { Initial: Select a restricted limit of records of the table. Depending on available memory and estimation of record length. Depending on entering control-Up/Down keys or WM_VSCROLL messages, select other parts of the large table. If I enter more control-Up/Down keys in a short period, the move of the display (shown Grid records) over the total records will go faster up or down wards, like a ‘train’. A train begins slow, but will go every time faster until the maximum speed has reached. }

[peterlaursen]

Idea is good.  But I do not know if we can get reliable information from Windows itself. We will check this. Besides with remote servers/slow connection getting those metadata will possibly slow down the whole process (what is no problem if you show 100,000's or rows (but could be an annoyance if you show only 20).

[jan marco alkema]

>But I do not know if we can get reliable information from Windows itself.

Try example of GetProcessMemoryInfo: http://msdn.microsoft.com/en-us/library/aa366589(VS.85).aspx

>(what is no problem if you show 100,000's or rows (but could be an annoyance if you show only 20).

Make a tresh hold for remote host, if estimate size is higher (or lower) than x % of available memory, to do the check (or don't do) the check.

[peterlaursen]

As told we plan to display RESULT tab in pages as default (similar to what DATA tab does) in next major release (5.3). This will eliminate the problem. 

First beta will probably be available next week. Please check this out when available.  

[navyashree.r]

Hi,

We have released SQLyog 8.3 Beta1 with this feature, which is available publicly!

“In RESULT tab results can now be retrieved page-wise. This is ON as default with this build with a defined LIMIT of 1000 rows. For a specific query/resultset user can change and for specific combination of query, database and table the setting is persistent across sessions.”

Also please go through 'miscellaneous' in the link given below.

Please refer to:

http://www.webyog.com/blog/2010/02/18/sqlyog-mysql-gui-8-3-beta-1-has-been-released/

Thank you.

Regards,

Navya

[jan marco alkema]

Hello Navya,

I use “SQLyog 8.3 Beta1”.

If I open table “white_subscriber” with number of Rows = 1,300,000 rows. It works good. See included “Second_screen.zip” also.

If I open table “white_subscriber” with number of Rows = 1,400,000 rows. SQLyog don’t disappear, but has the process status “not reponding”. At the moment it 'crashes' there will appear more than 1 copies of “SQLyog Trail..” in the “task list tool bar= bottom ‘row’ in my screen dump”. See “Third_screen.zip” for the details.

Jan Marco

P.S. Mem.zip is little program to monitor the current memory usage.

[navyashree.r]

Hi Marco,

Quote:

If I open table “white_subscriber” with number of Rows = 1,400,000 rows. SQLyog don’t disappear, but has the process status “not responding”. At the moment it 'crashes' there will appear more than 1 copies of “SQLyog Trail..”

We are already prompting 'Not enough memory' in some places (For example: if Blob data is crossing the memory limit). Please attach the dump file, so we can analyze where it crashed exactly.

Crash Dump path:

Windows 2K/XP/2003:

C:Application Data{user}SQLyog

Windows Vista/2008/7:

C:Users{user}AppDataRoamingSQLyog

Quote:

Mem.zip is little program to monitor the current memory usage.

Thank You for your valuable suggestion. We will discuss about this and update.

Regards,

Navya

[peterlaursen]

We know that it will crash if you display more than available memory allows for.  There is no change in this respect  between 5.3 beta and previous releases. But you do have an option to do page-wise display in both DATA and RESULT tab and that is what we recommend.

Further action here is not priority for us at the moment. 

[navyashree.r]

Oops Sorry…

The crash dump path I gave was wrong

Correct path is here for Windows 2K/XP/2003:

C:Documents and Settings{user}Application DataSQLyog

Regards,

Navya

[jan marco alkema]

Hello Navya, I have used Vista home Premium, 4 Gb RAM.

The dumpfile is empty. I sqlyog.err appears when I try to close SQLyog icon in “tasklist bar”.

When it crashes I see an empty window on screen and an extra icon in “tasklist bar”. I think that the message “Not enough memory, application terminated!” is meant to be printed in the new (crash) window.

Greetings Jan Marco

Type sqlyog.err

Not enough memory, application terminated!

Directory of C:UsersalkemaAppDataRoamingSQLyog

26-02-2010 08:25

.

26-02-2010 08:25

..

26-02-2010 08:18 16.384 ColumnAttributes.db

26-02-2010 08:25 0 dir.txt

17-05-2009 14:57

Favorites

24-02-2010 09:27 180 sja.log

24-02-2010 09:27 42 sjasession.xml

26-02-2010 08:19 45 sqlyog.err

26-02-2010 08:17 2.138 sqlyog.ini

26-02-2010 08:17 0 SQLyog_Dump_000.dmp

[peterlaursen]

Just a short comment from me here to this: “The dumpfile is empty”. This is also what I would expect here.  Also if you kill the process the dump file is empty.  The reason for the crash is not 'internal' for SQLyog. 

SQLyog will prepare a file handle (ie: create an empty file) at start up as there is not much time available in case of a crash. And here nothing gets written to the empty file.

[jan marco alkema]

Hello Peter,

I use Trail SQLyog v8.3 beta 2,

Quote:

But you do have an option to do page-wise display in both DATA and RESULT tab and that is what we recommend.

How can I select “page-wise display”? Which option must I select in which window?

Greetings Jan Marco

[navyashree.r]

Hi,

We have released SQLyog 8.3 Beta2 with this feature, which is available publicly!

Quote:

But you do have an option to do page-wise display in both DATA and RESULT tab and that is what we recommend.

How can I select “page-wise display”? Which option must I select in which window?

In RESULT tab results can now be retrieved page-wise (same as the Table Data tab) by executing a SELECT (with out Limit clause) query. This is ON as default with this build with a defined LIMIT of 1000 rows. For a specific query user can change and for this specific query the setting is persistent across sessions. Also read ‘miscellaneous’ paragraph below under Beta1 section.

FYI:

http://www.webyog.com/blog/category/sqlyog/

Regards,

Navya

[jan marco alkema]

Hello Navya,

I talked to database specialists. They tell me, it could be better solved in database design. They mean selection on id is preferred above the limit selection method.

I made a test program to test the performance on retrieving display length =150 records random in a table of 5056088 records.

My conclusion is that the id_selection method (Appendix B ) is 15 times faster than the limit selection method (Appendix A ).

Navya, Why not load only the display length records in memory. If the user enters or keys, load new records on demand if the are out of the display records boundaries. For example 150 records. I think it could be programmaly be determined which field is an index/key index field.

Greetings Jan Marco

Appendix A: Limit method:

0!2010-03-20 07:58:16!QUERY1= select * from white_subscriber limit 3684932,150;!2010-03-20 07:58:18!duration= 2!

1!2010-03-20 07:58:18!QUERY1= select * from white_subscriber limit 3912782,150;!2010-03-20 07:58:20!duration= 2!

2!2010-03-20 07:58:20!QUERY1= select * from white_subscriber limit 4267014,150;!2010-03-20 07:58:23!duration= 3!

.

.

997!2010-03-20 08:23:19!QUERY1= select * from white_subscriber limit 927186,150;!2010-03-20 08:23:20!duration= 1!

998!2010-03-20 08:23:20!QUERY1= select * from white_subscriber limit 567862,150;!2010-03-20 08:23:20!duration= 0!

999!2010-03-20 08:23:20!QUERY1= select * from white_subscriber limit 790336,150;!2010-03-20 08:23:20!duration= 0!

counter=1000!total_duration_seconds=1504!avg duration= 1 second(s)!

Appendix A: Id selection method:

0!2010-03-20 08:25:39!QUERY1=select * from white_subscriber where (id >=1889324) AND (id < 1889474);!2010-03-20 08:25:39!duration= 0! 1!2010-03-20 08:25:39!QUERY1=select * from white_subscriber where (id >=3179392) AND (id < 3179542);!2010-03-20 08:25:40!duration= 1! 2!2010-03-20 08:25:40!QUERY1=select * from white_subscriber where (id >=450716) AND (id < 450866);!2010-03-20 08:25:40!duration= 0! . . 997!2010-03-20 08:27:18!QUERY1=select * from white_subscriber where (id >=2427516) AND (id < 2427666);!2010-03-20 08:27:19!duration= 1! 998!2010-03-20 08:27:19!QUERY1=select * from white_subscriber where (id >=3875214) AND (id < 3875364);!2010-03-20 08:27:19!duration= 0! 999!2010-03-20 08:27:19!QUERY1=select * from white_subscriber where (id >=4867814) AND (id < 4867964);!2010-03-20 08:27:19!duration= 0! counter=1000!total_duration_seconds= 99!avg duration= 0 second(s)!

[Author]

Posts