Memory Usage Is Very High After About 10-15mins

[carltond]

Hi Guys,

I am loving the free SQLYog program but currently running into problems and finding that SQLYog is using up huge amounts of memory after about 10-15minutes usage.

The DB I am accessing is MySQL 4.1.12a-nt on my local computer where SQLYog is found. After a few minutes I check the Windows' Task Manager and it suggests that SQLYog is using 800,000K worth of memory.

Are there common things that you have come across that are likely to cause this sorts of problems?

Here is a example of a query that I was performing…

Code:

(SELECT * FROM mysqlCustomer WHERE HO_NO ='' AND CODE !='' AND (DATE(NOW()) < DATE_OFF OR DATE_OFF = '0000-00-00') AND SLEEPER !='T' AND (mysqlCustomer.BASIC_S != 0.00 AND mysqlCustomer.HOUR24_S != 0.00 AND mysqlCustomer.NIGHT_S != 0.00 AND mysqlCustomer.W_END_S != 0.00 AND mysqlCustomer.VMAIL_S != 0.00))
UNION
(SELECT mysqlCustomer.* FROM mysqlCustomer LEFT JOIN mysqlCustomer AS Parent ON mysqlCustomer.CLIENT = Parent.CLIENT WHERE mysqlCustomer.HO_NO IN
(SELECT mysqlCustomer.CLIENT FROM mysqlCustomer LEFT JOIN mysqlCustomer AS Parent ON mysqlCustomer.CLIENT = Parent.CLIENT WHERE mysqlCustomer.HO_NO IN
(SELECT mysqlCustomer.CLIENT FROM mysqlCustomer LEFT JOIN mysqlCustomer AS Parent ON mysqlCustomer.CLIENT = Parent.CLIENT WHERE mysqlCustomer.HO_NO IN
(SELECT CLIENT FROM mysqlCustomer WHERE (DATE(NOW()) < DATE_OFF OR DATE_OFF = '0000-00-00') AND (CLIENT > 5000 AND CLIENT < 6000) AND CODE !='') AND mysqlCustomer.SLEEPER !='T' AND NOT (mysqlCustomer.BASIC_S != 0.00 AND mysqlCustomer.HOUR24_S != 0.00 AND mysqlCustomer.NIGHT_S = !0.00 AND mysqlCustomer.W_END_S = !0.00 AND mysqlCustomer.VMAIL_S = !0.00)))) ORDER BY CODE

[Ritesh]

How many rows does the query return?

Are you sure the query is proper i.e. indexes are correctly used? It might be that the MySQL client library needs to create lot of data at the client side to create the correct resultset for your query.

Personally, I believe its a query problem rather then SQLyog.

[carltond]

Ritesh wrote on Dec 21 2005, 03:21 PM:

How many rows does the query return?

Are you sure the query is proper i.e. indexes are correctly used? It might be that the MySQL client library needs to create lot of data at the client side to create the correct resultset for your query.

Personally, I believe its a query problem rather then SQLyog.

[post=”8247″]<{POST_SNAPBACK}>[/post]

The query only returns 1000 rows and at the moment I am trying to fine tune my query so I am submitting a similar query each time with a few minor changes

[Ritesh]

Can you execute the same query using the mysql command line once and tell me the result?

[peterlaursen]

Isn't there two identical nested SELECT .. WHERE .. IN ?

One must be redundant, I think!?

Suggestion: try each SELECT for itself and see which one uses the memory!

[vygi]

carltond wrote on Dec 21 2005, 03:45 PM:

After a few minutes I check the Windows' Task Manager and it suggests that SQLYog is using 800,000K worth of memory.

[post=”8245″]<{POST_SNAPBACK}>[/post]

Recently I've got same problems!

After running of several queries, SQLyog has suddenly occupied almost one gigabyte of RAM on a XP machine which has 512 MB RAM. I've tried to close all (actually both) DB connections but memory usage remained that high: about 960MB without any opened connections! I had to closed and restart SQLyog to free this memory. 2-3 hours later, memory usage was again 447MB.

Same problems (“out of virtual memory”) were reported by one of my workmates.

That's really strange because I don't run any queries who return big amount of data. All result sets are usually small, mostly just 3-4 rows, in rare cases more than 100.

I also did not have such problems before so it can be something related to one of the newest versions.

[vygi]

Ritesh wrote on Dec 21 2005, 04:21 PM:

Are you sure the query is proper i.e. indexes are correctly used? It might be that the MySQL client library needs to create lot of data at the client side to create the correct resultset for your query.

[post=”8247″]<{POST_SNAPBACK}>[/post]

Isn't SERVER always creating the final result set and sending it to the (dumb) client?

I always thought that clients don't need to make any operations with the data they get from the server.

[Ritesh]

This is very strange. Can you provide me with steps to reproduce the error?

Any hints where I can start?

We run our code through the best memory leak detection tool and it has never reported us anything like this.

@carltond: Can you send me the table structure and sample data so that I can run your query against the db and try to reproduce the problem?

[vygi]

Ritesh wrote on Dec 23 2005, 04:26 PM:

This is very strange. Can you provide me with steps to reproduce the error?

Right now I have again 321.524K (1/3 G:cool: “Private Bytes” reserved by SQLyog with no open connections. I have run several queries which return few rows (or even no rows at all) from a table with 12+ GB of data. I will try to reproduce it… stay tuned! 😉

[peterlaursen]

Quote:

… from a table with 12+ GB of data

that is quite a large table. I think the index structure of the table in relation to the queries (is there an appropriate index for the query?) could be interesting.

But nothing that I know about in particular. I never had any table exceeding 20 MB.

[vygi]

20 MB?.. Tables larger than 4 GB are usual here! 😛

And they work very well!

Most of our tables consist of so-called CDRs (Call Data Records): every telephone call generates one CDR on one telecommunication switch, mostly 2-3 CDRs pro call, sometimes up to 6-8 as several devices are affected. We (a small telecom company) have more than 0.5 mio calls every day, that means far more than 1,000,000 new data rows daily, recently more than 5 GB every month. The biggest table at the moment contains 44 mio. rows, data file size is 18.5 GB, index size 1.3 GB. An it is still fast enough for our purposes: response time for simple insert and update queries is always below 5ms while sequential processing of data. Our main OLAP database (still under construction) which is based on star schema has almost 150 mio rows in a merged MyISAM table. Total data size is 15 GB, index size 12 GB. Some simple queries take just several milliseconds, larger ones can take up to few minutes, which was the reason to introduce aggredated summary tables.

BTW I can't reproduce this memory leak, SQLyoug works very well.

[peterlaursen]

@vygi

I think that partitioned tables as of MySQL 5.1 could be interesting for you then?

Maybe you could 'trigger' the proces of implementing GUI-support for that with SQLyog?

[vygi]

Ritesh wrote on Dec 23 2005, 04:26 PM:

This is very strange. Can you provide me with steps to reproduce the error?

Any hints where I can start?

[post=”8268″]<{POST_SNAPBACK}>[/post]

NOW I HAVE IT!! 😎

I was just searching for some information in the databases and realized that SQLyog has reserved more than 100MB RAM having just two open connections and 4 query editor tabs with few result lines in each. I have closed all connections but memory use was still above 90 MB (“Private Bytes” in Process Explorer by sysinternals.com).

I have saved all my queries, restarded SQLyog and tried to reproduce this issue… without any results at the beginning. Then I saw that with every run of a query (every press of F5) SQLyog takes a bit more memory.

1. Fresh restart, one DB connection: 5,352 KB

2. Load a file with some queries: 7,536 KB

strange: it needs more than TWO MEGAbytes just to load an ONE-KILObyte text file with 4 queries inside, without executing anything?

3. Close connection: 6,348 KB

4. Re-open connection and re-load same file: 7,560 KB

5. Run a query once (it takes about 50 ms and returns 78 short rows): 7,688 KB

6. Run same query one more time (just press F5): 7,752 KB

7. Press F5 10 times: 8,308 KB

8. Close connection: 7,168 KB

9. Re-open connection: 8,360 KB

10. Select 1,000 rows from a big table (SELECT * FROM table LIMIT 1000): 10,680

NOW, THINGS START TO GO CRAZY:

11. Change approximately 10 times with Alt-Tab between SQLyog and Process Explorer: 21,408 KB

Note: this is without doing anything with SQLyog!!!

At this point, I thought I must stop this switching between SQLyog and Process Explorer, so I did following:

12. Maximize Process Explorer window, switch to SQLyog, restore (“un-maximize”) SQLyog window and change its height with the mouse… WOW! memory usage grows permanently! After ca. 30 s of changing window size, it has occupied more than 200 GB. The growing speed of “Private Bytes” is approximately 10MB/s when I change the SQLyog window size or move window with the mouse, or aprox. 1.1 MB with each switching with Alt-Tab to some other program and then back.

Huh… I think that you will not need any data examples because it works with any MySQL table: just select some data, and then try to move SQLyog window around and/or change window size and/or switch to other applications and then back. It needs more memory with every re-painting of its own window.

Happy Fixing (and Happy New Year indeed),

Vygi

[vygi]

To sum up:

– amount of used memory grows just slowly if you resize/move window but there are no query results in the grid, or you switch eg. to “Message” tab, but it grows very rapidly (up to many MB/s) if a big result is being displayed.

– I think, repeated F5 only increases memory use because the result grid must be updated (like while window switching/moving/resizing).

[peterlaursen]

@vygi

1)

A 'small memory build-up' will always occur as HISTORY grows. Actually about a year ago a program change was introduced, with VERY BIG queries. I don't remember details. But it was something like queries taking more than 4 KB was not added to HISTORY.

Actually I have proposed before that histroy was 'spooled' to a file. Then you also could close down the program and open HISTORY from a previous session.

2)

Will I need that 'proces explorer' program to reproduce? Task manager will do as well I guess? You are sure that it is not that 'proces explorer' program that smashes Windows Menory management?

[peterlaursen]

I also would add that extensive memory usage is no problem as long as it is available without the need for swapping and as long as no other program needs that memory! But the OS should free the memory when some other program needs it.

Modern Windows is not DOS! Memory management is quite different!

But I can recommend this program

http://www.yourwaresolutions.com/software.html#framxpro

for improving /additinal memory management with WinXP!

[peterlaursen]

I also can add that on my system (Win XP SP2) I can open SQLyog, move program window around, maximize, minimize etc – and memory usage according to Task manager does not change!

[vygi]

peterlaursen wrote on Dec 30 2005, 03:26 PM:

Will I need that 'proces explorer' program to reproduce?  Task manager will do as well I guess? You are sure that it is not that 'proces explorer' program that smashes Windows Menory management?

[post=”8314″]<{POST_SNAPBACK}>[/post]

No need for Process Explorer, it's clearly visible also using Windows Task manager.

Start it, and start to move/resize SQLyog. You'll see…

BTW, Process Explorer is nothing that runs permanently, so it's impossible that it “smashes Windows memory management”.

It can replace Task manager and be called using Ctrl-Shift-Esc if you choose that. I like it and use it because it delivers much better overview about things going in Windows. Just try it – it's free!

[peterlaursen]

Quote:

Start it, and start to move/resize SQLyog. You'll see…

No! Not here! :huh:

[peterlaursen]

And …

A simple SELECT returning about 40.000 rows of my music database makes SQLyog use about 20M RAM and 20 MB Swapfile. Next after another SELECT returning only one row if decreases to almost what it was when program was opened (HISTROY explains that it does not quite) When minimizing SQLyog RAM usage goes to around 340 KB.

I can do that repeatedly!

And actually HISTORY built-up is swapped by my system! Repeated queries increses Swap-file usage but not physical memory usage!

It is the same with and without that additional memory manager. Except that using it cutting about 25% in memory footprint with SQLyog. But there is no build-up in neither case!

I could suspect that this is related to the Graphics system/driver! I testet on my 'mini-PC' with a VIA graphics chip. I'll try with another system with NVIDIA graphics!

[vygi]

peterlaursen wrote on Dec 30 2005, 03:34 PM:

I also can add that on my system (Win XP SP2) I can open SQLyog, move program window around, maximize, minimize etc – and memory usage according to Task manager does not change!

[post=”8316″]<{POST_SNAPBACK}>[/post]

Huh… that's strange then.

I also use Windows XP (Professional) with SP2.

Now I have re-booted the whole system, switched all possible tasks and programs off, and this memory issue is still clearly reproduceable! After moving around a bit, SQLyog (“Free v5.01 BTW) needs 270 MB already. OK, I don't need to resize window when I work, but memory usage is growing with every switch to another application and back, and with every query execution, so this is a real problem to me.

Would be interesting to know if same hapens on carltond's (originator of this thread) machine.

[vygi]

peterlaursen wrote on Dec 30 2005, 04:13 PM:

I could suspect that this is related to the Graphics system/driver!

Maybe… but it can't be only graphic-related as the video driver don't know how big the result set actually is. And memory usage grows much faster if some longer result is loaded.

I have Intel 82865G Graphics Controller here.

OK… enough for today… happy new year!

Vygi

[peterlaursen]

I just edited another post of mine

Quote:

I could suspect that this is related to the Graphics system/driver! I testet on my 'mini-PC' with a VIA graphics chip. I'll try with another system with NVIDIA graphics!

Ten minutes!

[peterlaursen]

Not reproducable either!

This is an very traditional Athlon PC with old Nvidia GF2/MX board, but with a recent Forceware driver.

[peterlaursen]

What I meant …

That memory usage builds up when moving program window around could be graphics related. But you are right of course that queries returning more rows that what is displayed should not cause any Graphics related issue!

[carltond]

vygi wrote on Dec 30 2005, 03:15 PM:

Would be interesting to know if same hapens on carltond's (originator of this thread) machine.

[post=”8320″]<{POST_SNAPBACK}>[/post]

Hey, I've still been watching this thread but haven't replied due to that I was asked for data to work with and as it's company data and individual's can be identified by it I wasn't interested in sharing it (for data protection reasons).

However I have managed to recreate the problem.

It seems to stem from along query…I generated the initial problem with this query

Code:

SELECT mysqlCustomer.*, Parent.HO_NO AS PARENT FROM mysqlCustomer LEFT JOIN mysqlCustomer AS Parent ON mysqlCustomer.CLIENT = Parent.CLIENT WHERE mysqlCustomer.HO_NO IN (SELECT mysqlCustomer.CLIENT FROM mysqlCustomer LEFT JOIN mysqlCustomer AS Parent ON mysqlCustomer.CLIENT = Parent.CLIENT WHERE mysqlCustomer.HO_NO IN (SELECT mysqlCustomer.CLIENT FROM mysqlCustomer LEFT JOIN mysqlCustomer AS Parent ON mysqlCustomer.CLIENT = Parent.CLIENT WHERE mysqlCustomer.HO_NO IN (SELECT mysqlCustomer.CLIENT FROM mysqlCustomer LEFT JOIN mysqlCustomer AS Parent ON mysqlCustomer.CLIENT = Parent.CLIENT WHERE mysqlCustomer.HO_NO IN (SELECT CLIENT FROM mysqlCustomer WHERE (DATE(NOW()) < DATE_OFF OR DATE_OFF = '0000-00-00') AND (CLIENT > 5000 AND CLIENT < 6000) AND CODE !='')))) AND mysqlCustomer.CODE = 126728 ORDER BY CODE

The query takes a second and returns 159 rows from 6000+ records.

Then as vygi suggests if you resize the window (I double clicked the application toolbar at top of screen to restore and then maximize) and the mem usage increases by 1500 – 2500k each time, more so when the window is maximised.

However when I minimise the window mem usage shoots DOWN to 800k or something minor. I can then follow the maximise/restore procedure and it all happens again.

I can't really restart my comp at the moment cos I should be cracking on with work but will check it on tuesday and see how it handles small queries because at the moment even a select * query returning 6000 records increases the mem usage by a similar amount 😮

Hope that helps identify the issue, this slowdown issue hasn't been a problem of late because I am working on slightly smaller queries…it may be this large query that starts SQLYog struggling.

I love this prog…over things like phpMyAdmin and Query Browser so will defo stick with it anyway.

I would love to see the history sent to a file, as I am testing loads at the moment I want to keep my queries but find myself having to manually save them to a text file which is annoying but would be good if it could be done automatically

[peterlaursen]

As I have written elsewhere the office in Bangalore is closed down for a short New Year's Holiday. But they will show up again monday or tuesday. I think there is little more that we can do now.

@carltond: what is your Graphics?

[vygi]

peterlaursen wrote on Dec 30 2005, 04:28 PM:

Not reproducable either!

This is an very traditional Athlon PC with old Nvidia GF2/MX board, but with a recent Forceware driver.

[post=”8323″]<{POST_SNAPBACK}>[/post]

And I am sitting at home now and have same issue on Windows XP Home SP2 with nVidia 6600 graphics, latest SQLyog Enterprise 5.01 beta.

There is one difference though: SQLyog grabs more memory only if some query result is displayed in the Result tab. Right now memory use grows by 164 KB on every restore after minimizing the window. On minimize, allocated memory size either stays constant or increases by 4 KB but never shoots down.

Interestingly, even if I switch to Table Data tab which displays data in similar way like Result tab — everything is OK, no memory leak anymore! I am sure it was different in the office.

[vygi]

Believe me or not but here are three screenshots of SQLyog 5.01 (final) in front of Opera Browser wit Webyog home page. Click on the thumbnails for full size pictures (sorry but I was unable to upload to webyog.com).

I habe managed to get these nice pictures after I was watching how fast (or how slow) SQLyog occupies memory when I resize its window with the mouse. After growing to over 40..50 MB, it has started to destroy itself and other windows….

[peterlaursen]

@vygi

.. I think we all believed you all the time. Even you ….. 😛

But since I can't reproduce this, there must be some decisive differences of our system! You write

Quote:

when I resize its window….it has started to destroy itself and other windows….

I still think it is a combination of OS, Graphics driver and SQLyog, that results that memory used by graphics is not free'd. The 'destruction' could indicate that the OS/the driver does not know which memory adresses to read and write to and from.

About this Graphics System: how does it use memory?

I guess it uses 'shared memory' but allocated dynamically or statically?

Is there some kind of Video-shadowing set active in BIOS?

My miniPC has shared memory (too), but a 'fixed memory pool' is allocated at system startup.

I think I understood that you have tested at work. Do you have a chance to test with another PC at home or somewhere else? And maybe try another driver for the Graphics card?

All my tests done with SQLyog 5.02 beta.

BTW: This webiste where you upload those pictures have some 'dirty' popups that crash my browsers …

[peterlaursen]

Sorry .. yes I can reproduce some of it! But no 'destruction' phenomenon.

If I start doing some SELECt that occupies some 30-40 MB memory, then memory load increases for each maximization – also if I don't do anything else in between. And just moving program window also trigger more memory use

However there must be some significant memory load to start with to reproduce this! A freshly started program does not!

Also if I next issue a SELECT returning only one row, memory decreases, but not to the 'state of the beginning'. The 'build up extra' is still there.

But still: It is not a problem here! It does not cause any destruction, does not slow down program etc. And memory is freed when needed by another program. (however Swap-file used is not)

Atttached show Task Manager after some 'moving around' (first>>second)and nothing else

[vygi]

peterlaursen wrote on Dec 30 2005, 10:50 PM:

About this Graphics System: how does it use memory? 

I guess it uses 'shared memory' but allocated dynamically or statically?

Is there some kind of Video-shadowing set active in BIOS?

My miniPC has shared memory (too), but a 'fixed memory pool' is allocated at system startup.

I think I understood that you have tested at work.  Do you have a chance to test with another PC at home or somewhere else?  And maybe try another driver for the Graphics card?

well… first I have tested at work, some on-board Intel graphics adapter with shared memory… and SQLyog was taking more and more RAM when moving/resizing window; even at the very beginning, fresh started, without any queries!!

Then here at home I have anothe PC with some REAL video card, nVidia 6600, real video memory, nothing shared. And SQLyog here takes more memory only when some query result is loaded, and only when Result tab is visible. Just like your system, I assume???…

So the issue seems to be always here, just sometimes hard to see.

BTW this 'destruction' phenomenon — it is reproducible on my PC here at home, but it needs almost whole minute of permanent window resizing and moving:

1. run some query and make result tab visible

2. restore (unmaximize) SQLyog and make its window smaller than fullscreen (60% or so)

3. grab the top of the window and slowly (1cm/s) change its size several times, 8-12 seconds long up and down…..

4. now release the window and move it a little bit (few pixels) to the left or to the right;

5. repeat steps 3 and 4 several timesn, and after some attempt to move (step 4), SQLyog “explodes” and starts to paint black and white (and gray) all around.

[peterlaursen]

I think we agree!!

This additional memory manager that I have starts freeing memoy when there is less than 25% free. And it frees the excess memory consumed by SQLyog. So that could be the reason that I don't see the destruction.

[peterlaursen]

This is a recent phenomenon!

I have SQLyog 4.1 and 4.2 RC1 installed as well!

With 4.1 I can't reproduce ANY of the 'memory-build-up' phenomenon's

With 4.2 RC1 it is similar to 5.x.

That is so for both of my systems.

This observation of vygi's

Quote:

… and only when Result tab is visible

is important I believe. There is a very-very-very-small build up when DATA-tab is active (a few KB's for each move), but it is MUCH MORE when RESULT-tab is active. And the more data that is stored with the RESULT-buffer the faster it builds up!

But still vygi does not experience that difference with his PC at office.

I still believe that it is related to the implemation of the graphics buffer – simply moving an empty SQLyog around makes memory use increase on some systems and not on others.

BTW: it is reproducable with Linux/Wine too! It is here the 'wine-preloader' proces that grows!

I also now can reproduce the 'destruction' – but only on my Athlon/Nvidia PC, not on my 'mini' that has a highly integrated MINI-ITX Motherboard with all VIA components. See attached. Also note the high memory consumption. The initial SELECT resulted in usage of about 20 MB physical and 20 MB virtual memory. The rise is reproduced only by 'moving around' the program window.

[carltond]

peterlaursen wrote on Dec 30 2005, 04:07 PM:

@carltond: what is your Graphics?

[post=”8326″]<{POST_SNAPBACK}>[/post]

From what I can see graphics card is…

Intel ® 82915G Express Chipset Controller, 128MB…it's a work PC so pretty crap card I think.

CPU is 3.2 Pentium 4 though with 1GB RAM which is pretty good.

[peterlaursen]

That is a typical office PC. Just as Vygi's at work.

Tomorrow Ri Coder gets back from the palmy beachs of Goa to the dusty offices of Bangalore, so things are going to happen again 😀

There is no doubt any more that some issue with memory management was introduced with 4.2 Betas. Most surprising that so much time had to pass before anyone noticed.

[carltond]

Well let's just hope the problem (as minor as it is really) can be fixed…great program though! Thumbs up so far

[Ritesh]

OK, we are back in action 😀

We are now working on a similar memory related issue while importing millions of rows of data using Migration Toolkit. Once fixed, we will start working on this one.

Rest assured, 5.02 FINAL will have the bug fixed.

[carltond]

Ritesh wrote on Jan 4 2006, 02:21 PM:

OK, we are back in action  😀

We are now working on a similar memory related issue while importing millions of data using Migration Toolkit. Once fixed, we will start working on this one.

Rest assured, 5.02 FINAL will have the bug fixed.

[post=”8361″]<{POST_SNAPBACK}>[/post]

That's nice to hear…thanks 😀

[Ritesh]

@vygi, @peterlaursen, @carltond – You have a PM!

[vygi]

Ritesh wrote on Jan 5 2006, 04:23 PM:

@vygi, @peterlaursen, @carltond – You have a PM!

[post=”8374″]<{POST_SNAPBACK}>[/post]

@Ritesh: you have one as well 😛

[carltond]

vygi wrote on Jan 5 2006, 04:04 PM:

@Ritesh: you have one as well  😛

[post=”8375″]<{POST_SNAPBACK}>[/post]

Yep one from me too…It was asking me to reg the program unfortunately 😮

[peterlaursen]

I now tested the (non-public) beta2 on two different Windows and one linux/WINE.

There is no memory build-up.

I aso don't have this request for re-registration on neither of these 3 systems.

[carltond]

I can also confirm that the enterprise edition that I am using no longer causes an increase in memory when I resize the application window repeatedly…the memory usage stays at around 6000K-8000K even if I repeat the query across differet tabs.

Well done 😀

[Ritesh]

Cool so it works for everybody 🙂

[Author]

Posts