Yoshinori Matsunobu

Having now been exposed to both...

If you ever want to use it as a caching system ... solr is the way to go.

I personally think very highly of Sphinx, yet be aware that Sphinx will return ids not "content".

When handlesocket was announced [1], for me the gem other than the obvious was this nugget of information.

"No duplicate cache When you use memcached to cache MySQL/InnoDB records, records are cached in both memcached and InnoDB buffer pool. They are duplicate so less efficient (Memory is still expensive!). Since HandlerSocket plugin accesses to InnoDB storage engine, records are cached inside InnoDB buffer pool, which can be reused by other SQL statements."

Knowing that with the latest version of sphinx your can make use of their Query format via code or via the mysql console... it has me more sold on Sphinx than solr.

We use solr where I work, it is nice to use and coming features will make it better...

The most annoying aspect of solr for me is, you cant index elsewhere and easily merge it...

Either are miles better than fulltext search.

Perhaps list a few of your needs and requirements then we might be able to see if one stands out ahead of the other.

[1]http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-as-nosql-story-for.html

Percona Server has lots of improvements (some small things and others that are pretty essential for me) On the SSD front, the main ones are:

* You can put the innodb doublewrite buffer on your spinning disks - that's the best place for it since it's heavy sequential writes. See section 2.2.2 here http://yoshinorimatsunobu.blogspot.com/2009/05/tables-on-ssd-redobinlogsystem.html

* The ability to tune various parts of innodb that were originally designed to perform well on regular disks - those with fast sequential access, slower random access, and no real wear considerations. See http://www.percona.com/doc/percona-server/5.1/scalability/innodb_io.html

Here's a recent post from Percona showing MySQL vs Percona server on an Intel 320 SSD:
http://www.mysqlperformanceblog.com/2012/02/23/percona-server-vs-mysql-on-intel-320-ssd/

--

MariaDB is pretty nice too. Their team has introduced some sorely needed query optimizations (http://kb.askmonty.org/en/what-is-mariadb-53/#subquery-optimizations) and it does include Percona's XtraDB.

I haven't looked at MariaDB 5.5 to see how it compares to Percona Server 5.5. I put Percona Server into production last fall and MariaDB was still at 5.3 at the time.

The OP video describes a FS based on Innodb and MySQL. It gets relatively poor performance (7-9X slower for common FS ops). However, the killer FS ops for a DB are subtree operations - 'rm -rf', 'chown -R', 'chmod -R'. In research in this field, these are the operations that get less limelight but are most important for a working system.

I hazard a guess that the above implementation could still be made many times faster (maybe even 10X) by using InnoDB's native API, instead of the much slower MySQL Server SQL API. Here's an example of what you can achieve by going native - 1m ops/sec on InnoDB 8 years ago. This is the main guy (sorry Mark!) behind RocksDB implementation:

http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-as-nosql-story-for.html

Unfortunately, it's not that easy. Don't forget about the impact of SQL parsing and connection costs.

Here is a very illuminating post about it:

http://yoshinorimatsunobu.blogspot.com/2010/10/using-mysql-as-nosql-story-for.html

The authors created a MySQL plugin that served as a network server and hooked into InnoDB without SQL parsing overhead. It also avoided the cost of opening/closing tables by keeping them open.

According to their benchmark, performance went from 100,000 queries per second to 750,000 per second on the same server.

Well worth the read.

For context, the first NoSQL interface for MySQL was:
http://yoshinorimatsunobu.blogspot.ca/2010/10/using-mysql-as-nosql-story-for.html

(It's appeared on HN a few times).

The MySQL team decided to run with a more standard memcached interface, and MySQL actually embeds memcached to do this (with MySQL as backend storage).

Despite being read-only, the over 1M number highlights a lot work thats been done for multi-core scalability. This test was on a 48 score machine.. that was a distant dream a few years ago on MySQL 5.1.