List:General Discussion« Previous MessageNext Message »
From:Johan De Meersman Date:July 7 2014 11:49am
Subject:Re: Doubts tuning MySQL Percona Server 5.5
View as plain text  
----- Original Message -----

> From: "Antonio Fernández Pérez" <antoniofernandez@stripped>
> 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. 

Thread
Doubts tuning MySQL Percona Server 5.5Antonio Fernández Pérez4 Jul 2014
  • Re: Doubts tuning MySQL Percona Server 5.5Johan De Meersman5 Jul 2014
    • Re: Doubts tuning MySQL Percona Server 5.5Antonio Fernández Pérez7 Jul 2014
      • Re: Doubts tuning MySQL Percona Server 5.5Johan De Meersman7 Jul 2014