|
|
| Author |
Message |
gthe
Cacti User
Joined: 29 Jul 2006
Posts: 62
Location: RU
|
 Posted: Tue Feb 12, 2008 9:26 am Post subject: some speedup for MAC Track |
 |
|
Sorry for my English...
In my mysql_slowquery.log I see constant records:
| Code: | # Time: 080117 10:48:06
# User@Host: cactiuser[cactiuser] @ localhost []
# Query_time: 80 Lock_time: 0 Rows_sent: 0 Rows_examined: 1532615
REPLACE INTO mac_track_scan_dates (SELECT DISTINCT scan_date from mac_track_ports);
|
The reason of their occurrence:
| Code: |
mysql> SELECT DISTINCT scan_date from mac_track_ports;
+---------------------+
| scan_date |
+---------------------+
| 2008-01-29 01:13:07 |
| 2008-01-29 01:22:27 |
| 2008-01-29 01:34:54 |
| 2008-01-29 01:43:33 |
| 2008-01-29 01:52:28 |
| 2008-01-29 02:07:40 |
| 2008-01-29 02:14:47 |
............................................
| 2008-02-12 18:06:39 |
+---------------------+
1738 rows in set (25.60 sec)
|
For optimization I have made:
| Code: |
ALTER TABLE `mac_track_ports` ADD INDEX `scan_date` USING BTREE(`scan_date`);
|
And after that my results:
| Code: |
mysql> SELECT DISTINCT scan_date from mac_track_ports;
+---------------------+
| scan_date |
+---------------------+
| 2008-01-29 01:13:07 |
| 2008-01-29 01:22:27 |
| 2008-01-29 01:34:54 |
| 2008-01-29 01:43:33 |
| 2008-01-29 01:52:28 |
.......................................
| 2008-02-12 17:53:10 |
| 2008-02-12 18:06:39 |
| 2008-02-12 18:17:09 |
+---------------------+
1739 rows in set (0.01 sec)
|
From 25 to 0.01 sec!! |
|
| Back to top |
|
 |
streaker69
Cacti Pro User
Joined: 27 Mar 2006
Posts: 634
Location: Psychic Amish Network Administrator
|
| Posted: Tue Feb 12, 2008 1:49 pm Post subject: |
|
|
Interesting: Mine went from 3.77 seconds to 0.04 seconds.
Nice. |
|
| Back to top |
|
|
TheWitness
Developer
Joined: 14 May 2002
Posts: 9257
Location: MI, USA
|
| Posted: Tue Feb 12, 2008 8:58 pm Post subject: |
|
|
Wow!
TheWitness |
|
| Back to top |
|
|
chrisgapske
Cacti User
Joined: 22 May 2007
Posts: 263
Location: Padacuh, Ky-Alpena, MI-Gulf Shores,AL
|
| Posted: Thu Feb 14, 2008 9:33 am Post subject: Re: some speedup for MAC Track |
|
|
I went from 10 seconds to .04?
| 2008-02-04 07:10:59 |
| 2008-02-04 07:25:46 |
| 2008-02-04 07:35:51 |
| 2008-02-04 07:50:54 |
| 2008-02-04 08:06:07 |
| 2008-02-04 08:20:41 |
| 2008-02-04 08:30:56 |
| 2008-02-04 08:40:57 |
| 2008-02-04 08:55:42 |
+---------------------+
2835 rows in set (0.04 sec) |
|
| Back to top |
|
|
ceyounger
Joined: 10 Feb 2006
Posts: 28
Location: San Jose, CA USA
|
| Posted: Tue Feb 19, 2008 5:20 pm Post subject: |
|
|
Nice catch!
I showed this to my DBAs and they said, "Duh!" 
I'm no DBA...
Chris |
|
| Back to top |
|
|
msw1970
Cacti User
Joined: 09 Jan 2007
Posts: 149
|
| Posted: Wed Apr 02, 2008 7:03 am Post subject: |
|
|
I went from 46.30 seconds to 0.02!!!!  |
|
| Back to top |
|
|
TheWitness
Developer
Joined: 14 May 2002
Posts: 9257
Location: MI, USA
|
| Posted: Wed Apr 02, 2008 8:34 am Post subject: |
|
|
Ok, I feel pwn'd now... 
Larry |
|
| Back to top |
|
|
lavermil
Joined: 15 Feb 2007
Posts: 45
Location: Phoenix, Arizona
|
| Posted: Tue Apr 08, 2008 7:20 pm Post subject: |
|
|
| TheWitness wrote: | Ok, I feel pwn'd now...
Larry |
Is this going to be rolled into the next release? |
|
| Back to top |
|
|
dogs
Joined: 14 Mar 2007
Posts: 17
|
| Posted: Tue Jun 24, 2008 10:33 am Post subject: |
|
|
| Can someone give me more info on this please. I'm not too sure how i go about changing this. |
|
| Back to top |
|
|
chrisgapske
Cacti User
Joined: 22 May 2007
Posts: 263
Location: Padacuh, Ky-Alpena, MI-Gulf Shores,AL
|
| Posted: Tue Jun 24, 2008 11:24 am Post subject: |
|
|
I believe you have to just go to a mysql cli and type
ALTER TABLE `mac_track_ports` ADD INDEX `scan_date` USING BTREE(`scan_date`);
it will add an index to your mactrack database making the seraching much faster. |
|
| Back to top |
|
|
dogs
Joined: 14 Mar 2007
Posts: 17
|
| Posted: Wed Jun 25, 2008 4:53 am Post subject: |
|
|
| thanks chris....... |
|
| Back to top |
|
|
|
FAQ
Search
Memberlist
Register
Profile
Log in to check your private messages
Log in
