Test vitess configuration

[Charles Shapiro]

I've been trying to test out company's application for compatibility issues with vitess .  For this, I initially have been trying to use the vitess/base docker with the /vt/src/vitess.io/vitess/examples/local/ scripts, with very few changes.

I'd like to try it a bit differently, for a simpler configuration

1) I want to test with a single vttablet/mysqld , with no replication.

2) I want to use a MySQL 5.5 mysqld running directly on the Centos host, not inside the docker

After we've ported the application to run without errors connecting to a vitess vgate instead of a normal 5.5 or 5.6 mysql, then we would want to expand testing with replication and multiple shards to see if it can give us a significant boost in performance and scaling.

When I'm running with the /vt/src/vitess.io/vitess/examples/local scripts, the running processes look like:

/vt/bin/vtgate -topo_implementation zk2 -topo_global_server_address localhost:21811,localhost:21812,localhost:21813 -topo_global_root /vitess/global -log_dir /vt/vtdataroot/tmp -port 15001 -grpc_port 15991 -mysql_server_port 15306 -mysql_server_socket_path /tmp/mysql.sock -mysql_auth_server_static_file ./mysql_auth_server_static_creds.json -cell test -cells_to_watch test -tablet_types_to_wait MASTER,REPLICA -gateway_implementation discoverygateway -service_map grpc-vtgateservice -pid_file /vt/vtdataroot/tmp/vtgate.pid

/vt/bin/vttablet -topo_implementation zk2 -topo_global_server_address localhost:21811,localhost:21812,localhost:21813 -topo_global_root /vitess/global -log_dir /vt/vtdataroot/tmp -tablet-path test-0000000100 -tablet_hostname  -init_keyspace schema1 -init_shard 0 -init_tablet_type replica -health_check_interval 5s -enable_semi_sync -enable_replication_reporter -backup_storage_implementation file -file_backup_storage_root /vt/vtdataroot/backups -restore_from_backup -queryserver-config-query-timeout 30000 -queryserver-config-transaction-timeout 30000 -queryserver-config-max-result-size 1999999999 -queryserver-config-transaction-cap 3300 -port 15100 -grpc_port 16100 -service_map grpc-queryservice,grpc-tabletmanager,grpc-updatestream -pid_file /vt/vtdataroot/vt_0000000100/vttablet.pid -vtctld_addr http://bd2da89d6063:15000/ -db-config-dba-uname vt_dba -db-config-dba-charset utf8 -db-config-app-uname vt_app -db-config-app-dbname vt_schema1 -db-config-app-charset utf8 -db-config-appdebug-uname vt_appdebug -db-config-appdebug-dbname vt_schema1 -db-config-appdebug-charset utf8 -db-config-allprivs-uname vt_allprivs -db-config-allprivs-dbname vt_schema1 -db-config-allprivs-charset utf8 -db-config-repl-uname vt_repl -db-config-repl-dbname vt_schema1 -db-config-repl-charset utf8 -db-config-filtered-uname vt_filtered -db-config-filtered-dbname vt_schema1 -db-config-filtered-charset utf8

If I'm running these services for a single vttablet with no replication, what do I need to change ?

Thanks in advance.

--Charles Shapiro

[Sugu Sougoumarane]

Use the latest version pulled from master branch.

Remove:

-enable_semi_sync

All -db-config flags

Add:

-disable_active_reparents

--
You received this message because you are subscribed to the Google Groups "vitess" group.
To unsubscribe from this group and stop receiving emails from it, send an email to vitess+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Simon Mudd]

Hi,

> On 26 Jul 2018, at 15:01, Charles Shapiro <charlie...@gmail.com> wrote:
>
> I've been trying to test out company's application for compatibility issues with vitess . For this, I initially have been trying to use the vitess/base docker with the /vt/src/vitess.io/vitess/examples/local/ scripts, with very few changes.
>
> I'd like to try it a bit differently, for a simpler configuration
>
> 1) I want to test with a single vttablet/mysqld , with no replication.
> 2) I want to use a MySQL 5.5 mysqld running directly on the Centos host, not inside the docker

Please don’t use MySQL 5.5. It is very old.
Use MySQL 5.7. Or MariaDB 10.X which is also supported. These are supported by Vitess.

>
> After we've ported the application to run without errors connecting to a vitess vgate instead of a normal 5.5 or 5.6 mysql, then we would want to expand testing with replication and multiple shards to see if it can give us a significant boost in performance and scaling.
>
> When I'm running with the /vt/src/vitess.io/vitess/examples/local scripts, the running processes look like:
>
>
> /vt/bin/vtgate -topo_implementation zk2 -topo_global_server_address localhost:21811,localhost:21812,localhost:21813 -topo_global_root /vitess/global -log_dir /vt/vtdataroot/tmp -port 15001 -grpc_port 15991 -mysql_server_port 15306 -mysql_server_socket_path /tmp/mysql.sock -mysql_auth_server_static_file ./mysql_auth_server_static_creds.json -cell test -cells_to_watch test -tablet_types_to_wait MASTER,REPLICA -gateway_implementation discoverygateway -service_map grpc-vtgateservice -pid_file /vt/vtdataroot/tmp/vtgate.pid
>
> /vt/bin/vttablet -topo_implementation zk2 -topo_global_server_address localhost:21811,localhost:21812,localhost:21813 -topo_global_root /vitess/global -log_dir /vt/vtdataroot/tmp -tablet-path test-0000000100 -tablet_hostname -init_keyspace schema1 -init_shard 0 -init_tablet_type replica -health_check_interval 5s -enable_semi_sync -enable_replication_reporter -backup_storage_implementation file -file_backup_storage_root /vt/vtdataroot/backups -restore_from_backup -queryserver-config-query-timeout 30000 -queryserver-config-transaction-timeout 30000 -queryserver-config-max-result-size 1999999999 -queryserver-config-transaction-cap 3300 -port 15100 -grpc_port 16100 -service_map grpc-queryservice,grpc-tabletmanager,grpc-updatestream -pid_file /vt/vtdataroot/vt_0000000100/vttablet.pid -vtctld_addr http://bd2da89d6063:15000/ -db-config-dba-uname vt_dba -db-config-dba-charset utf8 -db-config-app-uname vt_app -db-config-app-dbname vt_schema1 -db-config-app-charset utf8 -db-config-appdebug-uname vt_appdebug -db-config-appdebug-dbname vt_schema1 -db-config-appdebug-charset utf8 -db-config-allprivs-uname vt_allprivs -db-config-allprivs-dbname vt_schema1 -db-config-allprivs-charset utf8 -db-config-repl-uname vt_repl -db-config-repl-dbname vt_schema1 -db-config-repl-charset utf8 -db-config-filtered-uname vt_filtered -db-config-filtered-dbname vt_schema1 -db-config-filtered-charset utf8
>
> If I'm running these services for a single vttablet with no replication, what do I need to change ?

To fully appreciate vitess you need to use replication and thus have a master and some slaves. For vitess it’s probably good to have at least 4 slaves as some workflows like retarding need configuration adjustments if you use less. [ With the correct configuration that can be resolved. ]

There are example scripts for starting a sharded test setup. I’d look at those and apply the same rules to your environment. You can use either bare metal with one (or more) tablet + mysqld running on the server, or
do this in the container world with something like kubernetes which is also supported.

If using one or more replicas don’t forget to ensure that semi-sync replication is enabled. I’d guess it’s disabled atm if you only have a single tablet running.

Does that help?

Simon

[Charles Shapiro]

On Thursday, July 26, 2018 at 5:05:46 PM UTC+3, Simon Mudd wrote:

Hi,

> On 26 Jul 2018, at 15:01, Charles Shapiro <charlie...@gmail.com> wrote:
>
> I've been trying to test out company's application for compatibility issues with vitess .  For this, I initially have been trying to use the vitess/base docker with the /vt/src/vitess.io/vitess/examples/local/ scripts, with very few changes.
>
> I'd like to try it a bit differently, for a simpler configuration
>
> 1) I want to test with a single vttablet/mysqld , with no replication.
> 2) I want to use a MySQL 5.5 mysqld running directly on the Centos host, not inside the docker

Please don’t use MySQL 5.5. It is very old.
Use MySQL 5.7. Or MariaDB 10.X which is also supported.  These are supported by Vitess.

Our tested options are just MySQL 5.5 or Percona 5.6.

We haven't done a full functionality/performance test of our company's application with MySQL 5.7 or MariaDB.

If we have to completely test the application with MySQL 5.7 first it will mean delays.

Ideally, we like to get this working with Percona 5.6 and tokudb.

 

>
> After we've ported the application to run without errors connecting to a vitess vgate instead of a normal 5.5 or 5.6 mysql, then we would want to expand testing with replication and multiple shards to see if it can give us a significant boost in performance and scaling.
>
> When I'm running with the /vt/src/vitess.io/vitess/examples/local scripts, the running processes look like:
>
>
> /vt/bin/vtgate -topo_implementation zk2 -topo_global_server_address localhost:21811,localhost:21812,localhost:21813 -topo_global_root /vitess/global -log_dir /vt/vtdataroot/tmp -port 15001 -grpc_port 15991 -mysql_server_port 15306 -mysql_server_socket_path /tmp/mysql.sock -mysql_auth_server_static_file ./mysql_auth_server_static_creds.json -cell test -cells_to_watch test -tablet_types_to_wait MASTER,REPLICA -gateway_implementation discoverygateway -service_map grpc-vtgateservice -pid_file /vt/vtdataroot/tmp/vtgate.pid
>
> /vt/bin/vttablet -topo_implementation zk2 -topo_global_server_address localhost:21811,localhost:21812,localhost:21813 -topo_global_root /vitess/global -log_dir /vt/vtdataroot/tmp -tablet-path test-0000000100 -tablet_hostname  -init_keyspace schema1 -init_shard 0 -init_tablet_type replica -health_check_interval 5s -enable_semi_sync -enable_replication_reporter -backup_storage_implementation file -file_backup_storage_root /vt/vtdataroot/backups -restore_from_backup -queryserver-config-query-timeout 30000 -queryserver-config-transaction-timeout 30000 -queryserver-config-max-result-size 1999999999 -queryserver-config-transaction-cap 3300 -port 15100 -grpc_port 16100 -service_map grpc-queryservice,grpc-tabletmanager,grpc-updatestream -pid_file /vt/vtdataroot/vt_0000000100/vttablet.pid -vtctld_addr http://bd2da89d6063:15000/ -db-config-dba-uname vt_dba -db-config-dba-charset utf8 -db-config-app-uname vt_app -db-config-app-dbname vt_schema1 -db-config-app-charset utf8 -db-config-appdebug-uname vt_appdebug -db-config-appdebug-dbname vt_schema1 -db-config-appdebug-charset utf8 -db-config-allprivs-uname vt_allprivs -db-config-allprivs-dbname vt_schema1 -db-config-allprivs-charset utf8 -db-config-repl-uname vt_repl -db-config-repl-dbname vt_schema1 -db-config-repl-charset utf8 -db-config-filtered-uname vt_filtered -db-config-filtered-dbname vt_schema1 -db-config-filtered-charset utf8
>
> If I'm running these services for a single vttablet with no replication, what do I need to change ?

To fully appreciate vitess you need to use replication and thus have a master and some slaves. For vitess it’s probably good to have at least 4 slaves as some workflows like retarding need configuration adjustments if you use less.  [ With the correct configuration that can be resolved. ]

I know that vitess is intended to work with sharding and replication.  That is for phase 2 of our testing, when we have resolved compatibility issues relating to changing any vitess unsupported SQL into functionally equivalent vitess-supported SQL.

[Sugu Sougoumarane]

For 5.5, you should be able to use vitess to serve queries. But cluster management features like reparenting, etc. will not work because they require GTID. For 5.6, all features will work as long as GTID is enabled.

--

[Charles Shapiro]

When I try to start it up, I get:

++ /vt/bin/vttablet -topo_implementation zk2 -topo_global_server_address localhost:21811,localhost:21812,localhost:21813 -topo_global_root /vitess/global -log_dir /vt/vtdataroot/tmp -tablet-path test-0000000100 -tablet_hostname '' -init_keyspace stanehr -init_shard 0 -init_tablet_type replica -health_check_interval 5s -enable_replication_reporter -backup_storage_implementation file -file_backup_storage_root /vt/vtdataroot/backups -restore_from_backup -queryserver-config-query-timeout 30000 -queryserver-config-transaction-timeout 30000 -queryserver-config-max-result-size 1999999999 -queryserver-config-transaction-cap 3300 -port 15100 -grpc_port 16100 -service_map grpc-queryservice,grpc-tabletmanager,grpc-updatestream -pid_file /vt/vtdataroot/vt_0000000100/vttablet.pid -vtctld_addr http://f618af282aaf:15000/ -db-config-app-unixsocket /bigssd/percona56/mysql/mysql.sock -grpc_max_message_size 33554432

ERROR: logging before flag.Parse: E0731 09:16:19.848534     227 syslogger.go:122] can't connect to syslog

E0731 09:16:19.898159     227 syslogger.go:84] no connection, dropping syslog event: &events.KeyspaceChange{KeyspaceName:"stanehr", Keyspace:(*topodata.Keyspace)(0xc42008c140), Status:"created"}

E0731 09:16:19.922248     227 syslogger.go:84] no connection, dropping syslog event: &events.ShardChange{KeyspaceName:"stanehr", ShardName:"0", Shard:(*topodata.Shard)(0xc4203c6f30), Status:"created"}

E0731 09:16:19.935493     227 syslogger.go:84] no connection, dropping syslog event: &events.ShardChange{KeyspaceName:"stanehr", ShardName:"0", Shard:(*topodata.Shard)(0xc4203c70e0), Status:"updated"}

E0731 09:16:19.980504     227 syslogger.go:84] no connection, dropping syslog event: &events.TabletChange{Tablet:topodata.Tablet{Alias:(*topodata.TabletAlias)(0xc4203f3bc0), Hostname:"", PortMap:map[string]int32{"vt":15100, "grpc":16100}, Keyspace:"stanehr", Shard:"0", KeyRange:(*topodata.KeyRange)(nil), Type:2, DbNameOverride:"", Tags:map[string]string(nil), MysqlHostname:"", MysqlPort:0, XXX_NoUnkeyedLiteral:struct {}{}, XXX_unrecognized:[]uint8(nil), XXX_sizecache:44}, Status:"created"}

E0731 09:16:19.984919     227 syslogger.go:84] no connection, dropping syslog event: &events.TabletChange{Tablet:topodata.Tablet{Alias:(*topodata.TabletAlias)(0xc42014a680), Hostname:"f618af282aaf", PortMap:map[string]int32{"vt":15100, "grpc":16100, "mysql":3306}, Keyspace:"stanehr", Shard:"0", KeyRange:(*topodata.KeyRange)(nil), Type:2, DbNameOverride:"", Tags:map[string]string(nil), MysqlHostname:"f618af282aaf", MysqlPort:3306, XXX_NoUnkeyedLiteral:struct {}{}, XXX_unrecognized:[]uint8(nil), XXX_sizecache:87}, Status:"updated"}

E0731 09:16:19.987265     227 syslogger.go:84] no connection, dropping syslog event: &events.TabletChange{Tablet:topodata.Tablet{Alias:(*topodata.TabletAlias)(0xc420496200), Hostname:"f618af282aaf", PortMap:map[string]int32{"vt":15100, "grpc":16100, "mysql":3306}, Keyspace:"stanehr", Shard:"0", KeyRange:(*topodata.KeyRange)(nil), Type:7, DbNameOverride:"", Tags:map[string]string(nil), MysqlHostname:"f618af282aaf", MysqlPort:3306, XXX_NoUnkeyedLiteral:struct {}{}, XXX_unrecognized:[]uint8(nil), XXX_sizecache:87}, Status:"updated"}

E0731 09:16:19.990140     227 backup.go:777] No backup to restore on BackupStorage for directory stanehr/0. Starting up empty.

E0731 09:16:19.995410     227 syslogger.go:84] no connection, dropping syslog event: &events.TabletChange{Tablet:topodata.Tablet{Alias:(*topodata.TabletAlias)(0xc420497b00), Hostname:"f618af282aaf", PortMap:map[string]int32{"vt":15100, "grpc":16100, "mysql":3306}, Keyspace:"stanehr", Shard:"0", KeyRange:(*topodata.KeyRange)(nil), Type:2, DbNameOverride:"", Tags:map[string]string(nil), MysqlHostname:"f618af282aaf", MysqlPort:3306, XXX_NoUnkeyedLiteral:struct {}{}, XXX_unrecognized:[]uint8(nil), XXX_sizecache:87}, Status:"updated"}

E0731 09:16:20.042496     227 state_change.go:268] Cannot start query service: require sql_mode to be STRICT_TRANS_TABLES: got 'NO_ENGINE_SUBSTITUTION'

++ ./lvtctl.sh InitShardMaster -force stanehr/0 test-100

W0731 09:16:30.764377     262 main.go:60] W0731 09:16:30.763752 reparent.go:181] master-elect tablet test-0000000100 is not the shard master, proceeding anyway as -force was used

W0731 09:16:30.764815     262 main.go:60] W0731 09:16:30.763960 reparent.go:187] master-elect tablet test-0000000100 is not a master in the shard, proceeding anyway as -force was used

W0731 09:16:30.766262     227 rpc_server.go:89] TabletManager.ResetReplication()(on test-0000000100 from ) error: ExecuteFetch(RESET MASTER) failed: Binlog closed, cannot RESET MASTER (errno 1186) (sqlstate HY000) during query: RESET MASTER

E0731 09:16:30.770566     262 main.go:63] Remote error: rpc error: code = Unknown desc = Tablet test-0000000100 ResetReplication failed (either fix it, or Scrap it): rpc error: code = Unknown desc = TabletManager.ResetReplication on test-0000000100 error: ExecuteFetch(RESET MASTER) failed: Binlog closed, cannot RESET MASTER (errno 1186) (sqlstate HY000) during query: RESET MASTER

++ ./lvtctl.sh ListAllTablets test

test-0000000100 stanehr 0 replica f618af282aaf:15100 f618af282aaf:3306 []

+++ cat create_test_table.sql

++ ./lvtctl.sh ApplySchema -sql 'CREATE TABLE messages (

  page BIGINT(20) UNSIGNED,

  time_created_ns BIGINT(20) UNSIGNED,

  message VARCHAR(10000),

  PRIMARY KEY (page, time_created_ns)

) ENGINE=InnoDB' stanehr

E0731 09:16:30.818184     283 main.go:63] Remote error: rpc error: code = Unknown desc = shard: 0 does not have a master

++ ./lvtctl.sh RebuildVSchemaGraph

++ ./vtgate-up.sh

enter zk2 env

Access vtgate at http://f618af282aaf:15001/debug/status

++ set +x

 

Can you see what I'm doing wrong?

--Charles

[Charles Shapiro]

I was able to fix the "can't start query service" error by adding:

sql_mode = STRICT_TRANS_TABLES

to /etc/my.cnf , but it still fails to create a master.

vttablet command:

/vt/bin/vttablet -topo_implementation zk2 -topo_global_server_address localhost:21811,localhost:21812,localhost:21813 -topo_global_root /vitess/global -log_dir /vt/vtdataroot/tmp -tablet-path test-0000000100 -tablet_hostname "" -init_keyspace schema1 -init_shard 0 -init_tablet_type replica -health_check_interval 5s -enable_replication_reporter -backup_storage_implementation file -file_backup_storage_root /vt/vtdataroot/backups -restore_from_backup -queryserver-config-query-timeout 30000 -queryserver-config-transaction-timeout 30000 -queryserver-config-max-result-size 1999999999 -queryserver-config-transaction-cap 3300 -port 15100 -grpc_port 16100 -service_map grpc-queryservice,grpc-tabletmanager,grpc-updatestream -pid_file /vt/vtdataroot/vt_0000000100/vttablet.pid -vtctld_addr http://187d59f62911:15000/ -db-config-app-unixsocket /bigssd/percona56/mysql/mysql.sock -grpc_max_message_size 33554432 -disable_active_reparents 

Percona 5.6 as the mysql: 

/etc/my.cnf:

[mysql]

max_allowed_packet = 8M

[mysqld]

sql_mode = STRICT_TRANS_TABLES

datadir=/bigssd/percona56/mysql

socket=/bigssd/percona56/mysql/mysql.sock

user=mysql

port = 3306

max_connections = 200

key_buffer = 1M

max_allowed_packet = 8M

# table_cache = 256

sort_buffer_size = 1M

read_buffer_size = 1M

read_rnd_buffer_size = 4M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size= 16M

# thread_concurrency = 24

innodb_read_io_threads = 24

innodb_buffer_pool_size = 70G

innodb_buffer_pool_instances = 16

innodb_additional_mem_pool_size = 20M

innodb_io_capacity = 50000

innodb_checksums = 0

innodb_log_file_size = 2000M

innodb_log_buffer_size = 1024M

innodb_flush_log_at_trx_commit = 0

innodb_file_format=Barracuda

bulk_insert_buffer_size=16M

innodb_file_per_table

transaction-isolation = READ-UNCOMMITTED

tmpdir = /bigssd/tmp

secure-file-priv = ""

performance_schema = off

server-id = 1810861327

# Disabling symbolic-links is recommended to prevent assorted security risks

symbolic-links=1

[mysqld_safe]

thp-setting=never

log-error=/var/log/mysqld.log

pid-file=/var/run/mysqld/mysqld.pid

[Sugu Sougoumarane]

I think it's because you have -disable_active_reparents set. It looks like you've already elected the mysql master, and just have to inform vitess about it. If so, execute 'TabletExternallyReparented' instead of 'InitShardMaster'.

InitShardMaster is to be used only if you want vitess to manage replication and mastership.

[Sugu Sougoumarane]

Also, you can ask vttablet to not require STRICT_TRANS_TABLE by setting: -enforce_strict_trans_tables=false.