Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-23961

Extremly slow after Upgrade to 10.3

    XMLWordPrintable

Details

    • Bug
    • Status: Open (View Workflow)
    • Major
    • Resolution: Unresolved
    • 10.3.25
    • None
    • Configuration
    • Linux/Apache/PHP7.4

    Description

      After the datacenter updated my server with 10.3.25 MariaDB I am having issues with a php script running extremely slow and the datacenter techs don't know how to fix. I had a programmer check and he reported the following:

      hello, I put some debuging to see which requests are slow
      This one takes 60 seconds : SELECT wmsurvey_company_coach.username, qrycompanysurveys.status, Count(qrycompanysurveys.companysurveyid) AS status_count FROM wmsurvey_company_coach INNER JOIN qrycompanysurveys ON wmsurvey_company_coach.companyid = qrycompanysurveys.companyid GROUP BY wmsurvey_company_coach.username, qrycompanysurveys.status ORDER BY username;

      this one 170 seconds

      SELECT * FROM qrysurveydashboard ORDER BY name, companyname, status
      

      it is a view, which translate to the following query :

      select `qrycompanysurveys`.`companysurveyid` AS `companysurveyid`,`tntadven_wmsurvey`.`wmsurvey_company_coach`.`username` AS `username`,`tntadven_wmsurvey`.`wmsurvey_users`.`name` AS `name`,`tntadven_wmsurvey`.`wmsurvey_companies`.`companyname` AS `companyname`,`tntadven_wmsurvey`.`wmsurvey_surveys`.`name` AS `surveyname`,`qrycompanysurveys`.`status` AS `status`,if((`qrycompanysurveys`.`status` = 'Preparing'),`qrystatusnotes`.`preparing_note`,if((`qrycompanysurveys`.`status` = 'Underway'),`qrystatusnotes`.`underway_note`,if((`qrycompanysurveys`.`status` = 'Completed'),`qrystatusnotes`.`completed_note`,''))) AS `notes` from (`tntadven_wmsurvey`.`wmsurvey_users` join (`tntadven_wmsurvey`.`qrystatusnotes` join (`tntadven_wmsurvey`.`wmsurvey_company_coach` join ((`tntadven_wmsurvey`.`qrycompanysurveys` join `tntadven_wmsurvey`.`wmsurvey_companies` on((`qrycompanysurveys`.`companyid` = `tntadven_wmsurvey`.`wmsurvey_companies`.`companyid`))) join `tntadven_wmsurvey`.`wmsurvey_surveys` on((`qrycompanysurveys`.`surveyid` = `tntadven_wmsurvey`.`wmsurvey_surveys`.`surveyid`))) on((`tntadven_wmsurvey`.`wmsurvey_company_coach`.`companyid` = `qrycompanysurveys`.`companyid`))) on((`qrystatusnotes`.`companysurveyid` = `qrycompanysurveys`.`companysurveyid`))) on((`tntadven_wmsurvey`.`wmsurvey_users`.`username` = `tntadven_wmsurvey`.`wmsurvey_company_coach`.`username`)))
      

      i see the tables are innoDb, maybe the upgrade switched them to innodb, or maybe the upgrade changed the settings for the database and now the memory allocated is too low ?
      You should tweak the database parameters, but i don't know if you can directly tweak it

      Please advise if you see anything in the query's or if you are able to help figure out the issue. This is a client site and has been working fine until this upgrade. I am not sure what version of MySql or MariaDB was there before the upgrade. You can log in to the script here to see how long it takes to complete the page.

      http://tntadventec.com/survey_system/main.php
      U: vTechTeam
      P: FrxVr

      Attachments

        Activity

          People

            Unassigned Unassigned
            vTechTeam Terry Anglin
            Votes:
            0 Vote for this issue
            Watchers:
            2 Start watching this issue

            Dates

              Created:
              Updated:

              Git Integration

                Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.