It's been a time, and what little dev I do these days is much more on the DB side of things... and I am a little drunk. So I am liable to ramble, and will quite probably be talking rubbish, but here goes
Firstly it looks like you are opening the connection elsewhere and keeping it open...
MySqlCommand cmd = new MySqlCommand("SELECT " + type + ", Gallery.Type, ChangeLog.Time FROM Gallery LEFT JOIN ChangeLog ON ChangeLog.Item=Gallery.ID AND ChangeLog.Chart='Gallery' WHERE Gallery.ID=?ID", Con);
or simplified
MySqlCommand cmd = new MySqlCommand("<sql stuff>", Con);
Con is your SQL connection and was presumably opened elsewhere with something like
MysqlConnection con = new MySqlConnection(<Connection String>);
and presumably
con.Open();
This is a dodgy way of doing things, and it would be much better to open the SQL connection just before executing the command
Con.Open();
using (MySqlDataReader reader = cmd.ExecuteReader())
and then close it at the end with
Con.Close();
This would free up the connections when they are no longer needed, instead of waiting for garbage collection to get them. The downside of this, is that you would need to check where else that con object is used and change that all that code similarly. (On a really really petty point, for which I apologies... it is normally called
Conn not
Con!)
One thing you could do easily is to close your datareader and two memory streams by changing your code to something like:
data = mstream.GetBuffer();
mstream.Close();
}
stream.Close();
}
Response.ContentType = micro || small ? "image/jpeg" : (string)reader[1];
if (reader[2] is DateTime && !original) Response.Cache.SetLastModified((DateTime)reader[2]-(DateTime.UtcNow - DateTime.Now));
Response.OutputStream.Write(data, 0, data.Length);
}
else
Response.StatusCode = 404;
}
}
reader.Close();
}
That should free up some memory I guess, but don't know how much
The other thing which is also worth looking at is the DB connection pooling. If you look at your connection string for
Con see if it has
Pooling = False or
Pooling = No If it does change it, and if it doesn't add it so you have
Pooling = True
That should force connection pooling on if it isn't already. Causing the connections to be reused, instead of spawning loads of new DB connections. (For a simple query opening the connection can often take more resource and time that running the query itself.)
As I mentioned earlier the
Con connection must be opened elsewhere, it is also worth checking if it closed elsewhere and in particular if you have a couple of line like:
Con.Close();
Con.Dispose();
Closing is good and we definitely want that, but disposing of it can under some circumtances mess up connection pooling, by stoping the pool manager from recognising connections with the same ID. Worth a look.
As to why you've suddenly got this problem? Might be worth asking your host if they recreated any ODBC connections if you connect to MYsql via one? Have they upgraded any .Net, ADO, MySQl recently? Applied any MS updates? etc... I'm assuming not as I'm sure they would have said, but it is worth asking the question.
Or could it be as someone else suggested all the new VU pictures, causing a DB table to suddenly grow?
If it is a DB performance issue I can probably be a lot more help than I have been with the code, but you need to get me the DB schema somehow.
Bla! enough witterings from me...
Peatie
