You can find below the slides of the MySQL Connect 2013, enjoy!
(and much more in the slides page)
|Showing entries 1 to 30 of 30|
I was sincerely affected by this last MySQL post and this other very long post from Jeremy Cole.
Yes, these two guys are MySQL rock stars and they are really impressives, their involvement in the MySQL community is utter!
I don’t want to write a long long speech about my simple life…
I just want to clarify why I do what I do.
Many people have asked or wondered without asking why I do what I do (Jeremy Cole - 2013)
A few years ago Ashley Unitt asked me what I was most proud of, and now, I can make a complete answer. I’m very proud to take part of a community, MySQL has transformed my job into a passion and an incredible desire to share this[Read more...]
A few years ago, I asked to check with me in the long (very long) change history of MySQL 5.5 documentation what are the changes in relation to the SQL syntax.
Chris Calender helped me to retrieve a list of the main changes, thanks again Chris.
Today, I would like to share this list with you.
It is simply a curated transcript of what you might find in the documentation but I’m sure it can help some of you.
Previously, the parser accepted an INTO clause in nested SELECT statements, which is invalid because such statements must return their results to the outer context. As of MySQL 5.5.3, this syntax is no longer permitted and statements that use it must be changed.
Did you see this when you are migrating from your lovely MySQL 5.1 to MySQL 5.5?
Oh, sorry, you remain attached to your pretty 4.1. Yes, I know, MyISAM has become so important in your life…
Ok, seriously, I would like to share this little observation I made recently when switching to MySQL 5.5 on one slave.
You can see below two graphs for the transactional activity, there is exactly the same volume of update, delete and insert queries :
MySQL 5.1[Read more...]
As a former Oracle DBA, I know how the system and the database are linked.
The first one who told me that the installation of Oracle has never been a problem is a liar!
Yes, your database and your system are the best of friends, you must respect that.
I’d make a list of linux system settings to configure a MySQL databases server and share my sources with you.
In return, I would like you to share your sources with the community by publishing your tips in the comments.
In my job, I do a lot of testing. And no matter how much organized we try to be, we end up with fewer machines than we would need to run all the tests that we want.
For some tasks, we can run MySQL Sandbox, and get the job done. But sometimes we need to make sure that applications and systems work well across the network, and we need to install and run systems on separate servers.
However, when you test replication systems, and every cluster takes three or four servers, you run our of available hosts very quickly. So you decide to use the clusters that are dedicated to automated testing to also run your own manual tests. Soon you realize that the tests that you are running manually are clashing with the automated ones, or with the ones that your colleagues are running.
A simple solution[Read more...]
I’m sure that many of you use the magic query from Shlomi.
This query retrieves the difference between two status values with a single query.
I’m really fan of this kind of query and about this query in particular. This query is very useful to retrieve an information without to have to store a middle result.
Now, I tried this query on a MariaDB 5.5 server and I had a surprise :
The result was a bit strange, with negative or null values.
I’ve just had these server for few hours in my hands and I had no more time to analyze the situation.
I assume that
A beautiful picture is sometimes better than words :
With standard HDDs (here in RAID 10), the innodb_io_capacity variable may have non expected effects.
You can see on this picture the result of a test on one of my server with only the replication thread activated. The value of the innodb_io_capacity variable was the single modification during the period.
So, don’t touch this parameter without a strong reason…
(Of course 30000 was a bit snooty)
[MAJ 2013-01-08] : I know 30k is a too high value for innodb_io_capacity, I used this value to make the graphe really eloquent.
Read this excellent post from Chris for more details :
I wish you all my best wishes for this new year.
I hope 2013 will be as exciting as 2012 for the MySQL community.
Thank you for spending time on MySQL[Plus] in 2012, I hope to have more and more readers in 2013.
If you would like to read (or read back) my posts published in 2012, here is the summary :
While testing MySQL 5.6, I came across some curious values for the new values used to set the crash-safe slave tables. To get safety, we need to set relay_log_info_repository and master_info_repository to 'TABLE'. That way, the replication information, instead of going to a file, will be saved to two tables in the mysql schema (mysql.slave_relay_log_info and mysql.slave_master_info).
So I was setting these values back and forth between 'FILE' and 'TABLE', until I made a "mistake." Instead of typing
set global relay_log_info_repository='table';
To [Read more...]
set global relay_log_info_repository=1;
I would like to make a quick MySQL 101 about numeric types in MySQL and especially explain the difference between the numeric type attributes and the maximum value that a numeric type can store.
Someone asked me yesterday why when he inserts a value in a table, the value 127 appears instead of the value 160 that he’s just inserted.
Ok, let me explain, consider this table :
What happens if you want to insert this row in the table :
sql > insert into product values (160, 554, "iPad mini"); Query OK, 1 row affected, 1 warning (0.01 sec) sql > show warnings; +---------+------+---------------------------------------------+ | Level | Code | Message |[Read more...]
I’m currently working on a new project about the explain command output.
For my research I’ve read a lot of documentations, presentations and blog posts and I would like to focus on the join type in the explain output.
And in particular when type=index in this explain output.
Take a look at what says the official documentation about type=index :
Wait a second, only the index tree is scanned? really?
This description seems to be the definition of a covering index.
But a covering index is only characterized by a “Using index” in
For the first time since the beginning of my long long career, I decided today to use an advice tool to check my MySQL configuration.
I decided to launch pt-variable-advisor and mysqltuner V2 on one of my MySQL server.
Here are the result outputs :
Then I ask myself what to do with these results, just raw results without much explanations, these tools are supposed to be done for beginners…
Just one example from mysqltuner : Too many temporary tables are being written to[Read more...]
I really like the percona toolkit, we all love the percona toolkit.
I know how it’s difficult to write operational and efficient scripts (I try to do that myself everyday)
And it is even more difficult to share a script, to take the responsibility to share its own code.
From there, understand that this article is simply a review of my own thoughts about pt-archiver (with the invaluable assistance of @maximefouilleul), I don’t want to question the quality or usefulness of this tool.
I tried pt-archiver for the first time this week, and the first thing I do before using a tool is read the documentation (yes, I really like to read documentations)
I was intrigued by some options of this tool, first, I can read “It deletes data from the source by default“.
Personally, I hate that you want to remove my data by
I’m sure you’ve heard of Box Anemometer from the Box (MySQL) team, an excellent UI tool based on pt-query-digest. Now the guys from the box team offer us another killer UI tool based on pt-stalk, and I’m sure you will really appreciate to use it !
“RainGauge” consists of three parts :
In relation to these two posts from Justin Swanhart and Anders Karlsson about transaction isolation levels, I thought it was interesting to do a little survey to get an idea of the most commonly used isolation levels.
There has been a lot of talk about MySQL 5.6 replication improvements. With few exceptions, what I have seen was either marketing messages or hearsay. This means that few people have really tried out the new features to see whether they meet the users needs.
As usual, I did try the new version in my environment. I like to form my own opinion based on experiments, and so I have been trying out these features since they have appeared in early milestones.
What follows is a list of (potentially) surprising results that you may get when using MySQL 5.6.
All the examples are made using MySQL 5.6.6.
I have already mentioned that MySQL 5.6 is too verbose when creating data directory. This also means that your error[Read more...]
Have you ever spent a lot of time trying to locate where MySQL keeps some file? Here is a quick way to find all this information in one place.
The obvious way is through examining database options in my.cnf or looking at the output of SHOW GLOBAL VARIABLES. But not every path may be explicitly set in the configuration, in such case MySQL may assume some default, while other options may be set using relative paths.
A different approach is listing all files that a running database instance keeps open and searching for the required information there. I find that method by far the fastest whenever I need to learn any of such details.
garfield ~ # lsof -nc mysqld | grep -vE '(\.so(\..*)?$|\.frm|\.MY?|\.ibd|ib_logfile|ibdata|TCP)' COMMAND PID USER FD TYPE DEVICE SIZE/OFF NODE NAME mysqld 30257[Read more...]
Recently I had to turn a few EC2 instances into MySQL database servers. The third time I had to do it, I grabbed the list of steps from my previous sessions and just replayed it. Later I thought maybe polishing information a little bit and publishing a step-by-step walkthrough on the blog may help a few people. So here it is.Before you begin.
For my MySQL instances I used the following:
The configuration template provided in this post assumes the new MySQL instance only needs InnoDB storage engine.Grab the packages.
Download the appropriate packages from MySQL web[Read more...]
Have you ever tried to kill a query, but rather than just go away, it remained among the running ones for an extended period of time? Or perhaps you have noticed some threads makred with killed showing up from time to time and not actually dying. What are these zombies? Why does MySQL sometimes seem to fail to terminate queries quickly? Is there any way to force the kill command to actually work instantaneously? This article sheds some light on it.Threads and connections
MySQL uses a separate thread for each client connection. A query sent to MySQL is handled by a thread that was previously associated with the connection over which the query arrived. Anyone with sufficient privileges can see the list of currently active threads, along with some additional details, by running SHOW PROCESSLIST command, which returns a[Read more...]
Last month, Ronald Bradford, Giuseppe Maxia and Mark Leith spoke about how to simulate a mysqldump –ignore-database.
This mysqldump option doesn’t exist and these three guys have given us various helpful solutions.
But for those of us who use ZRM community to make backup with mysqldump, the –exclude-pattern seems to do the job :
--exclude-pattern "pattern" All databases or tables that match the pattern are not backed up. If --all-databases or --databases are[Read more...]
I would like to share this story based on a true event about the temporary files behavior in MySQL.
MONyog reports this error to my already full mailbox several times a day :
I don’t have access to the client logs but I would like to know which query is involved in this error.
Let me explain how I can retrieve informations about this query with MONyog and a very simple shell loop :
I just want to share about a strange behavior of one of our MySQL server yesterday.
This server is a 5.1.50 MySQL server on debian 4.0 (Yes, I know…)
When a “mysqld got signal 6” error occurred yesterday, the MySQL server crashed and didn’t want to restart.
Then, I found these informations in the error log file :
/usr/local/mysql/bin/mysqld: File '*** glibc detected *** malloc():memory corruption: 0x00002aac2d5ab460 ***' not found (Errcode: 2) 120306 17:19:47 [ERROR] Failed to open log (file '*** glibc detected *** malloc():memory corruption: 0x00002aac2d5ab460 ***', errno 2) 120306 17:19:47 [ERROR] Could not open log file 120306 17:19:47 [ERROR] Can't init tc log 120306 17:19:47 [ERROR] Aborting
120306 17:19:47 InnoDB: Starting shutdown... 120306 17:19:53 InnoDB: Shutdown completed; log sequence number 55 1061584593[Read more...]
Today I’m working on integrate the Percona toolkit (instead of maatkit) in my own tools and I’m playing with pt-query-digest and pt-query-advisor.
These tools can be very interesting to identify some queries from established rules.
The –review option is available for two of them and helps me to store a sample of each class of query and match them with an advice.
The rules (or advices) are available in the pt-query-advisor documentation and let you identify various problems such as queries with an argument with leading wildcard or with a table joined twice, for[Read more...]
We still have a few seats left for our MySQL Cluster training in Washington DC January 24 - 26. If you're interested in learning about MySQL Cluster, the architecture, how to install it, administer it and troubleshoot it this is the course for you. The course will also cover replication and optimization and we will also discuss the exciting new features coming in version 7.2 of MySQL Cluster. For more information about the course contents visit http://www.skysql.com/services/training/courses/administering-mysql-cluster and to sign up to the course go to http://www.skysql.com/services/training/schedule/administering-mysql-cluster-1.
See you there!
Regardless of the monitoring tool you use to monitor your databases, it can be better to monitor this tool.
No, it’s not a joke ! Do you think you can have a benefit with a monitoring tool not connected to your servers ? ( without being alerted )
I choose to talk about MONyog here but this can apply to all existing monitoring tools.
I just want to share the message, the tool does not matter, so, do it !
So, let me explain how to control if you have fresh data with MONyog.
With MONyog it’s easy because it’s an agentless monitoring tool.
There are two ways to check that :
Per server general info :
I was glad to present how to schedule and monitor mysqldump with ZRM community last week in Paris as part of the MySQL Meetup Viadeo / LeMUG
You can find my slides below, enjoy ! :MYSQLDUMP & ZRM COMMUNITY (EN) View more presentations from Cédric PEINTRE Thanks to Olivier and all the viadeo team in Paris for this event.
I’m sure you are aware that the last Percona server release includes a new improved MEMORY storage engine for MySQL.
This new engine is based on Dynamic Row Format and offers some of great features, specialy for VARCHAR, VARBINARY, TEXT and BLOB fields in MEMORY tables.
But because this new MEMORY engine by Percona has some limitations and because Percona server hasn’t used it for its internal temporary tables yet, I would like to talk about what can be the real benefits to have a brand new MEMORY engine based on Dynamic row format specialy for internal memory[Read more...]
COLUMNStable from the
INFORMATIOn_SCHEMA, and so I came up with this query, where I sum the number of columns that are inside either a PRIMARY or UNIQUE key and filter only the ones where such sum is zero (i.e. no primary or unique [Read more...]
Scribd is a top 100 site on the web and one of the largest sites built using Ruby on Rails. As one of the first rails sites to reach scale, we’ve built a lot of infrastructure and solved a lot of challenges to get Scribd to where it is today. We actively try to push the envelope and have contributed substantial work back to the open source community.
Scribd has an agile, startup culture and an unusually close working relationship between engineering and ops. You’ll regularly find cross-over work at Scribd, with ops people writing application-layer code and engineers figuring out operations-level problems. We think we’re able to make that work because of the uniquely talented people we have on the team.
To allow us to keep scaling, we’re now looking to add a[Read more...]
|Showing entries 1 to 30 of 30|