Skip to content

MySQL enable MaxOpenCon and MaxIdleCon regards how constring is configured.  #9784

Closed
@dfredell

Description

@dfredell

Please include this information:

  • What Grafana version are you using?
    grafana:4.6.0-beta3 I can't use anything newer because I need alerts fixed in "tsdb.HandleRequest() error invalid value type" #9777
  • What datasource are you using?
    prometheus 2.0.0-rc.2
  • What OS are you running grafana on?
    docker
  • What did you do?
    loading the Home page
  • What was the expected result?
    take under 5 seconds
  • What happened instead?
    took 20 seconds
  • If related to metric query / data viz:
    • Include raw network request & response: get by opening Chrome Dev Tools (F12, Ctrl+Shift+I on windows, Cmd+Opt+I on Mac), go the network tab.
      image
  • config
    Using env variable to point to a remote mysql db via GF_DATABASE_URL

I tried restarting the mysql and the grafana, with no change. I ran grafana from my development box with GF_DATABASE_URL and the home page loaded fast which made me think it was an issue with the docker.

Then I enabled SQL debugging in Grafana for the database and found queries against the user table were taking 5 secs. I only have 2 users in grafana.

"[SQL] SELECT
u.id as user_id,
u.is_admin as is_grafana_admin,
u.email as email,
u.login as login,
u.name as name,
u.help_flags1 as help_flags1,
u.last_seen_at as last_seen_at,
org.name as org_name,
org_user.role as org_role,
org.id as org_id
FROM user as u
LEFT OUTER JOIN org_user on org_user.org_id = 1 and org_user.user_id = u.id
LEFT OUTER JOIN org on org.id = org_user.org_id WHERE u.id=? [1] - took: 5.020826667s" logger=sqlstore.xorm

Then I tried using GF_DATABASE_TYPE, GF_DATABASE_HOST, GF_DATABASE_NAME, GF_DATABASE_USER, GF_DATABASE_PASSWORD variables instead of GF_DATABASE_URL and the docker was blazing fast. So I kept digging and found that when using the GF_DATABASE_URL config var grafana doesn't set the MaxOpenConn and MaxIdleConn like it does when the DATABASE configs are separate.

DbCfg.MaxOpenConn = sec.Key("max_open_conn").MustInt(0)

So I tried adding the MaxOpenConn and MaxIdleConn options to the GF_DATABASE_URL section and sure enough the home page loads fast again.

Now it is taking under a second to load in the docker, when running with GF_DATABASE_URL and my change to add MaxOpenConn and MaxIdleConn
image

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions