STOP: DELETE IGNORE on Tables with Foreign Keys Can Break Replication – MySQL Performance Blog
DELETE IGNORE suppresses errors and downgrades them as warnings, if you are not aware how IGNORE behaves on tables with FOREIGN KEYs, you could be in for a surprise.
How to recover a single InnoDB table from a Full Backup – MySQL Performance Blog
Sometimes we need to restore only some tables from a full backup maybe because your data loss affect a small number of your tables. In this particular scenario is faster to recover single tables than a full backup. This is easy with MyISAM but if your tables are InnoDB the process is a little bit different story.
SQL Generator for testing SQL servers (MySQL, JavaDB, PostgreSQL) in Launchpad
This project implements a pseudo-random data and query generator that can be used to test any Perl DBI, JDBC or ODBC-compatible SQL server, in particular MySQL, but also JavaDB and PostgreSQL.
A day in the life of a slow page at Stack Overflow
Tuning SQL is key, the simple act of tuning it reduced the load time for the particular page by almost 50% in production. However, having a page take 300ms just because your ORM is inefficient is not excusable. The page is now running at the totally awesome speed of 20-40ms render time. In production. ORM inefficiency cost us a 10x slowdown.
PHP implementation of the MySQL old_password function
MySQL has a built in function called password that calculates the hash of a password for secure storage in a database. In MySQL versions older than 4.1 the hashing function was very basic so all newer versions uses the cryptograpichally secure SHA-1 hashing algorithm
How to calculate a password hash the ‘old way’
PHP: Transparent load balancing and sharding with mysqlnd
30+ lines of PHP to add round-robin connection load balancing and, 70+ lines of PHP to add MySQL master slave replication or sharding support to your application without changing the application
Thinking about running OPTIMIZE on your Innodb Table ? Stop! | MySQL Performance Blog
Innodb/XtraDB tables do benefit from being reorganized often. You can get data physically laid out in primary key order as well as get better feel for primary key and index pages and so using less space,
it is just OPTIMIZE TABLE might not be best way to do it.
MySQL Limitations Part 3: Subqueries
The following query will surprise users unpleasantly:
select * from a where a.id in (select id from b);
Users expect the inner query to execute first, then the results to be substituted into the IN() list. But what happens instead is usually a full scan or index scan of table a, followed by N queries to table b. This is because MySQL rewrites the query to make the inner query dependent on the outer query, which could be an optimization in some cases, but de-optimizes the query in many other cases.
We are using “only MySQL”. We still use memcached for front-end caching (i.e. preprocessed HTML, count/summary info), but we do not use memcached for caching rows. We do not use NoSQL, either. Why? Because we could get much better performance from MySQL than from other NoSQL products. In our benchmarks, we could get 750,000+ qps on a commodity MySQL/InnoDB 5.1 server from remote web clients. We also have got excellent performance on production environments.
After partitioning, tests showed that we could sustain an insert rate of 10K rows per second for some time. As the table size grew past 10 million records, the insert rate dropped to about 8500 rows per second, but it stayed at that rate for well over 44 million records. I tested inserts up to 350 million records and we were able to sustain an insert rate of around 8500 rows per second. Coincidentally, during Michael Jackson’s memorial service, we actually did hit an incoming rate of a little over 8000 records per second for a few hours.
Fixing Poor MySQL Default Configuration Values (by Jeremy Zawodny)
I’ve recently been accumulating some MySQL configuration variables that have defaults which have proven to be problematic in a high-volume production environment. The thing they all have in common is a network blip or two can trigger some very undesirable behavior.
Here’s one to keep for later:
Maatkit: A toolkit that provides advanced functionality for MySQL
Maatkit makes MySQL easier and safer to manage. It provides simple, predictable ways to do things you cannot otherwise do. It would be nice if these features were included with MySQL, but they are not. That’s why Maatkit is now shipping by default with many GNU/Linux distributions such as Debian and CentOS.
Multi-Master Replication Manager for MySQL [MMM for MySQL Wiki]
MMM (Multi-Master Replication Manager for MySQL) is a set of flexible scripts to perform monitoring/failover and management of MySQL master-master replication configurations (with only one node writable at any time).
The toolset also has the ability to read balance standard master/slave configurations with any number of slaves, so you can use it to move virtual IP addresses around a group of servers depending on whether they are behind in replication.
How to find un-indexed queries in MySQL, without using the log
Let’s use tcpdump to capture queries, consume the output with mk-query-digest, and filter out all but ones that don’t use an index or use no good index
HSCALE is a plugin written for MySQL Proxy which allows you to transparently split up tables into multiple tables called partitions. In later versions you will be able to put each partition on a different MySQL server.
jurriaanpersyn.com — Database Sharding at Netlog, with MySQL and PHP
This article accompanies the slides from a presentation on database sharding. Sharding is a technique used for horizontal scaling of databases we are using at Netlog. If you’re interested in high performance, scalability, MySQL, php, caching, partitioning, Sphinx, federation or Netlog, read on
MySQL: tinyint(1) and tinyint(4), which one is bigger?
For numeric types length modifier is actually display width, which has nothing to do with what can be stored in field. Yes, that’s it – both TINYINT(1) and TINYINT(4) can store values in range -128..127 (or for unsigned values 0..255), and are absolutely identical datatypes, with one small twist
mk-log-parser reads MySQL log files and generates a report that’s useful for optimizing a server. It can also perform other actions, such as helping you do a query review by showing all distinct queries running on your server.
mk-log-parser reads the files you specify on the command line, or standard input.
Using MySQL’s Built-In Replication To Maximize Availability
MySQL’s internal replication is built on a master-slave relationship between two or more servers, with one acting as the master, and any number acting as slaves. I’ll walk through configuring two servers as a master slave pair, describing the process as we move through it.
step by step instructions on setting up master-slave replication on MySQL