20、 Obama
B1 中級 他 5857 タグ追加 保存
このエントリーをはてなブックマークに追加
動画の字幕をクリックしてすぐ単語の意味を調べられます！
単語帳読み込み中…
字幕の修正報告
>> Okay. Hi, guys. So this is Jay Pipes. He's a MySQL employee. He's in charge of North
American Community Relations. I met him this week at the MySQL User Conference which is
down in Sta. Clara. Now, the deal is that we're going to host this on Google video.
So if you have confidential questions, if you could leave them till the cameras are
turned off, that would be cool. With that, no more ado, Jay Pipes.
>> PIPES: All right. Nice to meet all of you. I'm going to be giving a talk that I gave
on, I think, Tuesday or Wednesday, on "Performance Tuning Best Practices for MySQL." Some of
the things are version specific but for the most part, it's pretty general. We're going
to be covering Profiling and Benchmarking Core Concepts, Sources of Problems that you
might encounter, Index Guidelines, Schema Guidelines, Coding Techniques, and lastly,
we'll go over some general things about tuning server parameters. Since the audience is,
I think, sort of mixed levels of experience, we'll just shout out questions or tell me
if you want some grand stuff or so. All right, Benchmarking Concepts. The general idea behind
benchmarking MySQL is to, obviously, have a track record. You know, when you change
things in your code, in your application, or the configuration files with MySQL, or
your schema design, or you're indexing, you want to make sure you have a track record
of what you're doing. Because a lot of times, if you--if you don't keep a track record and
don't do benchmarking, you won't really know whether a specific change that you made had
a detrimental impact or whether it actually increased performance. So, the baseline--and
you always want to have your baseline for your starting point. And always give yourself
a target. Don't just do it so it's a moving start--moving target, you always want to have
an end goal, you know. You either want to get, you know, 20% increase in performance.
So that's your goal. But just don't have it, you know, just kind of end with nothing because
then you'll just spend years and years and years trying to, you know, tune milliseconds
out of your--out of your application when there's really no benefit to it. So, change
only one thing at a time. A pretty basic concept but it really--it goes a long way in benchmarking.
If you change two things and you rerun a test, how do you know which one of those two things
actually affected the difference between the two tests? So, always change one thing at
a time and rerun the benchmarks. And record everything, configuration files, information
on the hardware, information on obviously the operating system, the version of MySQL,
and everything to do with the benchmarks. Keep it in a separate folder and keep everything
for historical record. A lot of times, you'll think that you won't need information when
you're running benchmarks. And then, six months later, you realize that, "Wow! It would be
really useful if I had--oh, I threw it away". So, always keep information from your benchmarks
so you have a historical record of what you did. And specifically with MySQL, you want
to disable the query cache whenever you're running benchmarks. The reason is it will
skew the results that you get from read-intensive queries and write-intensive queries as well.
So, disable the query cache by setting the query cache size equal to zero when you--when
you run benchmarks, so. Prof… Yes. Go ahead. >> [INDISTINCT] benchmarking for [INDISTINCT]
problem with the operating system, caches [INDISTINCT]?
>> PIPES: Right. Yes. The question was--what you were experiencing that the operating system
caching was skewing the results of the benchmark. Generally, the idea is either you can disable
caching if it's actually getting in the way or if you bump up the actual number of runs
in the benchmark tests you can minimize the skew effect. So that's usually what we recommend.
But, when you get into specific caches there's very specific things to do. So it kind of
depend on the exact scenario that you're doing, so. But, yes. Bumping up the number of runs
in the benchmarks generally tends to mitigate a lot of that, so. Does that answer your question?
Okay. Profiling Concepts, profiling is a little different than benchmarking. Generally, you're
profiling a production or running a test system that you're trying to figure out, you know,
diagnosing what's going on while it's running. So, instead of doing--you know finding the
stress or the load that the system can take, you're actually trying to pinpoint where the
bottlenecks are in the code while it's running. With MySQL, the best tool that you have is
the EXPLAIN command. So, regardless of what version of MySQL you are running, you really
want to get familiar with everything in the EXPLAIN command. Understand what the access
types, does everyone know what the EXPLAIN command is? Everyone use it? Okay. Does everyone
know what every single type of access type is? Okay. It's sort of a poorly named column
in the--in the EXPLAIN results. But, it actually describes the path or the optimization pattern
at which the optimizer's saying, "Okay well, I'm going to, you know, access, you know,
these results at and join it to these results at." And each of those things, the ref, the
refrenol, the equaf range, all the different access types mean different things. And the
performance that you get from each of them, you can go into MySQL manual, depending on
your version. You can see which ones have a better performance for different types of
queries. It's not always the same. So, you want to make sure you understand those. Understanding
obviously that if you see all in the access type, it means it's doing a full table scan.
You know just little things like that. Be aware of what they all mean and what the performance
impact of the different ones are. So, by using the Slow Query Log and mysqldumpslow to parse
the query log, very easy to set up, Log Slow Queries and then you give it a long query
time, anything above that, it will log to the--to the Slow Query Log. There's also--I
think in 4.1, we put it an option that you can--anything that--any table, any select
statement that does not use an index on a table can get logged to the Slow Query Log
regardless of how fast it runs. And that's--and that's useful for when you're doing an indexing
strategy and you're trying to determine okay my patterns have access, do I--do I have indexes
on those fields that I'm actually accessing? You know, and my where clause and ON clause
and that kind of thing. So, low hanging fruit law of diminishing returns tackles stuff that's
going to get you the absolute best return on investment for your time. Don't concentrate
on, you know, I know you guys are Google. So, a millisecond definitely does count. But,
if you got a--if you got a choice of, you know, going from five milliseconds to four
milliseconds, you're going from a minute to, you know, a second, it's a no-brainer. You
know, concentrate on the stuff that--that's going to give you the biggest impact. Using
Mytop. Jeremy Zawodny wrote a nice little tool. Most of you are probably familiar with
the top utility in UNIX. It's almost identical but it's just for MySQL. And it gives you
an ability to on a delay--on a repeating delay, you know, show you the process list of what's
going on in MySQL, and show, you know, show various show commands, the status variables
and gives you a nice little interface to profile into the system while it's running. And one
nice thing about Mytop that I really like is that you can--you can use it for remote
hosts as well. So, you can--you can run it on your local machine and access a remote
host. So that it is a nice little tool. Okay. Sources of Problems. This is kind of my personal
opinion of where I rank the law of diminishing returns, like what I actually like go for
immediately. The performance team tends to think that, you know, sometimes you can--you
can tune a server parameter and that's like the biggest bang for the least, you know,
amount of time. But I think, really, where performance problems come in are the--are
the definitely the top three here. Poor Indexing Choices will kill an application faster than
really anything else. If you're querying on a field and you don't have an index on it
and you're repeatedly hitting that, it's going to kill the application faster than, you know,
whether sort buffer size can be adjusted. So, same thing with the Inefficient or Bloated
Schema Design, you know, I talked in the--in the performance talk. I've seen a number of
cases where people designing new applications, well, actually de-normalized from the beginning
because they think it's like going to give them better performance. Don't do that. You
know, there's specific cases where you can de-normalize the schema and you can get some
performance benefits. But don't do it right away. You know, just because you think it's
going to get, you know, a performance benefit. Use good, efficient, normalize schemas. And
we'll talk--we'll go into detail on this later. Bad coding practices. MySQL has some inefficiency
problems with sub-queries. But in general, using joins is a lot more efficient than using
sub-queries. We're going to go show some examples of specific where you can really get a performance
increase by just simply changing the way that you--the way that you code SQL. Server Variables
Not Tuned Properly, that's a source of problems. But in my opinion, very specific situations
can be--can be helped by tuning server parameters. It's not some catch-all, you know, it totally
depends on, you know, how much memory you have in the machine, what the architecture
is, what operating system you're running on, what version of MySQL, what storage engines
you're using, your, you know, the type of the application that you have, whether it's
OLTP or heavy reads, you know, all these things. Yes. Tuning server parameters can help but
it's very specific to certain situations. And it also requires retesting and rerunning.
But, you know, you tweak one thing, you have to re, you know, run the benchmark test to
see if--see if you got the performance gain out it. It can be time consuming to tweak
server variables whereas it's pretty easy to, you know, change some seq--some SQL statements
and immediately rerun it and see if you got a huge performance gain from doing that. And
finally, Hardware and--and Network Bottlenecks. I/O, CPU bound, network latency. Right. There
is a good book that I brought along that I recommend for that and when we get to the
last slide, I'll show you that, so. All right, Indexing Guidelines. A poor or missing index,
definitely the fastest way to kill an application, like I said. Look for covering index opportunities.
We're going to go into some slides here to kind of explain what that is. But the covering
index is--especially for my--who's using MyISAM versus InnoDB? InnoDB, raise hands. MyISAM,
raise hands. Nobody is using MyISAM? For a single thing, okay.
>> [INDISTINCT] >> PIPES: Say again?
>> [INDISTINCT] >> PIPES: Got you, okay. Well, we'll explain
some things about covering index opportunities that are very specific to InnoDB. But covering
index is basically what it means is that the MySQL can get all the information from the
index records which are by definition slimmer than the data records because they don't contain
all the fields in the table and use that information to complete whatever the query was without
having to go into the data records. One of the key concepts to this, the slimmer that
your index records are, the more that you can fit into a single index block and the
more that you can fit into an index block, the fewer reads you're going to do, the faster
your application's going to be. So, covering index opportunities definitely look around
for those. When you're deciding on which fields you actually want to index, make sure you
take a look at what the selectivity of the--of the field is. Selectivity is the cardinality
or the number of unique values that are contained in that field divided by the total number
of values in a table. So, obviously a unique index would be a cardinality of one. Because
it's, you know, unique over the total count. That's obviously the best-case scenario but
you can get situations where you've got awful selectivity. If you're querying just on that
index or just on that field you may get awful performance from that because--or may just
decide not to use the index because it ceases to be useful. I think generally like, [INDISTINCT]
could talk a little bit more about this but I think that it numbers about 30%. If your
optimizer sees that, okay you do--you do a query select, you know, start from whatever,
where column A equals two. And it will take a look and see an estimate from what the information
that has on the--on the index, well this particular query returned a third--you know, around 30%
or more of the rows in the table. If it does it's actually much more efficient to just
sequentially scan the table than it is to go into the index and randomly seek and grab
a bunch of records and then do lookups back to the data records. So, be aware of--of the
uniqueness of the values in your index. For lower selective fields were you--were you
don't have very much selectivity you can sometimes tack them on, you know, to a multicolumn index
so that, especially we grouped by queries and we'll show an example of this you get
some efficiencies there. But just be aware of the selectivity, so. On multicolumn indexes,
like those kind of explained just now, the order of the fields is very important. You
have to look at the queries that you're running against the database. You know, if you have
the columns mixed up or they're not optimal, the optimizer just won't use the index. And
I'll show you a perfect example of something that happens all the time with this on group
by queries. And as the database grows you want to ensure that the distribution is good.
You know if you set up an application, writing new application and, you know, you go through
a couple of months and you got a couple of hundred thousand records in there. Okay, great.
Everything looks great. The index strategy that you designed for it is wonderful but
that doesn't necessarily mean that in nine months, you know, you've got millions of records
in there that the index strategy that you initially picked out is going to be optimal,
you know. Make sure that you understand how your data changes over time, you know. If
business practices can change. And so, if you've got values, let say's you got seven
values in a--in a field, right. And you've got an index on that, seven distinct values
that you're putting into like a status field. Okay. And over time 90% of the field values
contain one specific thing, that index ceases to be as useful as it was when you initially
designed the application because 90% of the--the rows contain the same value. So just be aware
that as your database grows and your application changes that you need to reexamine, you know,
what you initially thought was a good index strategy. Okay. Remove redundant indexes for
faster write performance. Obviously, every index that you put on a field you get a, you
know, slight performance impact on writes because it's got to write an index record
as well as the data record. Every time you do an insert and every time you update. If,
you know, update the key value, it's got to write to the index. So unfortunately, MySQL
allows you to create redundant indexes on the exact same things. So, I've seen cases
where you get 40 indexes on the table and, you know, 10 of them are redundant. You know,
because over time teams of people work on it and no one wants to change other people's
stuff, you know. So, they just add their own indexes and with different names and all that
but, they're redundant. They're on the same field. So, you know, as time goes by make
sure you check, remove redundant indexes to get some write performance. Okay. Common Index
Problem here. Kind of a web 2.0 example. You got a--you got a tag's table which got tag
ID and a text for the tag and a products table. Both of these tables have an auto-incrementing
primary key and then you've got a relationship table that stores the many to many relationship
between products and tags. And if you noticed that the primary key on these Products2Tags
table is product ID--tag ID. Makes sense? Pretty basic? Okay. Here's two examples of
pretty common queries run on this Products2Tags table. Only the top one's going to use the
index on it. And the reason is because of the order of the columns in the index. On
the top one, you're grouping by the--what's essentially the product ID. And so, it's going
to be able to take that left side of the index and use it in order to count the right side
value which is the tag ID. Now in the bottom you're reversing that. You're saying for each
distinct tag give me the--give me the count of products. You can't use the index. You
can't use that primary key index. So, you won't use it, you get no read performance
from it. The remedy would be to create an index that has tag on the left side. Okay.
Now, there's two examples here where it says create index, the--the top example, create
index, I X'd tag on just the tag ID for all you guys using InnoDB, that's exactly what
you'd want to do. From MyISAM, you'd want to do the bottom one to create a covering
index opportunity. Does anyone know why the top one should be used for InnoDB and not
the bottom one? What's the difference between the secondary indexes in InnoDB versus MyISAM?
>> Because it went back to the primary key. >> PIPES: Exactly. For it--it--because it's
a clustered index organization in InnoDB, every time you add an index on a field, that's
not the primary--every non-primary key index, the primary key is actually appended to every
single index record. So, if you've got a primary key product ID, tag ID and you add an index
on tag ID, product ID you're being redundant because you've already got the product ID
actually in the index record in InnoDB. So, it's important to understand what the difference
between the cluster organization and the MyISAM which is non-clustered where it's just a record
number that's attached to each index record instead of the clustering key. So, be aware
of those differences. So, I was talking about redundant indexes. That one on the bottom
would be redundant for InnoDB. Did the mic just change?
>> [INDISTINCT]. >> PIPES: Okay. Okay. Sorry. Schema Guidelines.
Inefficient schema, another great way to kill your application. Use the smallest data types
possible that you really need. If--if you don’t have four billion rows in the table,
don’t use big int. I mean, it’s just the basics, you know. Keep them as trim as possible,
you know, if you have maximum of a hundred values, don’t use int. Use tiny int, you
can feed, you know, 255 unique values in it. Don’t use char[200], you know, when a varchar
or you know, smaller char will do. Little things like this make a huge impact in performance.
And the reason is remember that every index record has got to contain, you know, that
width. So, if you've got an index on a char[40], every single records got 40 bytes in it. And
well, a little bit different because there's some index prefix compression that’s going
on but, you know, the--the general concept stays. If you have a index on first_name,
just index the first 10 characters of it or the first 15 characters of it. You fit much--many
more index records into a single key block and therefore you get fewer reads, faster
performance. When you've got many columns in a table, especially many nullable columns,
consider splitting the tables into two tables like a main and a detail table, something
like that. If you've got many nullable columns or columns that are rarely even accessed or
rarely searched on, put those in a separate table and join them on the one-to-one, you
know, relationship, okay? And the reason for that is you can pull many more of the main
records that you are actually searching on into memory. You get--especially, if you don’t
need the detail records of those fields, you can have a lot more opportunities to get more
performance from that. Consider vertically splitting many road tables using partitioning
or the MyISAM merge tables which--since you guys are using InnoDB, it doesn’t matter.
Partitioning is in 5.1, so, you guys are a little ways away from that. It's all right.
You can also do, you know, your own application level vertical, you know, partitioning where
you're naming different sets of tables and then using views on--the one person that was
using 5.0 you can use views in order to aggregate many tables into a single view. And always
remember that, you know, fewer reads has faster results. And this is--this is my--the limit
of my graphical ability right here. That's about it. It's just, you know, a little flow,
smaller data types placed in narrower index records. More records per block, fewer reads,
faster performance. It's just the general thing. How to get better performance from
your index, use schema. InnoDB, choose the smallest possible clustering key. Since like,
I just explained, it's appended to every single secondary index record. So, if you've got
a 12 byte primary key on your index in--on your InnoDB table, that's 12 bytes per index
record that are being appended every single time you insert in the table. So, be aware
of that. Don’t use surrogate keys when a naturally occurring one exists. We see this
all the time in the forums. And I often wonder what the use of it is and why people do it.
You've got a naturally occurring product ID, tag ID key. It's a naturally occurring primary
key. Yet a lot of people will just kind of put an auto-increment in there just for the
heck of it, you know, but you're never really going to be looking up by record ID. You will
always going to be looking up based on product ID or tag ID. So, some people call it a surrogate
key or a false key, get rid of them, they're just wasting space. Coding Guidelines, I tend
to call it chunky coding habits. Cutting everything down into the smallest possible units you
can. You know, thinking of SQL in terms of procedural programming is a bad idea. At least
to--well, correlated sub-queries and other things I hate. You want to--you want to be
an SQL programmer that thinks in sets. Don’t think in terms of for-loops, okay. Think in
terms of merging and intersecting sets of data. And you'll be able to cut it up into
chunks and you'll be much more efficient in the way when--in the way you code SQL. The
5.0 guy, use store procedures. If you're not using store procedures, you're missing out
on a performance increase in 5.0. InnoDB, how many of you using SELECT COUNT(*) on InnoDB
tables? I know they saw you. Okay. Don’t. InnoDB cannot optimize SELECT COUNT(*) queries,
okay? So, your table and your schema will not scale well, okay? The more records you
get in there, it requires actually reading through and counting all the records. Whereas
with MyISAM, it can actually get the count from the index. With InnoDB, it cannot. It
actually has to count all the values. And the reason for that is the way that InnoDB
implements what's called multi-version concurrency control. And there's a version so that the
row level locking can occur in InnoDB. There's a version for each row. So there's complexities
involved with actually counting, you know, the number of values because well--which version
are you--are you comparing and so use counter tables. So if you're doing, you know, select
counts to our products, have a separate table that stores the number of products and when
you insert into the product table, increment it, when you delete, decrement it. Okay. That’s
how to scale InnoDB for performance. We've seen this by a number of big form applications
that, you know, that they always do, you know, count the number of threads and then count--some--how
many users are logged in. This is a big problem when, you know, if people use--switch from
MyISAM to InnoDB without looking at the code, just simply switch the engines, you know,
it was a--yeah. >> Well, also consider you don’t actually
necessarily want to know the exact number of rows. Do you care if you've got a 1000
threads or 1100 threads? It really doesn’t matter. You want the ballpark figure. So even
if your numbers are not fully up-to-date, it doesn’t matter. If you want to run Chrome
job that does the InnoDB count once an hour, then put that values [INDISTINCT]. You could
put it in another table. You could put it in a file that can include into whatever page
is displayed, that works fine. Then reduce the problem to once an hour rather than every
time you run that query. >> PIPES: All right. Isolating indexes or--index
fields on one side of the equation, you know, your basic--your basic Math class, you want
to put the single on the one side and had your equation on the other, you know. Well,
we'll show an example of how to do this in a second. And using calculated fields when
they're necessary, you know. Let's go into the example of isolating index fields on one
side of the equation. Okay. Here we've got--whenever you apply a function to a field that’s indexed,
it--the itemizer can't use the index on, okay. So you want to look for opportunities where
you can rewrite SQL statements like on the top here into the exact same SQL statement
or the exact same result, but you’ve isolated the index field without any function operating
on it on, you know, the left or whatever, right. And just converted the actual statement
into a--just a different way of representing it, okay. Now, in this case, you're just taking
the two days and converting it into current date minus seven day, you know. Just make
sure you understand the greater than and equals all that, you know, so you're including that.
Now, what's wrong with the second part? How can we improve that with our performance?
Anyone know? >> [INDISTINCT]
>> PIPES: Say again? >> [INDISTINCT]
>> PIPES: Yes, well, yeah, basically that. The current date function is a non-deterministic
function and the problem with that is that the query cache won't cache this query. Well,
the thing about current date is, it only returns a different value once every what, 24 hours?
So if you're running this, you know, let's say a hundred thousand times in a day, instead
of running this once, it's going to run a hundred thousand times. You know, with the
query cache it actually puts the query into the cache stores the results and every time
you issue that exact same query, just takes it rather to query cache never even has to
hit the Storage Engine, the mySQLs, whatever. It just takes right in the query cache. So
in your Perl Scripts or your PHP scripts or whatever you're using to write, just insert
the date instead of using current date, okay. A lot of people don’t realize this, but
very good way to get performance out of your query cache. Okay, using calculated fields.
When you got [INDISTINCT] email address like present.com, index can't be used. The reason
is because you can't have a wildcard on the left side of a constant, comparing it with
like. If you put the wildcard on the right--on the end and you have a constant string and
then wildcard the index can be used because you can look at the left side of the values,
okay. So the way to do this is to do a little trickery and put a reversed email address
actually in your table, okay, with 5.0 you can use a trigger. It actually inserts the
reverse of the email address that you're inserting directly into that new reversed email address
field, okay. And then on the bottom, you can just do the like reversed CONCAT and now you
can--now you actually can use an index on that field. So that’s a way of using calculated
fields or, you know, fields that, you know store different values based on other fields
on the table to get performance out of something that you'd never be able to use an index on,
you know. >> [INDISTINCT]
>> PIPES: Yes. This is something, you know, if you have an application that's looking
up, you know, all the email addresses in your database that end it dotcom or aol.com or
whatever it is, you know, that's--you don’t want to be running that top one every single
time if someone request that. You want to be operating on a reversed email address and
using the index on it. Okay. Learn to use joins. Like I said earlier, eliminating correlated
subqueries is a great way to get performance out of your application currently. Like, Timour
who's going to be speaking in a little bit, is on the optimizer team and he's working
on subquery optimizations but there's, you know, there's just a better way of doing things
with the joins, okay? It's easier and it follows the set-based programming principles of SQL
instead of the procedural language principles that most of us are very familiar with, you
know. The for-loop, the operating on, you know, everything one at the time. So, let's
go to some examples here. Don’t try and outthink the optimizing team. The team works
pretty smart. You'll meet them in a little bit but he's really smart. You can give the
SQL server--the MySQL server in a join hint using like straight join but the problem with
that is while you may get some performance benefit if the optimizer currently can't figure
out the right path and I've only seen this in like one or two instances. Well, in two
months your data sets changes and the optimizer that you've now overwritten with your straight
join hint, now you may have the suboptimal plan for it and you forgot that you put straight
join in there and now you've got a suboptimal plan. So, if you are going to use join hint,
make sure you thoroughly document where you put it in the code and don’t forget about
it. Because as your data set changes, your optimizer could much more easily pick an optimal
path for the query execution. Okay. Eliminating correlated subqueries. With 4.01, it looked
like most of you are on 4.01. You could use subqueries. I tend to avoid them like the
plague. We see this a lot on the forms, this kind of thing. Most of you like advance SQL
programmers, you probably not do anything like this but we see it everyday. You just,
I mean, the thing is, to try and get the maximum sold price from an order items table for each
product. And the procedural programmer, you know, who's a pro coder doesn’t necessarily,
you know, isn't familiar with SQL, hasn’t been trained in set-based, you know, thinking.
This is a very natural way of doing something. Your saying, "Okay. I want my products table.
I'm selecting for my product's table. Okay. And I want each of those products." And for
each one, I want to get the maximum price that it was sold in the order items table.
You know, correlating on the product ID. It's a very natural way for a procedural programmer
to think of things, you know. But remember, it's for each product, I mean, that's the
way that people think about it. That's not the set-based way of doing things. On the
right is the set-based way. You're joining, you say, "Okay. I've got two sets of information.
I've got my products and I got my order items. Okay. And I want to transform these order
items by grouping them on a product id and then finding the maximum price." So, that’s
my set. The maximum price is for each product today. And then, I have my products set and
the maximum price is set and I join them. That--that actually is two operations, right?
Two conceptual operations, grouping and joining to the products. On the left, you are actually
doing a query for every single product so, it doesn’t scale to linear model. Actually,
you have a thousand products; you're doing a thousand sub queries to get the maximum
price from the order items table. You have a million, you do a million queries. You're
still doing two queries on the--on the right. So, think about how you can get rid of correlated
subqueries. This is one of the best ways to get better performance out of your SQL code.
Using derived tables, it might seem like a big mess but it’s actually more efficient
on the right. And it goes back to the same principle of how many queries are you actually
issuing? On the left, you've got a join from customers to orders and in the where clause,
you have a correlated subquery with a group by in it, okay? Not a group, with an aggregate.
So, you're saying, the customers join to the orders but I want a group by the company of
the customer, not the customer id. And in order date equals the maximum or the last
order date in the orders table where the customer's the same, it's correlated to your customer's
table C and then group by the company. Well, you're going to be doing a sub-query for this
select MAX--for each customer in the order's table. Now, on the right, we say, "Okay, are
these customers set?" And we're interjoining on what's called the derived table, a sub-query
in the FROM clause. That’s one set. We're taking the maximum or the last order date
for each customer, distinct customer ID in our order's table, that’s a set. And then
we're joining back to the order's table so there's three--three operations. Three conceptual
sets to get that result versus the non-scaling method of a correlated sub-query in the WHERE
clause which has to be executed for every single customer in the order's table. Okay.
All right. And this is my last slide and I tend to kind of scheme over server parameters
because it's--I'm very happy to talk specifics with anyone of you but to generalize about
server parameters, it's okay but there're very specific situations where they can help--where
you can get some definite performance boost. Be aware of which server parameters are global
versus what's per thread--per connection made to the database. Okay. For instance, key buffer
size. It's a global variable. Sort buffer size, it's per thread so be aware of that.
Don’t, you know, assume that sort buffer size is for the whole database and bump it
up because it's actually per thread. So make small changes, preferably a single change
to your configuration file, retest. See if you get a performance benefit from that specific,
you know, change. Often provide a quick solution but they can be temporary. Really--really,
the performance benefit is optimizing application, your indexes, your schema--your coding. Query
cache, it's off by default so the size is zero for your query cache so if you haven't
changed it, it's not on. You can do show status and they'll say query cache type--I think
it's query cache type on but there's no size to it so that actually is use by default.
So unless you turned it on, you're not using your query cache. If you're doing read-intensive
applications, like, how many are doing, like, lots and lots of writes versus lots and lots
of reads? Who has 90% reads or more? Okay. Do you have your query cache as turned on?
Who has the query cache turned on that they know they've added size to? One guy in the
back, all right. Two, all right, excellent. Okay. If you're doing read-intensive applications,
turn the query cache on. Why? Because it doesn’t have to execute the query. If it's the exact
same query and your dataset isn’t changing much, it just takes it directly from the query
cache. The query cache actually stores the results set. It doesn’t just, like, compile
a query and store the query so they can be re-executed. It actually stores the rows so
that it doesn’t actually have to do in either parsing or anything and the optimization.
It just sends the rows right back to the client. So if you're doing repeated queries like that,
turn the query cache on and give it some size the--in the configuration file, it's the query_cache_size,
set it to, whatever, 32 meg or 64 meg. You can play around with it. You can also do it
online. I think it's sets global query cache size equal something or other, it's in the
menu. So you can do it online or you can do it in your configuration file. Beware of--since
you don’t use MyISAM tables. How many of you think that you're not MyISAM at all? I
hope not. Okay. The MySQL's internal database is in MyISAM. Okay. So that’s why if you
do show status, you'll see, actually, that you have MyISAM operations going on. So even
if all your tables are, InnoDB, the MySQL internal database is MyISAM, so you still
need to put, you know, something in there. So leave the defaults for key buffer size
there so the MySQL database can, you know, live happily. But the InnoDB buffer pool size,
that’s--that’s your main one for InnoDB that determines how many--how much memory
is allocated for storing date and index pages in InnoDB. I think Hecky recommends about
50 to 80% of your total memory on the box should be allocated to InnoDB buffer size.
Okay. Other important InnoDB stuff is InnoDB log, final size and log buffer size. I think
he recommends that the log--I think the log buffer size maxes out for a gig but you can
adjust it depending on, you know, what's your situation is and I can take some questions.
Oh, yeah, memory is the cheapest, fastest, easiest way to get better performance out
of MySQL. Don't even ask your boss. Just stick another piece of RAM in there. It really is.
MySQL just loves RAM, you know. The more RAM you give it, it's like Pac-Man it just eats
the stuff up. It loves it. So, that's the easiest and cheapest way to get better performance
from a hardware situation. Yeah? >> you talked about
>> PIPES: Okay. The question was if you've got a logical key in a character field, right?
Say for instance--I don’t know, Social Security number, something like that. Is there a benefit
to making a--what you're calling a surrogate key out of an auto incrementing ID? There
is a benefit to that, a big benefit to that. Depending on the--especially like if you have,
you know, a CHAR 4 versus an INT you're probably not going to see much of performance difference.
If you got, you know, CHAR 8, CHAR 16, that--that's, you know, your product ID or your record ID
or whatever it is versus an auto incrementing key. You're going to get a lot better performance
from the numeric data type in four bytes. It's optimized for that, so.
>> Why do you go up to [INDISTINCT] >> PIPES: Okay. There's that string data type
that we need. Just--you mean versus like VARCHAR 4,000 or something versus text or whatever,
there's some--maybe [INDISTINCT] can talk to that. I don't know--I don’t know if there's
a specific, you know, how much performance benefit you get from doing VARCHAR versus
text. I think in InnoDB, there maybe something but…
>> Yeah. So the question is there a benefit to using CHAR rather a VARCHAR in certain
cases? >> PIPES: Or text versus VARCHAR.
>> Or text versus VARCHAR. In MyISAM, text fields actually store it separately, text
in blobs. The actual row contains a pointer to where the text field is. In case of a CHAR
or VARCHAR it's part of the row. In InnoDB, same up to a point. In certain cases you would
just find everything in the row depends on which version and so on. InnoDB itself doesn’t
care where the things are CHAR or VARCHAR. It's always internally uses VARCHAR basically.
So, in InnoDB you don't need to make that distinction. Okay. I want to optimize for
fixed length fields, that kind of stuff. It doesn't care about that. MyISAM would care
so that's the basic story. Does that answer your question? Okay.
>> PIPES: Any other questions? No one? All right. Well, thanks guys. Thanks for letting
us come here.
コツ：単語をクリックしてすぐ意味を調べられます！

読み込み中…

B1 中級 他

Performance Tuning Best Practices for MySQL

5857 タグ追加 保存
このエントリーをはてなブックマークに追加
陳柏霖 2015 年 2 月 13 日 に公開

お気に入り

loading

コメント

読み込み中…
お勧め動画

  1. 1. クリック一つで単語を検索

    右側のスプリクトの単語をクリックするだけで即座に意味が検索できます。

  2. 2. リピート機能

    クリックするだけで同じフレーズを何回もリピート可能！

  3. 3. ショートカット

    キーボードショートカットを使うことによって勉強の効率を上げることが出来ます。

  4. 4. 字幕の表示/非表示

    日・英のボタンをクリックすることで自由に字幕のオンオフを切り替えられます。

  5. 5. 動画をブログ等でシェア

    コードを貼り付けてVoiceTubeの動画再生プレーヤーをブログ等でシェアすることが出来ます！

  6. 6. 全画面再生

    左側の矢印をクリックすることで全画面で再生できるようになります。

  1. クイズ付き動画

    リスニングクイズに挑戦！

  1. クリックしてメモを表示

  1. UrbanDictionary 俚語字典整合查詢。一般字典查詢不到你滿意的解譯，不妨使用「俚語字典」，或許會讓你有滿意的答案喔