@Jgreen does the fact we only INSERT on those tables & don't UPDATE or DELETE from them change things - my quick look suggests the issue is on UPDATE & DELETE.

Altering the large tables will require an outage - we'll have to establish how long

In T176631#3633230, @Eileenmcnaughton wrote:

@Jgreen does the fact we only INSERT on those tables & don't UPDATE or DELETE from them change things - my quick look suggests the issue is on UPDATE & DELETE.

Altering the large tables will require an outage - we'll have to establish how long

I'm not sure, and while I'm also not positive this is the right way to measure...it sure looks like ~something~ is causing full table scans of those tables (this should be a counter since mysql has been up, which in this case is about 6 days):

MariaDB [sys]> select object_schema,sum(rows_full_scanned) from schema_tables_with_full_table_scans where object_name like "log_%" group by object_schema;
+---------------+------------------------+

+---------------+------------------------+

+---------------+------------------------+

MariaDB [sys]> select object_schema,sum(rows_full_scanned) from schema_tables_with_full_table_scans where object_name not like "log_%" group by object_schema
+---------------+------------------------+

+---------------+------------------------+

There are a few non-log_* tables that also lack primary/unique constraint:

civicrm.temp_contribution (12669971 rows)
civicrm.blank_addresses (518 rows)
drupal.services_user (0 rows )
drupal.taxonomy_index (0 rows)
drupal.temp_import (53812 rows)
drupal.temp_reload (1231 rows)
drupal.users_disabled (122 rows)
drupal.wmf_eoy_receipt_donor (0 rows)
pgehres.donatewiki_counts (11131 rows)

IMO we should fix at least civicrm.temp_contribution.

Ouch - I think these tables are probably not needed .now

civicrm.temp_contribution (12669971 rows)
civicrm.blank_addresses (518 rows)
drupal.temp_import (53812 rows)
drupal.temp_reload (1231 rows)

& possibly

drupal.users_disabled (122 rows)
drupal.wmf_eoy_receipt_donor (0 rows)

@Ejegg ?

Does starting mysql without the -A do full scans? Also am now wondering if those SELECT COUNT(*) queries have come back - for reporting back

In T176631#3633342, @Eileenmcnaughton wrote:

Also am now wondering if those SELECT COUNT(*) queries have come back - for reporting back

I can't think of any way this would make it into the replication stream, unless it's done by a trigger on the slave. If it's happening on the master it could block replication globally, but I don't think it would explain what we're seeing re. replication

I put some more table statistics at frpm1001:/tmp/database_20170925 -- there's some really interesting stuff in there, probably easier to discuss it in realtime though.

Maybe we should stick something in our calendars. I guess tomorrow you have a big outage / are busy

Update from group time spent scrutinizing sys schema reports:

• we only insert to the log_* tables, and that doesn't seem like it would require table scans
• on closer inspection the scans mentioned above look connected to mysqldump, not normal traffic. We concluded this b/c scans on the log_tables happened 6x each on frdb2001 correlating to the number of times the dump has run, and there were no such scans frdev1001 which doesn't dump

Also...while it's not ideal schema design, the nagging fact that hosts don't fall behind together, and are clearly capable of catching up very quickly after a restart.

@Jgreen My recommendation, in preparation for next year, would be to try to research this and ROW migration; it requires non-trivial amount of work and testing, but it is worth in the long run (it would have avoided this year's issues from the root). We may not be able to do this on all of mediawiki production soon, but we did partially and on other mysql hosts and it gave us a boost in performance an lower latency for the replicas.

In T176631#3841697, @jcrespo wrote:

@Jgreen My recommendation, in preparation for next year, would be to try to research this and ROW migration; it requires non-trivial amount of work and testing, but it is worth in the long run (it would have avoided this year's issues from the root). We may not be able to do this on all of mediawiki production soon, but we did partially and on other mysql hosts and it gave us a boost in performance an lower latency for the replicas.

Yup, good points--I created T183140 for row replication specifically, and epic task T183138 for 2018 database improvements.

Question: for the purposes of row replication, can a composite index primary key work? Or does it need to be a single column index?

There is no reason to change how PK are done because of ROW, while it depends on each table, there is no need to create an extra column if you have a better existing set of columns as a candidate.

Survey of replicated tables that have no primary key or unique index:

Schema.Table Rows
civicrm.blank_addresses 568
civicrm.log_civicrm_acl 20
civicrm.log_civicrm_acl_entity_role 1
civicrm.log_civicrm_action_mapping 6
civicrm.log_civicrm_action_schedule 40
civicrm.log_civicrm_activity 58764228
civicrm.log_civicrm_activity_contact 99709105
civicrm.log_civicrm_address 60878810
civicrm.log_civicrm_address_format 0
civicrm.log_civicrm_batch 67
civicrm.log_civicrm_campaign 169823
civicrm.log_civicrm_campaign_group 0
civicrm.log_civicrm_case 0
civicrm.log_civicrm_case_activity 0
civicrm.log_civicrm_case_contact 0
civicrm.log_civicrm_case_type 3
civicrm.log_civicrm_component 9
civicrm.log_civicrm_contact 56107918
civicrm.log_civicrm_contact_type 3
civicrm.log_civicrm_contribution 51459411
civicrm.log_civicrm_contribution_page 0
civicrm.log_civicrm_contribution_product 0
civicrm.log_civicrm_contribution_recur 4677955
civicrm.log_civicrm_contribution_soft 75679
civicrm.log_civicrm_contribution_widget 0
civicrm.log_civicrm_country 251
civicrm.log_civicrm_county 6
civicrm.log_civicrm_currency 233
civicrm.log_civicrm_custom_field 5225
civicrm.log_civicrm_custom_group 64
civicrm.log_civicrm_cxn 0
civicrm.log_civicrm_dashboard 25
civicrm.log_civicrm_dashboard_contact 3118
civicrm.log_civicrm_dedupe_exception 4298
civicrm.log_civicrm_dedupe_rule 58
civicrm.log_civicrm_dedupe_rule_group 25
civicrm.log_civicrm_discount 0
civicrm.log_civicrm_domain 32
civicrm.log_civicrm_email 41772300
civicrm.log_civicrm_entity_batch 0
civicrm.log_civicrm_entity_file 285
civicrm.log_civicrm_entity_financial_account 157
civicrm.log_civicrm_entity_financial_trxn 82063381
civicrm.log_civicrm_entity_tag 69505859
civicrm.log_civicrm_event 3
civicrm.log_civicrm_event_carts 0
civicrm.log_civicrm_events_in_carts 0
civicrm.log_civicrm_extension 15
civicrm.log_civicrm_file 296
civicrm.log_civicrm_financial_account 26
civicrm.log_civicrm_financial_item 50962344
civicrm.log_civicrm_financial_trxn 41003586
civicrm.log_civicrm_financial_type 18
civicrm.log_civicrm_geocoder 5
civicrm.log_civicrm_grant 0
civicrm.log_civicrm_group 25408
civicrm.log_civicrm_group_contact 7293837
civicrm.log_civicrm_group_nesting 17
civicrm.log_civicrm_group_organization 0
civicrm.log_civicrm_im 15
civicrm.log_civicrm_job 356
civicrm.log_civicrm_line_item 27225139
civicrm.log_civicrm_loc_block 0
civicrm.log_civicrm_location_type 13
civicrm.log_civicrm_mail_settings 1
civicrm.log_civicrm_mailing 103685
civicrm.log_civicrm_mailing_abtest 0
civicrm.log_civicrm_mailing_bounce_pattern 163
civicrm.log_civicrm_mailing_bounce_type 11
civicrm.log_civicrm_mailing_component 13
civicrm.log_civicrm_mailing_group 1614
civicrm.log_civicrm_mailing_job 9058269
civicrm.log_civicrm_mailing_spool 0
civicrm.log_civicrm_mailing_stats 25994
civicrm.log_civicrm_mailing_stats_performance 0
civicrm.log_civicrm_mailing_trackable_url 632
civicrm.log_civicrm_managed 61
civicrm.log_civicrm_mapping 37
civicrm.log_civicrm_mapping_field 3598
civicrm.log_civicrm_membership 0
civicrm.log_civicrm_membership_block 0
civicrm.log_civicrm_membership_payment 0
civicrm.log_civicrm_membership_status 7
civicrm.log_civicrm_membership_type 0
civicrm.log_civicrm_merge_conflict 0
civicrm.log_civicrm_msg_template 216
civicrm.log_civicrm_navigation 431
civicrm.log_civicrm_note 63474
civicrm.log_civicrm_omnimail_job_progress 41333013
civicrm.log_civicrm_openid 2
civicrm.log_civicrm_option_group 117
civicrm.log_civicrm_option_value 11734
civicrm.log_civicrm_participant 0
civicrm.log_civicrm_participant_payment 0
civicrm.log_civicrm_participant_status_type 15
civicrm.log_civicrm_payment_processor 7
civicrm.log_civicrm_payment_processor_type 20
civicrm.log_civicrm_payment_token 0
civicrm.log_civicrm_pcp 0
civicrm.log_civicrm_pcp_block 0
civicrm.log_civicrm_persistent 0
civicrm.log_civicrm_phone 50476
civicrm.log_civicrm_pledge 0
civicrm.log_civicrm_pledge_block 0
civicrm.log_civicrm_pledge_payment 0
civicrm.log_civicrm_preferences_date 10
civicrm.log_civicrm_premiums 0
civicrm.log_civicrm_premiums_product 0
civicrm.log_civicrm_price_field 1
civicrm.log_civicrm_price_field_value 0
civicrm.log_civicrm_price_set 2
civicrm.log_civicrm_price_set_entity 0
civicrm.log_civicrm_print_label 0
civicrm.log_civicrm_product 0
civicrm.log_civicrm_recurring_entity 6
civicrm.log_civicrm_relationship 7704
civicrm.log_civicrm_relationship_type 21
civicrm.log_civicrm_report_instance 129
civicrm.log_civicrm_saved_search 85
civicrm.log_civicrm_setting 21627
civicrm.log_civicrm_sms_provider 0
civicrm.log_civicrm_state_province 3907
civicrm.log_civicrm_status_pref 15423
civicrm.log_civicrm_subscription_history 7320017
civicrm.log_civicrm_survey 0
civicrm.log_civicrm_tag 145
civicrm.log_civicrm_tell_friend 0
civicrm.log_civicrm_timezone 0
civicrm.log_civicrm_uf_field 104
civicrm.log_civicrm_uf_group 23
civicrm.log_civicrm_uf_join 25
civicrm.log_civicrm_uf_match 251
civicrm.log_civicrm_value_1_check_number_9 2183333
civicrm.log_civicrm_value_1_communication_4 119977
civicrm.log_civicrm_value_1_currency_3 2306736
civicrm.log_civicrm_value_1_gift_data_7 8801370
civicrm.log_civicrm_value_1_language 86460
civicrm.log_civicrm_value_1_note_11 2401007
civicrm.log_civicrm_value_1_primary_contact_name_9 2
civicrm.log_civicrm_value_1_prospect_5 73512
civicrm.log_civicrm_value_1_soft_credit_data_8 1790488
civicrm.log_civicrm_value_1_solicitation_6 1
civicrm.log_civicrm_value_1_stock_information_10 1553664
civicrm.log_civicrm_value_anonymous_ 21894
civicrm.log_civicrm_value_assistant_15 23603
civicrm.log_civicrm_value_organization_contact_14 4928
civicrm.log_civicrm_value_partner_19 128
civicrm.log_civicrm_value_relationship__20 0
civicrm.log_civicrm_website 12180
civicrm.log_civicrm_word_replacement 0
civicrm.log_civicrm_worldregion 6
civicrm.log_wmf_contribution_extra 39277582
civicrm.log_wmf_donor 54374190
drupal.taxonomy_index 0
drupal.temp_import 53789
drupal.temp_reload 1231
drupal.users_disabled 122
drupal.wmf_eoy_receipt_donor 0

Removing this from the replag (T173472) task dependency tree, but it's still a dependency for changing from mixed- to row-based replication.

I have two ideas for the civicrm.log* tables: we could add a new autoincrement primary key column, or we could do a two-column unique index on an existing foreign key id column + log_date (for example in log_civicrm_activity it would be id+log_date). I like the second idea better and if log_date were in microseconds I think that would be a winner. But log_date is in seconds, so there's a greater likelihood of a collision on insert and I'm not sure what civi would do in this case. @Eileenmcnaughton what are your thoughts on this?

In T176631#4466885, @Jgreen wrote:

I have two ideas for the civicrm.log* tables: we could add a new autoincrement primary key column, or we could do a two-column unique index on an existing foreign key id column + log_date (for example in log_civicrm_activity it would be id+log_date). I like the second idea better and if log_date were in microseconds I think that would be a winner. But log_date is in seconds, so there's a greater likelihood of a collision on insert and I'm not sure what civi would do in this case. @Eileenmcnaughton what are your thoughts on this?

Upon further inspection I think we may just have to add a primary key column, something like "log_id". Several of the tables I've looked at have collisions with just "id" plus "log_date", so we'd have to include other columns to get a unique index with current data.

@Jgreen I agree that the existing columns are not unique enough :-(

Here's what I propose:

alter table dev_civicrm.log_{blah} add column log_id bigint unsigned primary key auto_increment;

This takes ~15 min on dev_civicrm.log_wmf_donor, I'm going to do that entire db for a better timing test.

Adding a log_id column to every log_* table took 249m14.875s on frdev1001's dev_civicrm database, I think that should give us a pretty good estimate for how long it will take on the master. Since the larger individual tables take about 15 min, I don't think the slaves should take much longer than that after the master is done to complete the last query.

That's a lot better timing that we anticipated - yay

woot