Table logs
|
|
date_time
|
user
|
message
|
2013-05-21 15:39:55
|
krusty_ar
|
hi all, I have a question about indexes that can't seem to answer from the docs, I have a table with an index on column A, that get's used when I select using a where clause with A in it, but if I make a second select using A and B (another column), it does a full table sweep, is this expected? is it possible to use the index to "narrow" the search?
|
2013-05-21 15:40:50
|
serg
|
krusty_ar: depends on your query
|
2013-05-21 15:41:08
|
krusty_ar
|
serg: https://gist.github.com/krusty/5620146
|
2013-05-21 15:41:15
|
krusty_ar
|
doesn't seem to complex
|
2013-05-21 15:41:56
|
georg__
|
kostja_osipov, ping ?
|
2013-05-21 15:44:40
|
serg
|
krusty_ar: just in case, analyze your table. but anyway. perhaps most of the rows satisfy that WHERE condition, then it'd be faster to do a table scan
|
2013-05-21 15:44:42
|
georg__
|
I wonder if the behaviour of 10.0 client library is correct: When executing a stored procedure mysql_stmt_reset only flushes the current unbuffered result, not the following unbuffered results
|
2013-05-21 15:45:39
|
krusty_ar
|
serg: actually running the queries goes from 4 seconds to more than a minute
|
2013-05-21 15:45:54
|
krusty_ar
|
what do I need to check on the table?
|
2013-05-21 15:46:22
|
serg
|
ANALYZE TABLE api_person
|
2013-05-21 15:46:59
|
krusty_ar
|
Msg_text: OK
|
2013-05-21 15:47:54
|
krusty_ar
|
going back to the original question, is this behaviour expected?
|
2013-05-21 15:55:02
|
svar
|
Hello, any chance we have a release date for 5.5.31 before 2 days ?
|
2013-05-21 15:56:10
|
serg
|
yes
|
2013-05-21 15:57:13
|
svar
|
serg was that yes for me :)
|
2013-05-21 15:57:19
|
serg
|
yes
|
2013-05-21 15:57:20
|
serg
|
:)
|
2013-05-21 15:57:25
|
svar
|
yes
|
2013-05-21 15:57:57
|
georg__
|
also calling mysql_stmt_close after mysql_prepare(CALL proc()) and mysql_stmt_execute ends up in a client library crash
|
2013-05-21 16:11:24
|
krusty_ar
|
serg: what should I interpret from this? https://gist.github.com/krusty/5620572
|
2013-05-21 17:09:21
|
svar
|
krusty_ar it looks like this is expected as you can see you already scan 1M row with 2 values in the first query the second query have *5 the number of conditions that make you clause to 1/4 of the table read by index
|
2013-05-21 17:11:39
|
krusty_ar
|
svar: yes, forcing the index actually takes a lot of time
|
2013-05-21 17:12:00
|
krusty_ar
|
so so I have any choices that don't require knowing the filters in advance?
|
2013-05-21 17:14:57
|
krusty_ar
|
do I have*
|
2013-05-21 17:19:51
|
krusty_ar
|
svar: I think I see your point, so if I had a similar situation but with a more "selective" index, it would work right?
|
2013-05-21 17:28:00
|
svar
|
krusty_ar, yes if selectivity is good then it will pick the index or you can force index but that could be dangerous
|
2013-05-21 17:28:47
|
svar
|
krusty_ar, is that innodb or myisam ?
|
2013-05-21 17:30:08
|
svar
|
krusty_ar, this is propably happening because you have not enough memory to cache the dataset in memory
|
2013-05-21 17:34:19
|
archivist
|
krusty_ar_, with those sample bits of sql perhaps you should have summary tables and keep them up to date with triggers or cron jobs so you only have to look at a row or three
|
2013-05-21 17:34:55
|
krusty_ar_
|
archivist: you mean a poor mans cube?
|
2013-05-21 17:35:15
|
archivist
|
less to scan
|
2013-05-21 17:35:21
|
krusty_ar_
|
if I can't coherce the users to use more "mandatory" filters, I guess that's my only choice
|
2013-05-21 17:36:20
|
krusty_ar_
|
I was hoping I didn't have to do any fine tuning on the actual SQL, but it may not be possible
|
2013-05-21 17:36:44
|
svar
|
krusty_ar, this is propably happening because you have not enough memory to cache the dataset in memory
|
2013-05-21 17:38:40
|
svar
|
krusty_ar_, you can give more memory, divide the size of the table with better data type , SSD , or use clustered index of tokuDB in coming 5.5.32
|
2013-05-21 17:39:48
|
svar
|
krusty_ar_, have you try mrr optim if you are disk bound
|
2013-05-21 18:15:16
|
svar
|
serg, on 5.5 sometime uninstall plugin on audit plugin does not call deinit function and say that will be deleted on restart. On dev all is fine but with some load it failed any idea on what can cause this ?
|
2013-05-21 18:15:35
|
serg
|
yes
|
2013-05-21 18:15:54
|
serg
|
the server cannot uninstall a plugin if it is used
|
2013-05-21 18:16:13
|
svar
|
Serg any workaround
|
2013-05-21 18:17:06
|
serg
|
so, if you "uninstall" it it'll wait until all users will stop using it
|
2013-05-21 18:18:04
|
svar
|
hello so stop slave and max_connection =0 should be fine
|
2013-05-21 18:20:41
|
svar
|
serg, more we can not get the system user from the the queries that are coming from replication ?
|
2013-05-21 18:21:51
|
svar
|
serg, sorry it 's because we have no host and we are looking for @ (so not a bug)
|
2013-05-21 18:23:16
|
svar
|
serg workaround is ok tx
|
2013-05-21 18:27:34
|
serg
|
svar: you don't have to stop all threads, they release audit plugins between queries too
|
2013-05-21 18:27:49
|
serg
|
but if there's a long-running query, it'll keep an audit plugin locked for a while
|
2013-05-21 18:28:30
|
svar
|
serg we have constant workload about 400 writes/s
|
2013-05-21 18:28:48
|
serg
|
after UNINSTALL the plugin is marked for uninstallation
|
2013-05-21 18:29:15
|
serg
|
so new threads won't use it, it will only wait for those that were already using it
|
2013-05-21 18:29:16
|
svar
|
yes i saw that it has disapear from mysql.plugin
|
2013-05-21 18:29:38
|
serg
|
and eventually it'll go away automatically
|
2013-05-21 18:29:41
|
svar
|
so in my case the replication
|
2013-05-21 18:30:23
|
svar
|
the replication was bringing those 400qps
|
2013-05-21 19:11:18
|
kolbe
|
what's going on w/ 5.5.31? any ETA?
|
2013-05-21 19:13:39
|
serg
|
yes, as soon as builtbot will be ok
|
2013-05-21 19:15:29
|
kolbe
|
ok :)
|
2013-05-22 01:05:37
|
BJohnson
|
anyone know of a potential memory leak in 5.5.30?
|
2013-05-22 01:06:07
|
grknight
|
BJohnson: from a single query?
|
2013-05-22 01:06:20
|
BJohnson
|
upgraded a server from 5.5.29 to 5.5.30 this morning, it's been killed by oom-killer 10 times already today. Not seeing anything abnormal other than mysql creeping up. The box has 24 GB memory and 12 GB allocated to the buffer pool, max I've ever seen it hit before today was 18 GB
|
2013-05-22 01:06:37
|
grknight
|
https://mariadb.atlassian.net/browse/MDEV-4336
|
2013-05-22 01:06:38
|
BJohnson
|
grknight: unknown at this point…but the load on the server hasn't changed, only the mariadb version.
|
2013-05-22 01:07:08
|
grknight
|
that's one possibility
|
2013-05-22 01:07:09
|
BJohnson
|
grknight: certainly sounds like what could be occurring.
|
2013-05-22 01:07:21
|
grknight
|
5.5.31 is due any day now
|
2013-05-22 01:07:23
|
BJohnson
|
and that's a query that I'd expect to be very common
|
2013-05-22 01:07:26
|
BJohnson
|
in our environment.
|
2013-05-22 01:14:20
|
BJohnson
|
grknight: yep…just found evidence, that's it. :(
|
2013-05-22 01:14:25
|
BJohnson
|
grknight++ thanks
|
2013-05-22 07:22:18
|
will_
|
abarkov: Thanks for the help on the CONNECT stuff. Sorry about yesterday. Went out for a quick bite to eat then got distracted
|
2013-05-22 07:22:54
|
abarkov
|
will_: no problem. thanks for trying it.
|
2013-05-22 07:23:32
|
abarkov
|
will_: did you actually manage to do selects from an ODBC table?
|
2013-05-22 07:23:45
|
will_
|
Unfortunately not. I don't have an ODBC set up
|
2013-05-22 07:24:06
|
will_
|
But our client was able to get it to work (so he says)
|
2013-05-22 07:24:58
|
abarkov
|
will_: your last message says you got "Data source name not found", which is better than "Unsupported table type ODBC", but still not the whole thing it can do :)
|
2013-05-22 07:25:24
|
abarkov
|
will_: good to know your client was able.
|
2013-05-22 07:27:30
|
will_
|
Also, I might not be understanding all of this very well. But I only replaced the mysqld binary and it worked. I can swap it out and it won't work anymore.
|
2013-05-22 07:27:49
|
will_
|
abarkov: Yeah, exactly. The error was from unixODBC, that's why I passed the answer along
|
2013-05-22 07:27:51
|
abarkov
|
will_: did you compile it from sources?
|
2013-05-22 07:28:16
|
will_
|
Yes
|
2013-05-22 07:28:51
|
abarkov
|
probably you enabled WITH_CONNECT_STORAGE_ENGINE when running cmake or cmake-gui
|
2013-05-22 07:29:05
|
abarkov
|
in this case it compiles inside mysqld.
|
2013-05-22 07:29:16
|
will_
|
Ah! Ok.
|
2013-05-22 07:29:23
|
will_
|
I just used the defaults :)
|
2013-05-22 07:29:49
|
abarkov
|
If no WITH_CONNECT_STORAGE_ENGINE is given, then it still compiles, but as a dynamic module.
|
2013-05-22 07:30:05
|
abarkov
|
how did you run cmake?
|
2013-05-22 07:30:23
|
will_
|
RUnning BUILD/compile-pentium64-max
|
2013-05-22 07:30:42
|
abarkov
|
I see. It enables all storage engine, IIRC.
|
2013-05-22 07:30:55
|
abarkov
|
... all storage engineS ....
|
2013-05-22 07:31:13
|
abarkov
|
That explains.
|
2013-05-22 07:31:25
|
will_
|
In any case, works great! :)
|
2013-05-22 07:32:55
|
abarkov
|
will_: MariaDB binaries compile ha_connect.so as a dynamic module.
|
2013-05-22 07:33:28
|
abarkov
|
will_: to avoid mysqld dependency on libxml2 and unixodbc (which are not needed for "normal" mysqld)
|
2013-05-22 07:33:43
|
will_
|
ah
|
2013-05-22 07:35:38
|
will_
|
abarkov: Is there a difference between CentOS 6.3 and CentOS 6.4? I thought they would be the same?
|
2013-05-22 07:38:47
|
abarkov
|
abarkov: libxml2 and unixodbc library versions might be different.
|
2013-05-22 07:38:58
|
abarkov
|
will_: libxml2 and unixodbc library versions might be different.
|
2013-05-22 07:39:07
|
abarkov
|
will_: I did not check though.
|
2013-05-22 07:41:37
|
will_
|
abarkov: Hmm ok. From my understanding, RHEL tries to keep ABI compatibility, so we wouldn't have to worry about the minor versions. But I have heard some edge cases where it did matter. Just curious if you knew
|
2013-05-22 07:50:34
|
abarkov
|
will_: no I did not. thanks.
|
2013-05-22 08:01:25
|
jplindst
|
serg: How you build MariaDB/MySQL so that you can run it with normal user and debug it using ddd e.g innodb code normally, now I get following from (gdb) break /home/jan/Downloads/mysql-5.6.11/storage/innobase/os/os0file.cc:4525
|
2013-05-22 08:01:25
|
jplindst
|
Cannot access memory at address 0xbc392f
|
2013-05-22 08:02:06
|
jplindst
|
serg: It seems that something has changed on MySQL (plugins?) from last time I have debugged it
|
2013-05-22 08:42:15
|
serg
|
jplindst: I've just tried to load 5.6 in gdb, with a breakpoint on that function in os0file.cc - worked normally, no surprises.
|
2013-05-22 08:46:25
|
jplindst
|
serg: I see how you compile MariDB ?
|
2013-05-22 08:46:48
|
serg
|
cmake -DCMAKE_BUILD_TYPE=Debug .
|
2013-05-22 08:46:51
|
serg
|
nothing fancy
|
2013-05-22 08:48:54
|
jplindst
|
serg: I see thanks
|
2013-05-22 09:17:44
|
svar
|
serg , for audit plugin we have replication system user that is empty is this expected behavior ?
|
2013-05-22 09:20:15
|
knielsen
|
serg: I need help with sys_vars.cc and warnings. I emit a warning with push_warning_printf() from do_check(). I _do_ see the warning in SHOW WARNINGS. But I do _not_ see the "0 rows affected, 1 warning" message.
|
2013-05-22 09:20:49
|
knielsen
|
As far as I can see I'm doing the same as other places in sys_vars.cc that emit warnings correctly. Any hint to where I should start looking?
|
2013-05-22 09:21:01
|
serg
|
in what statement?
|
2013-05-22 09:21:04
|
serg
|
ah
|
2013-05-22 09:21:33
|
knielsen
|
serg: SET global gtid_slave_pos='' - this is new code not pushed, but I can show it to you if you want of course ...
|
2013-05-22 09:22:00
|
knielsen
|
serg: or maybe you know which value to printf() to track the warning counter and why it is not incremented / or perhaps reset ?
|
2013-05-22 09:24:53
|
serg
|
knielsen: thd->stmt_da->m_statement_warn_count
|
2013-05-22 09:25:26
|
knielsen
|
serg: ok, thanks, I'll try to see if/why that variable is different for my code than for the other cases that work
|
2013-05-22 09:29:37
|
knielsen
|
knielsen hates the mess that is sys_vars.cc and friends, I always waste hours upon hours in that code to do even simple stuff
|
2013-05-22 10:06:04
|
knielsen
|
serg|away: ok, I found the problem. SET GLOBAL gtid_slave_pos="" pushes a warning, then goes modify the mysql.gtid_slave_pos table. As part of modifying the table, it does THD::reset_for_next_command(). This then clears the warnings just pushed
|
2013-05-22 10:06:51
|
knielsen
|
serg|away: So my code is wrong, but I am not sure how to fix it. I wonder what is the correct way to modify a table inside SET ... ? Maybe I should just omit the reset_for_next_command() ?
|
2013-05-22 10:07:49
|
knielsen
|
svoj: ^^^ do you know by any chance?
|
2013-05-22 10:11:52
|
knielsen
|
Oh, this is more complex, I need to consider if SET GLOBAL gtid_slave_pos is done in the middle of a user transaction, this should be an error...
|
2013-05-22 10:12:44
|
knielsen
|
knielsen back to the drawing board
|
2013-05-22 10:18:39
|
knielsen
|
serg|away: anyway, thanks for the hint with m_statement_warn_count, now I found the real problem, which is deeper and something with my own code
|
2013-05-22 11:04:01
|
knielsen
|
serg|away: just in case you want to hear the conclusion: It works now, solution was to not THD::reset_for_next_command() in the middle of executing SET GLOBAL gtid_slave_pos='xxx'. Thanks for the hint
|
2013-05-22 11:16:19
|
wlad
|
elenst, do you remember which packafge inclues static libraries? on Redhat offspings?
|
2013-05-22 11:20:19
|
elenst
|
wlad: no, i'm always lost in those -shared -compat -shared-compat etc. if that's what you mean
|
2013-05-22 11:20:39
|
elenst
|
i basically install one by one until i find what i need :facepalm:
|
2013-05-22 11:20:41
|
wlad
|
elenst, not exactly
|
2013-05-22 11:20:48
|
wlad
|
I'm looking for static libs
|
2013-05-22 11:20:51
|
wlad
|
not shared
|
2013-05-22 11:22:21
|
elenst
|
well, common, client... wlad: common maybe?
|
2013-05-22 11:22:29
|
elenst
|
i'm not sure, just guessing
|
2013-05-22 11:23:21
|
wlad
|
elenst, ok. I just thought you might remember ;)
|
2013-05-22 11:23:40
|
elenst
|
never.. it's like i never remember when the first snow falls
|
2013-05-22 11:39:00
|
Jb_boin
|
there isnt much documentation about the optimizer switch optimize_join_buffer_size, if its on it will limit the join buffer for a query to the join_buffer_space_limit value or i am wrong?
|
2013-05-22 11:43:47
|
spetrunia__
|
ulong JOIN_CACHE::get_max_join_buffer_size(bool optimize_buff_size)
|
2013-05-22 11:43:48
|
spetrunia__
|
...
|
2013-05-22 11:43:58
|
spetrunia__
|
If the parameter
|
2013-05-22 11:43:58
|
spetrunia__
|
optimize_buff_size true then this value does not exceed the size of the
|
2013-05-22 11:43:58
|
spetrunia__
|
space needed for the estimated number of records 'max_records' in the
|
2013-05-22 11:43:58
|
spetrunia__
|
partial join that joins tables from the first one through join_tab.
|
2013-05-22 11:44:12
|
spetrunia__
|
hmm
|
2013-05-22 11:45:12
|
spetrunia__
|
This
|
2013-05-22 11:45:12
|
spetrunia__
|
value is also capped off by the value of join_tab->join_buffer_size_limit,
|
2013-05-22 11:45:12
|
spetrunia__
|
if it has been set a to non-zero value,
|
2013-05-22 11:45:44
|
svar
|
spetrunia__, did you catch on this http://dom.as/2013/04/16/the-saddest-bug-of-them-all-sql-is-dead ?
|
2013-05-22 11:46:54
|
spetrunia__
|
svar: I saw that.. there are rumors Oracle is working on fixing that...
|
2013-05-22 11:49:46
|
Jb_boin
|
spetrunia__, thanks, i am checking the sql_join_cache.c comment
|
2013-05-22 11:51:40
|
spetrunia__
|
Jb_boin: I think the description is not very clear.. I could ask the developer who wrote the code, tonight (he's is in USA)
|
2013-05-22 11:54:56
|
Jb_boin
|
so if i understood correctly, when its on it will try to determine the join cache buffer size according to the row count but it will be limited by the join_cache_size
|
2013-05-22 11:56:01
|
Jb_boin
|
yep, no problem, i will document that on the KB after if its ok
|
2013-05-22 11:59:17
|
spetrunia__
|
Jb_boin: yes, my understanding is same as yours.. it won't hurt to ask, to be sure
|
2013-05-22 13:33:32
|
bandroidx
|
hi, if i have a "balance" field in a row, and i want to withdraw from the balance, and ensure that the withdraw will work, would i want to select for update when i read the balance, update it and then commit?
|
2013-05-22 13:37:15
|
knielsen
|
bandroidx: that sounds sensible
|
2013-05-22 13:38:01
|
bandroidx
|
i would want to use select for update, not select lock in shared mode ? if i read this correctly
|
2013-05-22 13:39:35
|
knielsen
|
lock in shared mode would allow two withdravals to start in parallel on the same value, so not what is wanted
|
2013-05-22 13:40:04
|
bandroidx
|
ok then i read it correctly :)
|
2013-05-22 13:40:48
|
catphish
|
i have a maria server where clients are reporting their connections are being lost, specifically ruby reports "Lost connection to MySQL server during query", wait_timeout is set to 1 year, and max_allowed_packet is 256MB, (which I'm not exceeding). are there any other limits that would cause this behaviour?
|
2013-05-22 13:44:26
|
Jb_boin
|
interactive_timeout
|
2013-05-22 13:44:38
|
wlad
|
catphish, you need to google for sveta smirnova's essays on "Lost connection".
|
2013-05-22 13:45:14
|
wlad
|
I recall she listed 20 reasons, including server shutdown :)
|
2013-05-22 13:45:23
|
catphish
|
thanks, i realize it's a wide scope, but i've addressed all the issues i could find
|
2013-05-22 13:45:43
|
wlad
|
if your clients read rtoo slowly and server needs to wait in socket write
|
2013-05-22 13:45:56
|
wlad
|
then server might close the socket after write_timeout
|
2013-05-22 13:46:00
|
wlad
|
or so
|
2013-05-22 13:46:19
|
catphish
|
wlad: that seems likely, these queries elicit large (multi megabyte) results
|
2013-05-22 13:46:32
|
wlad
|
yes, this is often an issue with different connectors
|
2013-05-22 13:46:57
|
wlad
|
one way it to up the write timeout parameter
|
2013-05-22 13:47:33
|
wlad
|
(another way is to rewrite the queries, or maybe the clients so they constantly read from net)
|
2013-05-22 13:48:19
|
catphish
|
hmm, net_write_timeout is 60 seconds, i wouldn't expect it to take that long to consume the reply
|
2013-05-22 13:52:51
|
catphish
|
thanks for the pointers, i'll try increasing the net timeouts first
|
2013-05-22 14:07:46
|
Jb_boin
|
does the order of the arguments on a query when using OR or AND is significative or does the optimizer re-order them?
|
2013-05-22 14:11:14
|
sam363
|
I have created a user using two questions, but I'm unable to figure how to authenticate using the plugin.
|
2013-05-22 14:16:46
|
spetrunia__
|
Jb_boin: in 99% of cases no
|
2013-05-22 14:17:04
|
spetrunia__
|
there could be exceptions with very complicated, deep-nested AND-ORed range condition
|
2013-05-22 14:19:15
|
Jb_boin
|
ok, as the show warnings after an explain extended was changing if i modified the order i wondered
|