This week, Giuseppe Maxia joins us as a special guest and we
complete our 2-part series on what we always put in our
configuration files, and why.
Part 1 in the
series.
strings man page
MySQL manual page about defaults-group-suffix
Giuseppe's article about the hidden options file trick
Logging:
log_error
A special extended edition of Tech Messages for 2011-02-10 through 2011-03-07:
Given the popular response from my first lesson in improving scalability where I detailed simple ways to eliminate unnecessary SQL, let me share another common bottleneck with MySQL scalability that can be instantly overcome.
Analyzing the writes that occur on a system can expose obvious potential bottlenecks. The MySQL Binary Log is a wealth of information that can be mined. Simple DML Counts per table can be achieved by a single line command.
Let’s look at the following example output of a production system:
mysqlbinlog /path/to/mysql-bin.000999 | \ grep -i -e "^update" -e "^insert" -e "^delete" -e "^replace" -e "^alter" | \ cut -c1-100 | tr '[A-Z]' '[a-z]' | \ sed -e "s/\t/ /g;s/\`//g;s/(.*$//;s/ set …[Read more]
It can be very easy to improve scalability with a MySQL server by a few simple rules. Here is one of them.
“The most efficient way to improve an SQL statement is to eliminate it”
There are numerous ways to eliminate SQL statements, however before I give a classic example that I’ve observed again with a client, let me explain the basic premise of why this improves scalability?
The MySQL kernel can only physically process a certain number of SQL statements for a given time period (e.g. per second). Regardless of the type of machine you have, there is a physical limit. If you eliminate SQL statements that are unwarranted and unnecessary, you automatically enable more important SQL statements to run. There are numerous other downstream affects, however this is the simple math. To run more SQL, reduce the number of SQL you need to run.
Here is the output of a small sample of analyzed TCP/IP …
[Read more]Using Net::HandlerSocket, here are some fun numbers for a single connection (open & close). When connecting to “localhost”, here’s the strace: open("/etc/hosts", O_RDONLY) = 3 fcntl(3, F_GETFD) = 0 fcntl(3, F_SETFD, FD_CLOEXEC) = 0 fstat(3, {st_mode=S_IFREG|0644, st_size=187, ...}) = 0 mmap(NULL, 4096, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_ANONYMOUS, -1, 0) = 0x2b909a1a3000 read(3, "# Do not remove the following [...]
Replication as most people know it, has mostly been SQL statement propagation from master to slave. This is known as "statement-based" replication. But there is also another kind of replication that is available, "the row-based replication" and that has quite a lot of benefits. In this post I intend on highlighting the advantages and disadvantages of both the types of replication to help you choose the best one. I also follow up with my own recommendation.
Some people don't probably know, but there is a difference between how indexes work in MyISAM and how they work in InnoDB, particularly when talking from the point of view of performance enhancement. Now since, InnoDB is starting to be widely used, it is important we understand how indexing works in InnoDB. Hence, the reason for this post!
I've just started learning MySQL's internals but I've got an idea which I want to convert to a question here, to ask people who are already deeper in it. Is it a bad idea to completeley avoid query parsing on the server side and use a binary protocol instead? This way the client parses the query and could cache the statement structure for further usage or another client API uses a NoSQL approach to send the request data to the server.
A special extended edition of Tech Messages for 2010-12-14 through 2010-12-21:
…
[Read more]I had earlier written a post on tuning the MySQL server configuration which was more geared towards the MyISAM storage engine. While that is not because I didn't intend on ignoring InnoDB but because I had planned a whole post on tuning InnoDB related configuration. So this post is the post that I had planned, I have discussed the major configuration parameters in here that should help you out most of the times.