<ul dir="auto">
<li>
<p dir="auto">table_demo (id int primary key, column_a int, column_b longtext)</p>
</li>
<li>
<p dir="auto">mybatis dynamic sql like this:</p>
</li>
</ul>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="insert into table_demo (id, column_a, column_b) values ((#{id,jdbcType=VARCHAR}, #{columnA,jdbcType=INTEGER}, #{columnB,jdbcType=LONGVARCHAR})"><pre class="notranslate"><code class="notranslate">insert into table_demo (id, column_a, column_b) values ((#{id,jdbcType=VARCHAR}, #{columnA,jdbcType=INTEGER}, #{columnB,jdbcType=LONGVARCHAR})
</code></pre></div>
<p dir="auto">If jdbcType of columnB is specified as <code class="notranslate">org.apache.ibatis.type.JdbcType.LONGVARCHAR</code>, this parameter will be handled in <code class="notranslate">SimpleExecutor.preparedStatement(..)</code> method, and then <code class="notranslate">org.apache.ibatis.type.ClobTypeHandler.setNonNullParameter(..)</code> method will be called.</p>
<p dir="auto">Type of column_b will be packaged in StringReader, thus the <code class="notranslate">parameters</code> property's type in ShardingPreparedStatement will be set to StringReader.</p>
<p dir="auto">Subsequently, <code class="notranslate">replaySetParameter(final PreparedStatement preparedStatement, final List&lt;Object&gt; parameters)</code> will be called in <code class="notranslate">ShardingPreparedStatement's route()</code> method.</p>
<p dir="auto">When the code goes to <code class="notranslate">each.invoke(preparedStatement);</code>, <code class="notranslate">com.mysql.jdbc.PreparedStatement.setObject(int parameterIndex, Object parameterObj)</code> will be invoked, and <code class="notranslate">StringReader</code> cannot be identified by <code class="notranslate">PreparedStatement</code>, then <code class="notranslate">setSerializableObject</code> will be called, but <code class="notranslate">StringReader</code> doesnot implement <code class="notranslate">Serializable</code>, so <code class="notranslate">java.io.NotSerializableException</code> occur.</p>
<p dir="auto">For English only, other languages we will close it directly.</p>
<p dir="auto">Please answer these questions before submitting your issue. Thanks!</p>
<h3 dir="auto">Which version of Sharding-Sphere do you using?</h3>
<p dir="auto">sharding-proxy-3.0.0.M2-SNAPSHOT （with github dev branch）</p>
<p dir="auto">I use the database pressure measurement tool to test the sharding-proxy performance, But the results are very unexpected.<br>
I can get 3000 QPS in normal master-slave replication state, however only get 1000 QPS with sharding-proxy.</p>
<p dir="auto">My configuration is as follows：</p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="dataSources:
  ds_0:
    url: jdbc:mysql://172.60.20.34:3306/sbtest?serverTimezone=UTC&amp;useSSL=false
    username: root
    password: 123456
    autoCommit: false
    connectionTimeout: 30000
    idleTimeout: 60000
    maxLifetime: 1800000
    maximumPoolSize: 65
  ds_1:
    url: jdbc:mysql://172.60.20.46:3306/sbtest?serverTimezone=UTC&amp;useSSL=false
    username: root
    password: 123456
    autoCommit: false
    connectionTimeout: 30000
    idleTimeout: 60000
    maxLifetime: 1800000
    maximumPoolSize: 65
masterSlaveRule:
  name: ds_ms
  masterDataSourceName: ds_0
  slaveDataSourceNames:
    - ds_1
  props:
    proxy.mode: MEMORY_STRICTLY
    proxy.transaction.mode: TCC
    proxy.max.working.threads: 100
    sql.show: true
proxyAuthority:
  username: root
  password: root"><pre class="notranslate"><code class="notranslate">dataSources:
  ds_0:
    url: jdbc:mysql://172.60.20.34:3306/sbtest?serverTimezone=UTC&amp;useSSL=false
    username: root
    password: 123456
    autoCommit: false
    connectionTimeout: 30000
    idleTimeout: 60000
    maxLifetime: 1800000
    maximumPoolSize: 65
  ds_1:
    url: jdbc:mysql://172.60.20.46:3306/sbtest?serverTimezone=UTC&amp;useSSL=false
    username: root
    password: 123456
    autoCommit: false
    connectionTimeout: 30000
    idleTimeout: 60000
    maxLifetime: 1800000
    maximumPoolSize: 65
masterSlaveRule:
  name: ds_ms
  masterDataSourceName: ds_0
  slaveDataSourceNames:
    - ds_1
  props:
    proxy.mode: MEMORY_STRICTLY
    proxy.transaction.mode: TCC
    proxy.max.working.threads: 100
    sql.show: true
proxyAuthority:
  username: root
  password: root
</code></pre></div>
<p dir="auto">and test tool is <strong>sysbench</strong></p>
<p dir="auto">the test script is as follows：</p>
<p dir="auto"><strong>oltp_common.lua</strong></p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="function init()
   assert(event ~= nil,
          &quot;this script is meant to be included by other OLTP scripts and &quot; ..
             &quot;should not be called directly.&quot;)
end

if sysbench.cmdline.command == nil then
   error(&quot;Command is required. Supported commands: prepare, prewarm, run, &quot; ..
            &quot;cleanup, help&quot;)
end

-- Command line options
sysbench.cmdline.options = {
   table_size =
      {&quot;Number of rows per table&quot;, 10000},
   range_size =
      {&quot;Range size for range SELECT queries&quot;, 100},
   tables =
      {&quot;Number of tables&quot;, 1},
   point_selects =
      {&quot;Number of point SELECT queries per transaction&quot;, 10},
   simple_ranges =
      {&quot;Number of simple range SELECT queries per transaction&quot;, 1},
   sum_ranges =
      {&quot;Number of SELECT SUM() queries per transaction&quot;, 1},
   order_ranges =
      {&quot;Number of SELECT ORDER BY queries per transaction&quot;, 1},
   distinct_ranges =
      {&quot;Number of SELECT DISTINCT queries per transaction&quot;, 1},
   index_updates =
      {&quot;Number of UPDATE index queries per transaction&quot;, 1},
   non_index_updates =
      {&quot;Number of UPDATE non-index queries per transaction&quot;, 1},
   delete_inserts =
      {&quot;Number of DELETE/INSERT combinations per transaction&quot;, 1},
   range_selects =
      {&quot;Enable/disable all range SELECT queries&quot;, true},
   auto_inc =
   {&quot;Use AUTO_INCREMENT column as Primary Key (for MySQL), &quot; ..
       &quot;or its alternatives in other DBMS. When disabled, use &quot; ..
       &quot;client-generated IDs&quot;, true},
   skip_trx =
      {&quot;Don't start explicit transactions and execute all queries &quot; ..
          &quot;in the AUTOCOMMIT mode&quot;, false},
   secondary =
      {&quot;Use a secondary index in place of the PRIMARY KEY&quot;, false},
   create_secondary =
      {&quot;Create a secondary index in addition to the PRIMARY KEY&quot;, true},
   mysql_storage_engine =
      {&quot;Storage engine, if MySQL is used&quot;, &quot;innodb&quot;},
   pgsql_variant =
      {&quot;Use this PostgreSQL variant when running with the &quot; ..
          &quot;PostgreSQL driver. The only currently supported &quot; ..
          &quot;variant is 'redshift'. When enabled, &quot; ..
          &quot;create_secondary is automatically disabled, and &quot; ..
          &quot;delete_inserts is set to 0&quot;}
}

-- Prepare the dataset. This command supports parallel execution, i.e. will
-- benefit from executing with --threads &gt; 1 as long as --tables &gt; 1
function cmd_prepare()
   local drv = sysbench.sql.driver()
   local con = drv:connect()

   for i = sysbench.tid % sysbench.opt.threads + 1, sysbench.opt.tables,
   sysbench.opt.threads do
     create_table(drv, con, i)
   end
end

-- Preload the dataset into the server cache. This command supports parallel
-- execution, i.e. will benefit from executing with --threads &gt; 1 as long as
-- --tables &gt; 1
--
-- PS. Currently, this command is only meaningful for MySQL/InnoDB benchmarks
function cmd_prewarm()
   local drv = sysbench.sql.driver()
   local con = drv:connect()

   assert(drv:name() == &quot;mysql&quot;, &quot;prewarm is currently MySQL only&quot;)

   -- Do not create on disk tables for subsequent queries
   con:query(&quot;SET tmp_table_size=2*1024*1024*1024&quot;)
   con:query(&quot;SET max_heap_table_size=2*1024*1024*1024&quot;)

   for i = sysbench.tid % sysbench.opt.threads + 1, sysbench.opt.tables,
   sysbench.opt.threads do
      local t = &quot;sbtest&quot; .. i
      print(&quot;Prewarming table &quot; .. t)
      con:query(&quot;ANALYZE TABLE sbtest&quot; .. i)
      con:query(string.format(
                   &quot;SELECT AVG(id) FROM &quot; ..
                      &quot;(SELECT * FROM %s FORCE KEY (PRIMARY) &quot; ..
                      &quot;LIMIT %u) t&quot;,
                   t, sysbench.opt.table_size))
      con:query(string.format(
                   &quot;SELECT COUNT(*) FROM &quot; ..
                      &quot;(SELECT * FROM %s WHERE k LIKE '%%0%%' LIMIT %u) t&quot;,
                   t, sysbench.opt.table_size))
   end
end

-- Implement parallel prepare and prewarm commands
sysbench.cmdline.commands = {
   prepare = {cmd_prepare, sysbench.cmdline.PARALLEL_COMMAND},
   prewarm = {cmd_prewarm, sysbench.cmdline.PARALLEL_COMMAND}
}


-- Template strings of random digits with 11-digit groups separated by dashes

-- 10 groups, 119 characters
local c_value_template = &quot;###########-###########-###########-&quot; ..
   &quot;###########-###########-###########-&quot; ..
   &quot;###########-###########-###########-&quot; ..
   &quot;###########&quot;

-- 5 groups, 59 characters
local pad_value_template = &quot;###########-###########-###########-&quot; ..
   &quot;###########-###########&quot;

function get_c_value()
   return sysbench.rand.string(c_value_template)
end

function get_pad_value()
   return sysbench.rand.string(pad_value_template)
end

function create_table(drv, con, table_num)
   local id_index_def, id_def
   local engine_def = &quot;&quot;
   local extra_table_options = &quot;&quot;
   local query

   if sysbench.opt.secondary then
     id_index_def = &quot;KEY xid&quot;
   else
     id_index_def = &quot;PRIMARY KEY&quot;
   end

   if drv:name() == &quot;mysql&quot; or drv:name() == &quot;attachsql&quot; or
      drv:name() == &quot;drizzle&quot;
   then
      if sysbench.opt.auto_inc then
         id_def = &quot;INTEGER NOT NULL AUTO_INCREMENT&quot;
      else
         id_def = &quot;INTEGER NOT NULL&quot;
      end
      engine_def = &quot;/*! ENGINE = &quot; .. sysbench.opt.mysql_storage_engine .. &quot; */&quot;
      extra_table_options = mysql_table_options or &quot;&quot;
   elseif drv:name() == &quot;pgsql&quot;
   then
      if not sysbench.opt.auto_inc then
         id_def = &quot;INTEGER NOT NULL&quot;
      elseif pgsql_variant == 'redshift' then
        id_def = &quot;INTEGER IDENTITY(1,1)&quot;
      else
        id_def = &quot;SERIAL&quot;
      end
   else
      error(&quot;Unsupported database driver:&quot; .. drv:name())
   end

   print(string.format(&quot;Creating table 'sbtest%d'...&quot;, table_num))

   query = string.format([[
CREATE TABLE sbtest%d(
  id %s,
  k INTEGER DEFAULT '0' NOT NULL,
  c CHAR(120) DEFAULT '' NOT NULL,
  pad CHAR(60) DEFAULT '' NOT NULL,
  %s (id)
) %s %s]],
      table_num, id_def, id_index_def, engine_def, extra_table_options)

   con:query(query)

   if (sysbench.opt.table_size &gt; 0) then
      print(string.format(&quot;Inserting %d records into 'sbtest%d'&quot;,
                          sysbench.opt.table_size, table_num))
   end

   if sysbench.opt.auto_inc then
      query = &quot;INSERT INTO sbtest&quot; .. table_num .. &quot;(k, c, pad) VALUES&quot;
   else
      query = &quot;INSERT INTO sbtest&quot; .. table_num .. &quot;(id, k, c, pad) VALUES&quot;
   end

   con:bulk_insert_init(query)

   local c_val
   local pad_val

   for i = 1, sysbench.opt.table_size do

      c_val = get_c_value()
      pad_val = get_pad_value()

      if (sysbench.opt.auto_inc) then
         query = string.format(&quot;(%d, '%s', '%s')&quot;,
                               sb_rand(1, sysbench.opt.table_size), c_val,
                               pad_val)
      else
         query = string.format(&quot;(%d, %d, '%s', '%s')&quot;,
                               i, sb_rand(1, sysbench.opt.table_size), c_val,
                               pad_val)
      end

      con:bulk_insert_next(query)
   end

   con:bulk_insert_done()

   if sysbench.opt.create_secondary then
      print(string.format(&quot;Creating a secondary index on 'sbtest%d'...&quot;,
                          table_num))
      con:query(string.format(&quot;CREATE INDEX k_%d ON sbtest%d(k)&quot;,
                              table_num, table_num))
   end
end

local t = sysbench.sql.type
local stmt_defs = {
   point_selects = {
      &quot;SELECT c FROM sbtest%u WHERE id=?&quot;,
      t.INT},
   simple_ranges = {
      &quot;SELECT c FROM sbtest%u WHERE id BETWEEN ? AND ?&quot;,
      t.INT, t.INT},
   sum_ranges = {
      &quot;SELECT SUM(k) FROM sbtest%u WHERE id BETWEEN ? AND ?&quot;,
       t.INT, t.INT},
   order_ranges = {
      &quot;SELECT c FROM sbtest%u WHERE id BETWEEN ? AND ? ORDER BY c&quot;,
       t.INT, t.INT},
   distinct_ranges = {
      &quot;SELECT DISTINCT c FROM sbtest%u WHERE id BETWEEN ? AND ? ORDER BY c&quot;,
      t.INT, t.INT},
   index_updates = {
      &quot;UPDATE sbtest%u SET k=k+1 WHERE id=?&quot;,
      t.INT},
   non_index_updates = {
      &quot;UPDATE sbtest%u SET c=? WHERE id=?&quot;,
      {t.CHAR, 120}, t.INT},
   deletes = {
      &quot;DELETE FROM sbtest%u WHERE id=?&quot;,
      t.INT},
   inserts = {
      &quot;INSERT INTO sbtest%u (id, k, c, pad) VALUES (?, ?, ?, ?)&quot;,
      t.INT, t.INT, {t.CHAR, 120}, {t.CHAR, 60}},
}

function prepare_begin()
   stmt.begin = con:prepare(&quot;BEGIN&quot;)
end

function prepare_commit()
   stmt.commit = con:prepare(&quot;COMMIT&quot;)
end

function prepare_for_each_table(key)
   for t = 1, sysbench.opt.tables do
      stmt[t][key] = con:prepare(string.format(stmt_defs[key][1], t))

      local nparam = #stmt_defs[key] - 1

      if nparam &gt; 0 then
         param[t][key] = {}
      end

      for p = 1, nparam do
         local btype = stmt_defs[key][p+1]
         local len

         if type(btype) == &quot;table&quot; then
            len = btype[2]
            btype = btype[1]
         end
         if btype == sysbench.sql.type.VARCHAR or
            btype == sysbench.sql.type.CHAR then
               param[t][key][p] = stmt[t][key]:bind_create(btype, len)
         else
            param[t][key][p] = stmt[t][key]:bind_create(btype)
         end
      end

      if nparam &gt; 0 then
         stmt[t][key]:bind_param(unpack(param[t][key]))
      end
   end
end

function prepare_point_selects()
   prepare_for_each_table(&quot;point_selects&quot;)
end

function prepare_simple_ranges()
   prepare_for_each_table(&quot;simple_ranges&quot;)
end

function prepare_sum_ranges()
   prepare_for_each_table(&quot;sum_ranges&quot;)
end

function prepare_order_ranges()
   prepare_for_each_table(&quot;order_ranges&quot;)
end

function prepare_distinct_ranges()
   prepare_for_each_table(&quot;distinct_ranges&quot;)
end

function prepare_index_updates()
   prepare_for_each_table(&quot;index_updates&quot;)
end

function prepare_non_index_updates()
   prepare_for_each_table(&quot;non_index_updates&quot;)
end

function prepare_delete_inserts()
   prepare_for_each_table(&quot;deletes&quot;)
   prepare_for_each_table(&quot;inserts&quot;)
end

function thread_init()
   drv = sysbench.sql.driver()
   con = drv:connect()

   -- Create global nested tables for prepared statements and their
   -- parameters. We need a statement and a parameter set for each combination
   -- of connection/table/query
   stmt = {}
   param = {}

   for t = 1, sysbench.opt.tables do
      stmt[t] = {}
      param[t] = {}
   end

   -- This function is a 'callback' defined by individual benchmark scripts
   prepare_statements()
end

-- Close prepared statements
function close_statements()
   for t = 1, sysbench.opt.tables do
      for k, s in pairs(stmt[t]) do
         stmt[t][k]:close()
      end
   end
   if (stmt.begin ~= nil) then
      stmt.begin:close()
   end
   if (stmt.commit ~= nil) then
      stmt.commit:close()
   end
end

function thread_done()
   close_statements()
   con:disconnect()
end

function cleanup()
   local drv = sysbench.sql.driver()
   local con = drv:connect()

   for i = 1, sysbench.opt.tables do
      print(string.format(&quot;Dropping table 'sbtest%d'...&quot;, i))
      con:query(&quot;DROP TABLE IF EXISTS sbtest&quot; .. i )
   end
end

local function get_table_num()
   return sysbench.rand.uniform(1, sysbench.opt.tables)
end

local function get_id()
   return sysbench.rand.default(1, sysbench.opt.table_size)
end

function begin()
   stmt.begin:execute()
end

function commit()
   stmt.commit:execute()
end

function execute_point_selects()
   local tnum = get_table_num()
   local i

   for i = 1, sysbench.opt.point_selects do
      param[tnum].point_selects[1]:set(get_id())

      stmt[tnum].point_selects:execute()
   end
end

local function execute_range(key)
   local tnum = get_table_num()

   for i = 1, sysbench.opt[key] do
      local id = get_id()

      param[tnum][key][1]:set(id)
      param[tnum][key][2]:set(id + sysbench.opt.range_size - 1)

      stmt[tnum][key]:execute()
   end
end

function execute_simple_ranges()
   execute_range(&quot;simple_ranges&quot;)
end

function execute_sum_ranges()
   execute_range(&quot;sum_ranges&quot;)
end

function execute_order_ranges()
   execute_range(&quot;order_ranges&quot;)
end

function execute_distinct_ranges()
   execute_range(&quot;distinct_ranges&quot;)
end

function execute_index_updates()
   local tnum = get_table_num()

   for i = 1, sysbench.opt.index_updates do
      param[tnum].index_updates[1]:set(get_id())

      stmt[tnum].index_updates:execute()
   end
end

function execute_non_index_updates()
   local tnum = get_table_num()

   for i = 1, sysbench.opt.non_index_updates do
      param[tnum].non_index_updates[1]:set_rand_str(c_value_template)
      param[tnum].non_index_updates[2]:set(get_id())

      stmt[tnum].non_index_updates:execute()
   end
end

function execute_delete_inserts()
   local tnum = get_table_num()

   for i = 1, sysbench.opt.delete_inserts do
      local id = get_id()
      local k = get_id()

      param[tnum].deletes[1]:set(id)

      param[tnum].inserts[1]:set(id)
      param[tnum].inserts[2]:set(k)
      param[tnum].inserts[3]:set_rand_str(c_value_template)
      param[tnum].inserts[4]:set_rand_str(pad_value_template)

      stmt[tnum].deletes:execute()
      stmt[tnum].inserts:execute()
   end
end

-- Re-prepare statements if we have reconnected, which is possible when some of
-- the listed error codes are in the --mysql-ignore-errors list
function sysbench.hooks.before_restart_event(errdesc)
   if errdesc.sql_errno == 2013 or -- CR_SERVER_LOST
      errdesc.sql_errno == 2055 or -- CR_SERVER_LOST_EXTENDED
      errdesc.sql_errno == 2006 or -- CR_SERVER_GONE_ERROR
      errdesc.sql_errno == 2011    -- CR_TCP_CONNECTION
   then
      close_statements()
      prepare_statements()
   end
end"><pre class="notranslate"><code class="notranslate">function init()
   assert(event ~= nil,
          "this script is meant to be included by other OLTP scripts and " ..
             "should not be called directly.")
end

if sysbench.cmdline.command == nil then
   error("Command is required. Supported commands: prepare, prewarm, run, " ..
            "cleanup, help")
end

-- Command line options
sysbench.cmdline.options = {
   table_size =
      {"Number of rows per table", 10000},
   range_size =
      {"Range size for range SELECT queries", 100},
   tables =
      {"Number of tables", 1},
   point_selects =
      {"Number of point SELECT queries per transaction", 10},
   simple_ranges =
      {"Number of simple range SELECT queries per transaction", 1},
   sum_ranges =
      {"Number of SELECT SUM() queries per transaction", 1},
   order_ranges =
      {"Number of SELECT ORDER BY queries per transaction", 1},
   distinct_ranges =
      {"Number of SELECT DISTINCT queries per transaction", 1},
   index_updates =
      {"Number of UPDATE index queries per transaction", 1},
   non_index_updates =
      {"Number of UPDATE non-index queries per transaction", 1},
   delete_inserts =
      {"Number of DELETE/INSERT combinations per transaction", 1},
   range_selects =
      {"Enable/disable all range SELECT queries", true},
   auto_inc =
   {"Use AUTO_INCREMENT column as Primary Key (for MySQL), " ..
       "or its alternatives in other DBMS. When disabled, use " ..
       "client-generated IDs", true},
   skip_trx =
      {"Don't start explicit transactions and execute all queries " ..
          "in the AUTOCOMMIT mode", false},
   secondary =
      {"Use a secondary index in place of the PRIMARY KEY", false},
   create_secondary =
      {"Create a secondary index in addition to the PRIMARY KEY", true},
   mysql_storage_engine =
      {"Storage engine, if MySQL is used", "innodb"},
   pgsql_variant =
      {"Use this PostgreSQL variant when running with the " ..
          "PostgreSQL driver. The only currently supported " ..
          "variant is 'redshift'. When enabled, " ..
          "create_secondary is automatically disabled, and " ..
          "delete_inserts is set to 0"}
}

-- Prepare the dataset. This command supports parallel execution, i.e. will
-- benefit from executing with --threads &gt; 1 as long as --tables &gt; 1
function cmd_prepare()
   local drv = sysbench.sql.driver()
   local con = drv:connect()

   for i = sysbench.tid % sysbench.opt.threads + 1, sysbench.opt.tables,
   sysbench.opt.threads do
     create_table(drv, con, i)
   end
end

-- Preload the dataset into the server cache. This command supports parallel
-- execution, i.e. will benefit from executing with --threads &gt; 1 as long as
-- --tables &gt; 1
--
-- PS. Currently, this command is only meaningful for MySQL/InnoDB benchmarks
function cmd_prewarm()
   local drv = sysbench.sql.driver()
   local con = drv:connect()

   assert(drv:name() == "mysql", "prewarm is currently MySQL only")

   -- Do not create on disk tables for subsequent queries
   con:query("SET tmp_table_size=2*1024*1024*1024")
   con:query("SET max_heap_table_size=2*1024*1024*1024")

   for i = sysbench.tid % sysbench.opt.threads + 1, sysbench.opt.tables,
   sysbench.opt.threads do
      local t = "sbtest" .. i
      print("Prewarming table " .. t)
      con:query("ANALYZE TABLE sbtest" .. i)
      con:query(string.format(
                   "SELECT AVG(id) FROM " ..
                      "(SELECT * FROM %s FORCE KEY (PRIMARY) " ..
                      "LIMIT %u) t",
                   t, sysbench.opt.table_size))
      con:query(string.format(
                   "SELECT COUNT(*) FROM " ..
                      "(SELECT * FROM %s WHERE k LIKE '%%0%%' LIMIT %u) t",
                   t, sysbench.opt.table_size))
   end
end

-- Implement parallel prepare and prewarm commands
sysbench.cmdline.commands = {
   prepare = {cmd_prepare, sysbench.cmdline.PARALLEL_COMMAND},
   prewarm = {cmd_prewarm, sysbench.cmdline.PARALLEL_COMMAND}
}


-- Template strings of random digits with 11-digit groups separated by dashes

-- 10 groups, 119 characters
local c_value_template = "###########-###########-###########-" ..
   "###########-###########-###########-" ..
   "###########-###########-###########-" ..
   "###########"

-- 5 groups, 59 characters
local pad_value_template = "###########-###########-###########-" ..
   "###########-###########"

function get_c_value()
   return sysbench.rand.string(c_value_template)
end

function get_pad_value()
   return sysbench.rand.string(pad_value_template)
end

function create_table(drv, con, table_num)
   local id_index_def, id_def
   local engine_def = ""
   local extra_table_options = ""
   local query

   if sysbench.opt.secondary then
     id_index_def = "KEY xid"
   else
     id_index_def = "PRIMARY KEY"
   end

   if drv:name() == "mysql" or drv:name() == "attachsql" or
      drv:name() == "drizzle"
   then
      if sysbench.opt.auto_inc then
         id_def = "INTEGER NOT NULL AUTO_INCREMENT"
      else
         id_def = "INTEGER NOT NULL"
      end
      engine_def = "/*! ENGINE = " .. sysbench.opt.mysql_storage_engine .. " */"
      extra_table_options = mysql_table_options or ""
   elseif drv:name() == "pgsql"
   then
      if not sysbench.opt.auto_inc then
         id_def = "INTEGER NOT NULL"
      elseif pgsql_variant == 'redshift' then
        id_def = "INTEGER IDENTITY(1,1)"
      else
        id_def = "SERIAL"
      end
   else
      error("Unsupported database driver:" .. drv:name())
   end

   print(string.format("Creating table 'sbtest%d'...", table_num))

   query = string.format([[
CREATE TABLE sbtest%d(
  id %s,
  k INTEGER DEFAULT '0' NOT NULL,
  c CHAR(120) DEFAULT '' NOT NULL,
  pad CHAR(60) DEFAULT '' NOT NULL,
  %s (id)
) %s %s]],
      table_num, id_def, id_index_def, engine_def, extra_table_options)

   con:query(query)

   if (sysbench.opt.table_size &gt; 0) then
      print(string.format("Inserting %d records into 'sbtest%d'",
                          sysbench.opt.table_size, table_num))
   end

   if sysbench.opt.auto_inc then
      query = "INSERT INTO sbtest" .. table_num .. "(k, c, pad) VALUES"
   else
      query = "INSERT INTO sbtest" .. table_num .. "(id, k, c, pad) VALUES"
   end

   con:bulk_insert_init(query)

   local c_val
   local pad_val

   for i = 1, sysbench.opt.table_size do

      c_val = get_c_value()
      pad_val = get_pad_value()

      if (sysbench.opt.auto_inc) then
         query = string.format("(%d, '%s', '%s')",
                               sb_rand(1, sysbench.opt.table_size), c_val,
                               pad_val)
      else
         query = string.format("(%d, %d, '%s', '%s')",
                               i, sb_rand(1, sysbench.opt.table_size), c_val,
                               pad_val)
      end

      con:bulk_insert_next(query)
   end

   con:bulk_insert_done()

   if sysbench.opt.create_secondary then
      print(string.format("Creating a secondary index on 'sbtest%d'...",
                          table_num))
      con:query(string.format("CREATE INDEX k_%d ON sbtest%d(k)",
                              table_num, table_num))
   end
end

local t = sysbench.sql.type
local stmt_defs = {
   point_selects = {
      "SELECT c FROM sbtest%u WHERE id=?",
      t.INT},
   simple_ranges = {
      "SELECT c FROM sbtest%u WHERE id BETWEEN ? AND ?",
      t.INT, t.INT},
   sum_ranges = {
      "SELECT SUM(k) FROM sbtest%u WHERE id BETWEEN ? AND ?",
       t.INT, t.INT},
   order_ranges = {
      "SELECT c FROM sbtest%u WHERE id BETWEEN ? AND ? ORDER BY c",
       t.INT, t.INT},
   distinct_ranges = {
      "SELECT DISTINCT c FROM sbtest%u WHERE id BETWEEN ? AND ? ORDER BY c",
      t.INT, t.INT},
   index_updates = {
      "UPDATE sbtest%u SET k=k+1 WHERE id=?",
      t.INT},
   non_index_updates = {
      "UPDATE sbtest%u SET c=? WHERE id=?",
      {t.CHAR, 120}, t.INT},
   deletes = {
      "DELETE FROM sbtest%u WHERE id=?",
      t.INT},
   inserts = {
      "INSERT INTO sbtest%u (id, k, c, pad) VALUES (?, ?, ?, ?)",
      t.INT, t.INT, {t.CHAR, 120}, {t.CHAR, 60}},
}

function prepare_begin()
   stmt.begin = con:prepare("BEGIN")
end

function prepare_commit()
   stmt.commit = con:prepare("COMMIT")
end

function prepare_for_each_table(key)
   for t = 1, sysbench.opt.tables do
      stmt[t][key] = con:prepare(string.format(stmt_defs[key][1], t))

      local nparam = #stmt_defs[key] - 1

      if nparam &gt; 0 then
         param[t][key] = {}
      end

      for p = 1, nparam do
         local btype = stmt_defs[key][p+1]
         local len

         if type(btype) == "table" then
            len = btype[2]
            btype = btype[1]
         end
         if btype == sysbench.sql.type.VARCHAR or
            btype == sysbench.sql.type.CHAR then
               param[t][key][p] = stmt[t][key]:bind_create(btype, len)
         else
            param[t][key][p] = stmt[t][key]:bind_create(btype)
         end
      end

      if nparam &gt; 0 then
         stmt[t][key]:bind_param(unpack(param[t][key]))
      end
   end
end

function prepare_point_selects()
   prepare_for_each_table("point_selects")
end

function prepare_simple_ranges()
   prepare_for_each_table("simple_ranges")
end

function prepare_sum_ranges()
   prepare_for_each_table("sum_ranges")
end

function prepare_order_ranges()
   prepare_for_each_table("order_ranges")
end

function prepare_distinct_ranges()
   prepare_for_each_table("distinct_ranges")
end

function prepare_index_updates()
   prepare_for_each_table("index_updates")
end

function prepare_non_index_updates()
   prepare_for_each_table("non_index_updates")
end

function prepare_delete_inserts()
   prepare_for_each_table("deletes")
   prepare_for_each_table("inserts")
end

function thread_init()
   drv = sysbench.sql.driver()
   con = drv:connect()

   -- Create global nested tables for prepared statements and their
   -- parameters. We need a statement and a parameter set for each combination
   -- of connection/table/query
   stmt = {}
   param = {}

   for t = 1, sysbench.opt.tables do
      stmt[t] = {}
      param[t] = {}
   end

   -- This function is a 'callback' defined by individual benchmark scripts
   prepare_statements()
end

-- Close prepared statements
function close_statements()
   for t = 1, sysbench.opt.tables do
      for k, s in pairs(stmt[t]) do
         stmt[t][k]:close()
      end
   end
   if (stmt.begin ~= nil) then
      stmt.begin:close()
   end
   if (stmt.commit ~= nil) then
      stmt.commit:close()
   end
end

function thread_done()
   close_statements()
   con:disconnect()
end

function cleanup()
   local drv = sysbench.sql.driver()
   local con = drv:connect()

   for i = 1, sysbench.opt.tables do
      print(string.format("Dropping table 'sbtest%d'...", i))
      con:query("DROP TABLE IF EXISTS sbtest" .. i )
   end
end

local function get_table_num()
   return sysbench.rand.uniform(1, sysbench.opt.tables)
end

local function get_id()
   return sysbench.rand.default(1, sysbench.opt.table_size)
end

function begin()
   stmt.begin:execute()
end

function commit()
   stmt.commit:execute()
end

function execute_point_selects()
   local tnum = get_table_num()
   local i

   for i = 1, sysbench.opt.point_selects do
      param[tnum].point_selects[1]:set(get_id())

      stmt[tnum].point_selects:execute()
   end
end

local function execute_range(key)
   local tnum = get_table_num()

   for i = 1, sysbench.opt[key] do
      local id = get_id()

      param[tnum][key][1]:set(id)
      param[tnum][key][2]:set(id + sysbench.opt.range_size - 1)

      stmt[tnum][key]:execute()
   end
end

function execute_simple_ranges()
   execute_range("simple_ranges")
end

function execute_sum_ranges()
   execute_range("sum_ranges")
end

function execute_order_ranges()
   execute_range("order_ranges")
end

function execute_distinct_ranges()
   execute_range("distinct_ranges")
end

function execute_index_updates()
   local tnum = get_table_num()

   for i = 1, sysbench.opt.index_updates do
      param[tnum].index_updates[1]:set(get_id())

      stmt[tnum].index_updates:execute()
   end
end

function execute_non_index_updates()
   local tnum = get_table_num()

   for i = 1, sysbench.opt.non_index_updates do
      param[tnum].non_index_updates[1]:set_rand_str(c_value_template)
      param[tnum].non_index_updates[2]:set(get_id())

      stmt[tnum].non_index_updates:execute()
   end
end

function execute_delete_inserts()
   local tnum = get_table_num()

   for i = 1, sysbench.opt.delete_inserts do
      local id = get_id()
      local k = get_id()

      param[tnum].deletes[1]:set(id)

      param[tnum].inserts[1]:set(id)
      param[tnum].inserts[2]:set(k)
      param[tnum].inserts[3]:set_rand_str(c_value_template)
      param[tnum].inserts[4]:set_rand_str(pad_value_template)

      stmt[tnum].deletes:execute()
      stmt[tnum].inserts:execute()
   end
end

-- Re-prepare statements if we have reconnected, which is possible when some of
-- the listed error codes are in the --mysql-ignore-errors list
function sysbench.hooks.before_restart_event(errdesc)
   if errdesc.sql_errno == 2013 or -- CR_SERVER_LOST
      errdesc.sql_errno == 2055 or -- CR_SERVER_LOST_EXTENDED
      errdesc.sql_errno == 2006 or -- CR_SERVER_GONE_ERROR
      errdesc.sql_errno == 2011    -- CR_TCP_CONNECTION
   then
      close_statements()
      prepare_statements()
   end
end
</code></pre></div>
<p dir="auto"><strong>oltp_read_write.lua</strong></p>
<div class="snippet-clipboard-content notranslate position-relative overflow-auto" data-snippet-clipboard-copy-content="require(&quot;oltp_common&quot;)

function prepare_statements()
   if not sysbench.opt.skip_trx then
      prepare_begin()
      prepare_commit()
   end

   prepare_point_selects()

   if sysbench.opt.range_selects then
      prepare_simple_ranges()
      prepare_sum_ranges()
      prepare_order_ranges()
      prepare_distinct_ranges()
   end

   prepare_index_updates()
   prepare_non_index_updates()
   prepare_delete_inserts()
end

function event()
   if not sysbench.opt.skip_trx then
      begin()
   end

   execute_point_selects()

   if sysbench.opt.range_selects then
      execute_simple_ranges()
      execute_sum_ranges()
      execute_order_ranges()
      execute_distinct_ranges()
   end

   execute_index_updates()
   execute_non_index_updates()
   execute_delete_inserts()

   if not sysbench.opt.skip_trx then
      commit()
   end
end"><pre class="notranslate"><code class="notranslate">require("oltp_common")

function prepare_statements()
   if not sysbench.opt.skip_trx then
      prepare_begin()
      prepare_commit()
   end

   prepare_point_selects()

   if sysbench.opt.range_selects then
      prepare_simple_ranges()
      prepare_sum_ranges()
      prepare_order_ranges()
      prepare_distinct_ranges()
   end

   prepare_index_updates()
   prepare_non_index_updates()
   prepare_delete_inserts()
end

function event()
   if not sysbench.opt.skip_trx then
      begin()
   end

   execute_point_selects()

   if sysbench.opt.range_selects then
      execute_simple_ranges()
      execute_sum_ranges()
      execute_order_ranges()
      execute_distinct_ranges()
   end

   execute_index_updates()
   execute_non_index_updates()
   execute_delete_inserts()

   if not sysbench.opt.skip_trx then
      commit()
   end
end
</code></pre></div>
<p dir="auto">Is the 60% loss of the sharding-proxy tool normal?</p>