1

I have the following in .my.cnf 

# LOGGING #
slow_query_log                  = ON
slow_query_log_file             = /var/log/mariadb/slow_query.log
log-queries-not-using-indexes   = 1

When I run tuning-primer.sh I get this:

SLOW QUERIES
The slow query log is NOT enabled.
Current long_query_time = 10.000000 sec.
You have 0 out of 36 that take longer than 10.000000 sec. to complete
Your long_query_time seems to be fine

Can someone explain how is this possible?

Uhl Hosting
  • 57
  • 12
  • Do lower long_query_time to 1 (or some other small value). – Rick James Jan 31 '16 at 19:47
  • Were those settings in the `[mysqld]` section of `.my.cnf`? Is `.my.cnf` actually being read? (Changing `long_query_time` in it would apparently provide such a clue.) – Rick James Jan 31 '16 at 19:49
  • +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 1.000000 | +-----------------+----------+ – Uhl Hosting Jan 31 '16 at 19:52
  • Yes the settings are in [mysqld], what command do I use to see the current config in use? – Uhl Hosting Jan 31 '16 at 19:53
  • `SHOW GLOBAL VARIABLES;` (from within the mysql commandline tool; UIs have some place to do arbitrary commands.) It looks like you did that. – Rick James Jan 31 '16 at 19:58
  • https://gist.github.com/highacid/395cdcf160a7662776e7 here are variables. – Uhl Hosting Feb 01 '16 at 01:47
  • @RickJames any updates on this? – Uhl Hosting Feb 02 '16 at 00:39
  • Does `/var/log/mariadb/slow_query.log` exist? If not, does the 'user' mariadb or mysql have permissions to write to the directory `/var/log/mariadb`? Does `SHOW GLOBAL STATUS` say any slow queries happened? You could change `long_query_time` to 0 to catch all queries -- that would see if the cutoff of 1 was too high. – Rick James Feb 02 '16 at 00:43
  • Can you dissect `tuning-primer.sh` to see how it checks for "NOT enabled"? – Rick James Feb 02 '16 at 00:44
  • It seems that slow queries are working: Uptime: 215129 Threads: 3 Questions: 4368920 Slow queries: 159989 Opens: 1453 Flush tables: 1 Open tables: 1448 Queries per second avg: 20.308 – Uhl Hosting Feb 03 '16 at 07:38
  • Nearly 1/sec and 3% of queries -- that's high. There are some other questions in the comments that you have not yet answered. – Rick James Feb 03 '16 at 17:00
  • @RickJames I did not managed to find in tunner there and why is not detecting slow_query_log, yet I know is on due to 100 MB log file size. Not sure what to do about this. – Uhl Hosting Feb 04 '16 at 17:47
  • If _you_ can find the slowlog, then _you_ can run `mysqldumpslow` or `pt-query-digest`. Or you can simply look at the file (it is text). Meanwhile, let's ignore that the 'tuner' could not find it. (BTW, I posted a bug report against it mentioning 3 other errors in that part of the script.) – Rick James Feb 04 '16 at 23:34
  • @RickJames I looked at the log, still I do not know what to do with this data... Its over 100 MB. – Uhl Hosting Feb 06 '16 at 11:15
  • Use one of those tools to summarize it. With the first one, use `-s t -t 5`; that will give the 5 "worst" queries. 5 is usually more than enough to work on optimizing. – Rick James Feb 06 '16 at 15:48
  • https://gist.github.com/highacid/476d745c232349284b01 here is my feedback @RickJames – Uhl Hosting Feb 07 '16 at 16:25

1 Answers1

0

Apparently the slowlog is working now. Do you know what fixed that issue?

Meanwhile, this has morphed into query tuning...

What is #1 for? Why is it run so often? It returns an average of 156K rows examined (the whole table?), but only 665 rows returned. 665 is a lot of rows; do you really need them all? Could more filtering be done in the SQL?

It sounds like there is no INDEX(autoload) -- add it; it should speed up the query considerably.

#1
SELECT  option_name, option_value
    FROM  wp_options
    WHERE  autoload = 'S'

What are you doing with the thousands of rows from the following? And you are preforming them thousands of times?

#2
SELECT  st.value AS tra, s.value AS org, s.domain_name_context_md5 AS ctx
    FROM  wp_icl_strings s
    LEFT JOIN  wp_icl_string_translations st ON s.id=st.string_id
      AND  st.status=N
      AND  st.language='S'
      AND  s.language!='S'
#3
SELECT  slug, taxonomy
    FROM  wp_posts
    INNER JOIN  wp_term_relationships  ON (wp_posts.ID = wp_term_relationships.object_id)
    INNER JOIN  wp_term_taxonomy  ON (wp_term_relationships.term_taxonomy_id =
                                           wp_term_taxonomy.term_taxonomy_id )
    INNER JOIN  wp_terms ON (wp_term_taxonomy.term_id = wp_terms.term_id )
    WHERE  wp_posts.ID IN ("S","S","S","S","S","S","S","S","S",...)
    ORDER BY  wp_terms.name ASC 
#4
SELECT  t.element_id, tax.term_id, tax.taxonomy
    FROM  wp_icl_translations t
    JOIN  wp_term_taxonomy tax ON t.element_id = tax.term_taxonomy_id
      AND  t.element_type = CONCAT('S', tax.taxonomy)
    JOIN  wp_terms terms ON terms.term_id = tax.term_id
    WHERE  tax.term_id != tax.term_taxonomy_id

Why the LEFT in #2? That probably prevents starting with st, which might be more selective with INDEX(language, status).

In #3: wp_terms might benefit from INDEX(name).

In #4: The schema design led to the clumsy CONCAT('S', tax.taxonomy); can that be rectified? That is, can t.element_type and tax.taxonomy look the same -- either both with the prefix or both without? Or maybe the prefix is a separate column?

If you would like to discuss any of these further, please provide SHOW CREATE TABLE and EXPLAIN SELECT ....

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • How do I run the SHOW CREATE TABLE and EXPLAIN SELECT? Do they need to be runned on a specific DB? Also how do I add Index(autoload). Thanks – Uhl Hosting Feb 08 '16 at 09:46
  • Get familiar with one of these: `phpmyadmin`, `MySQL Workbench`, or mysql commandline tool. WP does some nice things, but it isn't keeping you from learning a bunch about the underlying MySQL. – Rick James Feb 08 '16 at 18:37
  • can you please tell me if MySQL Workbench can help me with enabling the INDEX,autoload? – Uhl Hosting Mar 05 '16 at 02:24
  • "autoload" is not a MySQL term; please explain where it comes from and what it does. – Rick James Mar 05 '16 at 18:39
  • INDEX(autoload) You mentioned this in the answer above. – Uhl Hosting Mar 05 '16 at 18:47
  • Maybe the real question about #1 is "Why does WP need to load its options so often?" and "Why did they pick such a slow way to do it?". Probably adding an index to wp_options would not help. – Rick James Mar 05 '16 at 18:59
  • I had many transients I have cleaned @ 8000 lines of transients rows. – Uhl Hosting Mar 05 '16 at 19:04