322 lines
13 KiB
SQL
322 lines
13 KiB
SQL
-- Copyright (c) 2011, 2013, Oracle and/or its affiliates. All rights reserved.
|
|
--
|
|
-- This program is free software; you can redistribute it and/or modify
|
|
-- it under the terms of the GNU General Public License, version 2.0,
|
|
-- as published by the Free Software Foundation.
|
|
--
|
|
-- This program is also distributed with certain software (including
|
|
-- but not limited to OpenSSL) that is licensed under separate terms,
|
|
-- as designated in a particular file or component or in included license
|
|
-- documentation. The authors of MySQL hereby grant you an additional
|
|
-- permission to link the program and your derivative works with the
|
|
-- separately licensed software that they have included with MySQL.
|
|
--
|
|
-- This program is distributed in the hope that it will be useful,
|
|
-- but WITHOUT ANY WARRANTY; without even the implied warranty of
|
|
-- MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
|
|
-- GNU General Public License, version 2.0, for more details.
|
|
--
|
|
-- You should have received a copy of the GNU General Public License
|
|
-- along with this program; if not, write to the Free Software
|
|
-- Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
|
|
|
|
-- Configuration version 1.3
|
|
|
|
|
|
CREATE SCHEMA IF NOT EXISTS `ndbmemcache` DEFAULT CHARACTER SET latin1 ;
|
|
USE `ndbmemcache` ;
|
|
|
|
-- ------------------------------------------------------------------------
|
|
-- Table `meta`
|
|
--
|
|
-- At startup, memcached (and possibly other applications) will check for the
|
|
-- existance of one or more <my-application-name, metadata-version> tuples,
|
|
-- and proceed to process the configuration based on the ones found.
|
|
-- End-users should consider it to be a private and undocumented system table.
|
|
--
|
|
-- ------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `meta` (
|
|
`application` VARCHAR(16) NOT NULL ,
|
|
`metadata_version` VARCHAR(6) NOT NULL ,
|
|
PRIMARY KEY (`application`, `metadata_version`)
|
|
)
|
|
ENGINE = ndbcluster;
|
|
|
|
|
|
-- ------------------------------------------------------------------------
|
|
-- Table `ndb_clusters`
|
|
--
|
|
-- Each record in this table represents a MySQL Cluster.
|
|
-- The record with cluster_id 0 is the primary cluster, which stores this
|
|
-- metadata schema. Additional clusters, storing application data only, may
|
|
-- also be defined.
|
|
-- The microsec_rtt field is one of two performance-related parameters -- see
|
|
-- the discussion below regarding memcache_server_roles.
|
|
--
|
|
-- ------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `ndb_clusters` (
|
|
`cluster_id` INT NOT NULL ,
|
|
`ndb_connectstring` VARCHAR(128) NULL ,
|
|
`microsec_rtt` INT UNSIGNED NOT NULL default 250,
|
|
PRIMARY KEY (`cluster_id`) )
|
|
ENGINE = ndbcluster;
|
|
|
|
|
|
-- ------------------------------------------------------------------------
|
|
-- Table `memcache_server_roles`
|
|
--
|
|
-- Each memcache_server performs a role, which can be selected by the role
|
|
-- name supplied on the memcached command line. The default is named
|
|
-- "default_role". The role_id from this table is used in the primary key
|
|
-- of the key_prefixes table.
|
|
--
|
|
-- The two performance-related tunables in the NDB Memcache configuration are
|
|
-- the fields ndb_clusters.microsec_rtt and memcache_server_roles.max_tps.
|
|
-- The NDB Memcache engine will attempt to size its internal data structures so
|
|
-- that max_tps transactions per second can be attained. This involves
|
|
-- multiplying max_tps times microsec_rtt times a fixed constant, to calculate
|
|
-- the number of concurrent in-flight transactions that must be supported.
|
|
-- For example, using the default values of 100,000 TPS and 250 usec RTT:
|
|
-- 100000 tx/sec. * .00025 sec * 5 = 125 in-flight transactions.
|
|
-- (The value of this fixed constant is an internal implementation detail and
|
|
-- is subject to change, but if you start the server with "debug=true" you can
|
|
-- see the results of this calculation in how many NDB instances are created).
|
|
--
|
|
-- ------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `memcache_server_roles` (
|
|
`role_name` VARCHAR(40) NOT NULL ,
|
|
`role_id` INT UNSIGNED NOT NULL ,
|
|
`max_tps` INT UNSIGNED NOT NULL default 100000,
|
|
`update_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
PRIMARY KEY (`role_name`) )
|
|
ENGINE = ndbcluster;
|
|
|
|
|
|
-- ------------------------------------------------------------------------
|
|
-- Table `cache_policies`
|
|
--
|
|
-- Each record in this table represents a named caching policy, specifying:
|
|
-- * How the memcache GET command is executed, including whether to get
|
|
-- records from local cache only, from NDB only, from local cache if
|
|
-- present (treating NDB as a backing store), or not at all.
|
|
-- * Similarly, how memcache SET commands are executed.
|
|
-- * How memcache DELETE commands are executed.
|
|
-- * Whether flushing the cache should cause a mass delete from NDB.
|
|
--
|
|
-- ------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `cache_policies` (
|
|
`policy_name` VARCHAR(40) NOT NULL PRIMARY KEY,
|
|
`get_policy` ENUM('cache_only','ndb_only','caching','disabled') NOT NULL ,
|
|
`set_policy` ENUM('cache_only','ndb_only','caching','disabled') NOT NULL ,
|
|
`delete_policy` ENUM('cache_only','ndb_only','caching','disabled') NOT NULL ,
|
|
`flush_from_db` ENUM('false', 'true') NOT NULL DEFAULT 'false'
|
|
) ENGINE = ndbcluster;
|
|
|
|
|
|
-- ------------------------------------------------------------------------
|
|
-- Table `containers`
|
|
--
|
|
-- A container record describes an NDB table used for data storage by
|
|
-- NDB Memcache.
|
|
-- `key_columns` and `value_columns` are comma-separated lists of the
|
|
-- columns that make up the memcache key and value.
|
|
-- If `flags` is numeric, its value is used by memcached as the flags
|
|
-- value for every response. If it holds the name of a column, then
|
|
-- that column is used to store and retrieve the flags.
|
|
--
|
|
-- ------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `containers` (
|
|
`name` varchar(50) not null primary key,
|
|
`db_schema` VARCHAR(250) NOT NULL,
|
|
`db_table` VARCHAR(250) NOT NULL,
|
|
`key_columns` VARCHAR(250) NOT NULL,
|
|
`value_columns` VARCHAR(250),
|
|
`flags` VARCHAR(250) NOT NULL DEFAULT "0",
|
|
`increment_column` VARCHAR(250),
|
|
`cas_column` VARCHAR(250),
|
|
`expire_time_column` VARCHAR(250),
|
|
`large_values_table` VARCHAR(250)
|
|
) ENGINE = ndbcluster;
|
|
|
|
|
|
-- ------------------------------------------------------------------------
|
|
-- Table `key_prefixes`
|
|
--
|
|
-- A key_prefixes record is the culmination of the configuration.
|
|
-- For a particular memcache server role, and a prefix of a memcache key,
|
|
-- it defines a cluster, cache policy, and container.
|
|
--
|
|
-- ------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `key_prefixes` (
|
|
`server_role_id` INT UNSIGNED NOT NULL DEFAULT 0,
|
|
`key_prefix` VARCHAR(250) NOT NULL ,
|
|
`cluster_id` INT UNSIGNED NOT NULL DEFAULT 0,
|
|
`policy` VARCHAR(40) NOT NULL,
|
|
`container` VARCHAR(50),
|
|
PRIMARY KEY (`server_role_id`, `key_prefix`)
|
|
) ENGINE = ndbcluster;
|
|
|
|
|
|
-- ------------------------------------------------------------------------
|
|
-- Table `last_memcached_signon`
|
|
--
|
|
-- Whenever a memcached server starts up and reads its configuration, it
|
|
-- updates its signon record in this table.
|
|
--
|
|
-- ------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `last_memcached_signon` (
|
|
`ndb_node_id` INT UNSIGNED NOT NULL PRIMARY KEY,
|
|
`hostname` VARCHAR(255) NOT NULL,
|
|
`server_role` VARCHAR(40) NOT NULL,
|
|
`signon_time` timestamp NOT NULL
|
|
) ENGINE = ndbcluster;
|
|
|
|
|
|
-- ------------------------------------------------------------------------
|
|
-- DEMONSTRATION TABLES
|
|
-- ------------------------------------------------------------------------
|
|
|
|
-- ------------------------------------------------------------------------
|
|
-- Table `demo_table`
|
|
--
|
|
-- The demo_table is not strictly part of the configuration schema, but
|
|
-- it is defined as a container by the pre-loaded default configuration
|
|
-- and holds application data.
|
|
--
|
|
-- ------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `demo_table` (
|
|
`mkey` VARCHAR(250) NOT NULL ,
|
|
`math_value` BIGINT UNSIGNED,
|
|
`flags` INT UNSIGNED,
|
|
`cas_value` BIGINT UNSIGNED,
|
|
`string_value` VARBINARY(13500),
|
|
PRIMARY KEY USING HASH (mkey)
|
|
) ENGINE = ndbcluster;
|
|
|
|
-- ------------------------------------------------------------------------
|
|
-- Table `demo_table_large`
|
|
--
|
|
-- A table demonstrating externalized large values.
|
|
-- Use with key prefix "b:"
|
|
--
|
|
-- ------------------------------------------------------------------------
|
|
|
|
CREATE TABLE IF NOT EXISTS `demo_table_large` (
|
|
`mkey` VARCHAR(250) NOT NULL,
|
|
`flags` INT UNSIGNED,
|
|
`cas_value` BIGINT UNSIGNED,
|
|
`string_value` VARBINARY(2000),
|
|
`ext_id` INT UNSIGNED,
|
|
`ext_size` INT UNSIGNED,
|
|
PRIMARY KEY USING HASH (mkey)
|
|
) ENGINE = ndbcluster;
|
|
|
|
-- ------------------------------------------------------------------------
|
|
-- Table `external_values`
|
|
--
|
|
-- Used with demo_table_large to store large values
|
|
--
|
|
-- ------------------------------------------------------------------------
|
|
|
|
CREATE TABLE IF NOT EXISTS `external_values` (
|
|
`id` INT UNSIGNED AUTO_INCREMENT NOT NULL,
|
|
`part` SMALLINT NOT NULL,
|
|
`content` VARBINARY(13950) NOT NULL,
|
|
PRIMARY KEY (id,part)
|
|
) ENGINE = ndbcluster;
|
|
|
|
|
|
-- ------------------------------------------------------------------------
|
|
-- Table `demo_table_tabs`
|
|
--
|
|
-- A table demonstrating three value columns, tab-separated output, and
|
|
-- expire times.
|
|
-- By default you can use this table with key prefix "t:"
|
|
--
|
|
-- ------------------------------------------------------------------------
|
|
CREATE TABLE IF NOT EXISTS `demo_table_tabs` (
|
|
`mkey` INT NOT NULL PRIMARY KEY,
|
|
`val1` VARCHAR(60),
|
|
`val2` varchar(10),
|
|
`val3` INT,
|
|
`flags` INT UNSIGNED NULL,
|
|
expire_time TIMESTAMP NULL
|
|
) ENGINE = ndbcluster;
|
|
|
|
|
|
-- -----------------------------------------------------
|
|
-- Default entries for all tables
|
|
-- -----------------------------------------------------
|
|
|
|
-- meta table
|
|
-- Create an entry for ndbmemcache metadata v1
|
|
INSERT INTO meta VALUES ("ndbmemcache", "1.2");
|
|
|
|
-- memcache_server_roles table
|
|
-- Create an entry for the default role and example roles.
|
|
INSERT INTO memcache_server_roles (role_name, role_id) VALUES ("default_role", 0);
|
|
INSERT INTO memcache_server_roles (role_name, role_id) VALUES ("db-only", 1);
|
|
INSERT INTO memcache_server_roles (role_name, role_id) VALUES ("mc-only", 2);
|
|
INSERT INTO memcache_server_roles (role_name, role_id) VALUES ("ndb-caching", 3);
|
|
INSERT INTO memcache_server_roles (role_name, role_id) VALUES ("large", 4);
|
|
|
|
-- ndb_clusters table
|
|
-- Create an entry for the primary cluster.
|
|
INSERT INTO ndb_clusters values (0, @@ndb_connectstring, 250);
|
|
|
|
-- cache_policies table
|
|
-- Create some sample policies.
|
|
INSERT INTO cache_policies (policy_name, get_policy, set_policy, delete_policy)
|
|
VALUES("memcache-only", "cache_only", "cache_only", "cache_only"),
|
|
("ndb-only", "ndb_only", "ndb_only", "ndb_only"), /* no flush */
|
|
("ndb-test", "ndb_only", "ndb_only", "ndb_only"), /* flush allowed */
|
|
("caching", "caching", "caching", "caching"),
|
|
("caching-with-local-deletes", "caching", "caching", "cache_only"),
|
|
("ndb-read-only", "ndb_only", "disabled", "disabled");
|
|
|
|
-- FLUSH_ALL is enabled on the ndb-test policy
|
|
UPDATE cache_policies set flush_from_db = "true" where policy_name = "ndb-test";
|
|
|
|
-- containers table
|
|
INSERT INTO containers
|
|
SET name = "demo_table", db_schema = "ndbmemcache", db_table = "demo_table",
|
|
key_columns = "mkey", value_columns = "string_value", flags = "flags",
|
|
increment_column = "math_value", cas_column = "cas_value";
|
|
|
|
INSERT INTO containers
|
|
SET name = "demo_tabs", db_schema = "ndbmemcache", db_table = "demo_table_tabs",
|
|
key_columns = "mkey", value_columns = "val1,val2,val3", flags = "flags",
|
|
expire_time_column = "expire_time";
|
|
|
|
INSERT INTO containers
|
|
SET name = "demo_ext", db_schema = "ndbmemcache", db_table = "demo_table_large",
|
|
key_columns = "mkey", value_columns = "string_value",
|
|
cas_column = "cas_value", flags = "flags",
|
|
large_values_table = "ndbmemcache.external_values";
|
|
|
|
-- key_prefixes table
|
|
INSERT INTO key_prefixes (server_role_id, key_prefix, cluster_id,
|
|
policy, container)
|
|
VALUES (0, "", 0, "ndb-test", "demo_table"),
|
|
(0, "mc:", 0, "memcache-only", NULL),
|
|
(0, "t:", 0, "ndb-test", "demo_tabs"), /* default role */
|
|
(0, "b:", 0, "ndb-test", "demo_ext"),
|
|
|
|
(1, "", 0, "ndb-only", "demo_table"),
|
|
(1, "t:", 0, "ndb-only", "demo_tabs"), /* db-only role */
|
|
(1, "b:", 0, "ndb-only", "demo_ext"),
|
|
|
|
(2, "", 0, "memcache-only", NULL), /* mc-only role */
|
|
|
|
(3, "", 0, "caching", "demo_table"), /* ndb-caching role */
|
|
(3, "t:", 0, "caching", "demo_tabs"),
|
|
(3, "b:", 0, "caching", "demo_ext"),
|
|
|
|
(4, "" , 0, "ndb-test", "demo_ext");
|
|
;
|
|
|
|
|
|
|
|
|
|
|