MySQL Forums
Forum List  »  Partitioning

Re: Does partitioning make sense for this application?
Posted by: Rick James
Date: October 30, 2012 12:15PM

> the actual queries I was searching 10.21.21.0 to 10.21.30.255.
That will EXCLUDE
10.21.30.26 through 10.21.30.99
because it is doing a STRING comparison:

mysql> SELECT '10.21.21.99' BETWEEN '10.21.21.0' AND '10.21.30.255';
+-------------------------------------------------------+
| '10.21.21.99' BETWEEN '10.21.21.0' AND '10.21.30.255' |
+-------------------------------------------------------+
|                                                     1 |
+-------------------------------------------------------+
mysql> SELECT '10.21.30.25' BETWEEN '10.21.21.0' AND '10.21.30.255';
+-------------------------------------------------------+
| '10.21.30.25' BETWEEN '10.21.21.0' AND '10.21.30.255' |
+-------------------------------------------------------+
|                                                     1 |
+-------------------------------------------------------+
mysql> SELECT '10.21.30.26' BETWEEN '10.21.21.0' AND '10.21.30.255';
+-------------------------------------------------------+
| '10.21.30.26' BETWEEN '10.21.21.0' AND '10.21.30.255' |
+-------------------------------------------------------+
|                                                     0 |
+-------------------------------------------------------+
mysql> SELECT '10.21.30.99' BETWEEN '10.21.21.0' AND '10.21.30.255';
+-------------------------------------------------------+
| '10.21.30.99' BETWEEN '10.21.21.0' AND '10.21.30.255' |
+-------------------------------------------------------+
|                                                     0 |
+-------------------------------------------------------+
mysql> SELECT '10.21.30.100' BETWEEN '10.21.21.0' AND '10.21.30.255';
+--------------------------------------------------------+
| '10.21.30.100' BETWEEN '10.21.21.0' AND '10.21.30.255' |
+--------------------------------------------------------+
|                                                      1 |
+--------------------------------------------------------+
(0 = FALSE, 1 = TRUE)

I am not going to explain the difference between "strings" and "numbers" and "octets" and how comparisons are done. That comes from a beginning programming course.

If you have only IPv4 addresses, then use INET_ATON() and INET_NTOA(). and have saddr and daddr be INT UNSIGNED (only 4 bytes). This will solve the comparison bug.

At 1500 INSERTs per second, you should consider either LOAD DATA, or "batched" INSERTs (INSERT INTO tbl (a,b,c) VALUES (1,2,3), (2,3,4), (55,66,77)). Perhaps one second's worth of data in a single INSERT statement would be optimal.

> usually within the past 24 hours, although occasionally we get asked to look back

Possibly PARTITIONing by day would be good. Then, the typical queries would look at the latest partition or two, and keep that stuff in cache.

How many days will you keep the data? Will you purge old data? (PARTITIONing helps with that.)

(I'm still a bit confused -- is it 90GB/day or is it 9GB/day? See your first posting.)

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Does partitioning make sense for this application?
1325
October 30, 2012 12:15PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.