- Original Message -
> From: "Antonio Fernández Pérez"
> Subject: Re: Doubts tuning MySQL Percona Server 5.5
> I was checking MySQL performance ... Sometimes my database could be working
> slow. I have some queries that spend 9-10 seconds updating some columns by
> primary key. I'm not sure if is a data base problem ...
If the same query is sometimes OK and sometimes not, that's usually a
consequence of varying load, or possibly rushes on various resources. Those are
typically things that are, honestly, rather hard to figure out over email. The
Percona boys have some rather good blog posts and tutorials that may be of
interest.
Do an explain of the naughty queries, if the explain comes up good, there's
going to be an underlying cause.
> Moreover, I have checked tuning scripts and appear these variables.
Tuning scripts are a good first look, but they're just stupid little things,
they have no idea about the baseline performance for your environment. They
also mostly don't look at performance over time, they just see an average from
start of server until now - which is obviously mostly useless if you have
several months of uptime.
> InnoDB log waits is 103; innodb_log_buffer_size is 8M --> Maybe the next
> best value could be 16M?
8M is not a bad value, but you'd have to have a look at how much logging you
push to disk, and how fast that goes. Incidentally, as this is a theme in your
questions, you don't always have to double the values :-)
> Table cache hit rate is 12%; Open_tables is 627; table_open_cache is 1024
Well, the table cache isn't full, so I suspect not. Keep an eye on
Opened_tables - that tells you how many tables have been opened since service
start, so it shouldn't increase dramatically once it's up to speed.
> Key buffer hit rate is 93.7%; I have some queries that not using indexes ..
I keep coming back to the same question: why do you think that's a bad number?
:-)
Have a look at wether you can add indexes or otherwise optimise those queries,
but it's perfectly possible and acceptable if that's not possible - maybe it's
possible to offload the "hard" queries to a separate slave? Optimise in
function of your environment. Only your can define what constitutes acceptable
performance in your environment.
> join_buffer_size is 4M --> Next best value? Maybe 8M and then check it again?
I strongly recommend not touching those at all - oftentimes, those kind of
variables either don't do quite what you think at first glance, or are part of
a more complex system.
Specifically for the join_buffer_size, note that:
* it is the MINIMUM that gets allocated, wether or not it's needed
* it is not allocated per-session, but PER-JOIN, so a single complex query
may allocate several.
So, that means that even the smallest query that needs a join buffer will
allocate 4M, and while it depends on your environment, it's very probably that
you have more small queries than large ones :-)
Keep it small by default, and if you know a query is going to need big buffers,
you can still set it larger as a session variable when you need it.
The best advice I can give you is to set up Munin, Cacti or another tool to
monitor server status and performance over time; that way you will get a
baseline for what's "normal"; see any behaviour that deviates from the
baseline, *and* can meaningfully see the impact over time of any changes you
make.
/johan
--
Unhappiness is discouraged and will be corrected with kitten pictures.
