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?
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
multi-stage "transactions" that are holding locks which
are blocking other users.
after a restart, all locks go away, so things which were
blocked (e.g., the kinds of selects which run for 20 minutes
or 4 minutes and hit a lot of rows) won't be blocked by those
transactions anymore.
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).
I'm not deeply familiar with the internals of innodb or
berkeley DB (i'm a postgresql user, so i'm much more
familiar with postgresql's MVCC), but if you can provide
more detail about your environment, someone who knows more
about mysql might be able to point at opportunities for
optimization.
if you're using myisam, mysql will often take rowlocks or
table locks. those are typically short-lived, but in
very high transactions per second environments, locks on
crucial shared resources (e.g., sequence numbers) can
affect performance significantly.
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.
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.
5. How long after the restart are you measuring performance?
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).
tiger
--
Gerald Timothy Quimpo [EMAIL PROTECTED]
Business Systems Development, KFC/Mr Donut/Ramcar
Lisp and Scheme don't have built-in reflection, lack many important
libraries, and they look like oatmeal.
-- http://www.cabochon.com/~stevey/blog-rants/duck-season.html
_________________________________________________
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
