Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

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

Closed
dfredell opened this issue Nov 3, 2017 · 0 comments · Fixed by #9785
Closed

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

dfredell opened this issue Nov 3, 2017 · 0 comments · Fixed by #9785

Comments

@dfredell
Copy link
Contributor

dfredell commented Nov 3, 2017

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

dfredell added a commit to certusoft/grafana that referenced this issue Nov 3, 2017
Set MaxIdleConn and MaxOpenConn when using the GF_DATABASE_URL configuration. Also added GF_DATABASE_DEBUG flag to print SQL statements and SQL execution times.
See grafana#9784 for the details.
@bergquist bergquist changed the title MySQL Performance when using GF_DATABASE_URL MySQL enable MaxOpenCon and MaxIdleCon regards how constring is configured. Nov 9, 2017
bergquist added a commit that referenced this issue Nov 9, 2017
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

Successfully merging a pull request may close this issue.

1 participant