Re: Solution to slow queries
Paul Halliday wrote:
srcaddr VARCHAR(15),
dstaddr VARCHAR(15),
Are these ip-adresses? If they are, consider using UNSIGNED INT columns
and the INET_NTOA() and INET_ATON() funtions. It will save you a lot of
space, thus increase the amount of data your hw can handle.
They are indeed ip addresses. This infomation is gathered and input
into the db via a program called flow-export (export netflows). I
intially had the column as UNSIGNED INT but it would only pick up the
first octet, so I switched to VARCHAR.
This would happen if you did not use INET_ATON() to transform the IP to
a single integer. When you select the data later, you use INET_NTOA() to
transform the other way:
mysql> select inet_aton('127.0.0.1');
++
| inet_aton('127.0.0.1') |
++
| 2130706433 |
++
1 row in set (0.00 sec)
mysql> select inet_ntoa(2130706433);
+---+
| inet_ntoa(2130706433) |
+---+
| 127.0.0.1 |
+---+
1 row in set (0.00 sec)
http://dev.mysql.com/doc/mysql/en/miscellaneous-functions.html >
--
Roger
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Solution to slow queries
On 5/10/05, Roger Baklund <[EMAIL PROTECTED]> wrote: > Paul Halliday wrote: > > srcaddr VARCHAR(15), > > dstaddr VARCHAR(15), > > Are these ip-adresses? If they are, consider using UNSIGNED INT columns > and the INET_NTOA() and INET_ATON() funtions. It will save you a lot of > space, thus increase the amount of data your hw can handle. They are indeed ip addresses. This infomation is gathered and input into the db via a program called flow-export (export netflows). I intially had the column as UNSIGNED INT but it would only pick up the first octet, so I switched to VARCHAR. > > > I have read up a bit on merge tables (this is probably the answer) but > > I am unsure as to how you trigger the changes. ie, how do you do the > > rollover after every 24hours? > > You would have to program this yourself, there are no mechanisms for > this in MySQL. It's pretty straight forward, though. Just use a shell > script and cron or the equivalent if you are on a non-unix platform. > > -- > Roger > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- _ Paul Halliday http://dp.penix.org "Diplomacy is the art of saying "Nice doggie!" till you can find a rock." -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Solution to slow queries
Paul Halliday wrote: srcaddr VARCHAR(15), dstaddr VARCHAR(15), Are these ip-adresses? If they are, consider using UNSIGNED INT columns and the INET_NTOA() and INET_ATON() funtions. It will save you a lot of space, thus increase the amount of data your hw can handle. I have read up a bit on merge tables (this is probably the answer) but I am unsure as to how you trigger the changes. ie, how do you do the rollover after every 24hours? You would have to program this yourself, there are no mechanisms for this in MySQL. It's pretty straight forward, though. Just use a shell script and cron or the equivalent if you are on a non-unix platform. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Solution to slow queries
We did something similar for our large statistic tables. The older data that no longer changes would get shipped off into a very fast read only table with a cron job and then that is the table we would generate the reports on. Even with millions of entries it is incredibly fast. Eric Jensen [EMAIL PROTECTED] wrote: >Consider compressing the tables (Making them READ ONLY) with the MyISAM >engine. The updating table (current one) would not be available since it could >not be compressed. > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Solution to slow queries
Hi, you have to play with "explain" to see which index is used in your queries. Since you defined only mono-column indexes, i think they are not used in queries with multi-criteria search. Consider adding indexes with all used columns and eventually drop the not used ones to not slow updates and inserts. merge (Myisam) tables can help you to partition the data on relevant keys used in the queries. But i'm not sure it's certainly good because you then loose the innodb row locking which is better in your situation. you can also consider archiving of old (and not used) data. Finally, you can prepare agregation tables every day (or hour) for example if you can consider a gap of data in the results. Mathias Selon Paul Halliday <[EMAIL PROTECTED]>: > Hello, > > I am working on a database that deals with network statistics. I have > a program that generates web reports based on this data every ten > minutes. > > The table layout looks something like this: > > CREATE TABLE traffic > ( > unix_secs INT UNSIGNED NOT NULL, > dpkts INT UNSIGNED NOT NULL DEFAULT 0, > doctets INT UNSIGNED NOT NULL DEFAULT 0, > first INT UNSIGNED, > last INT UNSIGNED, > srcaddr VARCHAR(15), > dstaddr VARCHAR(15), > srcport SMALLINT UNSIGNED, > dstport SMALLINT UNSIGNED, > prot TINYINT UNSIGNED NOT NULL DEFAULT 0, > tos TINYINT UNSIGNED NOT NULL DEFAULT 0, > tcp_flags TINYINT UNSIGNED NOT NULL DEFAULT 0, > INDEX unix_secs (unix_secs), > INDEX srcaddr (srcaddr), > INDEX dstaddr (dstaddr), > INDEX srcport (srcport), > INDEX dstport (dstport) > ); > > Now, as time progresses the queires are getting slower and slower. > I know this is expected, so I am curious as to how I can have a main > table that has all traffic, so that I can do monthly/yearly reports, > and also have a daily table so that I can quickly do reports every > minute or so on that data. > > I have read up a bit on merge tables (this is probably the answer) but > I am unsure as to how you trigger the changes. ie, how do you do the > rollover after every 24hours? > > Any thoughts, or a pointer in the right direction would be greatly > appreciated. > > > Thanks. > > -- > _ > Paul Halliday > http://dp.penix.org > > "Diplomacy is the art of saying "Nice doggie!" till you can find a rock." > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Solution to slow queries
I'm somewhat a newbee on this database but some observations: As your table grows (and indexes) INSERTS will definitly slow because of the indexes. Consider MySQL's version of Oracle's partitioning and using MERGE TABLES "feature". Just remember that if you change 1 table, all of them have to be rebuilt the same way. Consider compressing the tables (Making them READ ONLY) with the MyISAM engine. The updating table (current one) would not be available since it could not be compressed. Paul Halliday <[EMAIL PROTECTED]> wrote: >Hello, > >I am working on a database that deals with network statistics. I have >a program that generates web reports based on this data every ten >minutes. > >The table layout looks something like this: > >CREATE TABLE traffic >( > unix_secs INT UNSIGNED NOT NULL, > dpkts INT UNSIGNED NOT NULL DEFAULT 0, > doctets INT UNSIGNED NOT NULL DEFAULT 0, > first INT UNSIGNED, > last INT UNSIGNED, > srcaddr VARCHAR(15), > dstaddr VARCHAR(15), > srcport SMALLINT UNSIGNED, > dstport SMALLINT UNSIGNED, > prot TINYINT UNSIGNED NOT NULL DEFAULT 0, > tos TINYINT UNSIGNED NOT NULL DEFAULT 0, > tcp_flags TINYINT UNSIGNED NOT NULL DEFAULT 0, > INDEX unix_secs (unix_secs), > INDEX srcaddr (srcaddr), > INDEX dstaddr (dstaddr), > INDEX srcport (srcport), > INDEX dstport (dstport) >); > >Now, as time progresses the queires are getting slower and slower. >I know this is expected, so I am curious as to how I can have a main >table that has all traffic, so that I can do monthly/yearly reports, >and also have a daily table so that I can quickly do reports every >minute or so on that data. > >I have read up a bit on merge tables (this is probably the answer) but >I am unsure as to how you trigger the changes. ie, how do you do the >rollover after every 24hours? > >Any thoughts, or a pointer in the right direction would be greatly appreciated. > > >Thanks. > >-- >_ >Paul Halliday >http://dp.penix.org > >"Diplomacy is the art of saying "Nice doggie!" till you can find a rock." > >-- >MySQL General Mailing List >For list archives: http://lists.mysql.com/mysql >To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > __ Switch to Netscape Internet Service. As low as $9.95 a month -- Sign up today at http://isp.netscape.com/register Netscape. Just the Net You Need. New! Netscape Toolbar for Internet Explorer Search from anywhere on the Web and block those annoying pop-ups. Download now at http://channels.netscape.com/ns/search/install.jsp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Solution to slow queries
On Tue, 2005-05-10 at 14:56 -0400, Frank Bax wrote: > At 02:22 PM 5/10/05, Paul Halliday wrote: > >Now, as time progresses the queires are getting slower and slower. > >I know this is expected, > > > I don't think so. I thought that if the number of rows returned does not > change and an index is properly used, then query time should not change > significantly as size of database grows. > True, for the appropriate definition of 'significantly'. Also false, for the appropriate definition of 'significantly'. The index's are trees which must be searched, this is fairly fast and the time doesn't grow linearly or anything like that, but the time does of course grow with more rows. So if the number of rows increases greatly then a noticable increase in the time to search the index may occur. John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Solution to slow queries
Don't forget to run an analyze to adjust the statistics for the optimizer/indexes. Also, after any updates (on dynamic tables which yours is) or any deletes run an optimize. Quoting Paul Halliday <[EMAIL PROTECTED]>: Hello, I am working on a database that deals with network statistics. I have a program that generates web reports based on this data every ten minutes. The table layout looks something like this: CREATE TABLE traffic ( unix_secs INT UNSIGNED NOT NULL, dpkts INT UNSIGNED NOT NULL DEFAULT 0, doctets INT UNSIGNED NOT NULL DEFAULT 0, first INT UNSIGNED, last INT UNSIGNED, srcaddr VARCHAR(15), dstaddr VARCHAR(15), srcport SMALLINT UNSIGNED, dstport SMALLINT UNSIGNED, prot TINYINT UNSIGNED NOT NULL DEFAULT 0, tos TINYINT UNSIGNED NOT NULL DEFAULT 0, tcp_flags TINYINT UNSIGNED NOT NULL DEFAULT 0, INDEX unix_secs (unix_secs), INDEX srcaddr (srcaddr), INDEX dstaddr (dstaddr), INDEX srcport (srcport), INDEX dstport (dstport) ); Now, as time progresses the queires are getting slower and slower. I know this is expected, so I am curious as to how I can have a main table that has all traffic, so that I can do monthly/yearly reports, and also have a daily table so that I can quickly do reports every minute or so on that data. I have read up a bit on merge tables (this is probably the answer) but I am unsure as to how you trigger the changes. ie, how do you do the rollover after every 24hours? Any thoughts, or a pointer in the right direction would be greatly appreciated. Thanks. -- _ Paul Halliday http://dp.penix.org "Diplomacy is the art of saying "Nice doggie!" till you can find a rock." -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] James Nobis Web Developer Academic Superstore 223 W. Anderson Ln. Suite A110, Austin, TX 78752 Voice: (512) 450-1199 x453 Fax: (512) 450-0263 http://www.academicsuperstore.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Solution to slow queries
At 02:22 PM 5/10/05, Paul Halliday wrote: Now, as time progresses the queires are getting slower and slower. I know this is expected, I don't think so. I thought that if the number of rows returned does not change and an index is properly used, then query time should not change significantly as size of database grows. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
