logs archiveChat DB / Freenode / #mysql / 2015 / October / 14 / 1
mrFrog
i'm wondering if there's a way to list the number of distinct values for all columns in a database. i have a simple example for listing all columns of a database: https://gist.github.com/anonymous/fdeeef964dd8f76597a8 but i really am not sure how to get the COUNT(DISTINCT()) for each column
i imagine it's either a subquery thing or something strange since it involves two databases?
MatheusOl
mrFrog: Do you mean "all columns in a _table_"?
mrFrog: One way (not sure if the best), would be: SELECT count(*) FROM (SELECT DISTINCT * FROM the_table) t
mrFrog
MatheusO1: i was thinking for all tables in a database, but even if it's by table that would be great (i can manually do it for each table). i just have hundreds of unused columns in some tables and want to determine which columns have no unique values
i'll try that out
MatheusOl
Ah, seems that you want col by col: SELECT COUNT(DISTINCT col1) AS col1, COUNT(DISTINCT col2) AS col2, ....
mrFrog
yeah something more like that, that's right
just not sure how to do this dynamically :)
MatheusOl
mrFrog: You can query INFORMATION_SCHEMA.COLUMNS and build the queries
mrFrog
that's what i have in my example (at the bottom): https://gist.github.com/anonymous/fdeeef964dd8f76597a8#file-example-sql-L82-L85
MatheusOl
mrFrog: Then use that to build a SELECT query. You can use GROUP_CONCAT for instance
mrFrog: Something like: SELECT 'SELECT '||GROUP_CONCAT('COUNT(DISTINCT '||column_name||') AS '||column_name SEPARATOR ', ')||' FROM '||table_name||';' FROM information_schema.columns GROUP BY table_name;
Probably filter a table_schema too
mrFrog
i'm trying that out, kind of advanced :)
Sonny_Jim
So, what's a good way to get data out of a sql database and into JSON form?
droopy
hmm..
Sonny-Jim: mysqldump + some kind of converter?
Here ya go: http://www.csvjson.com/sql2json
Sonny_Jim
One way I'm looking at involves exporting it as XML then converting it
I'm guessing it's a semi-popular thing to do so was wondering what peoples experience in doing so was like
Need something that's not a webpage really
droopy
Are you doing this manually or automatically?
Sonny_Jim
I'm looking at doing it automatically
At the moment it's a bit of a mess, but I have a load of product data in .xlsm files that we use to generate csv files to upload products to Amazon/EBay
I'm trying to migrate from that painful mess to something a little more scalable and automated
A lot of the shop APIs expect the product data in JSON form so I'm just looking around at the options right now
Naktibalda
!perror 13
ubiquity_bot
OS error code 13: Permission denied
OmicroN_
anyone know what would cause high server load from db but no like queries taking long to run?
darwin
background flushing?
OmicroN_
there is a bunch of queries that are "creating sort index"
darwin
that... sounds like someone ran an ALTER or something? or a bunch of inefficient queries?
OmicroN_
the queries aren't even that complicated
mgriffin
OmicroN_: they could be victims
OmicroN_: something else is the problem, they are affected
OmicroN_: i/o stall, some other nasty query, some information_schema queries
OmicroN_: pt-stalk is wonderful for such problems
OmicroN_: get a baseline: mysqladmin -i1 ex | grep Threads_running
OmicroN_
hmm
mgriffin
OmicroN_: pt-stalk --threshold=20 --cycles=3
(where 20 is slightly higher than Threads_running usually is)
OmicroN_
yeah its a 4 cpu server
and load average at 20 right now..
chucky_z
sounds like a good ol' time
OmicroN_
processlist shows like no queries running
chucky_z
whats your avg. iowait? `iotop` or `vmstat -c 1` or `iostat -c 1` or `sar`
OmicroN_
https://i.gyazo.com/d48ed48c61bb2c1360ceabca5c424ba0.png
but there is this every now and then
https://i.gyazo.com/dfa5642bba66430437015166ff294cd8.png
this is sar https://i.gyazo.com/94bc47e5ca6bb4b84258c83fbe7db891.png
oh lordy lol
load is at 62
chucky_z
quite a busy cpu
thumbs
don't rely on the load average.
chucky_z
a load of 62 means something is happening that's pretty bad though
Silent_Ninja
how many CPU cores you have :)?
aradapilot
yes, a load average of 62 indicates something is happening. few people have more than 32 cores in their mysql servers.
show full processlist, might just be a ton of long queries running
else, dump perf schema digest, truncate it, let it collect with the high load for a while, look at the top queries by sum_timer_wait
oh, and of course check for anything non-mysql using cpu
can probably be solved by optimizing the queries using all that cpu time, or, through thread concurrency setup or thread pooling
mgriffin
check df and dmesg, make sure no backups are running off schedule
OmicroN_: on a scale of no way to maybe, does pt-kill help you weather the storm?
OmicroN_: (kills queries that match certain patterns which run "too long")
OmicroN_
it has 4 cpu cores
and no i said earlier there's no queries taking long to run
there's like 5 or so queries that look like SELECT * FROM `chats` USE INDEX (fk_chats_users1, fk_chats_users2) WHERE (`from_id` = '73562' OR `to_id` = '73562') AND `date_end` IS NULL AND (`response` IS NULL OR `response` = 'Accept') ORDER BY `id` DESC
when i run show processlist every now and then
and that query only takes like 1 second to run
well between 1-3
mainly cause cpu load is high
and those queries in processlist have a state of "creating sort index"
mgriffin
OmicroN_: queries with forced index hints like that are usually signs of queries that have been a problem in the past
aradapilot
mgriffin++
mgriffin
OmicroN_: compare that query in a couple sessions where you do/don't use index hints (be sure to SELECT SQL_NO_CACHE * FROM `chats`
OmicroN_: look at explain, run time, and session handler stats
aradapilot
OmicroN_: try the perf schema thing i mentioned, there could be fifty thousand small queries running every second that you don't see in proclist
OmicroN_
the reason for the forced index is because we ran an older version of mysql
think 5.0 and the query ran fine then one day couple weeks ago we updated to 5.6 and the same query ran like crap
comparing the EXPLAIN of the queries from the server with 5.0 and 5.6 it was because 5.6 was using a different index for whatever reason causing the query to take like 10 seconds to run
so forcing it to use same index that 5.0 was using made it run < 1 sec like normal
aradapilot
a lot of changes in the optimizer between 5.0 and 5.6
you using index merge?
(Action) dislikes index merge
OmicroN_
i believe thats what explain says its using
https://i.gyazo.com/a4eeb29efe8b1ef393d23f20230e371c.png
aradapilot
buggrit
well
a union isn't that bad
kinda like doing an explicit union
same index lookups
thms
How can I know if mysql is using innodb or myisam ? What is the default ?
|_[O_O]_|
Morning everyone! Does anyone have experience using GRANT PROCESS?
chucky_z
thms: it's a per-table setting, you can find it in the information_schema.TABLES table, under the ENGINE column
|_[O_O]_|
im looking to GRANT PROCESS ON database_a TO user@hostname but I seem to be running into an issue when doing so -- the error that is returned is "Illegal Grant/Revoke" or "Incorrect usage of DB GRANT"a
(i inadvertently attempted to grant select on information_schema.processlist)
mgriffin
|_[O_O]_|: Enable the user to see all processes with SHOW PROCESSLIST. Level: Global.
!m |_[O_O]_| grant
ubiquity_bot
|_[O_O]_|: See http://dev.mysql.com/doc/refman/5.6/en/grant.html
« prev 1 2 3 4 5 6 7 next »