Index using from tcpdump
Creto
I'm looking to get my tcpdump read by pt_index_usage, but I keep
getting an error. In fact, pt_index_usage seems to ignored my --
database parameter, and only take the latest databases on my server,
mostly like I've read in the following issue:
http://code.google.com/p/maatkit/issues/detail?id=1140
Anyone tried this before? Here is my command to capture my log:
sudo tcpdump -s 65535 -x -nn -q -tttt port 3306 | ./pt-query-digest --
type tcpdump --print --no-report > output.log
This command fills my log file pretty quickly. Then, I use the next
command to try to determine the index usage:
./pt-index-usage --host=myhost --port=3306 --user=myUser --
password=myPassword --database mySpecificDatabase output.log
When running this command, I keep having the following error:
DBD::mysql::db selectall_arrayref failed: Table
'yh_test_main.ClusterElement' doesn't exist [for Statement
"EXPLAIN ..."] at ./pt-index-usage line 4394, <> line 3.
where yh_test_main is the latest schema on my database server.
Any idea of what I'm doing wrong?
Baron Schwartz
> --
> You received this message because you are subscribed to the Google Groups "Percona Discussion" group.
> To post to this group, send email to percona-d...@googlegroups.com.
> To unsubscribe from this group, send email to percona-discuss...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/percona-discussion?hl=en.
>
--
Chief Performance Architect at Percona <http://www.percona.com/>
+1 (888) 401-3401 x507
Calendar: <https://tungle.me/percona_baron>
Percona Live MySQL Conference & Expo <http://www.percona.com/live>
Creto
called Schema). The one used by the tool is the one that appears last
when you sort the schemas of my servers in a alphabetic order...
Baron Schwartz
Creto
figure out what's wrong: he seems to always try to locate the tables
in the wrong schema. Right now, I made manually a "slow query log" (in
fact, it was created from a perl script). Here is a sample of it:
# User@Host: cloud_strict[cloud_strict] @ plcaronstpierre.usine.8d.com
[192.168.142.136]
# Query_time: 0.002673 Lock_time: 0.000121 Rows_sent: 10
Rows_examined: 30
use jfcroteau_bss_ftk_main;
SET timestamp=1330090066;
select this_.id as id6_0_, this_.version as version6_0_,
this_.creationDate as creation3_6_0_, this_.encryptedData as
encrypte4_6_0_, this_.name as name6_0_ from KeyStoreData this_;
# User@Host: cloud_strict[cloud_strict] @ plcaronstpierre.usine.8d.com
[192.168.142.136]
# Query_time: 0.002673 Lock_time: 0.000121 Rows_sent: 10
Rows_examined: 30
use jfcroteau_bss_ftk_main;
SET timestamp=1330090066;
select this_.id as id31_1_, this_.version as version31_1_,
this_.capStatistics_id as capStati8_31_1_, this_.definition as
definition31_1_, this_.enabled as enabled31_1_,
this_.logicalTerminal_id as logicalT9_31_1_, this_.model as
model31_1_, this_.tag as tag31_1_, this_.terminalPassphrase as
terminal7_31_1_, capstatist1_.id as id32_0_, capstatist1_.connected as
connected32_0_, capstatist1_.lastCommunication as lastComm3_32_0_,
capstatist1_.lastConnection as lastConn4_32_0_,
capstatist1_.lastConnectionFederator as lastConn5_32_0_,
capstatist1_.lastConnectionStatusChange as lastConn6_32_0_,
capstatist1_.lastDeconnection as lastDeco7_32_0_,
capstatist1_.lastKnownRemoteIpAddress as lastKnow8_32_0_ from Terminal
this_ inner join TerminalCapStatistics capstatist1_ on
this_.capStatistics_id=capstatist1_.id where
capstatist1_.lastConnectionFederator='fed001' and
capstatist1_.connected=1;
That, is 2 queries, going in tables against jfcroteau_bss_ftk_main,
let's say. Here is the command I use:
./pt-index-usage --host=myMysqlhost --port=3306 --user=user --
password=password --database jfcroteau_bss_ftk_main --create-save-
results-database --empty-save-results-tables --save-results-database
h=myMysqlhost,P=3306,u=user,p=password,D=percona
slow_query_log_emulation.log
I keep getting this error:
myColumn from Terminal this_ inner join TerminalCapStatistics
capstatist1_ on this_.capStatistics_id=capstatist1_.id where
capstatist1_.lastConnectionFederator='something' and
capstatist1_.connected=1"] at ./pt-index-usage line 4394, <> line 2.
I don't know why, but instead of looking in my table in the
jfcroteau_bss_ftk_main, it goes against yh_test_main schema. Is it me?
Is there a problem in the file format? In the command? Is it related
to http://code.google.com/p/maatkit/issues/detail?id=1140 ?
Creto
jfcroteau_bss_ftk_main": I thought the pt-index-usage script was using
this line to determine in which schema the table was supposed to be.
Baron Schwartz
Thanks for the additional information. I'll try to see if I can reproduce this. Which version of pt-index-usage are you using?
Baron
> --
> You received this message because you are subscribed to the Google Groups "Percona Discussion" group.
> To post to this group, send email to percona-d...@googlegroups.com.
> To unsubscribe from this group, send email to percona-discuss...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/percona-discussion?hl=en.
>
- Baron
Baron Schwartz
I can't reproduce this problem. You might need to look at using PTDEBUG=1 and capture the output to a file, then inspect it to see what is happening.
Baron
> --
> You received this message because you are subscribed to the Google Groups "Percona Discussion" group.
> To post to this group, send email to percona-d...@googlegroups.com.
> To unsubscribe from this group, send email to percona-discuss...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/percona-discussion?hl=en.
>
- Baron
--
Creto
Like I've said, I tried many way to generate an input for pt-index-
usage (version 2.0.3 btw). One of them was to use tcpdump piped into
pt-query-digest then piped into pt-index-usage. Even that way (which
looks pretty straight-forward to me), as soon as I'm using a database
server which have many schemas on it, I have that kinda of error.
My command looks like this:
sudo tcpdump -s 65535 -c 1000 -x -nn -q -tttt port 3306 | ./pt-query-
digest --type tcpdump --print --no-report | ./pt-index-usage.8d --
host=myMysqlHost --port=3306 --user=user --password=password --
save-results-tables --save-results-database
Same thing trying to use pt-index-usage with our mysql slow query log.
It really seems that pt-index-usage can't deal with a database server
having more than 1 schema on it. Or I don't use the tool correctly?
Thank you.
Baron Schwartz
I'm pretty sure you've found a bug. Can you do the following and see what happens?
1. Use your short sample file that you showed before.
2. Run the following command.
PTDEBUG=1 ./pt-index-usage --
host=myMysqlHost --port=3306 --user=user --password=password --
database jfcroteau_bss_ftk_main --create-save-results-database --empty-
save-results-tables --save-results-database
h=myMsqlHost,P=3306,u=user,p=password,D=percona > out.txt 2>&1
Examine the out.txt file and see if you can figure out what it's doing wrong.
- Baron
Creto
I'm back on this problem. I'm try to figure out what is wrong with
the sample. Can I send you files privately? Because those files
contains sensitive data.
Quick question: the "use someScheme;" statement in the slow query log
is not always present. Is it normal? Is the algorith is something like
all the queries after a "use" are all against database until the next
use? I'm only asking for debugging purpose. Can't read the
confirmation of it on the web.
Thank you.
Baron Schwartz
Yes, you can send me the files privately. MySQL prints a "use"
statement every time the current database changes. We mimic this in
pt-query-digest. Unfortunately there is no way to do "use NULL" when a
statement doesn't have a default database.