Not the answer you need?
Register and ask your own question!

PMM2 scrape duration

GaddGadd EntrantCurrent User Role Contributor

Is it possible to configure scrape duration? Needed to huge MySQL instances with many tables, default is 10s is too low.

Best Answer

Answers

  • adivinhoadivinho Percona Percona Staff Role

    Hi Gadd,

    You may do it on the tab "PMM Setting"


  • GaddGadd Entrant Current User Role Contributor

    Hi adivinho,

    Thanks for your replay.

    But I can set here only scrape intervals but not timeout(duration) - 10s is default I didn't find where I can change it, even in postgres pmm-managed.settings table.


    - job_name: mysqld_exporter_agent_id_<agent>_mr-5m0s
      honor_timestamps: false
      params:
        collect[]:
        - custom_query.mr
        - engine_innodb_status
        - info_schema.innodb_cmp
        - info_schema.innodb_cmpmem
        - info_schema.processlist
        - info_schema.query_response_time
        - perf_schema.eventswaits
        - perf_schema.file_events
        - perf_schema.tablelocks
        - slave_status
      scrape_interval: 5m
      scrape_timeout: 10s
      metrics_path: /metrics
      scheme: http
      static_configs:
      - targets:
        - <ip>:42001
        labels:
          agent_id: /agent_id/<id>
          agent_type: mysqld_exporter
          cluster: <cluster>
          instance: /agent_id/<id>
          machine_id: /machine_id/<id>
          node_id: /node_id/<id>
          node_model: <model>
          node_name: <node_name>
          node_type: generic
          service_id: /service_id/<id>
          service_name: <node_name>
          service_type: mysql
      basic_auth:
        username: pmm
        password: <secret>
    


  • PeterPeter Percona CEO Percona Moderator Role

    Note,


    There is a reason for scrape timeouts as when data capture takes a long time It becomes inaccurate, as timestamps assigned to scrapes not to the individual collectors as such if scrape takes more than a fraction of the scrape interval the high resolution rate() functions and others will show bizarre data.

  • GaddGadd Entrant Current User Role Contributor

    Thanks for replays adivinho and Peter

    For example I have instance with 30000 tables, half of them is memory(temporary), I set table stat limit to 5000, but still collector run queries:

    # tablestats_group_table_limit: 5000
    # tooks ~ 4-6s
     SELECT
    	OBJECT_SCHEMA,
    	OBJECT_NAME,
    	COUNT_READ_NORMAL,
    	COUNT_READ_WITH_SHARED_LOCKS,
    	COUNT_READ_HIGH_PRIORITY,
    	COUNT_READ_NO_INSERT,
    	COUNT_READ_EXTERNAL,
    	COUNT_WRITE_ALLOW_WRITE,
    	COUNT_WRITE_CONCURRENT_INSERT,
    	COUNT_WRITE_LOW_PRIORITY,
    	COUNT_WRITE_NORMAL,
    	COUNT_WRITE_EXTERNAL,
    	SUM_TIMER_READ_NORMAL,
    	SUM_TIMER_READ_WITH_SHARED_LOCKS,
    	SUM_TIMER_READ_HIGH_PRIORITY,
    	SUM_TIMER_READ_NO_INSERT,
    	SUM_TIMER_READ_EXTERNAL,
    	SUM_TIMER_WRITE_ALLOW_WRITE,
    	SUM_TIMER_WRITE_CONCURRENT_INSERT,
    	SUM_TIMER_WRITE_LOW_PRIORITY,
    	SUM_TIMER_WRITE_NORMAL,
    	SUM_TIMER_WRITE_EXTERNAL
    	FROM performance_schema.table_lock_waits_summary_by_table
    	WHERE OBJECT_SCHEMA NOT IN ('mysql', 'performance_schema', 'information_schema')
    
    # tooks ~20s
    SELECT t.table_schema, t.table_name, column_name, `auto_increment`,
    		pow(2, case data_type
    		when 'tinyint' then 7
    		when 'smallint' then 15
    		when 'mediumint' then 23
    		when 'int' then 31
    		when 'bigint' then 63
    		end+(column_type like '% unsigned'))-1 as max_int
    		FROM information_schema.columns c
    		STRAIGHT_JOIN information_schema.tables t
    		ON BINARY t.table_schema = c.table_schema AND BINARY t.table_name = c.table_name
    		WHERE c.extra = 'auto_increment' AND t.auto_increment IS NOT NULL
    
    # tooks ~13s
    SELECT OBJECT_SCHEMA, OBJECT_NAME, ifnull(INDEX_NAME, 'NONE') as INDEX_NAME,
      COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE,
      SUM_TIMER_FETCH, SUM_TIMER_INSERT, SUM_TIMER_UPDATE, SUM_TIMER_DELETE
      FROM performance_schema.table_io_waits_summary_by_index_usage
      WHERE OBJECT_SCHEMA NOT IN ('mysql', 'performance_schema')
    
    #tooks ~13s
    SELECT OBJECT_SCHEMA, OBJECT_NAME, ifnull(INDEX_NAME, 'NONE') as INDEX_NAME,
    	COUNT_FETCH, COUNT_INSERT, COUNT_UPDATE, COUNT_DELETE,
    	SUM_TIMER_FETCH, SUM_TIMER_INSERT, SUM_TIMER_UPDATE, SUM_TIMER_DELETE
    	FROM performance_schema.table_io_waits_summary_by_index_usage
    	WHERE OBJECT_SCHEMA NOT IN ('mysql', 'performance_schema')
    
    

    There is no bottleneck in CPU or Disk and all mysql-lr and sometimes mysql-mr stats is failed because of this queries

  • PeterPeter Percona CEO Percona Moderator Role

    It is possible there is a bug and not all queries are disabled which are slow with many tables are disabled appropriately @lalit.choudhary Can you take a look ?

  • lalit.choudharylalit.choudhary Percona Percona Staff Role

    @Gadd

    scrape_interval can be set from PMM Setting dashboard as mentioned earlier , but for scrape_timeout we don't have similar option to change it from PMM GUI.

    This should be a Feature request that you can report on https://jira.percona.com/

    For tablestats-limit and query colletor issue, please report a bug https://jira.percona.com/ and i will investigate it further.

    add small decription with possible reproduciable stesps and version details etc

    Thank you.

Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.