[Bug 48875] Unable to explain queries on replicated databases

2014-08-26 Thread bugzilla-daemon
https://bugzilla.wikimedia.org/show_bug.cgi?id=48875

Marc A. Pelletier m...@uberbox.org changed:

   What|Removed |Added

 Status|REOPENED|RESOLVED
 Resolution|--- |WONTFIX

--- Comment #17 from Marc A. Pelletier m...@uberbox.org ---
Given the availability of the new tool, and the lack of desire upstream to
change what they perceive as WAD, closing this.

-- 
You are receiving this mail because:
You are on the CC list for the bug.
___
Wikibugs-l mailing list
Wikibugs-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikibugs-l


[Bug 48875] Unable to explain queries on replicated databases

2014-08-26 Thread bugzilla-daemon
https://bugzilla.wikimedia.org/show_bug.cgi?id=48875

Yuvi Panda yuvipa...@gmail.com changed:

   What|Removed |Added

 CC||yuvipa...@gmail.com

--- Comment #18 from Yuvi Panda yuvipa...@gmail.com ---
I'll also note that MariaDB 10.1 has an 'ANALYZE' feature coming up that is
open to everyone and that lets you evaluate queries without having to run them,
so whenever it is that we upgrade we'll get this as well.

-- 
You are receiving this mail because:
You are on the CC list for the bug.
___
Wikibugs-l mailing list
Wikibugs-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikibugs-l


[Bug 48875] Unable to explain queries on replicated databases

2014-08-26 Thread bugzilla-daemon
https://bugzilla.wikimedia.org/show_bug.cgi?id=48875

Tim Landscheidt t...@tim-landscheidt.de changed:

   What|Removed |Added

 Resolution|WONTFIX |FIXED

--- Comment #19 from Tim Landscheidt t...@tim-landscheidt.de ---
My reading is that the essence of this bug, that is explaining queries on
replicated databases, is now available.

-- 
You are receiving this mail because:
You are on the CC list for the bug.
___
Wikibugs-l mailing list
Wikibugs-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikibugs-l


[Bug 48875] Unable to explain queries on replicated databases

2014-08-04 Thread bugzilla-daemon
https://bugzilla.wikimedia.org/show_bug.cgi?id=48875

--- Comment #16 from metatron metat...@online.ms ---
Yes. With MariaDB 10 it is now possible to run  SHOW EXPLAIN with same
permission as running SHOW PROCESSLIST (ergo everyone can)

As posted on labs-l, I've created a tool to make this feature easy-to-use, even
for short running queries.
https://tools.wmflabs.org/tools-info/optimizer.py

If it stands the test, this bug may be closed.

https://mariadb.com/kb/en/mariadb/mariadb-documentation/sql-commands/administration-commands/show-explain/

-- 
You are receiving this mail because:
You are on the CC list for the bug.
___
Wikibugs-l mailing list
Wikibugs-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikibugs-l


[Bug 48875] Unable to explain queries on replicated databases

2014-07-30 Thread bugzilla-daemon
https://bugzilla.wikimedia.org/show_bug.cgi?id=48875

Dispenser dispen...@toolserver.org changed:

   What|Removed |Added

 Blocks||66868

-- 
You are receiving this mail because:
You are on the CC list for the bug.
___
Wikibugs-l mailing list
Wikibugs-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikibugs-l


[Bug 48875] Unable to explain queries on replicated databases

2014-07-30 Thread bugzilla-daemon
https://bugzilla.wikimedia.org/show_bug.cgi?id=48875

--- Comment #15 from Tim Landscheidt t...@tim-landscheidt.de ---
IIRC, with the move to the MariaDB 10 setup we can now EXPLAIN currently
running queries (i. e. long-running queries).  Could someone please document
how to do that either here or at [[wikitech:Nova Resource:Tools/Help]]?

-- 
You are receiving this mail because:
You are on the CC list for the bug.
___
Wikibugs-l mailing list
Wikibugs-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikibugs-l


[Bug 48875] Unable to explain queries on replicated databases

2014-06-02 Thread bugzilla-daemon
https://bugzilla.wikimedia.org/show_bug.cgi?id=48875

--- Comment #14 from Marc A. Pelletier m...@uberbox.org ---
Yeah, at best this partially populated schema offers an approximation; but
that's arguably better than /no/ information.

@metatron: I'm okay with making this available with a simpler name; but do you
have a process in place to /keep/ the schema synchronized with prod?

-- 
You are receiving this mail because:
You are on the CC list for the bug.
___
Wikibugs-l mailing list
Wikibugs-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikibugs-l


[Bug 48875] Unable to explain queries on replicated databases

2014-01-31 Thread bugzilla-daemon
https://bugzilla.wikimedia.org/show_bug.cgi?id=48875

--- Comment #13 from Brad Jorsch bjor...@wikimedia.org ---
Besides row counts, the database also maintains information on the distribution
of keys in the database, which may change the query plans if the distribution
on one instance of the table is different from another. See
https://dev.mysql.com/doc/refman/5.5/en/analyze-table.html for some details.

-- 
You are receiving this mail because:
You are on the CC list for the bug.
___
Wikibugs-l mailing list
Wikibugs-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikibugs-l


[Bug 48875] Unable to explain queries on replicated databases

2014-01-30 Thread bugzilla-daemon
https://bugzilla.wikimedia.org/show_bug.cgi?id=48875

Daniel Schwen dan...@schwen.de changed:

   What|Removed |Added

 CC||dan...@schwen.de

--- Comment #11 from Daniel Schwen dan...@schwen.de ---
Are the explain results independent of row counts? Or will MariaDB change
optimization strategies (or which indexes to pick) based on that?

-- 
You are receiving this mail because:
You are on the CC list for the bug.
___
Wikibugs-l mailing list
Wikibugs-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikibugs-l


[Bug 48875] Unable to explain queries on replicated databases

2014-01-30 Thread bugzilla-daemon
https://bugzilla.wikimedia.org/show_bug.cgi?id=48875

Bawolff (Brian Wolff) bawolff...@gmail.com changed:

   What|Removed |Added

 CC||bawolff...@gmail.com

--- Comment #12 from Bawolff (Brian Wolff) bawolff...@gmail.com ---
(In reply to comment #11)
 Are the explain results independent of row counts? Or will MariaDB change
 optimization strategies (or which indexes to pick) based on that?

explain sometimes changes based on row counts (Its noticeable sometimes when
developing mediawiki where locally queries do different things than they do in
production, which I've usually blamed on having 5 rows in my db vs 5 million)

-- 
You are receiving this mail because:
You are on the CC list for the bug.
___
Wikibugs-l mailing list
Wikibugs-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikibugs-l


[Bug 48875] Unable to explain queries on replicated databases

2014-01-29 Thread bugzilla-daemon
https://bugzilla.wikimedia.org/show_bug.cgi?id=48875

--- Comment #10 from metatron metat...@online.ms ---
To work around this issue I created a script that copies the current schema and
fills it with sample data from xxwiki_p.

The script is available under:
https://tools.wmflabs.org/wikiviewstats/misc/optimizer.sh.txt

A sample database is on enwiki.labsdb  as  u3710__enwiki_optimizer_p.

Currently this is _p accessible, but created per-user basis. Maybe Coren or
anyone else has a better solution in the future.

-- 
You are receiving this mail because:
You are on the CC list for the bug.
___
Wikibugs-l mailing list
Wikibugs-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikibugs-l


[Bug 48875] Unable to explain queries on replicated databases

2014-01-03 Thread bugzilla-daemon
https://bugzilla.wikimedia.org/show_bug.cgi?id=48875

metatron metat...@online.ms changed:

   What|Removed |Added

 CC||metat...@online.ms

--- Comment #9 from metatron metat...@online.ms ---
As tb mentioned before I would suggest to have a database in place with:

- up to date structure and indeces
- fake / redacted data

as a workaround until there is a better solution.

-- 
You are receiving this mail because:
You are on the CC list for the bug.
___
Wikibugs-l mailing list
Wikibugs-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikibugs-l


[Bug 48875] Unable to explain queries on replicated databases

2013-12-04 Thread bugzilla-daemon
https://bugzilla.wikimedia.org/show_bug.cgi?id=48875

Brad Jorsch bjor...@wikimedia.org changed:

   What|Removed |Added

 CC||bjor...@wikimedia.org

--- Comment #7 from Brad Jorsch bjor...@wikimedia.org ---
I did a little digging into this issue today.

http://bugs.mysql.com/bug.php?id=7014 (the one mentioning SAP) makes it
sound like the behavior we *want*: SHOW VIEW on the view *or* SELECT on the
underlying tables will allow EXPLAIN.

http://bugs.mysql.com/bug.php?id=64198 isn't really related. The complaint
there is that having SELECT on everything is no longer enough, SHOW VIEW is now
required too. This is the opposite of what we want, although the same
underlying change probably caused it.

So I tracked down the commit that it looks like actually made it be required to
have SELECT on the underlying tables:
https://bazaar.launchpad.net/~maria-captains/maria/10.0/revision/1810.4002.32.
You can see the change in behavior in the unit test file
mysql-test/t/view_grant.test: look at added line 206, before having SELECT and
SHOW VIEW on the view and nothing on the underlying table was wanting a
successful query and there it's being changed to expect the error.

It references a private (ugh) bug, but from the commit summary and comments
added in the patch it seems that the problem being fixed there is that
allowing EXPLAIN with only SHOW VIEW on the view was allowing people to get an
estimate of how many rows were in the underlying table. Oh noes!

That patch also points us right at the bit of code that would need to be
changed if someone wants to try getting the MariaDB people to change this.

-- 
You are receiving this mail because:
You are on the CC list for the bug.
___
Wikibugs-l mailing list
Wikibugs-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikibugs-l


[Bug 48875] Unable to explain queries on replicated databases

2013-12-04 Thread bugzilla-daemon
https://bugzilla.wikimedia.org/show_bug.cgi?id=48875

MZMcBride b...@mzmcbride.com changed:

   What|Removed |Added

 CC||sprin...@wikimedia.org

--- Comment #8 from MZMcBride b...@mzmcbride.com ---
(In reply to comment #7)

Thank you for doing this research.

 It references a private (ugh) bug, but from the commit summary and comments
 added in the patch it seems that the problem being fixed there is that
 allowing EXPLAIN with only SHOW VIEW on the view was allowing people to get
 an estimate of how many rows were in the underlying table. Oh noes!
 
 That patch also points us right at the bit of code that would need to be
 changed if someone wants to try getting the MariaDB people to change this.

Copying Sean P. on this bug as he may be interested in pursuing this.

Getting MariaDB to fix this behavior would be nice if it's just a matter of
leaking row count info. We could also run our own MariaDB fork (if we're not
already), but given the Labs data leak... there's probably some understandable
wariness to mucking around with this particular code. :-)  I believe Wikimedia
now has additional protections in place to avoid a repeat, even if the views
break and users can perform unfiltered SELECTs.

-- 
You are receiving this mail because:
You are on the CC list for the bug.
___
Wikibugs-l mailing list
Wikibugs-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikibugs-l


[Bug 48875] Unable to explain queries on replicated databases

2013-06-10 Thread bugzilla-daemon
https://bugzilla.wikimedia.org/show_bug.cgi?id=48875

--- Comment #6 from tb wikipe...@tiko.demon.co.uk ---
Thinking outside the box, can sufficient spare disk capacity be allocated to
the enwiki.labsdb host to create a static copy of enwiki_p containing just the
set data available in the dumps (http://dumps.wikimedia.org/), loaded into
plain-old tables and indexed per the replicated database?  This would suffice
to check the operation of some queries, particularly those touching the larger
tables.

-- 
You are receiving this mail because:
You are on the CC list for the bug.
___
Wikibugs-l mailing list
Wikibugs-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikibugs-l


[Bug 48875] Unable to explain queries on replicated databases

2013-06-01 Thread bugzilla-daemon
https://bugzilla.wikimedia.org/show_bug.cgi?id=48875

p858snake p858sn...@gmail.com changed:

   What|Removed |Added

   Keywords||upstream
 CC||p858sn...@gmail.com

-- 
You are receiving this mail because:
You are on the CC list for the bug.
___
Wikibugs-l mailing list
Wikibugs-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikibugs-l


[Bug 48875] Unable to explain queries on replicated databases

2013-05-31 Thread bugzilla-daemon
https://bugzilla.wikimedia.org/show_bug.cgi?id=48875

--- Comment #5 from Andre Klapper aklap...@wikimedia.org ---
Feel free to go ahead: https://mariadb.atlassian.net/

-- 
You are receiving this mail because:
You are on the CC list for the bug.
___
Wikibugs-l mailing list
Wikibugs-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikibugs-l


[Bug 48875] Unable to explain queries on replicated databases

2013-05-30 Thread bugzilla-daemon
https://bugzilla.wikimedia.org/show_bug.cgi?id=48875

--- Comment #3 from Marc A. Pelletier m...@uberbox.org ---
(As a note, while the right cannot be given, operations staff can run explain
on queries that are taking overly long in order to give you the information you
need.  It sucks, but it's a change in mysql we cannot circumvent).

-- 
You are receiving this mail because:
You are on the CC list for the bug.
___
Wikibugs-l mailing list
Wikibugs-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikibugs-l


[Bug 48875] Unable to explain queries on replicated databases

2013-05-30 Thread bugzilla-daemon
https://bugzilla.wikimedia.org/show_bug.cgi?id=48875

Marc A. Pelletier m...@uberbox.org changed:

   What|Removed |Added

 Status|UNCONFIRMED |RESOLVED
 Resolution|--- |WONTFIX

--- Comment #2 from Marc A. Pelletier m...@uberbox.org ---
Sadly, the SHOW VIEW privilege does not suffice; using explain requires having
SELECT privilege on the /underlying/ table (i.e., the one with the data that
cannot be shared publically)

viz.: http://bugs.mysql.com/bug.php?id=64198

In other words, the reason the toolserver could not grant privileges to explain
is also the reason we cannot.

-- 
You are receiving this mail because:
You are on the CC list for the bug.
___
Wikibugs-l mailing list
Wikibugs-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikibugs-l


[Bug 48875] Unable to explain queries on replicated databases

2013-05-30 Thread bugzilla-daemon
https://bugzilla.wikimedia.org/show_bug.cgi?id=48875

Alex Monk (Krenair) kren...@gmail.com changed:

   What|Removed |Added

 Status|RESOLVED|REOPENED
 CC||kren...@gmail.com
 Resolution|WONTFIX |---
 Ever confirmed|0   |1

--- Comment #4 from Alex Monk (Krenair) kren...@gmail.com ---
I talked to Marc about this on IRC, and asked why we can't get upstream to
allow us to do that.
Apparently people have asked MySQL for it and failed (WONTFIX - 'by design',
related to a non-public worklog task required by their agreement to SAP(?)),
and he thinks that MariaDB got the same change from a backport.

I'm reopening this with the suggestion that we file a request upstream with
MariaDB.

-- 
You are receiving this mail because:
You are on the CC list for the bug.
___
Wikibugs-l mailing list
Wikibugs-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikibugs-l


[Bug 48875] Unable to explain queries on replicated databases

2013-05-29 Thread bugzilla-daemon
https://bugzilla.wikimedia.org/show_bug.cgi?id=48875

Peter Bena benap...@gmail.com changed:

   What|Removed |Added

 Blocks||48930

-- 
You are receiving this mail because:
You are on the CC list for the bug.
___
Wikibugs-l mailing list
Wikibugs-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikibugs-l


[Bug 48875] Unable to explain queries on replicated databases

2013-05-28 Thread bugzilla-daemon
https://bugzilla.wikimedia.org/show_bug.cgi?id=48875

Peter Bena benap...@gmail.com changed:

   What|Removed |Added

   Keywords||replica
 CC||benap...@gmail.com

-- 
You are receiving this mail because:
You are on the CC list for the bug.
___
Wikibugs-l mailing list
Wikibugs-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikibugs-l


[Bug 48875] Unable to explain queries on replicated databases

2013-05-27 Thread bugzilla-daemon
https://bugzilla.wikimedia.org/show_bug.cgi?id=48875

Tim Landscheidt t...@tim-landscheidt.de changed:

   What|Removed |Added

 CC||t...@tim-landscheidt.de

--- Comment #1 from Tim Landscheidt t...@tim-landscheidt.de ---
We had (have :-() the same problem on Toolserver (cf.
https://jira.toolserver.org/browse/TS-1585), and there fale found that users
need to be given the SHOW VIEW privilege on the views.

-- 
You are receiving this mail because:
You are on the CC list for the bug.
___
Wikibugs-l mailing list
Wikibugs-l@lists.wikimedia.org
https://lists.wikimedia.org/mailman/listinfo/wikibugs-l