Hi, Tiger.
On 12/22/06, Tiger Quimpo <[EMAIL PROTECTED]> wrote:
On Fri, 2006-12-22 at 16:49 +0800, Jun Martin wrote:
> Now can you tell me why selecting data from a few tables in a mysql
> database (being used daily by tellers posting payments and new bills
> getting posted by the thousands daily) usually takes 20 minutes but
> takes only 4 minutes when the mysql service has been restarted less than
> one (1) day prior to the query?
I'm not much of a mysql user, but:
1. what storage engine is in use there? are you using a
transactions capable storage engine or myIsam?
Almost 100% InnoDB.
2. mysql might run better after a restart for several reasons.
an obvious one, if it's been running for a while, there
will be many locks in place. there might be long running
No locks. We start the transfer ("select * into outfile ... from table1
where ...) after office hours so no one else is issuing any queries. The
tellers and "billers" have already stopped posting new records at this time.
if you're using a transactional storage engine, then your
isolation level might have an effect on speed (more
conservative isolation levels block more transactions).
Hmmm... gotta read up on that, sounds technically deep.
3. mysql might also have some sort of resource leaks that
slow it down, particularly in high transaction per second
scenarios. generally though, I would go with the first
law of debugging (i.e., "It's your bug"). It's much more
likely that the problem is in your environment or setup/config
than it is a problem in mysql. That's certainly possible,
but the probabilities are on the side of it being a problem
on your side rather than mysql's.
I don't really consider it a bug, I just wanna understand why a restart
speeds up the transfer right after restarting.
4. with databases that are many times larger than memory,
and if transactions are not localized (e.g., if some
programs read all over the table several times a day),
then the memory-cached rows for the most recent data inserted
or updated may be thrown out as the data from all over the disk
is loaded into memory/cache. That can affect performance
quite a bit. Understanding your usage patterns will help
a lot.
Now this, or something similar to it, just might be what I'm looking for. I
guess
5. How long after the restart are you measuring performance?
A few or several minutes after restart. Maybe it *is* #4 or related to it.
if you restart right in the middle of the busiest period, and
then test an hour after that, the performance then will give
a good idea of whether or not the problem is in mysql or
if it's just due to the load at the time (of course, top and
vmstat can give you hints about that too, if top and vmstat
activity are low and you're still getting 20 minute performance,
then it's a mysql problem, otherwise, the load is just high
at that point).
I'll take note of these excellent suggestions. Thank you very much!
- Jun
_________________________________________________
Philippine Linux Users' Group (PLUG) Mailing List
[email protected] (#PLUG @ irc.free.net.ph)
Read the Guidelines: http://linux.org.ph/lists
Searchable Archives: http://archives.free.net.ph
