Continuation's questions

According to this paper on Facebook's Haystack:

"Because of how the NAS appliances manage directory metadata, placing thousands of files in a directory was extremely inefficient as the directory’s blockmap was too large to be cached effectively by the appliance. Consequently it was common to incur more than 10 disk operations to retrieve a single image. After reducing directory sizes to hundreds of images per directory, the resulting system would still generally incur 3 disk operations to fetch an image: one to read the directory metadata into memory, a second to load the inode into memory, and a third to read the file contents."

I had assumed the filesystem directory metadata & inode would always be cached in RAM by the OS and a file read would usually require just 1 disk IO.

Is this "multiple disk IO's to read a single file" problem outlined in that paper unique to NAS appliances, or does Linux have the same problem too?

I'm planning to run a Linux server for serving images. Any way I can minimize the number of disk IO - ideally making sure the OS caches all the directory & inode data in RAM and each file reads would only require no more than 1 disk IO?

When a Linux server is serving many concurrent requests to read many different files, does it:

1. Seek to File_1, read the entire file, then seek to File_2, read the entire file, then seek to File_3, etc etc

2. Seek to File_1, read part of it (up to the readahead value?), then seek to File_2, read part of it, then seek back to File_1 where it has left off, read more of it, then seek to File_3, etc, etc

If it's the 2nd case, then the server is doing a lot more seeks than is necessary, which would slow things down significantly. In that case is there any tuning I could do?

How do I perform crash recovery in a MySQL/InnoDB Master-Slave asynchronous replication setup?

Specifically:

1. If a slave crashes, how do I make it syncs up with the master after I bring it back up?

2. If the master crashes, a slave will become the master. How do I make the new master syncs up with other slaves? And when the original master is brought back up, how do I sync it with the new master?

Because replication is async, a transaction that has been committed to the master may not be able to leave the master before the crash happens. So there could be inconsistency between the original master and slaves, one of which will become the new master.

Likewise, the slave that are promoted to become the new master may not have the most up-to-date transactions among all the slaves. So the new master could be "behind" one of its slaves.

How do I resolve all these potential inconsistencies?

Any tools that help with these tasks?

Thanks.

I'm working on improving the performance of a MySQL server. I'm looking for something to measure & monitor the performance of MySQL (as query per second) over time so that I can measure any improvements I make.

Are their any easy to use open source software that does this?

Thank you.

Can you give me an example on how to enable MySQL's slow query log?

According to the doc:

As of MySQL 5.1.29, use --slow_query_log[={0|1}] to enable or disable the slow query log, and optionally --slow_query_log_file=file_name to specify a log file name. The --log-slow-queries option is deprecated.

So how do I use that option? Can I put it in my.cnf? An example would be greatly appreciated.

We have a production database that is made up entirely of MyISAM tables. We are considering converting them to InnoDB to gain better concurrency & reliability.

1. Can I just alter the myISAM tables to InnoDB without shutting down MySQL? What are the recommend procedures here?

2. How long will such a conversion take? All the tables have a total size of about 700MB

3. There are quite a large number of tables. Is there any way to apply ALTER TABLE to all the MyISAM tables at once instead of doing it one by one?

4. Any pitfalls I need to be aware of?

Thank you

A client is using PHP to connect to MySQL. The PHP scripts and the MySQL database are located on 2 different Linux servers. He complained that database connections were being dropped or timed out and asked me to take a look.

Is there any place in MySQL that can show me what and how many connections have been dropped or timed out? I looked into slow query log and didn't see anything.

Any suggestions on how to diagnose this dropped/timed out database connection problem?

Thanks

EDIT:

Slow query log is enabled in my.cnf:

log-slow-queries=/var/log/mysql-slow-queries.log

And when I do a

mysql> show global status;

I got:

| Slow_queries                      | 11402347     | 

So there are a lot of slow queries. But the file /var/log/mysql-slow-queries.log doesn't exist. Why is that?

I'm running MySQL 5.0 in production on CentOS. How do I migrate to Percona Server 5.1 safely?

The documentation of Percona Server doesn't include any information on migrating. Any help is greatly appreciated.

I have a MySQL database that contains almost 100 tables.

I want to set up N additional MySQL databases on the same server, each running on a different port. And I want each additional database to have the same schema/table structures as the original database.

Is there any way to automatically make N duplicates of the original database and set them up on N different ports?

Thanks

I have a MySQL table:

CREATE TABLE tweets (
tweet_id INT NOT NULL AUTO_INCREMENT,
author_id INT NOT NULL,
text CHAR(140) NOT NULL,
PRIMARY KEY (tweet_id)
)
ENGINE=InnoDB
PARTITION BY HASH(tweet_id)
PARTITIONS 12;

How do I move selected partitions from this table to a different machine?

For example, I'd want to move partitions 1, 3, 5, 7, 9, 11 of the above 12 partitions to a different machine. And I'd need to make sure the auto-increment PK "tweet_id" remains unchanged during the migration.

Is there a way to move an entire MySQL partition from one machine to another?

Say I have a MySQL table:

CREATE TABLE tweets (
tweet_id INT NOT NULL AUTO_INCREMENT,
author_id INT NOT NULL,
text CHAR(140) NOT NULL,
PRIMARY KEY (tweet_id)
)
PARTITION BY HASH(tweet_id)
PARTITIONS 12;

All is good. The table lives on a single server - Server1. But eventually I may want to scale out. So I'd want to shard the table and move 6 of the 12 partitions onto a new server - Server2.

1) Is there any quick & easy way to move those partitions from Server1 to Server2?

2) Now that I have 2 servers, how do I make sure the auto-increment tweet_id's generated by the 2 servers don't have the same value? I'd also need to make sure the tweet_id on each partition stays consistent, i.e. on Partition k every tweet_id's modulo 12 equals to k.

3) Ideally I'd like to continue this scale out process. So later on I'd want to add a 3rd server - Server3. I'd want to re-balance the partitions so that there're 4 partitions on each server. Again how do I make sure the auto-increment tweet_id's generated by the 3 servers are distinct and that the hash of tweet_id's stay consistent within each partition?

The biggest benefit of virtualization is usually said to be improved server utilization.

But why do I need virtualization for that?

Say I got N physical servers that are lightly used. Why don't I just combine all the apps on those N servers into 1 physical server? This way I don't incur the performance penalty of virtualization.

What does virtualization buy me in this case?

Pretty much every page of my website is dynamically generated. However they don't change that frequently (kinda similar to a forum page). So I'd like to cache them using a caching reverse proxy such as Squid, varnish or Nginx.

The problem is that for my logged-in users, each of them will see a personalized header saying "Welcome John Doe. Logout" on the upper right corner of the page (just like serverfault). While users who aren't logged in will see a header that says "Login" instead.

So basically even though every user will see the same page in general, they all slightly different version due to that personalized header.

Is there any way so that I can cache the "main" part of the page and serve it from cache while generate the personalized header dynamically for each individual user?

This must be a very common problem. How is it solved in general?

I'm working on a social website with an approximate read/write split of 90/10. Trying to decide on a MySQL engine. The ones I'm interested in are:

1. XtraDB
2. PBXT
3. TokuDB

What are the pros and cons of them for my use case?

A few specific questions:

• PBXT uses log-based structure that avoids double-writes. It sounds very elegant, but the benchmark I've seen doesn't show any/much advantages over XtraDB. Do you have any experience with PBXT/XtraDB you can share?

• TokuDB sounds VERY interesting. But all the benchmarks I've seen are about single-threaded bulk inserts - inserting 100M rows for example. that's not very relevant for OLTP. What about its performance with large number of concurrent threads writing and reading at the same time running on multiple cores? Anyone has tried that?

One of the biggest complaints I have heard about MySQL is that it locks up a table if you try to change its schema like adding a column or adding an index.

By "locking up the table" does it mean I can neither read nor write to the table? Sometimes for hours?

That seems a pretty severe limitations. I was going to use MySQL for my new project but this gives me pause.

Is there a workaround for this? How do you handle the task of changing the schema of your production MySQL database?

By the way someone told me Postgresql doesn't have this problem. Is that true - I can both read and write to a Postgresql table while changing its schema? Is there any performance penalty incurred?

Would love to hear your experiences.

MySQL has quite a few logs:

• InnoDB transaction log
• Binary log
• General query log
• Error log
• Slow query log

I know about Postgresql's WAL, which is equivalent to InnoDB's transaction log (correct?).

What about the other MySQL logs such as bin log - are there Postgresql equivalence of them?

I've heard a lot about using Cobbler together with Puppet/Cfengine for rapid deployment & configuration.

Can you point me to some tutorials or share you experience of how you do it?

Would also love to hear about any other systems you use for rapid provisioning & deployment. Thanks.

I heard recently that Nginx has added caching to its reverse proxy feature. I looked around but couldn't find much info about it.

I want to set up Nginx as a caching reverse proxy in front of Apache/Django: to have Nginx proxy requests for some (but not all) dynamic pages to Apache, then cache the generated pages and serve subsequent requests for those pages from cache.

Ideally I'd want to invalidate cache in 2 ways:

1. Set an expiration date on the cached item
2. To explicitly invalidate the cached item. E.g. if my Django backend has updated certain data, I'd want to tell Nginx to invalidate the cache of the affected pages

Is it possible to set Nginx to do that? How?

I keep hearing about people who want to get SSD for their databases. But instead of putting their tables on SSD, quite often people want to put their DB log on SSD while leaving their tables on regular hard drives.

But why would anyone want to do that?

Log uses sequential writes. And SSD's sequential IO speed isn't any faster than your regular disks. So putting log on SSD wouldn't provide any performance gain.

The one area at which SSD is much faster than regular disks is random IO. So shouldn't the sensible thing to do is to put your tables on SSD while leave the log on regular disks?

Am I missing something?

I tried to look for benchmark on the performances of various filesystems with MySQL InnoDB but couldn't find any.

My database workload is the typical web-based OLTP, about 90% read, 10% write. Random IO.

Among popular filesystems such as ext3, ext4, xfs, jfs, Reiserfs, Reiser4, etc. which one do you think is the best for MySQL?