#maria Log v0.1

logs Catalogue

This page loads from the db, refresh as and when. It displays 24 hrs
For older data http://marialog.archivist.info/previous.php, logs go back to 2009-01-18
Some user stats
Table logs
date_time
user
message
2015-08-31 13:06:22
jkavalik
Oreolek, can you check all variables from http://www.mysqlcalculator.com/ ?
2015-08-31 13:06:43
Oreolek
upd: updated to 10.0 branch, still the same
2015-08-31 13:07:05
Oreolek
jkavalik, it says the DB should eat around 500 Mb RAM
2015-08-31 13:07:46
Oreolek
but virt RAM is 700+ at the moment
2015-08-31 13:08:36
jkavalik
Oreolek, you were that fast putting all values from your config in there? http://www.mysqlcalculator.com/
2015-08-31 13:08:58
jkavalik
(sorry for pasting the url again)
2015-08-31 13:09:01
Oreolek
i just did it earlier
2015-08-31 13:09:07
Oreolek
before coming here
2015-08-31 13:10:34
jkavalik
ok :)
2015-08-31 13:10:44
jkavalik
then something is weird..
2015-08-31 13:11:11
jkavalik
did you check it is mysql and not other program?
2015-08-31 13:11:17
jkavalik
*mysqld
2015-08-31 13:11:56
Oreolek
well, htop gives it as the main memory hog
2015-08-31 13:12:18
Oreolek
and php has only 128M memory limit per process (4 total)
2015-08-31 13:12:37
Oreolek
and php even doesn't hit the limit
2015-08-31 13:13:04
jkavalik
hm, internal memory profiler is not implemented..
2015-08-31 13:13:16
jkavalik
check https://www.percona.com/blog/2013/01/09/profiling-mysql-memory-usage-with-valgrind-massif/
2015-08-31 13:13:47
jkavalik
may be some memleak from your use case or data corruption? hard to guess
2015-08-31 13:15:39
Oreolek
is it a good idea to start database binary with debug info? i'm sure i'll need to recompile mariadb for that
2015-08-31 13:16:43
jkavalik
can you reproduce it with your schema/load even in some testing environment?
2015-08-31 13:21:51
Oreolek
don't know, it would require a lot of time. my vps is struggling. i've adjusted nice priorities so php won't get killed but that's still a crisis.
2015-08-31 13:31:15
serg
serg . o O (any problem can be solved by adding another layer of indirection... expect for the problem of too many layers of indirection)
2015-08-31 13:31:45
serg
serg has apparently just solved two problems :)
2015-08-31 13:34:44
jkavalik
congrats :)
2015-08-31 15:05:19
reuss
is there known problems with recovering a mariadb instance that's suffered an oom? it won't start up again (mysql Ver 15.1 Distrib 10.1.6-MariaDB) it's failing during position recovery for some reason
2015-08-31 16:52:35
Joe_CoT
Hey, is this a good place to ask about issues with Galera SST over SSL, or is there a different channel for Galera
2015-08-31 17:13:50
nirbhay
Joe_CoT, Yep. What's the question?
2015-08-31 17:15:58
Joe_CoT
So I was using the xtrabackup v2 script with encrypt 2, and after the latest round of openssl updates (I think related to the hmac changes), now the script fails on the SSL handshake
2015-08-31 17:16:23
Joe_CoT
is there some update related to that, some new version of the script to look at? or should I just dive into debugging socat?
2015-08-31 17:29:19
nirbhay
Joe_CoT, It should be related to socat. Perhaps script's socat command needs to be updated to make it work with the new openssl version, not sure atm.
2015-08-31 17:30:55
nirbhay
Joe_CoT, Could you open a bug report (mariadb.org/jira) with the error message, mariadb server, system and openssl versions?
2015-08-31 17:32:09
Joe_CoT
yeah. I guess I'll dive into debugging the socat command and then put that in
2015-08-31 17:36:24
nirbhay
Joe_CoT, Thank you.
2015-08-31 18:38:38
Ninpo
Is there an easy way to script holding a FTWRL open while creating an LVM snapshot? I've looked at mylvmbackup but I need something that can grab my separate tokudb dir too which is outside the mysql directory but on the same volume
2015-08-31 20:38:16
phibonacci
Hello. I was using mysql 5.1.73 on my old server and I would like to restore my base on the last version of MariaDB avalable in my repo (5.5.44). Sadly the sql backup is not compatible, would you happen to know a way to use it?
2015-08-31 20:40:39
grknight
phibonacci: it should be compatible. what sort of error(s) are you getting?
2015-08-31 20:40:52
grknight
is this an SQL dump or some other method?
2015-08-31 20:47:48
phibonacci
yes, an sql dump
2015-08-31 20:48:08
phibonacci
ERROR: ASCII '\0' appeared in the statement, but this is not allowed unless option --binary-mode is enabled and mysql is run in non-interactive mode. Set --binary-mode to 1 if ASCII '\0' is expected. Query: 'oocarb2c_2015-08-31_18-00.sql'.
2015-08-31 20:48:34
phibonacci
This error appears only with mariadb (not mysql 5.5)
2015-08-31 20:48:43
phibonacci
with the flag --binary-mode:
2015-08-31 20:49:01
phibonacci
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'oocarb2c_2015-08-31_18-00.sql' at line 1
2015-08-31 20:49:19
phibonacci
this last error appears on both mysql and mariadb 5.5
2015-08-31 21:01:49
grknight
phibonacci: what command are you running?
2015-08-31 21:55:07
phibonacci
sorry grknight, I could not answer directly.
2015-08-31 21:55:50
phibonacci
I used both the classic `mysql -u user -ppassword base < base.sql` and source < in mysql directly
2015-08-31 21:57:26
grknight
it is odd to error like that with a .sql file name though from the client
2015-08-31 22:15:33
montywi
phibonacci: can you find the offending statement and put that in a pastebin ?
2015-08-31 22:20:28
montywi
normally you inly need --binary-mode if you are using mysqlbinlog to pipe to mysql
2015-08-31 22:21:26
montywi
if you backuped your data with mysqldump, then it should have automaticly escaped return, newlines and ascii(0), so there is no reason to use --binary-mode
2015-08-31 22:25:09
phibonacci
I backed it up with mysqldump
2015-08-31 22:25:38
montywi
phibonacci: how does your line 1 look like ?
2015-08-31 22:26:05
montywi
the one that you get a syntax error for
2015-08-31 22:26:27
phibonacci
http://pastebin.com/raw.php?i=FseqNEiG (from emacs)
2015-08-31 22:26:35
montywi
I just checked and mysqldump in 5.1 should escape things properly so that mysql < base.sql should work
2015-08-31 22:40:30
phibonacci
I got it! The one who wrote the script to compress the .sql file used tar cvf and named it sql.gz
2015-08-31 22:40:37
phibonacci
I was restoring a tar file!
2015-08-31 22:40:40
phibonacci
Darn.
2015-08-31 22:50:03
montywi
so all is well now, I assume ?
2015-09-01 03:18:14
bytee
kostja can't decide to stay or go!
2015-09-01 06:45:25
montywi
bytee: I have tried to convince with him several times, but not succeeded yet
2015-09-01 06:46:18
montywi
knielsen: ping
2015-09-01 07:01:00
knielsen
montywi: yes?
2015-09-01 07:02:02
montywi
knielsen: see query screen
2015-09-01 07:08:33
otto
dbart: thanks for working on 723. I have irssi open now if there is anything you want to chat about
2015-09-01 09:31:11
dreas
Hi guys! Hope all is well here. Last time I asked, I understand Aria is not getting any active development at the moment
2015-09-01 09:32:19
dreas
I found a big player that shares my ehh .. "negative attitude" towards InnoDB, but also requires some basic features MyISAM doesn't have at the moment (e.g. transactions / row level locking). Are there any plans at the moment to get that implemented in Aria and keep that engine alive? If not, would it help if I can convince them to sponsor such project?
2015-09-01 09:34:40
danblack
dreas: have you considered that implementing those features may give aria whatever 'negative attitude' you hold for innodb?
2015-09-01 09:36:05
danblack
https://mariadb.com/kb/en/mariadb/sponsoring-the-mariadb-project/ is there.
2015-09-01 09:36:45
dreas
danblack: I'm quite sure it won't :) The problem with InnoDB is that corruption of ibdata (e.g. bad hardware) basically causes you to lose all data. I believe Aria's design doesn't have that problem
2015-09-01 09:38:01
danblack
you are bias lot on belief. you seem to have convinsed yourself I won't bother to convince yourself otherwise.
2015-09-01 09:38:22
dreas
danblack: That's appreciated, saves a long discussion ;)
2015-09-01 09:38:33
dreas
danblack: Looking at that list, Aria is not "dead" yet actually. That's good to see. I'll reach out then and see if they'd be interested to contribute/sponsor indeed
2015-09-01 09:39:53
danblack
danblack feels like some developer time just got diverted away from what a lot of innodb positive people may consider important.
2015-09-01 09:55:40
dreas
danblack: If InnoDB solves the issue that all data becomes accessible when ibdata is corrupted, that would change things significantly. I guess that's for other people to figure out though
2015-09-01 09:55:49
dreas
accessible --> inaccessible
2015-09-01 09:57:15
danblack
sponsoring redundant page information for innodb might be a worthwhile goal too.
2015-09-01 09:58:57
danblack
most designs i've seen/done have duplicate servers that have redundency against hardware failure and a few other classes of failure.
2015-09-01 10:10:50
Ninpo
Hmm. In Percona 5.5 this query runs almost instantly, in MariaDB 10.0.21 it takes about 4 minutes http://dpaste.com/084B6R9
2015-09-01 10:10:55
Ninpo
Anyone any idea why?
2015-09-01 10:14:51
Ninpo
Like that's massively different
2015-09-01 10:17:27
danblack
Ninpo: does forcing an index on user_list_item(listID) correct the plan? Have you tried out the engine independent stats and histogram options of mariadb to see if that corrects it?
2015-09-01 10:18:17
jkavalik
Ninpo, check "show index from user_list_item;" before and after "analyze table user_list_item;" ? seems like percona is able to spot the non-existence of a row
2015-09-01 10:19:16
Ninpo
paste inc jkavalik
2015-09-01 10:19:42
Ninpo
http://dpaste.com/1XKCC8G
2015-09-01 10:20:31
jkavalik
ok, not much statistics but seems reasonable..
2015-09-01 10:21:09
danblack
danblack thinks based on the selectivity of this tables the engine independent stats/histogram should be able to pick this out quickly.
2015-09-01 10:22:01
danblack
even the innodb status should handle that.
2015-09-01 10:22:28
jkavalik
mariadb uses join buffer, is that implemented in percona 5.5? maybe it overrides something..
2015-09-01 10:24:31
danblack
Ninpo: set innodb_stats_traditional=0; then try the query - should use more sample on large tables like user_list_item
2015-09-01 10:26:41
danblack
Ninpo: https://mariadb.com/kb/en/mariadb/histogram-based-statistics/ https://mariadb.com/kb/en/mariadb/block-based-join-algorithms/
2015-09-01 10:32:26
jkavalik
I hope we get non-blocking analyze soon.. using histogram statistics on galera with big table does not seem very reasonable without it
2015-09-01 10:32:26
Ninpo
Sorry got called into a meeting
2015-09-01 10:33:30
Ninpo
danblack: set stats_trad to 0, doesn't seem to have made a difference
2015-09-01 10:33:34
Ninpo
looking at those other 2 links now
2015-09-01 10:35:29
jkavalik
try the use/force index (listID) if it changes the plan for better
2015-09-01 10:37:28
danblack
jkavalik: sorry to say they dropped it off the dev plan :-( MDEV-7901
2015-09-01 10:38:22
jkavalik
danblack, like moved to next version?
2015-09-01 10:38:37
jkavalik
I hope not totally dropped
2015-09-01 10:39:44
danblack
look like next major version based on Oleksandr's responses.
2015-09-01 10:40:47
danblack
best I can hope for is an additional option on analyze table that chooses a lockless approach.
2015-09-01 10:41:02
jkavalik
well, we might be allowed to upgrade to 10.1 maybe around the time 10.2 gets out..
2015-09-01 10:41:29
jkavalik
with a 40GB table analyze I fear analyze would run a long time..
2015-09-01 10:43:08
danblack
i know it does. I've been using the following to transfer stats from slave to master. ( mysqldump --skip-add-locks --skip-lock-tables --no-create-info --replace mysql column_stats index_stats table_stats ; echo 'flush tables;'; ) | mysql -h DESTINATION mysql
2015-09-01 10:43:59
danblack
and doing things like this to get many tables happening in parallel. mysql -NB -e "select CONCAT('ANALYZE NO_WRITE_TO_BINLOG TABLE ',TABLE_SCHEMA,'.',TABLE_NAME, ' PERSISTENT FOR ALL') from TABLES where TABLE_SCHEMA NOT IN ('information_schema', 'performance_schema', 'mysql', 'sys') and TABLE_TYPE='BASE TABLE'" information_schema | while read cmd; do mysql -e "${cmd}" & sleep 0.5; done
2015-09-01 10:44:38
jkavalik
I wont have a slave to galera for now, but maybe I might gather the stats for some tables on restored backup in testing
2015-09-01 10:45:50
danblack
yeh that's probably your best bet. upping fc_limit to rediculious is probably either not enough or too impacting.
2015-09-01 10:48:40
jkavalik
thanks for the commands; as the other master is mainly for backup, it should not desync more than absolutely needed..
2015-09-01 10:48:42
Ninpo
jkavalik: where would I throw force index (listID) in that query?
2015-09-01 10:49:24
danblack
after the table reference
2015-09-01 10:50:32
Ninpo
so INNER JOIN `user_list_item` force index (listID) ON `user_list_item`.`uniqueID` ?
2015-09-01 10:50:43
danblack
yes
2015-09-01 10:51:21
Ninpo
Hasn't helped :(
2015-09-01 10:51:32
Ninpo
Looking at the histogram stuff now
2015-09-01 10:51:32
jkavalik
ah, fc_limit only takes effect after transaction is certified, so nothing is lost.. should not fear that one
2015-09-01 10:51:34
danblack
https://mariadb.com/kb/en/mariadb/how-to-force-query-plans/ has straight_join
2015-09-01 10:52:12
danblack
jkavalik: its effectively a back queue. good way to avoid flow control if a node gets slow.
2015-09-01 10:53:28
jkavalik
danblack, yep, we met that in case our backup was slowing the other node too much (different cluster), so set it higher.. I just mixed it with something else now..
2015-09-01 10:55:03
danblack
hmm. a desync if fc_limit reached sounds like a useful feature. seen anything like that?
2015-09-01 10:56:47
jkavalik
no, was my misinterpretation actually
2015-09-01 10:57:02
Ninpo
Hmm. Histogram enabled and optimizer_use_condition_selectivity set to 4 still not seeming to help :/
2015-09-01 10:57:44
jkavalik
did you update the stats after enabling histogram?
2015-09-01 10:58:31
danblack
Ninpo: so you've done analyze table ... persistent for all , on all tables?
2015-09-01 10:59:08
danblack
the quicker version is ^ 15mins back by me. warning blocks reads on the table in question?
2015-09-01 10:59:13
danblack
s/?/./
2015-09-01 10:59:56
Ninpo
I ran analyze table on the tables in the query
2015-09-01 11:01:32
danblack
and join_cache_level ?
2015-09-01 11:01:57
danblack
what's your current optimizer_switch too.
2015-09-01 11:02:10
Ninpo
join_cache_level is 2
2015-09-01 11:03:10
Ninpo
I upgraded Percona 5.5 to MariaDB 10 over the weekend and fighting a fair few fires atm :/
2015-09-01 11:03:11
danblack
up to 8 to see fi that changes anything. optimizer_switch needs to enable the bka options except for cost based
2015-09-01 11:04:32
danblack
set optimizer_switch='mrr=on';
2015-09-01 11:04:33
danblack
set optimizer_switch='mrr_sort_keys=on';
2015-09-01 11:05:02
Ninpo
Any way to solve "waiting for table flush" on a table?
2015-09-01 11:05:25
danblack
wait :-)
2015-09-01 11:05:54
Ninpo
It doesn't seem to be going away
2015-09-01 11:05:54
danblack
this from the analyze tables above or generally?
2015-09-01 11:05:59
Ninpo
Been waiting ages :/
2015-09-01 11:06:09
danblack
can take a few mins i've seen.
2015-09-01 11:06:34
danblack
of you see it in operation it could be insufficient table_open_cache
2015-09-01 11:07:40
Ninpo
Opened_tables | 1802539
2015-09-01 11:07:42
Ninpo
:/
2015-09-01 11:07:50
Ninpo
table_open_cache is currently 262144
2015-09-01 11:08:25
danblack
no wonder flush tables it taking a while.
2015-09-01 11:08:40
danblack
what is open_tables?
2015-09-01 11:09:24
Ninpo
altering table open cache flushes tables?
2015-09-01 11:09:24
danblack
oh dear, is that blocking too now :-(
2015-09-01 11:09:28
Ninpo
huh
2015-09-01 11:09:46
Ninpo
Open_tables | 36826
2015-09-01 11:09:50
Ninpo
and rising rapidly
2015-09-01 11:10:27
danblack
what's in the process list? on of those things is stopping/causing the flush tables.
2015-09-01 11:11:18
Ninpo
danblack: http://dpaste.com/151VH3S
2015-09-01 11:12:27
danblack
can't see waiting for table flush any more
2015-09-01 11:12:41
Ninpo
Yeah seems to have stopped all of a sudden o.O
2015-09-01 11:13:13
danblack
did you do a flush tables?
2015-09-01 11:13:48
Ninpo
no but I did change table_open_cache which apparently flushes tables...I don't remember increasing that value causing a table flush in the past?
2015-09-01 11:13:52
Ninpo
Maybe I just didn't notice
2015-09-01 11:14:31
danblack
interesting. wouldn't have expected that.
2015-09-01 11:15:50
Ninpo
hhmm
2015-09-01 11:15:54
Ninpo
| jb_irelandweller.property | analyze | Warning | Incorrect string value: '\x91The Q...' for column 'max_value' at row 1 |
2015-09-01 11:16:23
danblack
MDEV-7383 known problem. won't be all columns.
2015-09-01 11:17:12
Ninpo
ok
2015-09-01 11:17:51
danblack
i'm suspecting it just means less info for those particular columns (you did pick the issue that I just added info to a few hrs ago)
2015-09-01 11:18:11
danblack
and wrote 8 months ago.
2015-09-01 11:19:24
Ninpo
Ok I've run analyze no_write table ... with persistent on all the tables involved in that query
2015-09-01 11:19:47
Ninpo
current optimizer switch:
2015-09-01 11:19:49
Ninpo
optimizer_switch | index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=off,mrr_sort_keys=on,outer_join_wit
2015-09-01 11:20:01
Ninpo
search_depth is 8
2015-09-01 11:20:13
danblack
explain extented...
2015-09-01 11:20:15
Ninpo
join cache level is 8
2015-09-01 11:20:51
Ninpo
danblack: http://dpaste.com/1JPPM35
2015-09-01 11:24:27
jkavalik
Ninpo, if you use some listID which actually exists, is the difference that big too?
2015-09-01 11:25:14
danblack
try without the force
2015-09-01 11:26:07
Ninpo
actually, no
2015-09-01 11:26:08
danblack
100% filtered seems really wrong. can you show session variables;
2015-09-01 11:26:09
Ninpo
if it exists, it's fast
2015-09-01 11:26:19
danblack
how fast?
2015-09-01 11:26:28
Ninpo
+-----+
2015-09-01 11:26:28
Ninpo
| num |
2015-09-01 11:26:29
Ninpo
+-----+
2015-09-01 11:26:29
Ninpo
| 1 |
2015-09-01 11:26:29
Ninpo
+-----+
2015-09-01 11:26:31
Ninpo
1 row in set (0.15 sec)
2015-09-01 11:26:33
Ninpo
oops sorry
2015-09-01 11:27:29
Ninpo
my session vars danblack http://dpaste.com/16MJTQ5
2015-09-01 11:28:06
danblack
use SELECT SQL_NO_CACHE to make sure it isn't just a query cache effect
2015-09-01 11:29:04
jkavalik
can you check explain for the case it exists? I suppose listID then shows relevant estimate
2015-09-01 11:31:39
danblack
Ninpo: your session variables differ from what you said above.
2015-09-01 11:32:01
Ninpo
query cache is off
2015-09-01 11:32:05
Ninpo
danblack: what do you mean?
2015-09-01 11:32:08
danblack
did you set them globally and expect them to have an effect on the current session?
2015-09-01 11:32:18
Ninpo
yes...does it not :S
2015-09-01 11:32:37
danblack
optimizer_switch has mrr_cost_based=on join_cache_level=2
2015-09-01 11:33:00
danblack
nope. globals are copied to session on connection creation only.
2015-09-01 11:33:31
Ninpo
well aren't I an idiot :/
2015-09-01 11:33:33
Ninpo
reconnected
2015-09-01 11:34:02
Ninpo
ok with all that stuff set
2015-09-01 11:34:08
Ninpo
still horrible if the list ID doesn't exist
2015-09-01 11:34:32
Ninpo
oh wait the list ID does exist
2015-09-01 11:34:33
Ninpo
ffs
2015-09-01 11:34:54
Ninpo
select * from user_list_item where listID=714561; gives ~24k rows
2015-09-01 11:35:17
Ninpo
got some new explain extended now though danblack
2015-09-01 11:35:38
Ninpo
http://dpaste.com/3KMPZQN
2015-09-01 11:36:43
danblack
remove straight and force index
2015-09-01 11:39:00
Ninpo
http://dpaste.com/2SCS61Z
2015-09-01 11:39:02
Ninpo
Done
2015-09-01 11:40:32
Ninpo
This is what Percona 5.5 does: http://dpaste.com/01MN39A
2015-09-01 11:41:00
Ninpo
and returns the result in about 0.6s
2015-09-01 11:41:11
danblack
that's for mariadb?
2015-09-01 11:41:21
Ninpo
http://dpaste.com/2SCS61Z is MariaDB
2015-09-01 11:41:26
Ninpo
http://dpaste.com/01MN39A is Percona 5.5
2015-09-01 11:41:36
Ninpo
(the previous version before I upgraded to MariaDB 10)
2015-09-01 11:42:32
Ninpo
I created a fallback server with the old version/data before the upgrade
2015-09-01 11:42:43
danblack
Ninpo: does listID=714561 really correspond to 19% of the table. are most items 'deleted' or not?
2015-09-01 11:44:35
Ninpo
MariaDB [jb_irelandweller]> select count(*) from user_list_item where listItemStatus='Deleted';
2015-09-01 11:44:38
Ninpo
+----------+
2015-09-01 11:44:40
Ninpo
| count(*) |
2015-09-01 11:44:43
Ninpo
+----------+
2015-09-01 11:44:46
Ninpo
| 7 |
2015-09-01 11:44:48
Ninpo
+----------+
2015-09-01 11:45:30
Ninpo
250420 rows in user_list_item, 23936 rows with listID 714561
2015-09-01 11:45:32
danblack
another maybe dumb select if you do the count(*) in query and remove the outer query.
2015-09-01 11:45:58
Ninpo
Pardon?
2015-09-01 11:46:17
danblack
SELECT count(*) AS num FROM `property` ....
2015-09-01 11:46:29
danblack
that make a differnece. sorry brain faiding.
2015-09-01 11:46:59
jkavalik
select count(distinct property.propertyID) and remove group by the same?
2015-09-01 11:47:24
danblack
nice thinking. i like it.
2015-09-01 11:48:04
danblack
of course we just think it should be the same and optimizer magic takes care of that for us :-)
2015-09-01 11:48:15
Ninpo
http://dpaste.com/3MPDWSC
2015-09-01 11:48:19
Ninpo
removed the outer select
2015-09-01 11:48:34
Ninpo
Still takes ages :(
2015-09-01 11:48:44
Ninpo
Gonna try the distinct now
2015-09-01 11:49:18
Naktibalda
could you format your SQL so it is readable?
2015-09-01 11:51:00
otto
elenst: should something trigger the builds for this https://buildbot.askmonty.org/buildbot/grid?branch=ok-debpkg-10.1&category=main or is there some more config to make somewhere?
2015-09-01 11:51:44
Ninpo
Naktibalda: http://dpaste.com/0TKG03X better?
2015-09-01 11:52:02
danblack
yep
2015-09-01 11:52:48
elenst
otto: if it's in our git repo, then as far as I know, only bb-XXX branches are picked up automatically. Otherwise, I'm pretty sure it requires some config changes, but if it can wait for a few hours, Daniel is much more efficient at this
2015-09-01 11:53:01
elenst
I can try, but I'm pretty sure I'll mess up at the beginning
2015-09-01 11:53:04
otto
ok, wait then
2015-09-01 11:53:17
Naktibalda
Ninpo: have you pasted your CREATE TABLE statements already?
2015-09-01 11:54:08
Ninpo
No, just explains and explain extendeds
2015-09-01 11:54:20
Ninpo
Why would it be a near instant result from Percona 5.5 though
2015-09-01 11:55:37
danblack
for some reason hidden deep in the optimizer user_list_item(listID) gets selected first on percona
2015-09-01 11:56:24
jkavalik
maria seems to somehow forget that user_list_item is the only table with any usable where conditions..
2015-09-01 11:56:26
danblack
Ninpo: try the distinct, without the group by
2015-09-01 11:57:45
danblack
select count(distinct property.propertyID) FROM `property` INNER ..WHERE `listID` = '714561' AND listItemStatus <> 'Deleted';
2015-09-01 11:58:49
Ninpo
danblack: http://dpaste.com/3R5WSNY like this you mean?
2015-09-01 11:59:09
Ninpo
oh wait a minute...
2015-09-01 12:00:56
Ninpo
on a different MariaDB 10 slave
2015-09-01 12:01:03
Ninpo
the optimizer hits user_list_item first
2015-09-01 12:01:06
Ninpo
and it's quick!
2015-09-01 12:01:20
Ninpo
using the distinct
2015-09-01 12:01:34
danblack
how quick + explain pls :-)
2015-09-01 12:01:48
Ninpo
dpaste has given up on me :/
2015-09-01 12:01:53
Ninpo
as quick as it is on percona
2015-09-01 12:02:05
danblack
pastie.org
2015-09-01 12:02:55
Ninpo
http://pastie.org/10389390/text
2015-09-01 12:03:00
Ninpo
oops
2015-09-01 12:03:03
Ninpo
http://pastie.org/pastes/10389390/text
2015-09-01 12:03:10
Ninpo
gonna paste my session vars
2015-09-01 12:04:06
danblack
why are you still wrapping it in a subquery?
2015-09-01 12:04:23
Ninpo
That's the original query in the code that's performing fine on MariaDB 10 on a different box
2015-09-01 12:04:44
danblack
http://pastie.org/10389391
2015-09-01 12:05:08
Ninpo
ERROR 1052 (23000): Column 'propertyID' in field list is ambiguous
2015-09-01 12:05:09
danblack
same result and time?
2015-09-01 12:05:19
danblack
property.propertyID then
2015-09-01 12:05:43
Ninpo
But yeah on the other MariaDB box it's performing as I'd expect it to before :S
2015-09-01 12:05:45
Ninpo
really odd
2015-09-01 12:05:55
Ninpo
trying to find somewhere I can paste the slave's session vars
2015-09-01 12:05:57
Ninpo
too big for pastie
2015-09-01 12:06:24
jkavalik
Ninpo, use ; instead of \G for variables
2015-09-01 12:06:41
danblack
on so the options we said had a negative effect but the query rewrite worked?
2015-09-01 12:06:59
jkavalik
no, wrong, \G is better..
2015-09-01 12:07:43
Ninpo
danblack: the original query on a different MariaDB server performs fine
2015-09-01 12:07:55
Ninpo
and query rewrite is slow on the system we've been playing on
2015-09-01 12:08:00
Ninpo
they're all quick on the other server :S
2015-09-01 12:08:17
jkavalik
can you do diff on variables from both servers?
2015-09-01 12:08:26
Ninpo
sure
2015-09-01 12:08:27
danblack
ok. pt-config-diff can help work out differences between running servers
2015-09-01 12:10:04
Ninpo
ok pastebinning pt-config-diff output
2015-09-01 12:10:42
Ninpo
http://pastie.org/pastes/10389405/text
2015-09-01 12:12:21
danblack
optimizer_search_depth =8?
2015-09-01 12:13:49
Ninpo
Set after reading https://mariadb.com/blog/setting-optimizer-search-depth-mysql
2015-09-01 12:13:53
Ninpo
It's been 0
2015-09-01 12:14:34
Ninpo
That's it
2015-09-01 12:14:37
Ninpo
that's the setting
2015-09-01 12:15:12
Ninpo
MariaDB [jb_irelandweller]> set session optimizer_search_depth=62;
2015-09-01 12:15:13
Ninpo
...
2015-09-01 12:15:19
Ninpo
1 row in set (0.57 sec)
2015-09-01 12:15:22
Ninpo
fffffffff
2015-09-01 12:16:09
danblack
the thing about blogs is validating you have exactly the same problem first.
2015-09-01 12:16:56
Ninpo
I assume the other optimizer stuff we did is still worth doing danblack?
2015-09-01 12:19:03
danblack
show the explain
2015-09-01 12:19:34
danblack
when you see mrr and fixted and bka(h) then its still having an effect.
2015-09-01 12:19:52
danblack
fixted/filterd/
2015-09-01 12:20:15
danblack
warning there are some orderby/limit functions that will choose bka(h) and make things slower.
2015-09-01 12:20:24
danblack
s/functions/queries/
2015-09-01 12:21:57
danblack
gtg - have fun
2015-09-01 12:25:26
Ninpo
Thanks for all your help folks
2015-09-01 12:47:22
bytee
montywi: you about ?
2015-09-01 12:49:11
catphish
where can i find information about build options? most notably i want to set the install prefix