dqsp60748 2018-11-08 10:00
浏览 79
已采纳

查询性能调试

I have recently installed a new module(paypal express) in Oxid 6.0.x backend. After installation, the backend menu point "orders", in my backend, is timing out. Cause the query needs too long. I am not able to view orders (which is a CMS vendor resource).

I already tried to debug it with our managed hoster support who doubled our ram to increased mysql buffer size as well as php timouts etc. within the php.ini.

After the hoster php.ini change I still see the query with status "Copying to tmp table" even so the hoster has increased the query buffers etc.

Question:
I am really not sure what the problem is. Does someone have an Idea what else I might try? I cannot imagine that this one small join needs so much buffer space and time to excecute. Especially since the join to payppaypalpluspayment is the same as to oxv_oxpayments_de which is instant and on the same primary key.

This is the query I see running in processlist
Showing rows 0 - 0 (1 total, Query took 200.9683 seconds.) Has already decreased from about 10 min after the php.ini change

select count(*) from  `oxorder`
       LEFT JOIN `oxv_oxpayments_de` AS `payments` on `payments`.oxid=oxorder.oxpaymenttype
           LEFT JOIN `oxv_oxpayments_de` AS pluspayments ON pluspayments.oxid = oxorder.oxpaymenttype
           LEFT JOIN payppaypalpluspayment ON payppaypalpluspayment.OXORDERID = oxorder.OXID
           LEFT JOIN payppaypalpluspui ON payppaypalpluspui.OXPAYMENTID = payppaypalpluspayment.OXPAYMENTID
        where 1  and ( oxorder.oxfolder = 'ORDERFOLDER_NEW' )

count(*) Result 150000
The problem may be this join, the others are instant results
rows 0 - 0 (1 total, Query took 153.2391 seconds.)

Select count(*) from  `oxorder`

           LEFT JOIN payppaypalpluspayment ON payppaypalpluspayment.OXORDERID = oxorder.OXID

EXPLAIN

id select_type  table                   type    possible_keys   key         key_len     ref     rows        Extra   
1   SIMPLE      oxorder                 index       NULL        MAINIDX     10          NULL    146861      Using index
1   SIMPLE      payppaypalpluspayment   index       NULL        OXORDERID   32          NULL    2630        Using where; Using index; Using join buffer (flat, BNL join)
1   SIMPLE      payppaypalpluspui       ALL         NULL        NULL        NULL        NULL    519         Using where

Changed after adding suggested indexes in

oxorder.OXPAYMENTTYPE; oxorder.oxfolder; payppaypalpluspui.OXPAYMENTID; payppaypalpluspayment.OXPAYMENTID;

id  select_type     table                   type    possible_keys   key         key_len ref                                 rows    Extra   
1   SIMPLE          oxorder                 ref     OXFOLDER        OXFOLDER    98      const                               73450   Using index condition
1   SIMPLE          payppaypalpluspayment   ALL     NULL            NULL        NULL    NULL                                2634    Using where; Using join buffer (flat, BNL join)
1   SIMPLE          payppaypalpluspui       ref     OXPAYMENTID     OXPAYMENTID 34      payppaypalpluspayment.OXPAYMENTID   1       Using where; Using index

After this count selects the backend is doing the same query with select oxorder.* and one column from payppaypalpluspui (takes about 600 seconds) The UI has then already timed out. When I delete the last two joins and try it manually on the server the query takes < 1sec

dbs

CREATE TABLE `payppaypalpluspayment` (
 `OXID` char(32) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL COMMENT 'Payment oxid id',
 `OXORDERID` char(32) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL COMMENT 'Order id',
 `OXSALEID` varchar(32) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT '' COMMENT 'PayPal Plus payment sale id',
 `OXPAYMENTID` varchar(32) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT '' COMMENT 'PayPal Plus payment id',
 `OXSTATUS` varchar(32) NOT NULL DEFAULT '' COMMENT 'PayPal Plus payment status',
 `OXDATECREATED` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Payment creation date',
 `OXTOTAL` double NOT NULL DEFAULT '0' COMMENT 'Total payment amount',
 `OXCURRENCY` varchar(32) NOT NULL DEFAULT '' COMMENT 'Payment currency',
 `OXPAYMENTOBJECT` blob NOT NULL COMMENT 'Serialized payment object',
 PRIMARY KEY (`OXID`),
 UNIQUE KEY `OXORDERID` (`OXORDERID`),
 UNIQUE KEY `OXSALEID` (`OXSALEID`)
 KEY `OXPAYMENTID` (`OXPAYMENTID`)    <<< added this index
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='PayPal Plus payment data model'

Rows 3,228 InnoDB utf8_general_ci 11.4 MiB

CREATE TABLE `oxorder` (
 `OXID` char(32) NOT NULL COMMENT 'Order id',
 `OXSHOPID` int(11) NOT NULL DEFAULT '1' COMMENT 'Shop id (oxshops)',
 `OXUSERID` char(32) NOT NULL DEFAULT '' COMMENT 'User id (oxuser)',
 `OXORDERDATE` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'Order date',
 `OXORDERNR` varchar(16) NOT NULL COMMENT 'Order number',
 .....
 PRIMARY KEY (`OXID`),
 KEY `MAINIDX` (`OXSHOPID`,`OXSTORNO`,`OXORDERDATE`),
 KEY `OXORDERNR` (`OXORDERNR`)
 KEY `OXPAYMENTTYPE` (`OXPAYMENTTYPE`),  <<<< added this index
 KEY `OXFOLDER` (`OXFOLDER`)             <<<< added this index
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Shop orders information'

149,068 InnoDB utf8_general_ci 258.1 MiB

CREATE TABLE `payppaypalpluspui` (
 `OXID` char(32) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL COMMENT 'Payment oxid id',
 `OXPAYMENTID` varchar(32) CHARACTER SET latin1 COLLATE latin1_general_ci NOT NULL DEFAULT '' COMMENT 'PayPal Plus payment id',
 `OXREFERENCENUMBER` varchar(255) NOT NULL DEFAULT '' COMMENT 'PayPal Plus PuI reference_number',
 `OXBANKNAME` varchar(255) NOT NULL DEFAULT '' COMMENT 'PayPal Plus PuI banking instruction bank name',
 `OXACCOUNTHOLDER` varchar(255) NOT NULL DEFAULT '' COMMENT 'PayPal Plus PuI banking instruction account holder',
 `OXIBAN` varchar(255) NOT NULL DEFAULT '' COMMENT 'PayPal Plus PuI banking instruction IBAN',
 `OXBIC` varchar(255) NOT NULL DEFAULT '' COMMENT 'PayPal Plus PuI banking instruction BIC',
 `OXDUEDATE` datetime NOT NULL DEFAULT '0000-00-00 00:00:00' COMMENT 'PayPal Plus PuI due date',
 `OXTOTAL` double NOT NULL DEFAULT '0' COMMENT 'PayPal Plus PuI Total invoice amount',
 `OXCURRENCY` varchar(32) NOT NULL DEFAULT '' COMMENT 'PayPal Plus PuI invoice currency',
 `OXPUIOBJECT` text NOT NULL COMMENT 'JSON representation of the payment instructions',
 PRIMARY KEY (`OXID`)
 KEY `OXPAYMENTID` (`OXPAYMENTID`)   <<<< added this index
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='PayPal Plus Pay upon Invoice data model'

Rows 655 InnoDB utf8_general_ci 1.5 MiB

innodb buffer pool = 8 GiB
RAM 10 GiB

  • 写回答

2条回答 默认 最新

  • douge3830 2018-11-08 15:22
    关注

    Your problem is that you are comparing columns encoded in different character sets, latin1 and utf8. In such cases, index may not be used for key look-ups. You should make sure to use the same character set for all your key columns.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题?

悬赏问题

  • ¥15 MATLAB动图问题
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题