Transcripción
Extracto de la transcripción automática del vídeo realizada por YouTube.
when you assess key good morning if you are coming to see a SQL expert this is not a right session I'm I'm not an expert myself of mysql and but i have been working with mysql expert in the past among which the people who have been writing the optimizer
of mysql to optimize community builder for large sites we had a customer who was running a radio show in New York with the morning talk where they representing one lady young lady every morning and when the presenter was saying the URL where people could go
and look at the profile of that lady and her friends using community builder you could count up to five seconds and then you had thousands and tens of thousands of hits on that website at the same time and even busy largest multiprocessor web server the web
server was going down so it was very well known in New York that it was so crowded that the web service in sustained so they decided to cure that and contact at us and contacted and put us in relation also with the actual address of MySQL so in that process
we learned quite a lot and I would like to bring a few of those simple ideas in an understandable fashion here so first thing that you need to understand if a slide is slow if it's PHP problem or a SQL problem I'm going only to treat the SQL problems
here and to illustrate that Nick and may have been building a dramatic read one that one stress test site with lots of articles categories half a million users to see better browser problem sir if you have a small site your data bars and your query doesn't
matter how it is written because it's a small data set so it's quickly cleared whatever you almost whatever you do so you need to be able to do stress testing and testing if your queries are good or not you need to have a very large data sets building
such a data set is not easy for instance I tried to build lots of tags to to test the new tags and it works for copying rad mess adding one her tags but I wanted to add 100,000 texts so that the jamba didn't allow that so we had a script from a language
did that okay before I go and do the practical work I will explain a few things and I think it broke the record set by new number yesterday I have not three slides I have zero slides so we'll be doing it here he's not there okay and Europe okay great
also one thing that is good to know is that MySQL has to database main database engines one of them is minus M and the other one is in 0 DB you probably know that my assam had this limitation of 60 four kilobytes per row and for instance evil Charles were
counting there I'm going now to more focus on in ODB and I'm going to focus to the more modern versions of MySQL 555 dot six mainly and if you're going to do some optimizations and do testing i recommend mysql five dot six which has quite some
profiling is quite some database performance queries that have been added it also has quite a few very nice optimizations in the performance of the engine you know DB which make me believe that for economical reasons hostess will be picking up that faster
than any other mysql release because they can have more sites per server so it's an economical reason there i also hope that today will be going to save a few power plants and the planet bit because slow queries means more processing mill means less sites
/ web server and when you optimize that we optimized also the power needed by each juma web page okay um sorry I think it's recording itself Thanks okay um so if you very basics about a mile SQL table and how things are so this is more beginners session
than an expert session as I said in the beginning so in my SQL table usually have rows and you have comments sometimes you have a primary key and ID which usually out to increments and usually the number it can be something else and then you have order integer
text bar charts floats and so on types of Collins one big difference from mean OD be compared to minus ASM is that you know DB will be storing the whole role in a cash each time so to access that it will be much faster I'm also going to concentrate on
lower and servers not on higher end servers which have enough memory and the mysql service is perfectly configured because that's most what most of the sites are using it's just default configured mysql database isn't just over like like it's
there so basically the when everything is in memory things go much better than if there's not enough memory configured but a service is well configured you would have ram which matches all your tables that you're using usually so that everything is
memory and you don't have any disk accesses but that's not the case and even worse as a default number of open tables in mysql is like 60 so not not even one drummer site can have all its table open means it closes some files to open other files and
that's just hitting the performance very much z so that's that's for the data then concentrating now on you know DB only each entry of see table will be one entry memory indexed by the primary key now if a primary key is the ID everything will
be indexed by that ID and now if you do a query for instance select star from that table where text equals ABC what will it be having in reality is that as we are looking the very on the text column here we will be having the MySQL engine which will be scanning
the whole table so if you have 1 million rows the processor will be scanning 1 million rows one after the other to check which one has ABC there is no magic they're just processing power even worse if your table has 1 million rows taking 200 megabytes
and your buffer memory size is 2 megabytes if you'll be reading all those tables and you know DB stores in something like 64 8 or 64 megabytes tranqs c 0 so it will be each time reading again from the disk to get the next set of rows and that can be very
slow so to to to optimize that you can use indexes and typically if you create an index on your database on the next row then what will be happening is that MySQL will be creating another table which does a correspondence between texts and the primary key
and that table will be sorted by in the text here and will be not stored as I like a stupid table like that with a degree by default stored like in a b-tree fashion which means that instead of scabbit having to scan this it will be having binary be sections
and then within just a few disk accesses or memory accesses find the right entry which corresponds to death row so we save say where text equals ABC it will be going ok I come here this is starting with ABC starting with m so I go here I'm simplifying
here this is so starting with a you so I'm going here here and I find something which is quite as the beginnings of the table and that way instead of scanning million of row it will be just doing one two three four five six seven accesses but we'll
still doing that and if you be doing at least two accesses if you have five or six entries on me sometimes adding an index on a table that you know that it's very small can actually decrease your performance instead of increasing it but if you don't
know if your table can be small or large it's better to add the index and do it that way mysql five dots x6 has a new feature which is that it can in some cases based on statistics that it does out create some indexes itself so that you don't have
to tell it but this happens on large databases with a lot of accesses and typically if you want to do a test on a search a database and have those index is created we need to do a voice up of the database like making queries for half an hour one hour intensively
[ ... ]
Nota: se han omitido las otras 3.818 palabras de la transcripción completa para cumplir con las normas de «uso razonable» de YouTube.