{"action":"created","issue":{"url":"https://api.github.com/repos/PowerDNS/pdns/issues/6700","repository_url":"https://api.github.com/repos/PowerDNS/pdns","labels_url":"https://api.github.com/repos/PowerDNS/pdns/issues/6700/labels{/name}","comments_url":"https://api.github.com/repos/PowerDNS/pdns/issues/6700/comments","events_url":"https://api.github.com/repos/PowerDNS/pdns/issues/6700/events","html_url":"https://github.com/PowerDNS/pdns/issues/6700","id":328247037,"node_id":"MDU6SXNzdWUzMjgyNDcwMzc=","number":6700,"title":"gmysql-zone-lastchange-query very slow","user":{"login":"sirjbaxter","id":3269720,"node_id":"MDQ6VXNlcjMyNjk3MjA=","avatar_url":"https://avatars3.githubusercontent.com/u/3269720?v=4","gravatar_id":"","url":"https://api.github.com/users/sirjbaxter","html_url":"https://github.com/sirjbaxter","followers_url":"https://api.github.com/users/sirjbaxter/followers","following_url":"https://api.github.com/users/sirjbaxter/following{/other_user}","gists_url":"https://api.github.com/users/sirjbaxter/gists{/gist_id}","starred_url":"https://api.github.com/users/sirjbaxter/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/sirjbaxter/subscriptions","organizations_url":"https://api.github.com/users/sirjbaxter/orgs","repos_url":"https://api.github.com/users/sirjbaxter/repos","events_url":"https://api.github.com/users/sirjbaxter/events{/privacy}","received_events_url":"https://api.github.com/users/sirjbaxter/received_events","type":"User","site_admin":false},"labels":[{"id":37445974,"node_id":"MDU6TGFiZWwzNzQ0NTk3NA==","url":"https://api.github.com/repos/PowerDNS/pdns/labels/auth","name":"auth","color":"eb6420","default":false},{"id":37446007,"node_id":"MDU6TGFiZWwzNzQ0NjAwNw==","url":"https://api.github.com/repos/PowerDNS/pdns/labels/defect","name":"defect","color":"cccccc","default":false}],"state":"open","locked":false,"assignee":null,"assignees":[],"milestone":{"url":"https://api.github.com/repos/PowerDNS/pdns/milestones/36","html_url":"https://github.com/PowerDNS/pdns/milestone/36","labels_url":"https://api.github.com/repos/PowerDNS/pdns/milestones/36/labels","id":2713610,"node_id":"MDk6TWlsZXN0b25lMjcxMzYxMA==","number":36,"title":"auth-4.1.x","description":"","creator":{"login":"aerique","id":89097,"node_id":"MDQ6VXNlcjg5MDk3","avatar_url":"https://avatars1.githubusercontent.com/u/89097?v=4","gravatar_id":"","url":"https://api.github.com/users/aerique","html_url":"https://github.com/aerique","followers_url":"https://api.github.com/users/aerique/followers","following_url":"https://api.github.com/users/aerique/following{/other_user}","gists_url":"https://api.github.com/users/aerique/gists{/gist_id}","starred_url":"https://api.github.com/users/aerique/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/aerique/subscriptions","organizations_url":"https://api.github.com/users/aerique/orgs","repos_url":"https://api.github.com/users/aerique/repos","events_url":"https://api.github.com/users/aerique/events{/privacy}","received_events_url":"https://api.github.com/users/aerique/received_events","type":"User","site_admin":false},"open_issues":22,"closed_issues":35,"state":"open","created_at":"2017-08-21T14:44:24Z","updated_at":"2018-06-06T08:19:29Z","due_on":null,"closed_at":null},"comments":1,"created_at":"2018-05-31T18:24:23Z","updated_at":"2018-06-07T12:34:16Z","closed_at":null,"author_association":"NONE","body":"<!-- Hi! Thanks for filing an issue. It will be read with care by human beings. Can we ask you to please fill out this template and not simply demand new features or send in complaints? Thanks! -->\r\n<!-- Also please search the existing issues (both open and closed) to see if your report might be duplicate -->\r\n<!-- Please don't file an issue when you have a support question, send support questions to the mailinglist or ask them on IRC (https://www.powerdns.com/opensource.html) -->\r\n\r\n<!-- Tell us what is issue is about -->\r\n - Program: Authoritative\r\n - Issue type: Bug report\r\n\r\n### Short description\r\n<!-- Explain in a few sentences what the issue/request is -->\r\nOn upgrading powerdns authoritative with gmysql backed  to version 4.1.3 it started maxing out the mysql backend due to the  gmysql-zone-lastchange-query \"select max(change_date) from records where domain_id=?\".\r\nNote that change_date is not indexed on the records table so this forces a table scan for the domain. In my case I am serving a local authoritative version of several top-level domains (.com included), so this results in > 300M rows being scanned on each query. \r\n\r\n<!--\r\nIf this is a bug report, use the following part of the the template and delete the part at the bottom\r\n-->\r\n### Environment\r\n<!-- Tell us about the environment -->\r\n - Operating system: Linux, OpenSUSE Tumbleweed\r\n - Software version: 4.1.3\r\n - Software source: Operating system repository\r\n\r\n### Steps to reproduce\r\n<!-- Tell us step-by-step how the issue can be triggered. Please include your configuration files and any (Lua) scripts that are loaded. -->\r\n1. Run powerdns with gmysql backend and a huge domain.\r\n2. Query powerdns.\r\n3. Watch mysql server load -> infinity.\r\n\r\n### Expected behaviour\r\nmysql backend easily handles powerdns load\r\n\r\n### Actual behaviour\r\nmysql backend gets hung up executing \"select max(change_date) from records where domain_id=?\"\r\n\r\n### Other information\r\nWorkaround: add gmysql-zone-lastchange-query=\"select 0\" to pdns.conf file. However I don't know if this causes problems elsewhere. Note that I don't need the autoserial functionality.\r\n"},"comment":{"url":"https://api.github.com/repos/PowerDNS/pdns/issues/comments/395404065","html_url":"https://github.com/PowerDNS/pdns/issues/6700#issuecomment-395404065","issue_url":"https://api.github.com/repos/PowerDNS/pdns/issues/6700","id":395404065,"node_id":"MDEyOklzc3VlQ29tbWVudDM5NTQwNDA2NQ==","user":{"login":"sirjbaxter","id":3269720,"node_id":"MDQ6VXNlcjMyNjk3MjA=","avatar_url":"https://avatars3.githubusercontent.com/u/3269720?v=4","gravatar_id":"","url":"https://api.github.com/users/sirjbaxter","html_url":"https://github.com/sirjbaxter","followers_url":"https://api.github.com/users/sirjbaxter/followers","following_url":"https://api.github.com/users/sirjbaxter/following{/other_user}","gists_url":"https://api.github.com/users/sirjbaxter/gists{/gist_id}","starred_url":"https://api.github.com/users/sirjbaxter/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/sirjbaxter/subscriptions","organizations_url":"https://api.github.com/users/sirjbaxter/orgs","repos_url":"https://api.github.com/users/sirjbaxter/repos","events_url":"https://api.github.com/users/sirjbaxter/events{/privacy}","received_events_url":"https://api.github.com/users/sirjbaxter/received_events","type":"User","site_admin":false},"created_at":"2018-06-07T12:34:16Z","updated_at":"2018-06-07T12:34:16Z","author_association":"NONE","body":"The domain_id in the query can be 1,2,3. Not sure exactly what you are asking for in regards to '0' serial, but I have logged the DB structure and example contents of what appear to be the relevant tables:\r\n```\r\nMariaDB [pdns]> show tables;\r\n+----------------+\r\n| Tables_in_pdns |\r\n+----------------+\r\n| comments       |\r\n| cryptokeys     |\r\n| domainmetadata |\r\n| domains        |\r\n| records        |\r\n| supermasters   |\r\n| tsigkeys       |\r\n+----------------+\r\n\r\nMariaDB [pdns]> select * from domains;\r\n+----+------+--------+------------+--------+-----------------+---------+\r\n| id | name | master | last_check | type   | notified_serial | account |\r\n+----+------+--------+------------+--------+-----------------+---------+\r\n|  1 | com  | NULL   |       NULL | native |            NULL | NULL    |\r\n|  2 | net  | NULL   |       NULL | native |            NULL | NULL    |\r\n|  3 | org  | NULL   |       NULL | native |            NULL | NULL    |\r\n+----+------+--------+------------+--------+-----------------+---------+\r\n\r\nMariaDB [pdns]> select * from records limit 100,5;\r\n+-----+-----------+-------------------------+------+-------------------------------+--------+------+-------------+----------+-----------+------+\r\n| id  | domain_id | name                    | type | content                       | ttl    | prio | change_date | disabled | ordername | auth |\r\n+-----+-----------+-------------------------+------+-------------------------------+--------+------+-------------+----------+-----------+------+\r\n| 101 |         1 | bestsellerbootcamp.com  | NS   | NS2.PARKINGCREW.NET           | 172800 | NULL |        NULL |        0 | NULL      |    1 |\r\n| 102 |         1 | brokergeneralagents.com | NS   | NS1.CA.EINSTEININDUSTRIES.COM | 172800 | NULL |        NULL |        0 | NULL      |    1 |\r\n| 103 |         1 | brokergeneralagents.com | NS   | NS2.CA.EINSTEININDUSTRIES.COM | 172800 | NULL |        NULL |        0 | NULL      |    1 |\r\n| 104 |         1 | newstrac.com            | NS   | NS07.DOMAINCONTROL.COM        | 172800 | NULL |        NULL |        0 | NULL      |    1 |\r\n| 105 |         1 | newstrac.com            | NS   | NS08.DOMAINCONTROL.COM        | 172800 | NULL |        NULL |        0 | NULL      |    1 |\r\n+-----+-----------+-------------------------+------+-------------------------------+--------+------+-------------+----------+-----------+------+\r\n\r\nMariaDB [pdns]> select count(*) from records;\r\n+-----------+\r\n| count(*)  |\r\n+-----------+\r\n| 376794421 |\r\n+-----------+\r\n\r\nMariaDB [pdns]> describe domainmetadata;\r\n+-----------+-------------+------+-----+---------+----------------+\r\n| Field     | Type        | Null | Key | Default | Extra          |\r\n+-----------+-------------+------+-----+---------+----------------+\r\n| id        | int(11)     | NO   | PRI | NULL    | auto_increment |\r\n| domain_id | int(11)     | NO   | MUL | NULL    |                |\r\n| kind      | varchar(32) | YES  |     | NULL    |                |\r\n| content   | text        | YES  |     | NULL    |                |\r\n+-----------+-------------+------+-----+---------+----------------+\r\n\r\nMariaDB [pdns]> select * from domainmetadata;\r\nEmpty set (0.00 sec)\r\n ```"}}
{"actor":{"display_login":"sirjbaxter"}}