1. MyISAM is frequently described and marketed as providing fast reads when it really provides fast index and table scans. This is a more narrow use case as fast reads implies great performance for most queries while fast scans implies great performance for single-table queries that are index only or do a full table scan.

    MyISAM caches index blocks but not data blocks. There can be a lot of overhead from re-reading data blocks from the OS buffer cache assuming mmap is not used. InnoDB and PBXT are 20X faster than MyISAM for some of my tests. However, I suspect that mutex contention on the key cache is also a factor in the performance differences.

    While there are many claims about the great performance of MyISAM. There are not as many examples that explain when it is fast. Alas, the same marketing technique is being repeated with NoSQL to the disadvantage of MySQL.
    Tests were run on a server that reports 16 CPU cores. The full test configuration is described elsewhere. For this test I modified the sysbench oltp test to do a self-join query. I will publish the code soon. The schema for the test is:
    CREATE TABLE sbtest (
      id int(10) unsigned NOT NULL AUTO_INCREMENT,
      k int(10) unsigned NOT NULL DEFAULT '0',
      c char(120) NOT NULL DEFAULT '',
      pad char(60) NOT NULL DEFAULT '',
      PRIMARY KEY (id),
      KEY k (k)
    ) ENGINE=InnoDB;
    The self-join query uses a range predicate that selects a fixed number (1, 10, 100, 1000 or 10000) of rows. This is an example that selects 1000 rows.
    SELECT t1.c, t2.c FROM sbtest t1, sbtest t2
    WHERE t1.id between 245793 and 246792 and t2.id = 2000000 - t1.id
    Tests were run using MySQL 5.1.45 for MyISAM, InnoDB plugin 1.0.6 and PBXT 1.1. Results are in queries per second for 1, 2, 4, 8, 16, 32, 64, 128, 256, 512 and 1024 concurrent clients. I do not report results for 512 and 1024 clients to avoid long lines in this post.

    The performance of MyISAM is much worse compared to InnoDB and PBXT as the number of rows selected grows from 1 to 10,000.

    Queries per second when the between predicate selects 1 row:
      6843  13157  24552  46822  62588  57023  46568  30582  18745 innodb
      6164  13627  25671  48705  63741  59217  48300  30964  18866 pbxt
      6354  12061  23373  44284  50778  49546  44412  30444  18827 myisam

    Queries per second when the between predicate selects 10 rows:
      4240   8466  16387  33221  53902  39599  36214  28026  18084 innodb
      4802   8835  17688  35917  57461  47691  41578  29087  18558 pbxt
      3890   7129  12512  16450  12272  12304  12441  12448  11304 myisam

    Queries per second when the between predicate selects 100 rows:
      1842   3455   7249  14842  20206  13875  13471  12942  12344 innodb
      2113   3522   7893  13411  18597  18905  18694  18123  12301 pbxt
      1608   2260   2263   1899   1371   1399   1451   1468   1442 myisam

    Queries per second when the between predicate selects 1000 rows:
       380    654   1222   2023   2487   1866   1791   1794   1942 innodb
       303    641   1149   1699   2044   2069   2072   2063   2056 pbxt
       232    248    227    189    141    143    149    148    148 myisam

    Queries per second when the between predicate selects 10000 rows:
        43     70    130    213    254    199    194    196    199 innodb
        49     69    123    182    213    216    216    216    216 pbxt
        24     24     23     19     14     14     15     15     15 myisam

    MyISAM is at a disadvantage because it does not cache data blocks, so I changed the query to be index only and it is listed below. This did not make MyISAM faster. I think the bottleneck is contention on the key cache mutex.
    SELECT t1.id, t2.id FROM sbtest t1, sbtest t2
    WHERE t1.id between 245793 and 246792 and t2.id = 2000000 - t1.id
    Queries per second for range 1000 using the index only query:
       457    706   1354   2146   2596   2044   1918   1887   1953 innodb
       576    837   1386   1681   2058   2094   2103   2095   2087 pbxt
       353    244    223    190    140    142    147    146    146 myisam

    Results for MySQL 5.0.84 are similar to 5.1.45 for the range 1000 query:
       390    642   1241   2045   2547   1891   1825   1813   1930 innodb
       303    239    225    189    140    141    147    146    146 myisam

    The query plan for the basic query:
    explain  SELECT t1.c, t2.c
    from sbtest t1, sbtest t2
    where t1.id between 245793 and 246792 and t2.id = 2000000 - t1.id

    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: t1
             type: range
    possible_keys: PRIMARY
              key: PRIMARY
          key_len: 4
              ref: NULL
             rows: 1072
            Extra: Using where; Using index
    *************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: t2
             type: ref
    possible_keys: PRIMARY
              key: PRIMARY
          key_len: 4
              ref: func
             rows: 1
            Extra: Using where; Using index
    2 rows in set (0.01 sec)

    The query plan for the index only join:
    explain  SELECT t1.id, t2.id
    from sbtest t1, sbtest t2
    where t1.id between 1916457 and 1917456 and t2.id = 2000000 - t1.id
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: t1
             type: range
    possible_keys: PRIMARY
              key: PRIMARY
          key_len: 4
              ref: NULL
             rows: 978
            Extra: Using where; Using index
    *************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: t2
             type: eq_ref
    possible_keys: PRIMARY
              key: PRIMARY
          key_len: 4
              ref: func
             rows: 1
            Extra: Using where; Using index
    2 rows in set (0.00 sec)
    Posted by
    Labels: innodb myisam mysql pbxt performance rant
    6

    View comments

  2. Baron wrote this in a comment to a recent blog post.
    I consider innodb_thread_concurrency a vestigial tail of the “built-in InnoDB” that ships by default with MySQL 5.0 or 5.1, and should generally be set to 0 with recent versions of XtraDB or the InnoDB Plugin.
    Can this be? I cannot wait for innodb_thread_concurrency to be made obsolete. I run a lot of CPU-bound benchmarks on 8 and 16 core servers and I always set it to 0 in my benchmark framework. A few times I have repeated tests with it set to a non-zero value to understand whether that helps and it has never helped. Alas, this will also make my FLIFO patch obsolete.

    I agree with Baron that it should be set to 0 with the InnoDB plugin and XtraDB. This is a big deal that has not received enough attention. InnoDB and XtraDB have gotten much better at supporting highly-concurrent workloads on many-core servers. For me highly-concurrent means 100 to 1000 concurrent transactions and many-core means 8 and 16 core servers.

    This is not an easy workload to support. MySQL is getting much better at it. A lot of work remains to be done. MySQL 5.5 has even more improvements and several problems have yet to be fixed in InnoDB. But this is a huge deal. Maybe we can have a going away party for innodb_thread_concurrency at the conference?
    Posted by
    Labels: innodb mysql performance
    12

    View comments

  3. I think I have rpl_transaction_enabled working for MySQL 5.1 and will publish the patch after more testing. I hope to never port this again but that depends on whether the distribution I use provides an equivalent feature. Apparently people in operations enjoy not having to restore slaves after hardware and software crashes.

    Some features require payment up front. They either cost a lot for developers to implement or for users to deploy. Others avoid the up front costs but require payment down the road by users who encounter many problems. I think that MySQL replication has been on the wrong side of this trade off for too long. But things are changing as the replication team has been done a lot of good things for the past few years. I am sure if we follow Mats around at the User conference we can find out what is coming.

    MySQL has to improve to remain competitive as PostgreSQL and others have compelling features pending or available now.
    Posted by
    Labels: mysql rant
    0

    Add a comment

  4. A lot of interesting work is being done today on SQL and NoSQL servers. This generates a lot of interesting discussions about CAP, ACID and BASE. Be careful what you read.

    The D in ACID stands for durability. A DBMS (SQL or NoSQL) is either durable or it is not. But there are several ways to be not durable. Two popular ways are lose-last-N-transactions (not durable) and lose-unspecified-amount-of-data (really not durable). One of these is much better than the other. Alas this distinction is frequently ignored when describing really-not-durable servers.
    • lose-last-N-transactions - not durable servers provide a configuration that enables better performance by allowing the last N transactions to be lost during a crash. InnoDB does this when innodb_flush_log_at_trx_commit=2. I am not a MySQL Cluster expert but I think that global checkpoints provide the same property. Cassandra can run in this mode. I think that HBase must run in this mode. Some filesystems provide a similar option. The key point is that the system will quickly recover to a consistent point in time after a crash and the time to which it recovers will be not too far in the past.
    • lose-unspecified-amount-of-data - really not durable servers can be great for batch and read-only workloads. I am skeptical about using them for OLTP. A server specific version of fsck must be run after a software or hardware crash. The really not durable servers that I am familiar with do not document this behavior prominently (the amount of data that might be lost after crash recovery and the amount of time required to run the recovery tool on a large database file). I suspect some of their users are not aware of the problems that await them.
    Posted by
    Labels: mysql
    15

    View comments

  5. MySQL uses SQL for data and name-value pairs for configuration files. Cassandra uses XML for configuration files and something closer to name-value pairs for data (or name-value-value-... pairs). Why does it use a stronger data model for configuration than for data?

    While I am writing this in jest I think this is an interesting question.
    Posted by
    Labels: mysql rant
    9

    View comments

  6. I am trying to understand the behavior of MYSQL_OPT_READ_TIMEOUT which can be used to set a client-side read timeout for connections to a MySQL server. This determines how long a client will wait for a response to a request. I was uncertain based on the documentation.
    The timeout in seconds for attempts to read from the server. Each attempt uses this timeout value and there are retries if necessary, so the total effective timeout value is three times the option value.
    I read the code. The documentation is correct. The code attempts to read from the socket three times. I prefer to not have to multiply by three to know the real timeout. But it is too late to change this. Maybe they could add a new option -- MYSQL_OPT_READ_TIMEOUT_DO_NOT_MULTIPLY_BY_THREE.

    I encountered this interesting claim while reading the source in sql/net_serv.c. We recently discussed this elsewhere. I wasn't aware that the claim is still in the source code.
    This file is the net layer API for the MySQL client/server protocol, which is a tightly coupled, proprietary protocol owned by MySQL AB.
    @note
      Any re-implementations of this protocol must also be under GPL, unless one has got an license from MySQL AB stating otherwise.
    UPDATE

    The read timeout is enforced by my_real_read in sql/net_serv.c. This code is hard to read. Output from the preprocessor is slightly better. I think it is an accident that the read is retried 3 times for client side code.

    I am pretty sure that Drizzle removed this code. Good for them.

    The outermost loop should be ignored as all retries occur in the first iteration of it. The comment that the first read is done with non blocking mode is wrong when this is used in the client library. That may explain why one of the retries is done:
        for (i=0 ; i < 2 ; i++)
        {
          while (remain > 0)
          {
            /* First read is done with non blocking mode */
            if ((long) (length= vio_read(net->vio, pos, remain)) <= 0L)
            {
    After that fails on the first read attempt this block of code runs and then continue is called to jump to the start of the while loop. This comment is again wrong as the code within this block changes the socket to use blocking mode. The comment also contradicts the previous comment mentioned above.

              /*
                We got an error that there was no data on the socket. We now set up
                an alarm to not 'read forever', change the socket to non blocking
                mode and try again
              */
              if ((interrupted || length == 0) && !thr_alarm_in_use(&alarmed))
              {
                if (!thr_alarm(&alarmed,net->read_timeout,&alarm_buff)) /* Don't wait too long */
                {
    The block above is not executed after the second read call fails because thr_alarm_in_use is true. In this case alarms aren't really used, the alarmed variable is an int set to 0 at function entry and set to 1 after the first read fails. This block of code is executed after the second read call fails and it executes a continue statement to branch to the start of the while loop and retry the read call for a third time. It increments retry_count to 1 before doing so.
              if (thr_alarm_in_use(&alarmed) && !thr_got_alarm(&alarmed) &&
                  interrupted)
              {                                     /* Probably in MIT threads */
                if (retry_count++ < net->retry_count)
                  continue;
    #ifdef EXTRA_DEBUG
                fprintf(stderr, "%s: read looped with error %d, aborting thread\n",
                        my_progname,vio_errno(net->vio));
    #endif /* EXTRA_DEBUG */
              }
    The previous block of code is also executed after the read fails for the second time. However, retry_count was previously incremented to 1 and net->retry_count equals 1. So the continue statement is not called after the third read failure and my_real_read returns.
    Posted by
    Labels: mysql rant
    16

    View comments

  7. I wish the case for Drizzle could be made without bashing MySQL. Sometimes it is, but too often it isn't. I guess this is karma.

    This isn't a rant against Drizzle. This is a rant against pulling up Drizzle by pushing down MySQL. I occasionally have negative things to say about MySQL, but I usually say them to get the problems fixed. We have lots of complaints about MySQL because we use it in production.

    What have I learned about the Drizzle vision?
    • Drizzle will re-think everything. 
      • Alas, I have problems to solve today. While I am passionate about doing things correctly, I am also aware that compromises must be made to get things done. Some of those compromises turn out to be mistakes. It isn't always possible to know which compromises will turn out to be a mistake. Nor is it always possible to identify the right thing.
    • You hate MySQL replication? You now love Drizzle. 
      • I love what Drizzle might do for replication. I love what MySQL is doing with replication. I can't compare the two until Drizzle replication is running in production.
    • MySQL has a data type called a 3-byte integer. Think about that for a moment. On today’s server hardware that does not make a whole lot of sense. 
      • I thought about it. Does this mean that some of my tables will grow from 3G to 4G on disk? I won't be happy if that is the result.
    • No triggers or stored procedures. That stuff is bloat as done in MySQL, and Drizzle has other ways to deal with these needs. These capabilities can be added in later as needed such that they are done right. 
      • I need stored procedures. They are required for high-performance OLTP as they minimize transaction duration for multi-statement transactions. Alas, I have yet to use them in MySQL.
    • MyISAM is gone. Long live the Queen! 
      • Alas, I need MyISAM. Long-running insert, update and delete statements consume too many resources in InnoDB. Such statements are used for reporting jobs on slaves and in that case I want to use InnoDB for production tables and MyISAM for transient tables.
    • Ever tried to compile MySQL from source. Hah! Yeah, drizzle builds like butter.
      • I have no problems building MySQL from source. I have had more problems building Drizzle because it has a few more dependencies (google protobufs, libdrizzle). But both are easy to build and nobody cares too much in either case with one exception. Does Drizzle build on Windows?
    Posted by
    Labels: mysql rant
    6

    View comments

  8. A problem with SQL is SQL. It is easy to write queries that require random IO in the worst case. It is usually easy to find queries that do too much random IO on a NoSQL system as you must code the extra data fetches manually.

    Digg has begun to write about their reasons for migrating from MySQL to Cassandra. They provide an excellent summary and then describe a performance problem fixed by the migration. I think Cassandra and a few other members of the NoSQL family are amazing technology but I don't think a migration was needed to fix this performance problem. A better index on the Diggs table would have done that. Others have said the same thing. Maybe I don't have all of the details. I can only go on what was written in the blog.

    You can learn more about the power of indexes at the MySQL conference.

    The Diggs table was the source of the problem:
    CREATE TABLE Diggs (
      id      INT(11),
      itemid  INT(11),
      userid  INT(11),
      digdate DATETIME,
      PRIMARY KEY (id),
      KEY user  (userid),
      KEY item  (itemid)
    ) ENGINE=InnoDB;
    It supported an important query that was too slow. A simple form of this query is:
    SELECT digdate, id
    FROM Diggs
    WHERE userid in (10, 20, 30) AND itemid = 50
    ORDER BY digdate DESC, id DESC LIMIT 4;
    This query requires too much random IO because it isn't index only. The query can use either the index on itemid or the index on userid. In both cases it will scan more entries than it needs to from the secondary index and then lookup the remaining columns from the primary index. Each lookup on the primary index can do one disk seek. On my test server the plan for this query is:
    id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
    1    SIMPLE    Diggs    ref    user,item    item    5    const    8960    Using where; Using filesort
    After running the query I ran SHOW SESSION STATUS LIKE "Handler_read%" and the result from that is below. The query scanned 5000 entries from the secondary index and would have done more than 5000 disk seeks in the worst case to lookup columns from the primary key index.

    Variable_name    Value
    Handler_read_first    0
    Handler_read_key    3
    Handler_read_next    5000
    Handler_read_prev    0
    Handler_read_rnd    0
    Handler_read_rnd_next    0
    The query is much faster for a table with different indexes
    CREATE TABLE DiggsFast (
      id      INT(11),
      itemid  INT(11),
      userid  INT(11),
      digdate DATETIME,
      PRIMARY KEY (itemid,userid,digdate,id),
      UNIQUE KEY (id)
    ) ENGINE=InnoDB;

    The query has a better plan:
    id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
    1    SIMPLE    DiggsFast    range    PRIMARY    PRIMARY    8    NULL    149    Using where; Using index; Using filesort
     It also is much better in reality. The output from SHOW SESSION STATUS LIKE "Handler_read%" is listed below. With a better index the query scans 150 entries from 5 range scans of the index. It should do about 5 disk seeks in the worst case. It is also index only so it doesn't have to lookup other columns after the index scan. Although that doesn't matter much in this case because the query uses the primary key index which has all columns for an InnoDB table. This query will be much faster than the previous one (5 disk seeks versus 5000).

    Note that this query uses the first two columns in the primary index for the predicates on itemid and userid. InnoDB stores all columns in the primary key index entries so any query that uses the PK index is index only.
    Variable_name    Value
    Handler_read_first    0
    Handler_read_key    5
    Handler_read_next    150
    Handler_read_prev    0
    Handler_read_rnd    0
    Handler_read_rnd_next    0
     UPDATE

    I created another variant of the Diggs table that uses a secondary index for the query. InnoDB includes all columns from a PK index in the secondary index to serve as the pointer to the row. Note there is a difference between being 'in the index' and being indexed.
    CREATE TABLE DiggsFast2 (
      id      INT(11),
      itemid  INT(11),
      userid  INT(11),
      digdate DATETIME,
      KEY itemuserdig (itemid,userid,digdate),
      PRIMARY KEY (id)
    ) ENGINE=InnoDB;
    From the query plan:
    id    select_type    table    type    possible_keys    key    key_len    ref    rows    Extra
    1    SIMPLE    DiggsFast2    range    itemuserdig    itemuserdig    10    NULL    150    Using where; Using index; Using filesort
    digdate    id
    And SHOW SESSION LIKE "Handler_read%"
    Variable_name    Value
    Handler_read_first    0
    Handler_read_key    5
    Handler_read_next    150
    Handler_read_prev    0
    Handler_read_rnd    0
    Handler_read_rnd_next    0
    Posted by
    Labels: mysql performance rant
    24

    View comments

  9. A few years ago MySQL+memcached and PostgreSQL+memcached were the only choices for high-scale applications. That has changed with the arrival of NoSQL. Change is good. Open-source monopolies are not much better than closed-source ones from the perspective of an end user. I expect MySQL to focus much more on the needs of high-scale applications to remain relevant. I also expect it to play better with others as it is no longer the only persistent data store for high-scale applications.

    I think that MySQL+memcached is still the default choice and I don't think it is going away in the high-scale market. But some high-scale applications either don't need all of the features of a SQL RDBMS or are willing to go without those features to scale. This isn't a blanket endorsement of NoSQL as the definition of NoSQL is weak. I am referring to the NoSQL systems that support high-scale.

    I don't believe all of the bad press that MySQL receives from high-scale applications. I know that some problems with MySQL are self-inflicted (seriously, I know this). It is hard to diagnose many problems for which the primary symptom is a slow MySQL server so it is also hard to identify self-inflicted problems. I also don't think that some NoSQL systems will provide a different scale-out experience than MySQL given that some NoSQL systems scale-out by sharding (just like MySQL) and that I can deploy MySQL like NoSQL (disallow joins and secondary indexes, use HANDLER statements)

    I also wonder whether affordable SSD/Flash reduces the need to migrate from MySQL to NoSQL. Many MySQL deployments that were IO bound when it was difficult to get more than a few thousand IOPs on a commodity server can now get 10,000 to 100,000 IOPs in that server at commodity prices.

    MySQL and NoSQL are also at significantly different stages. MySQL is mature and maturity has its benefits. MySQL has amazing support and documentation.  There are client libraries for almost every language that you should use. There are even bindings for languages you shouldn't use. The MySQL C API is easy to use. The JDBC driver is awesome, even if support for JDBC makes it much more complex than needed. There is a lot of MySQL expertise that can be hired or rented (MySQL, Monty Program, Percona, Open Query, Pythian, FromDual) and there is some innovation (not enough companies, but they are doing amazing things) from third-parties such as InfiniDB, InfoBright and TokuDB.

    What happened?

    NoSQL systems are improving faster than MySQL, MySQL has focused on features for the enterprise RDBMS market in the past two releases and the changes we need from MySQL are hard to implement.  Change is hard because MySQL is a complex server that supports many features. Change is also much harder than it should be because of the MySQL coding style. Parts of it are not modular and features are entangled. Some of the difficulty could be overcome were there interest from external contributors. There are external contributors willing and able to improve server code but they are working on other projects like NoSQL. The MySQL effort is also split (or diluted) between official MySQL, Drizzle and MariaDB.

    What really happened?

    I don't know. It may have been better for the business of MySQL to focus on the enterprise market. I can describe some of the problems that need to be fixed in MySQL to make things easier for me. I think other high-scale applications share these problems:
    • Multi-master - high-scale applications have users around the world. Latency is reduced by distributing databases and application servers around the world. Databases are rarely sharded by location so the data store must support multi-master deployments with conflict resolution and eventual consistency for some database tables. There is no support for conflict resolution in MySQL. It might be possible to do something with the output of row-based replication.
    • SQL - this is a problem that MySQL cannot fix. SQL makes it easy to make mistakes. Mistakes include insert, update and delete statements that lock all rows in a table. Alas, the EXPLAIN statement in MySQL does not support insert, update and delete statements. Another serious mistake is a query that has a lousy response time when the database buffer cache is cold because it does many random disk reads. The EXPLAIN statement in MySQL does not provide an estimate for the worst-case number of random disk IOs and many people who write SQL don't know how to interpret it to get an estimate. Worst-case performance is critical for queries run during web requests.
    • Write-optimization - several NoSQL systems are write-optimized including Cassandra, HBase and Bigtable. A write-optimized system makes it possible to use more indexes than an update-in-place data store. With more indexes it is more likely that there can be an index defined for every popular query and the index reduces the number of disk reads that must be done to evaluate the query.  This improves worst-case query response time and reduces the need to use memcached or a huge database buffer cache. Write-optimization has finally arrived for MySQL with the availability of TokuDB. I hope that RethinkDB provides a GA version in the future.
    • Monitoring - without good monitoring you will either spend too much time fixing performance problems or never find them and buy too much hardware. I suspect that monitoring in MySQL is much better than anything in a NoSQL system but MySQL is missing features that make it easy to understand current and new sources of workload. I need to aggregate the overhead (CPU time, disk operations, rows read, ...) by database user, table and statement. It is extremely hard or not possible to do this by database user and table. It became possible in MySQL 5.1 to do this by statement for short periods of time by using the slow query log in MySQL 5.1. Prior to MySQL 5.1, the slow query log was limited to queries that ran for at least two seconds. The alternative is to use tcpdump with mk-query-digest. Despite all of the work that has gone into the performance schema, MySQL has yet to support anything like my favorite feature -- user and table monitoring.
    • Crash proof slaves - replication slaves are not crash proof. The slave commits transactions to a storage engine and then updates a state file to maintain the replication offset. There is nothing to keep the state file and storage engine in sync. Until recently there wasn't even an option to force the state file to disk after it was updated. Unplanned hardware reboots are frequent when there are hundreds or thousands of slaves. If you are clever and use the right version of InnoDB it is possible in some cases to figure out the correct offset for the slave after a crash and repair it manually. This isn't a good use of DBA time. Otherwise DBAs must waste their time and network bandwidth to restore the slaves. The Google patch published two different fixes for this: rpl_transaction_enabled and global transaction IDs. MySQL is working on a fix.
    • Automated failover - for MySQL deployments that have many slaves connected to one master it isn't possible to automate failover when a master crashes. Tungsten and DRBD might make this better. 
    • Resharding - sharding is an excellent way to scale MySQL. Sharding usually requires resharding. Resharding is hard and must be done with minimal downtime. It might be possible to build a tool that uses row-based replication output to reshard a database in the background with little downtime. No such tool exists today.
    • Replication lag - a slave with replication lag is useless for OLTP scaleout. The replication thread is single-threaded. MySQL is working on support for parallel execution on a slave. Until then we need to improve mk-slave-prefetch (Domas can you hear me).
    • Schema change - these are frequently needed for growing high-scale applications. Long running schema changes in MySQL require downtime unless first done on a slave (assuming you have a spare slave and that slave can become the master after the change). Users don't like downtime. I think it is possible to do many of these on a master with minimal downtime using the output from row-based replication. Alas, there is no tool for that today.
    What is NoSQL?

    Do your homework when evaluating a NoSQL system as they differ greatly from each other:
    • Crash safety - most NoSQL systems are crash safe but a few are not. I would limit the use of systems that are not crash safe to supporting batch workload. Unplanned server reboots are frequent for high-scale applications when a large number of servers is used. At least two prominent members of the NoSQL family are not crash safe. That should be documented in bold text on their project pages. It is not.
    • Sharding - some NoSQL systems do sharding. BigTable and others do not. With sharding it is possible to support transactions and multiple-indexes on a table within the scope of a shard. That then requires support for resharding. It also requires that queries on secondary indexes to be run on all shards while queries on primary indexes can be limited to run on one shard which may limit the ability to use secondary indexes.
    • Index types - Many NoSQL systems are limited to hash indexes. You can't do range scans on hash indexes. I wonder whether this leads to data redundancy when every query must be resolved by one index lookup.
    • Secondary indexes - NoSQL systems like BigTable not only do not support transactions, they also do not support secondary indexes. You can explicitly maintain a secondary index but there is no support to make multiple-changes atomic and there can be a failure between the primary and secondary index updates which results in data drift. It is also difficult to do consistent reads between the two.
    • Consistent reads - consistency is usually the responsibility of the client and done by using per-row timestamps.
    • Single-node performance - I know that performance != scale-out but scale-out is not a substitute for lousy single-node performance in the high-scale application market. It might be acceptable to use 5X as many nodes because your data store is slow when you end up using 40 nodes. This becomes a show-stopper when you end up using thousands of nodes. One NoSQL system has accepted this compromise. While I know it has many other use cases I think that will limit the use of it for high-scale applications.
    • Network efficiency - MySQL reduces use of the network because all query evaluation is done at the server. All NoSQL systems evaluate predicates implied by the index access. Only some NoSQL systems evaluate non-indexed predicates. This can result in more data returned to the client.
    • Technology or solution - MySQL is more mature than the NoSQL systems. A lot of work remains to grow NoSQL from technology into solution with support for audit, backup, monitoring and all of the other things required to scale in a large company.
    Posted by
    Labels: mysql rant
    12

    View comments

Loading