configuration for high concurrency?

[Chia-Chun Shih]

Hi,

Are there any general suggestions for high concurrency applications? 

We found that performance drops significantly when concurrent requests grew above a range (say, 1800-2000). Can we modify configurations to alleviate this problem?

Thanks,

Chia-Chun 

[Daniel Black]

----- Original Message -----
> Hi,
>
> Are there any general suggestions for high concurrency applications?
>
> We found that performance drops significantly when concurrent requests grew
> above a range (say, 1800-2000).

You possibly have a general design/query/index innodb configuration problem to get this many concurrent connections.

Look at slow query log and how to make your queries quicker so there aren't that many concurrent connections.

> Can we modify configurations to alleviate
> this problem?

please provide current configuration (show global variables like 'wsrep_%') and status (show global status like 'wsrep_%').



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

--
--
Daniel Black, Engineer @ Open Query (http://openquery.com.au)
Remote expertise & maintenance for MySQL/MariaDB server environments.

[Chia-Chun Shih]

Dear Daniel,

Thanks for your response. Our configuration is as follows:

show global variables like 'wsrep_%'

Variable_name = Value

wsrep_auto_increment_control = ON

wsrep_causal_reads = OFF

wsrep_certify_nonpk = ON

wsrep_cluster_address = gcomm://

wsrep_cluster_name = my_wsrep_cluster

wsrep_convert_lock_to_trx = OFF

wsrep_data_home_dir = /galera/mxMariaDB/data/

wsrep_dbug_option = 

wsrep_debug = OFF

wsrep_desync = OFF

wsrep_drupal_282555_workaround = OFF

wsrep_forced_binlog_format = NONE

wsrep_load_data_splitting = ON

wsrep_log_conflicts = OFF

wsrep_max_ws_rows = 131072

wsrep_max_ws_size = 1073741824

wsrep_mysql_replication_bundle = 0

wsrep_node_address = 

wsrep_node_incoming_address = AUTO

wsrep_node_name = localhost

wsrep_notify_cmd = 

wsrep_on = ON

wsrep_osu_method = TOI

wsrep_provider = /galera/mariadb-galera-10.0.17-linux-x86_64/lib/libgalera_smm.so

wsrep_provider_options = base_host = xxx.xxx.xxx.xxx; base_port = 4567; cert.log_conflicts = no; debug = no; evs.auto_evict = 0; evs.causal_keepalive_period = PT1S; evs.debug_log_mask = 0x1; evs.delay_margin = PT1S; evs.delayed_keep_period = PT30S; evs.inactive_check_period = PT0.5S; evs.inactive_timeout = PT15S; evs.info_log_mask = 0; evs.install_timeout = PT7.5S; evs.join_retrans_period = PT1S; evs.keepalive_period = PT1S; evs.max_install_timeouts = 3; evs.send_window = 4; evs.stats_report_period = PT1M; evs.suspect_timeout = PT5S; evs.use_aggregate = true; evs.user_send_window = 2; evs.version = 0; evs.view_forget_timeout = P1D; gcache.dir = /galera/mxMariaDB/data/; gcache.keep_pages_size = 0; gcache.mem_size = 0; gcache.name = /galera/mxMariaDB/data//galera.cache; gcache.page_size = 128M; gcache.size = 128M; gcs.fc_debug = 0; gcs.fc_factor = 1.0; gcs.fc_limit = 16; gcs.fc_master_slave = no; gcs.max_packet_size = 64500; gcs.max_throttle = 0.25; gcs.recv_q_hard_limit = 9223372036854775807; gcs.recv_q_soft_limit = 0.25; gcs.sync_donor = no; gmcast.listen_addr = tcp://0.0.0.0:4567; gmcast.mcast_addr = ; gmcast.mcast_ttl = 1; gmcast.peer_timeout = PT3S; gmcast.segment = 0; gmcast.time_wait = PT5S; gmcast.version = 0; ist.recv_addr = 10.144.86.153; pc.announce_timeout = PT3S; pc.checksum = false; pc.ignore_quorum = false; pc.ignore_sb = false; pc.linger = PT20S; pc.npvo = false; pc.recovery = true; pc.version = 0; pc.wait_prim = true; pc.wait_prim_timeout = P30S; pc.weight = 1; protonet.backend = asio; protonet.version = 0; repl.causal_read_timeout = PT30S; repl.commit_order = 3; repl.key_format = FLAT8; repl.max_ws_size = 2147483647; repl.proto_max = 7; socket.checksum = 2; 

wsrep_recover = OFF

wsrep_replicate_myisam = OFF

wsrep_restart_slave = OFF

wsrep_retry_autocommit = 1

wsrep_slave_fk_checks = ON

wsrep_slave_threads = 3

wsrep_slave_uk_checks = OFF

wsrep_sst_auth = ********

wsrep_sst_donor = 

wsrep_sst_donor_rejects_queries = OFF

wsrep_sst_method = mysqldump

wsrep_sst_receive_address = AUTO

wsrep_start_position = 00000000-0000-0000-0000-000000000000:-1

wsrep_sync_wait = 0

show global status like 'wsrep_%'

Variable_name = Value

wsrep_local_state_uuid = a001efc9-e3db-11e4-8bd9-f6ef5b6a31d7

wsrep_protocol_version = 7

wsrep_last_committed = 2395353

wsrep_replicated = 166943

wsrep_replicated_bytes = 150594916

wsrep_repl_keys = 500829

wsrep_repl_keys_bytes = 7846321

wsrep_repl_data_bytes = 132064243

wsrep_repl_other_bytes = 0

wsrep_received = 301133

wsrep_received_bytes = 270297510

wsrep_local_commits = 166929

wsrep_local_cert_failures = 1

wsrep_local_replays = 0

wsrep_local_send_queue = 0

wsrep_local_send_queue_max = 841

wsrep_local_send_queue_min = 0

wsrep_local_send_queue_avg = 163.579358

wsrep_local_recv_queue = 0

wsrep_local_recv_queue_max = 693

wsrep_local_recv_queue_min = 0

wsrep_local_recv_queue_avg = 130.599164

wsrep_local_cached_downto = 2252874

wsrep_flow_control_paused_ns = 188925759135

wsrep_flow_control_paused = 0.246369

wsrep_flow_control_sent = 1522

wsrep_flow_control_recv = 5248

wsrep_cert_deps_distance = 2012.157272

wsrep_apply_oooe = 0.940891

wsrep_apply_oool = 0.006896

wsrep_apply_window = 8.638070

wsrep_commit_oooe = 0.000000

wsrep_commit_oool = 0.000122

wsrep_commit_window = 7.386953

wsrep_local_state = 4

wsrep_local_state_comment = Synced

wsrep_cert_index_size = 218

wsrep_causal_reads = 0

wsrep_cert_interval = 770.189502

wsrep_incoming_addresses = xx.xx.xx.xx:3306,yy.yy.yy.yy:3306,zz.zz.zz.zz:3306

wsrep_evs_delayed = 

wsrep_evs_evict_list = 

wsrep_evs_repl_latency = 0/0/0/0/0

wsrep_evs_state = OPERATIONAL

wsrep_gcomm_uuid = 24c176c9-e3e6-11e4-af98-96050a8ffa6d

wsrep_cluster_conf_id = 3

wsrep_cluster_size = 3

wsrep_cluster_state_uuid = a001efc9-e3db-11e4-8bd9-f6ef5b6a31d7

wsrep_cluster_status = Primary

wsrep_connected = ON

wsrep_local_bf_aborts = 0

wsrep_local_index = 0

wsrep_provider_name = Galera

wsrep_provider_vendor = Codership Oy <in...@codership.com>

wsrep_provider_version = 25.3.9(r3387)

wsrep_ready = ON

wsrep_thread_count = 4

Thanks,

Chia-Chun

Daniel Black於 2015年4月15日星期三 UTC+8下午8時54分53秒寫道：

[Daniel Black]

Given the high number of flow control message I'd raise gcs.fc_limit until there isn't many flow control messages.

> > You possibly have a general design/query/index innodb configuration
> > problem to get this many concurrent connections.
> >
> > Look at slow query log and how to make your queries quicker so there
> > aren't that many concurrent connections.
> >

^ This I suspect is the root cause.

[Alexis Guajardo]

What do your tables look like ?  You can change your offset's if they are auto_increment as mentioned here...

http://codership.blogspot.com/2009/02/managing-auto-increments-with-multi.html

So that they interleave, depending on your expected row count, you most like will want to use BIGINT .