https://bugzilla.wikimedia.org/show_bug.cgi?id=39327
Web browser: ---
Bug #: 39327
Summary: DB issues associated with leaving feedback
Product: MediaWiki extensions
Version: unspecified
Platform: All
OS/Version: All
Status: NEW
Severity: normal
Priority: Unprioritized
Component: ArticleFeedbackv5
AssignedTo: [email protected]
ReportedBy: [email protected]
CC: [email protected]
Classification: Unclassified
Mobile Platform: ---
Leaving a few words of feedback on an article results in 19 db write queries,
wrapped in 3 transaction. These result in incompatible row locks for the
length of each transaction that will result in serialization at the transaction
level.
Actual queries with transaction delimiters follow. This should speak for
itself, but everything occurring in the second and third transactions needs to
be eliminated, and the application logic behind this behavior redone. Do not
use mysql rows as counters that are updated every time someone leaves feedback,
whether on the per-page basis, or especially the "all pages" afc_page_id = '0'
aggregates. Do not update rows by deleting them and reinserting with the
desired value. Deleting by secondary key essentially results in a table lock
for the duration of the transaction. Anything that is a rollup should be
updated asynchronously in batches that combine and rollup writes.
BEGIN
INSERT /* DatabaseBase::insert */ INTO `aft_article_feedback`
(af_page_id,af_revision_id,af_created,af_user_id,af_user_ip,af_user_anon_token,af_form_id,af_experiment,af_link_id,af_has_comment)
VALUES
('534366','506813755','20120813223135','14719981',NULL,'','6','M5_6','0','1')
INSERT /* ApiArticleFeedbackv5::saveUserRatings */ INTO `aft_article_answer`
(aa_field_id,aa_response_rating,aa_response_text,aa_response_boolean,aa_response_option_id,aa_feedback_id,aat_id)
VALUES ('16',NULL,NULL,'1',NULL,'253294',NULL),('17',NULL,'Well sourced
article! (this is a test comment) ',NULL,NULL,'253294',NULL)
UPDATE /* ApiArticleFeedbackv5::saveUserRatings */ `aft_article_feedback` SET
af_cta_id = '2' WHERE af_id = '253294'
INSERT /* ApiArticleFeedbackv5::saveUserProperties */ INTO
`aft_article_feedback_properties` (afp_feedback_id,afp_key,afp_value_int)
VALUES
('253294','contribs-lifetime','3'),('253294','contribs-6-months','0'),('253294','contribs-3-months','0'),('253294','contribs-1-months','0')
INSERT /* ApiArticleFeedbackv5::updateRollupRow */ IGNORE INTO
`aft_article_revision_feedback_ratings_rollup`
(afrr_page_id,afrr_revision_id,afrr_field_id,afrr_total,afrr_count) VALUES
('534366','506813755','16','0','0')
UPDATE /* ApiArticleFeedbackv5::updateRollupRow */
`aft_article_revision_feedback_ratings_rollup` SET afrr_total = afrr_total +
1,afrr_count = afrr_count + 1 WHERE afrr_page_id = '534366' AND
afrr_revision_id = '506813755' AND afrr_field_id = '16'
COMMIT
---
BEGIN
DELETE /* ApiArticleFeedbackv5::updateRollupRow */ FROM
`aft_article_feedback_ratings_rollup` WHERE arr_page_id = '534366' AND
arr_field_id = '16'
INSERT /* ApiArticleFeedbackv5::updateRollupRow */ IGNORE INTO
`aft_article_feedback_ratings_rollup`
(arr_page_id,arr_field_id,arr_total,arr_count) VALUES ('534366','16','9','42')
COMMIT
---
BEGIN
INSERT /* ApiArticleFeedbackv5Utils::updateFilterCounts */ IGNORE INTO
`aft_article_filter_count` (afc_page_id,afc_filter_name,afc_filter_count)
VALUES
('534366','visible','0'),('0','visible','0'),('534366','notdeleted','0'),('0','notdeleted','0'),('534366','all','0'),('0','all','0'),('534366','visible-comment','0'),('0','visible-comment','0'),('534366','visible-relevant','0'),('0','visible-relevant','0')
UPDATE /* ApiArticleFeedbackv5Utils::updateFilterCounts */
`aft_article_filter_count` SET afc_filter_count = afc_filter_count + 1 WHERE
afc_page_id = '534366' AND afc_filter_name = 'visible'
UPDATE /* ApiArticleFeedbackv5Utils::updateFilterCounts */
`aft_article_filter_count` SET afc_filter_count = afc_filter_count + 1 WHERE
afc_page_id = '0' AND afc_filter_name = 'visible'
UPDATE /* ApiArticleFeedbackv5Utils::updateFilterCounts */
`aft_article_filter_count` SET afc_filter_count = afc_filter_count + 1 WHERE
afc_page_id = '534366' AND afc_filter_name = 'notdeleted'
UPDATE /* ApiArticleFeedbackv5Utils::updateFilterCounts */
`aft_article_filter_count` SET afc_filter_count = afc_filter_count + 1 WHERE
afc_page_id = '0' AND afc_filter_name = 'notdeleted'
UPDATE /* ApiArticleFeedbackv5Utils::updateFilterCounts */
`aft_article_filter_count` SET afc_filter_count = afc_filter_count + 1 WHERE
afc_page_id = '534366' AND afc_filter_name = 'all'
UPDATE /* ApiArticleFeedbackv5Utils::updateFilterCounts */
`aft_article_filter_count` SET afc_filter_count = afc_filter_count + 1 WHERE
afc_page_id = '0' AND afc_filter_name = 'all'
UPDATE /* ApiArticleFeedbackv5Utils::updateFilterCounts */
`aft_article_filter_count` SET afc_filter_count = afc_filter_count + 1 WHERE
afc_page_id = '534366' AND afc_filter_name = 'visible-comment'
UPDATE /* ApiArticleFeedbackv5Utils::updateFilterCounts */
`aft_article_filter_count` SET afc_filter_count = afc_filter_count + 1 WHERE
afc_page_id = '0' AND afc_filter_name = 'visible-comment'
UPDATE /* ApiArticleFeedbackv5Utils::updateFilterCounts */
`aft_article_filter_count` SET afc_filter_count = afc_filter_count + 1 WHERE
afc_page_id = '534366' AND afc_filter_name = 'visible-relevant'
UPDATE /* ApiArticleFeedbackv5Utils::updateFilterCounts */
`aft_article_filter_count` SET afc_filter_count = afc_filter_count + 1 WHERE
afc_page_id = '0' AND afc_filter_name = 'visible-relevant'
COMMIT
--
Configure bugmail: https://bugzilla.wikimedia.org/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the assignee for the bug.
You are on the CC list for the bug.
_______________________________________________
Wikibugs-l mailing list
[email protected]
https://lists.wikimedia.org/mailman/listinfo/wikibugs-l
