Re: MySQL daemons restarting every 7 minutes

[Suresh Kuna]

Can yo paste the complete error log, Ram memory size and configuration file
here and make sure the machine has enough memory to run the services.

Check the sys log for what is happening just before the service restart.

On Wed, Sep 7, 2011 at 10:51 PM,  wrote:

> Hi,
>
>  as of yesterday the MySQL Daemons keep restarting every 7 mins or so on
> one of my FreeBSD servers. The only work carried out recently related to
> MySQL on this server was to temporarily disable replication (its a slave) of
> one DB, and then re-enable it (via restore of data and updating the log file
> and pos).
> Now I keep seeing this:
>
> 110907 18:03:58 mysqld_safe mysqld restarted
> 110907 18:03:58 [Note] Plugin 'FEDERATED' is disabled.
> 110907 18:03:58  InnoDB: Initializing buffer pool, size = 2.0G
> 110907 18:03:59  InnoDB: Completed initialization of buffer pool
> 110907 18:03:59  InnoDB: Started; log sequence number 0 44233
> 110907 18:03:59 [Note] Event Scheduler: Loaded 0 events
> 110907 18:03:59 [Note] Slave SQL thread initialized, starting replication
> in log 'mysql-bin.002818' at position 46048, relay log
> './tau-relay-bin.37' position: 251
> 110907 18:03:59 [Note] /usr/local/libexec/mysqld: ready for connections.
> Version: '5.1.58'  socket: '/tmp/mysql.sock'  port: 3306  FreeBSD port:
> mysql-server-5.1.58
> 110907 18:03:59 [Note] Slave I/O thread: connected to master 
> 'slave@kappa:3306',replication
> started in log 'mysql-bin.002818' at position 46048
>
> Not seeing any other info, such as why the daemons stopped, just this
> repeated over and over...
> I didn't find much useful info searching on the internet, came up with
> this:
>
> http://bugs.mysql.com/bug.php?**id=26895
>
> but its an unresolved bug.
>
> I have tried, restoring all DBs from a working server and that didn't work.
> So wouldnt seem to be related to the contents of the databases.
>
> System is FreeBSD 8.2 amd64, MySQL 5.1.58 (I upgrade this from 5.1.51 as a
> try and fix it quick approach but no joy).
>
> Any one chip in from experience what this may be?? I'm considering
> upgrading to 5.5 to give that a try...
>
> thanks in advance,
>
> Andy.
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?**
> unsub=sureshkumar...@gmail.com
>
>


-- 
Thanks
Suresh Kuna
MySQL DBA

Re: strange mysql update ..

[Suresh Kuna]

Nice Rik!

On Thu, Sep 8, 2011 at 3:19 PM, Rik Wasmus  wrote:

> > I fired the update statement in a wrong way ..like this ..
> >
> > update user_info set login_date='2011-08-05 04:15:05' and user_id
> =16078845
> > limit 1 ;
> > ( I forgot to use where . instead of where I used and )
> > update user_info set login_date='2011-08-05 04:15:05' where user_id
> > =16078845 limit 1 ; ( this is the query intended )
> >
> > after the update ..I got this message ..
> > mysql> update user_info set login_date='2011-08-05 04:15:05' and user_id
> > =16078845 limit 1;
> > Query OK, 1 row affected, 1 warning (0.02 sec)
> > Rows matched: 1  Changed: 1  Warnings: 0
> >
> > It shows that one record is affected and one row changed ..
> > I did show warnings ..the output is like this ..
> >
> > | Warning | 1292 | Truncated incorrect DOUBLE value: '2011-08-05
> 04:15:05'
>
> > So my question is what happened exactly ?
> > Why no records updated ?
>
> A lot of casting:
>
> (1) login_date='2011-08-05 04:15:05' and user_id =16078845;
>
> And implies boolean, so the result is the either true or false. MySQL
> doesn't
> like using non-numbers as booleans (the '2011-08-05 04:15:05') , this is
> the
> double spoken of.
>
> (2) login_date = false (or true, but that doesn't matter)
>
> But MySQL doesn't know booleans, to a number it is:
>
> (3) login_date = 0
>
> But the column is a DATETIME (or TIMESTAMP) column, 0 or 1 is an incorrect
> value, cast to:
>
> (4) login_date = -00-00 00:00:00
>
> So, somewhere there's (or was, may be overwritten) a record with that
> value,
> just 1 due to the limit 1, otherwise, the whole table would have that as a
> login_date (doesn't matter wether it was true or false).
>
>
> Check out:
> DB 5.1.58-1-log:(none)  mysql> SELECT 1 AND 1;
> +-+
> | 1 AND 1 |
> +-+
> |   1 |
> +-+
> 1 row in set (0.00 sec)
>
> DB 5.1.58-1-log:(none)  mysql> SELECT 0 AND 1;
> +-+
> | 0 AND 1 |
> +-+
> |   0 |
> +-+
> 1 row in set (0.01 sec)
>
> DB 5.1.58-1-log:(none)  mysql> SELECT '1' AND 1;
> +---+
> | '1' AND 1 |
> +---+
> | 1 |
> +---+
> 1 row in set (0.03 sec)
>
> DB 5.1.58-1-log:(none)  mysql> SELECT 'a' AND 1;
> +---+
> | 'a' AND 1 |
> +---+
> | 0 |
> +---+
> 1 row in set, 1 warning (0.03 sec)
>
> DB 5.1.58-1-log:(none)  mysql> SHOW WARNINGS;
> +-+--+---+
> | Level   | Code | Message   |
> +-+--+---+
> | Warning | 1292 | Truncated incorrect DOUBLE value: 'a' |
> +-+--+---+
> 1 row in set (0.01 sec)
> --
> Rik Wasmus
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com
>
>


-- 
Thanks
Suresh Kuna
MySQL DBA

Re: trying to change wait_timeout

[Suresh Kuna]

Set the variable wait_timeout=xxx value under the mysqld section of the
configuration file and restart the mysqld server.

Now check show global variables like 'wait_timeout";  It should be you xxx
value what ever you set.

On Thu, Sep 8, 2011 at 7:25 PM, Andrew Moore  wrote:

> Check that you're looking at the variable in the GLOBAL scope not the
> SESSION scope.
>
> SHOW GLOBAL VARIABLE ...
>
> Andy
>
> On Thu, Sep 8, 2011 at 11:34 AM, Bruce Ferrell  >wrote:
>
> > On 09/08/2011 02:56 AM, Johan De Meersman wrote:
> >
> >> - Original Message -
> >>
> >>> From: "Bruce Ferrell"
> >>> To: mysql@lists.mysql.com
> >>> Sent: Thursday, 8 September, 2011 3:10:16 AM
> >>> Subject: trying to change wait_timeout
> >>>
> >>> I've read the documentation on MySQL for version 5.1 and it says all
> >>> I have to do is to place the following:
> >>> wait_timeout=xxx
> >>> under [mysqld]
> >>>
> >> That, and restart the service, of course. You *did* think of restarting
> >> the service, I trust? :-p
> >>
> >> That being said, it is also a dynamic variable, so if you didn't
> restart,
> >> prefer not to restart *and* are certain your config file is correct; you
> can
> >> also do "set global wait_timeout=xxx" to have it take effect immediately
> for
> >> all new sessions. Yes, that means you'll have to disconnect/reconnect to
> see
> >> the change in your own session.
> >>
> >
> > Good question to ask.  Yes, I did restart mysql.  Both before and after
> > show variables like 'wait_time%' returns 28800.  Most confusing.
> >
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/mysql?**
> > unsub=eroomy...@gmail.com<
> http://lists.mysql.com/mysql?unsub=eroomy...@gmail.com>
> >
> >
>



-- 
Thanks
Suresh Kuna
MySQL DBA

Re: Is it it posiible to combine the C++ algorithm code, the MYSQL source code and the UDF code into a single DLL/SO?

[Frank Chang]

Good evening, Apparently MySQL supports a single client connection to a
local MySQL server
Quoting from the MySQL :: MySQL 5.0 Reference Manual :: 4.2.2
Connecting to the MySQL Server URL by Booz-Allen New York City consultants

The following table shows the permissible --protocol option values on
different operating systems.
 TCP TCP/IP connection to local or remote server All OS
SOCKET Unix socket file connection to local server Unix only
PIPE Named-pipe connection to local or remote server Windows only
MEMORY Shared-memory connection to local server Windows only

On Thu, Sep 8, 2011 at 12:46 PM, Frank Chang  wrote:

> Good afternoon, We developed a C++ class algorithm and code together with
> sqlite3.c and the sqlite C/C++ UDFs. Everything is combined into a single
> Windows DLL/UNIX-LINUX SO
>
> Now we would like to change from SQLite to MySQL to take advantage of
> MySQL's ability to do parallel writes on separate threads.
>
> We would still like to combine our c++ algorithm class code, MySQL.c (I am
> not sure of the exact name) and the MySQL C/C++ UDFS into a single Windows
> DLL/ Unix-Linux SO.
>
> If so, How can this be done? Thank you,
>

Re: Query Optimization

[Brandon Phelps]

Ah I see.  Well thanks for your assistance!

-Brandon

On 09/08/2011 05:21 PM, Mihail Manolov wrote:

 From the manual: "The default behavior for UNION is that duplicate rows are removed 
from the result."

On Sep 8, 2011, at 4:50 PM, Brandon Phelps wrote:


Mihail,

Thanks so much!  I modified your example to include the proper ORDER BY and 
LIMIT clauses and this, so far, is running super fast (0.0007 seconds).  
Question, if a record's open_dt is between the range AND the close_dt is 
between the range as well, will the UNION output the record twice?  If so, is 
there any way to prevent that?

(SELECT
sc.open_dt,
sc.close_dt,
sc.protocol,
INET_NTOA(sc.src_address) AS src_address,
sc.src_port,
INET_NTOA(sc.dst_address) AS dst_address,
sc.dst_port,
sc.sent,
sc.rcvd,
spm.desc AS src_port_desc,
dpm.desc AS dst_port_desc
FROM sonicwall_connections AS sc
LEFT JOIN port_mappings AS spm ON spm.port = sc.src_port
LEFT JOIN port_mappings AS dpm ON dpm.port = sc.dst_port
WHERE open_dt BETWEEN '2011-09-07 13:18:58' AND '2011-09-08 13:18:58')
UNION
(SELECT
sc.open_dt,
sc.close_dt,
sc.protocol,
INET_NTOA(sc.src_address) AS src_address,
sc.src_port,
INET_NTOA(sc.dst_address) AS dst_address,
sc.dst_port,
sc.sent,
sc.rcvd,
spm.desc AS src_port_desc,
dpm.desc AS dst_port_desc
FROM sonicwall_connections AS sc
LEFT JOIN port_mappings AS spm ON spm.port = sc.src_port
LEFT JOIN port_mappings AS dpm ON dpm.port = sc.dst_port
WHERE close_dt BETWEEN '2011-09-07 13:18:58' AND '2011-09-08 13:18:58')
ORDER BY rcvd DESC LIMIT 10;

++--++++--+-++--++
| id | select_type  | table  | type   | possible_keys   
   | key  | key_len | ref| rows | Extra  |
++--++++--+-++--++
|  1 | PRIMARY  | sc | range  | 
open_dt,ndx_open_close_rcvd,ndx_open_close | open_dt  | 8   | NULL  
 | 1057 | Using where|
|  1 | PRIMARY  | spm| eq_ref | PRIMARY 
   | PRIMARY  | 2   | syslog.sc.src_port |1 ||
|  1 | PRIMARY  | dpm| eq_ref | PRIMARY 
   | PRIMARY  | 2   | syslog.sc.dst_port |1 ||
|  2 | UNION| sc | range  | ndx_close_dt
   | ndx_close_dt | 8   | NULL   | 1131 | Using where|
|  2 | UNION| spm| eq_ref | PRIMARY 
   | PRIMARY  | 2   | syslog.sc.src_port |1 ||
|  2 | UNION| dpm| eq_ref | PRIMARY 
   | PRIMARY  | 2   | syslog.sc.dst_port |1 ||
| NULL | UNION RESULT |  | ALL| NULL  
 | NULL | NULL| NULL   | NULL | Using filesort |
++--++++--+-++--++



On 09/08/2011 03:45 PM, Mihail Manolov wrote:

How about:

SELECT
sc.open_dt,
sc.close_dt,
sc.protocol,
INET_NTOA(sc.src_address) AS src_address,
sc.src_port,
INET_NTOA(sc.dst_address) AS dst_address,
sc.dst_port,
sc.sent,
sc.rcvd,
spm.desc AS src_port_desc,
dpm.desc AS dst_port_desc
FROM sonicwall_connections AS sc
LEFT JOIN port_mappings AS spm ON spm.port = sc.src_port
LEFT JOIN port_mappings AS dpm ON dpm.port = sc.dst_port
WHERE open_dt BETWEEN '2011-09-07 13:18:58' AND '2011-09-08 13:18:58'

UNION

SELECT
sc.open_dt,
sc.close_dt,
sc.protocol,
INET_NTOA(sc.src_address) AS src_address,
sc.src_port,
INET_NTOA(sc.dst_address) AS dst_address,
sc.dst_port,
sc.sent,
sc.rcvd,
spm.desc AS src_port_desc,
dpm.desc AS dst_port_desc
FROM sonicwall_connections AS sc
LEFT JOIN port_mappings AS spm ON spm.port = sc.src_port
LEFT JOIN port_mappings AS dpm ON dpm.port = sc.dst_port
WHERE close_dt BETWEEN '2011-09-07 13:18:58' AND '2011-09-08 13:18:58'



On Sep 8, 2011, at 3:25 PM, Brandon Phelps wrote:


Thanks for the idea Derek, however given the following query my EXPLAIN output 
is identical:

SELECT
sc.open_dt,
sc.close_dt,
sc.protocol,
INET_NTOA(sc.src_address) AS src_address,
sc.src_port,
INET_NTOA(sc.dst_address) AS dst_address,

Re: Query Optimization

[Mihail Manolov]

>From the manual: "The default behavior for UNION is that duplicate rows are 
>removed from the result."

On Sep 8, 2011, at 4:50 PM, Brandon Phelps wrote:

> Mihail,
> 
> Thanks so much!  I modified your example to include the proper ORDER BY and 
> LIMIT clauses and this, so far, is running super fast (0.0007 seconds).  
> Question, if a record's open_dt is between the range AND the close_dt is 
> between the range as well, will the UNION output the record twice?  If so, is 
> there any way to prevent that?
> 
> (SELECT
>   sc.open_dt,
>   sc.close_dt,
>   sc.protocol,
>   INET_NTOA(sc.src_address) AS src_address,
>   sc.src_port,
>   INET_NTOA(sc.dst_address) AS dst_address,
>   sc.dst_port,
>   sc.sent,
>   sc.rcvd,
>   spm.desc AS src_port_desc,
>   dpm.desc AS dst_port_desc
> FROM sonicwall_connections AS sc
>   LEFT JOIN port_mappings AS spm ON spm.port = sc.src_port
>   LEFT JOIN port_mappings AS dpm ON dpm.port = sc.dst_port
> WHERE open_dt BETWEEN '2011-09-07 13:18:58' AND '2011-09-08 13:18:58')
> UNION
> (SELECT
>   sc.open_dt,
>   sc.close_dt,
>   sc.protocol,
>   INET_NTOA(sc.src_address) AS src_address,
>   sc.src_port,
>   INET_NTOA(sc.dst_address) AS dst_address,
>   sc.dst_port,
>   sc.sent,
>   sc.rcvd,
>   spm.desc AS src_port_desc,
>   dpm.desc AS dst_port_desc
> FROM sonicwall_connections AS sc
>   LEFT JOIN port_mappings AS spm ON spm.port = sc.src_port
>   LEFT JOIN port_mappings AS dpm ON dpm.port = sc.dst_port
> WHERE close_dt BETWEEN '2011-09-07 13:18:58' AND '2011-09-08 13:18:58')
> ORDER BY rcvd DESC LIMIT 10;
> 
> ++--++++--+-++--++
> | id | select_type  | table  | type   | possible_keys 
>  | key  | key_len | ref| rows | Extra 
>  |
> ++--++++--+-++--++
> |  1 | PRIMARY  | sc | range  | 
> open_dt,ndx_open_close_rcvd,ndx_open_close | open_dt  | 8   | NULL
>| 1057 | Using where|
> |  1 | PRIMARY  | spm| eq_ref | PRIMARY   
>  | PRIMARY  | 2   | syslog.sc.src_port |1 |   
>  |
> |  1 | PRIMARY  | dpm| eq_ref | PRIMARY   
>  | PRIMARY  | 2   | syslog.sc.dst_port |1 |   
>  |
> |  2 | UNION| sc | range  | ndx_close_dt  
>  | ndx_close_dt | 8   | NULL   | 1131 | Using where   
>  |
> |  2 | UNION| spm| eq_ref | PRIMARY   
>  | PRIMARY  | 2   | syslog.sc.src_port |1 |   
>  |
> |  2 | UNION| dpm| eq_ref | PRIMARY   
>  | PRIMARY  | 2   | syslog.sc.dst_port |1 |   
>  |
> | NULL | UNION RESULT |  | ALL| NULL
>| NULL | NULL| NULL   | NULL | Using 
> filesort |
> ++--++++--+-++--++
> 
> 
> 
> On 09/08/2011 03:45 PM, Mihail Manolov wrote:
>> How about:
>> 
>> SELECT
>>  sc.open_dt,
>>  sc.close_dt,
>>  sc.protocol,
>>  INET_NTOA(sc.src_address) AS src_address,
>>  sc.src_port,
>>  INET_NTOA(sc.dst_address) AS dst_address,
>>  sc.dst_port,
>>  sc.sent,
>>  sc.rcvd,
>>  spm.desc AS src_port_desc,
>>  dpm.desc AS dst_port_desc
>> FROM sonicwall_connections AS sc
>>  LEFT JOIN port_mappings AS spm ON spm.port = sc.src_port
>>  LEFT JOIN port_mappings AS dpm ON dpm.port = sc.dst_port
>> WHERE open_dt BETWEEN '2011-09-07 13:18:58' AND '2011-09-08 13:18:58'
>> 
>> UNION
>> 
>> SELECT
>>  sc.open_dt,
>>  sc.close_dt,
>>  sc.protocol,
>>  INET_NTOA(sc.src_address) AS src_address,
>>  sc.src_port,
>>  INET_NTOA(sc.dst_address) AS dst_address,
>>  sc.dst_port,
>>  sc.sent,
>>  sc.rcvd,
>>  spm.desc AS src_port_desc,
>>  dpm.desc AS dst_port_desc
>> FROM sonicwall_connections AS sc
>>  LEFT JOIN port_mappings AS spm ON spm.port = sc.src_port
>>  LEFT JOIN port_mappings AS dpm ON dpm.port = sc.dst_port
>> WHERE close_dt BETWEEN '2011-09-07 13:18:58' AND '2011-09-08 13:18:58'
>> 
>> 
>> 
>> On Sep 8, 2011, at 3:25 PM, Brandon Phelps wrote:
>> 
>>> Thanks for the idea Derek, however given the following query my EXPLAIN 
>>> output is identical:
>>> 
>>> SELECT
>>> sc.open_dt,
>>> sc.close_dt,
>>> sc.protocol,
>>> INET_NTOA(sc.src_address) AS src_address,
>>> sc.sr

Re: Query Optimization

[Brandon Phelps]

Mihail,

Thanks so much!  I modified your example to include the proper ORDER BY and 
LIMIT clauses and this, so far, is running super fast (0.0007 seconds).  
Question, if a record's open_dt is between the range AND the close_dt is 
between the range as well, will the UNION output the record twice?  If so, is 
there any way to prevent that?

(SELECT
sc.open_dt,
sc.close_dt,
sc.protocol,
INET_NTOA(sc.src_address) AS src_address,
sc.src_port,
INET_NTOA(sc.dst_address) AS dst_address,
sc.dst_port,
sc.sent,
sc.rcvd,
spm.desc AS src_port_desc,
dpm.desc AS dst_port_desc
FROM sonicwall_connections AS sc
LEFT JOIN port_mappings AS spm ON spm.port = sc.src_port
LEFT JOIN port_mappings AS dpm ON dpm.port = sc.dst_port
WHERE open_dt BETWEEN '2011-09-07 13:18:58' AND '2011-09-08 13:18:58')
UNION
(SELECT
sc.open_dt,
sc.close_dt,
sc.protocol,
INET_NTOA(sc.src_address) AS src_address,
sc.src_port,
INET_NTOA(sc.dst_address) AS dst_address,
sc.dst_port,
sc.sent,
sc.rcvd,
spm.desc AS src_port_desc,
dpm.desc AS dst_port_desc
FROM sonicwall_connections AS sc
LEFT JOIN port_mappings AS spm ON spm.port = sc.src_port
LEFT JOIN port_mappings AS dpm ON dpm.port = sc.dst_port
WHERE close_dt BETWEEN '2011-09-07 13:18:58' AND '2011-09-08 13:18:58')
ORDER BY rcvd DESC LIMIT 10;

++--++++--+-++--++
| id | select_type  | table  | type   | possible_keys   
   | key  | key_len | ref| rows | Extra  |
++--++++--+-++--++
|  1 | PRIMARY  | sc | range  | 
open_dt,ndx_open_close_rcvd,ndx_open_close | open_dt  | 8   | NULL  
 | 1057 | Using where|
|  1 | PRIMARY  | spm| eq_ref | PRIMARY 
   | PRIMARY  | 2   | syslog.sc.src_port |1 ||
|  1 | PRIMARY  | dpm| eq_ref | PRIMARY 
   | PRIMARY  | 2   | syslog.sc.dst_port |1 ||
|  2 | UNION| sc | range  | ndx_close_dt
   | ndx_close_dt | 8   | NULL   | 1131 | Using where|
|  2 | UNION| spm| eq_ref | PRIMARY 
   | PRIMARY  | 2   | syslog.sc.src_port |1 ||
|  2 | UNION| dpm| eq_ref | PRIMARY 
   | PRIMARY  | 2   | syslog.sc.dst_port |1 ||
| NULL | UNION RESULT |  | ALL| NULL  
 | NULL | NULL| NULL   | NULL | Using filesort |
++--++++--+-++--++



On 09/08/2011 03:45 PM, Mihail Manolov wrote:

How about:

SELECT
sc.open_dt,
sc.close_dt,
sc.protocol,
INET_NTOA(sc.src_address) AS src_address,
sc.src_port,
INET_NTOA(sc.dst_address) AS dst_address,
sc.dst_port,
sc.sent,
sc.rcvd,
spm.desc AS src_port_desc,
dpm.desc AS dst_port_desc
FROM sonicwall_connections AS sc
LEFT JOIN port_mappings AS spm ON spm.port = sc.src_port
LEFT JOIN port_mappings AS dpm ON dpm.port = sc.dst_port
WHERE open_dt BETWEEN '2011-09-07 13:18:58' AND '2011-09-08 13:18:58'

UNION

SELECT
sc.open_dt,
sc.close_dt,
sc.protocol,
INET_NTOA(sc.src_address) AS src_address,
sc.src_port,
INET_NTOA(sc.dst_address) AS dst_address,
sc.dst_port,
sc.sent,
sc.rcvd,
spm.desc AS src_port_desc,
dpm.desc AS dst_port_desc
FROM sonicwall_connections AS sc
LEFT JOIN port_mappings AS spm ON spm.port = sc.src_port
LEFT JOIN port_mappings AS dpm ON dpm.port = sc.dst_port
WHERE close_dt BETWEEN '2011-09-07 13:18:58' AND '2011-09-08 13:18:58'



On Sep 8, 2011, at 3:25 PM, Brandon Phelps wrote:


Thanks for the idea Derek, however given the following query my EXPLAIN output 
is identical:

SELECT
sc.open_dt,
sc.close_dt,
sc.protocol,
INET_NTOA(sc.src_address) AS src_address,
sc.src_port,
INET_NTOA(sc.dst_address) AS dst_address,
sc.dst_port,
sc.sent,
sc.rcvd,
spm.desc AS src_port_desc,
dpm.desc AS dst_port_desc
FROM sonicwall_connections AS sc
LEFT JOIN port_mappings AS spm ON spm.port = sc.src_port
LEFT JOIN port_mappings AS dpm ON

Re: Query Optimization

[Brandon Phelps]

Andrew,

Generally there is only 1 user performing the complicated SELECT query at a time, however 
the background process that fills the table is constantly doing a fast SELECT (0.3 
seconds) and a subsequent UPDATE.  Basically whenever a connection is closed on the 
firewall, the bg process SELECTS from the table the last record that was opened (and 
hasn't already been closed) for a given source IP, source port, dest ip, and protocol.  
So for example whenever the firewall logs a "CONNECTION CLOSED" message, the bg 
process does:

select id from sonicwall_connections where src_address = w.x.y.z and src_port = 
x and dst_address = w.x.y.z and protocol = x ORDER BY open_dt LIMIT 0, 1

then it UPDATES the close_dt column for the record with the selected ID.  These 
select/update statements being run by the background process generally take 
0.000x seconds each.

The only slowdown we see is with the complicated SELECT I have below, and this 
is only executed once in a while throughout the day.  For example, if an IT 
manager sees a huge bandwidth spike on the network, he may access the web gui 
to determine what the source IP is so he can determine who is downloading a 
large file, etc.

I think what I will try to do is create 60 partitions for the table in question 
based on month and day.  This way each day will go into it's own partition and 
if someone runs the query for a date range such as 01/01/2011 - 01/07/2011 it 
will only need to access 7 partitions instead of the entire table.

My only question with this is how would I go about creating the table with 60 
partitions in such a way that I won't need to constantly add/remove new/old 
partitions every day?  Is there any partitioning function I can use that would 
not require me to update the partitions schema every day?  I already plan to 
have a cron run each night to purge records older than 60 days from the 
database.

On 09/08/2011 03:26 PM, Andrew Moore wrote:

Partitioning isn't a bad idea for this however I'm still thinking about your
dataset size and possible hardware limitations. It's not likely going to fit
into relevant buffers/memory so you're going to be on disk more then you
want. You're probably creating temporary tables like crazy and I would bet
that there are a good load of them heading to disk too. With your IO
performance limited to a small amount of disks as you describe, you're not
going to be able to get much more from these queries. Although a dedicated
DB server are there other databases been accessed on the server? When
looking at the scope of your data, are you capturing more then you need? How
often and how far back are the users querying? How many users concurrently
performing queries on the 32m record table?

On Thu, Sep 8, 2011 at 8:04 PM, Brandon Phelps  wrote:


Mihail,

I have considered this but have not yet determined how best to go about
partitioning the table.  I don't think partitioning by dst_address or
src_address would help because most of the queries do not filter on IP
address (except very specific queries where the end-user is searching the
table for history on a particular employee).

I could potentially partition the table based on the day of week the
connection was opened on which may improve performance for a while since
this would take me from a single 32million record table down to roughly 4.5
million records per partition (32 / 7) however we are looking to eventually
store up to 2 months worth of data in the table, and so far that 32 million
records is only for 1 month, so I estimate another 32 million-ish before the
month is out, bringing me to roughly 70 million records total (it would be
nice if I could store even more than 2 months, but given my currently
performance dilemma I don't expect that to happen).  Also this does not take
into account that the end-user will often be pulling data for multiple days
at a time, meaning that multiple partitions in this scheme will need to be
accessed anyway.

The only other logical partitioning scheme I can think of would be to
partition based on dst_port (the port the log relates to) but the majority
of records are all to port 80 (standard web traffic) so I don't think this
would be all that helpful.

I have never messed with partitioning so it is totally possible that I am
not thinking of something, so if you have any ideas on a decent partitioning
scheme based on my criteria and queries below, please let me know.

Thanks,
Brandon


On 09/08/2011 02:47 PM, Mihail Manolov wrote:


If you're running version 5.1+ you may wanna take a look at table
partitioning options you may have.

On Sep 8, 2011, at 2:27 PM, Brandon Phelps wrote:

  Thanks for the reply Andy.  Unfortunately the users will be selecting

varying date ranges and new data is constantly coming in, so I am not sure
how I could archive/cache the necessary data that would be any more
efficient than simply using the database directly.


On 09/08/2011 02:16 PM, Andrew Moore wrote:


Thinking ou

Re: Query Optimization

[Mihail Manolov]

How about:

SELECT
sc.open_dt,
sc.close_dt,
sc.protocol,
INET_NTOA(sc.src_address) AS src_address,
sc.src_port,
INET_NTOA(sc.dst_address) AS dst_address,
sc.dst_port,
sc.sent,
sc.rcvd,
spm.desc AS src_port_desc,
dpm.desc AS dst_port_desc
FROM sonicwall_connections AS sc
LEFT JOIN port_mappings AS spm ON spm.port = sc.src_port
LEFT JOIN port_mappings AS dpm ON dpm.port = sc.dst_port
WHERE open_dt BETWEEN '2011-09-07 13:18:58' AND '2011-09-08 13:18:58'

UNION

SELECT
sc.open_dt,
sc.close_dt,
sc.protocol,
INET_NTOA(sc.src_address) AS src_address,
sc.src_port,
INET_NTOA(sc.dst_address) AS dst_address,
sc.dst_port,
sc.sent,
sc.rcvd,
spm.desc AS src_port_desc,
dpm.desc AS dst_port_desc
FROM sonicwall_connections AS sc
LEFT JOIN port_mappings AS spm ON spm.port = sc.src_port
LEFT JOIN port_mappings AS dpm ON dpm.port = sc.dst_port
WHERE close_dt BETWEEN '2011-09-07 13:18:58' AND '2011-09-08 13:18:58'



On Sep 8, 2011, at 3:25 PM, Brandon Phelps wrote:

> Thanks for the idea Derek, however given the following query my EXPLAIN 
> output is identical:
> 
> SELECT
>   sc.open_dt,
>   sc.close_dt,
>   sc.protocol,
>   INET_NTOA(sc.src_address) AS src_address,
>   sc.src_port,
>   INET_NTOA(sc.dst_address) AS dst_address,
>   sc.dst_port,
>   sc.sent,
>   sc.rcvd,
>   spm.desc AS src_port_desc,
>   dpm.desc AS dst_port_desc
> FROM sonicwall_connections AS sc
>   LEFT JOIN port_mappings AS spm ON spm.port = sc.src_port
>   LEFT JOIN port_mappings AS dpm ON dpm.port = sc.dst_port
> WHERE ('2011-09-07 13:18:58' <= open_dt <= '2011-09-08 13:18:58') OR 
> ('2011-09-07 13:18:58' <= close_dt <= '2011-09-08 13:18:58');
> 
> 
> ++-+---++---+-+-++--+-+
> | id | select_type | table | type   | possible_keys | key | key_len | ref 
>| rows | Extra   |
> ++-+---++---+-+-++--+-+
> |  1 | SIMPLE  | sc| ALL| NULL  | NULL| NULL| 
> NULL   | 32393330 | Using where |
> |  1 | SIMPLE  | spm   | eq_ref | PRIMARY   | PRIMARY | 2   | 
> syslog.sc.src_port |1 | |
> |  1 | SIMPLE  | dpm   | eq_ref | PRIMARY   | PRIMARY | 2   | 
> syslog.sc.dst_port |1 | |
> ++-+---++---+-+-++--+-+
> 
> I did create indexes on open_dt and close_dt (2 separate indexes).
> 
> 
> 
> On 09/08/2011 02:55 PM, Derek Downey wrote:
>> Correct me if I'm wrong. You're wanting to get all records that have an 
>> open_date or a close_date between two times.
>> 
>> If that's correct, you might be able to get an index_merge by doing a query 
>> like:
>> 
>> WHERE ((starting time)<=open_dt<= (ending time)) OR ((starting 
>> time)<=close_dt<=(ending time))
>> 
>> and creating two indexes (one on 'open_dt' and the other on 'close_dt')
>> 
>> http://dev.mysql.com/doc/refman/5.1/en/index-merge-optimization.html
>> 
>> Regards,
>> Derek
>> 
>> On Sep 8, 2011, at 2:50 PM, Brandon Phelps wrote:
>> 
>>> Andy,
>>> 
>>> The queries take minutes to run.  MySQL is 5.1.54 and it's running on 
>>> Ubuntu server 11.04.  Unfortunately the machine only has 2GB of RAM but no 
>>> other major daemons are running on the machine.  We are running RAID 1 
>>> (mirroring) with 1TB drives.  The tables in question here are all MyISAM.  
>>> When running with the LIMIT 10 my EXPLAIN is:
>>> 
>>> ++-+---++-+-+-++--+-+
>>> | id | select_type | table | type   | possible_keys   | key 
>>> | key_len | ref| rows | Extra   
>>> |
>>> ++-+---++-+-+-++--+-+
>>> |  1 | SIMPLE  | sc| range  | ndx_open_close_rcvd | 
>>> ndx_open_close_rcvd | 8   | NULL   | 32393316 | Using 
>>> where; Using filesort |
>>> |  1 | SIMPLE  | spm   | eq_ref | PRIMARY | PRIMARY 
>>> | 2   | syslog.sc.src_port |1 | 
>>> |
>>> |  1 | SIMPLE  | dpm   | eq_ref | PRIMARY | PRIMARY 
>>> | 2   | syslog.sc.dst_port |1 | 
>>> |
>>> ++-+---++-+-+-++--+-+
>>> 
>>> When I remove

Is it it posiible to combine the C++ algorithm code, the MYSQL source code and the UDF code into a single DLL/SO?

[Frank Chang]

Good afternoon, We developed a C++ class algorithm and code together with
sqlite3.c and the sqlite C/C++ UDFs. Everything is combined into a single
Windows DLL/UNIX-LINUX SO

Now we would like to change from SQLite to MySQL to take advantage of
MySQL's ability to do parallel writes on separate threads.

We would still like to combine our c++ algorithm class code, MySQL.c (I am
not sure of the exact name) and the MySQL C/C++ UDFS into a single Windows
DLL/ Unix-Linux SO.

If so, How can this be done? Thank you,

Re: Query Optimization

[Andrew Moore]

Partitioning isn't a bad idea for this however I'm still thinking about your
dataset size and possible hardware limitations. It's not likely going to fit
into relevant buffers/memory so you're going to be on disk more then you
want. You're probably creating temporary tables like crazy and I would bet
that there are a good load of them heading to disk too. With your IO
performance limited to a small amount of disks as you describe, you're not
going to be able to get much more from these queries. Although a dedicated
DB server are there other databases been accessed on the server? When
looking at the scope of your data, are you capturing more then you need? How
often and how far back are the users querying? How many users concurrently
performing queries on the 32m record table?

On Thu, Sep 8, 2011 at 8:04 PM, Brandon Phelps  wrote:

> Mihail,
>
> I have considered this but have not yet determined how best to go about
> partitioning the table.  I don't think partitioning by dst_address or
> src_address would help because most of the queries do not filter on IP
> address (except very specific queries where the end-user is searching the
> table for history on a particular employee).
>
> I could potentially partition the table based on the day of week the
> connection was opened on which may improve performance for a while since
> this would take me from a single 32million record table down to roughly 4.5
> million records per partition (32 / 7) however we are looking to eventually
> store up to 2 months worth of data in the table, and so far that 32 million
> records is only for 1 month, so I estimate another 32 million-ish before the
> month is out, bringing me to roughly 70 million records total (it would be
> nice if I could store even more than 2 months, but given my currently
> performance dilemma I don't expect that to happen).  Also this does not take
> into account that the end-user will often be pulling data for multiple days
> at a time, meaning that multiple partitions in this scheme will need to be
> accessed anyway.
>
> The only other logical partitioning scheme I can think of would be to
> partition based on dst_port (the port the log relates to) but the majority
> of records are all to port 80 (standard web traffic) so I don't think this
> would be all that helpful.
>
> I have never messed with partitioning so it is totally possible that I am
> not thinking of something, so if you have any ideas on a decent partitioning
> scheme based on my criteria and queries below, please let me know.
>
> Thanks,
> Brandon
>
>
> On 09/08/2011 02:47 PM, Mihail Manolov wrote:
>
>> If you're running version 5.1+ you may wanna take a look at table
>> partitioning options you may have.
>>
>> On Sep 8, 2011, at 2:27 PM, Brandon Phelps wrote:
>>
>>  Thanks for the reply Andy.  Unfortunately the users will be selecting
>>> varying date ranges and new data is constantly coming in, so I am not sure
>>> how I could archive/cache the necessary data that would be any more
>>> efficient than simply using the database directly.
>>>
>>>
>>> On 09/08/2011 02:16 PM, Andrew Moore wrote:
>>>
 Thinking outside the query, is there any archiving that could happen to
 make
 your large tables kinder in the range scan?

 Andy

 On Thu, Sep 8, 2011 at 7:03 PM, Brandon Phelps
 wrote:

  On 09/01/2011 01:32 PM, Brandon Phelps wrote:
>
>  On 09/01/2011 12:47 PM, Shawn Green (MySQL) wrote:
>>
>>  On 9/1/2011 09:42, Brandon Phelps wrote:
>>>
>>>  On 09/01/2011 04:59 AM, Jochem van Dieten wrote:

> ...
>>
>
  WHERE
>> (open_dt>= '2011-08-30 00:00:00' OR close_dt>= '2011-08-30
>>
> 00:00:00')

> AND (open_dt<= '2011-08-30 12:36:53' OR close_dt<= '2011-08-30
>>
> 12:36:53')

  In that case your logic here simplifies to:
> WHERE
> open_dt>= '2011-08-30 00:00:00'
> AND
> close_dt<= '2011-08-30 12:36:53'
>

  Now add an index over open_dt and close_dt and see what happens.
>

  Jochem
>

 Jochem,

 I can't really use your WHERE logic because I also need to retrieve
 results where the open_dt time is out of the range specified. For
 example, a very large file download might span multiple days so
 given
 your logic if the connection was started 2 days ago and I want to
 pull 1
 days worth of connections, I would miss that entry. Basically I want
 to
 SELECT all of the records that were opened OR closed during the
 specified time period, ie. if any activity happened between my start
 and
 end dates, I need to see that record.

 Any other ideas?


  I believe Jochem was on the right track but he got his dates
>>> reversed.
>>>

Re: Query Optimization

[Brandon Phelps]

Thanks for the idea Derek, however given the following query my EXPLAIN output 
is identical:

SELECT
sc.open_dt,
sc.close_dt,
sc.protocol,
INET_NTOA(sc.src_address) AS src_address,
sc.src_port,
INET_NTOA(sc.dst_address) AS dst_address,
sc.dst_port,
sc.sent,
sc.rcvd,
spm.desc AS src_port_desc,
dpm.desc AS dst_port_desc
FROM sonicwall_connections AS sc
LEFT JOIN port_mappings AS spm ON spm.port = sc.src_port
LEFT JOIN port_mappings AS dpm ON dpm.port = sc.dst_port
WHERE ('2011-09-07 13:18:58' <= open_dt <= '2011-09-08 13:18:58') OR ('2011-09-07 
13:18:58' <= close_dt <= '2011-09-08 13:18:58');


++-+---++---+-+-++--+-+
| id | select_type | table | type   | possible_keys | key | key_len | ref   
 | rows | Extra   |
++-+---++---+-+-++--+-+
|  1 | SIMPLE  | sc| ALL| NULL  | NULL| NULL| NULL  
 | 32393330 | Using where |
|  1 | SIMPLE  | spm   | eq_ref | PRIMARY   | PRIMARY | 2   | 
syslog.sc.src_port |1 | |
|  1 | SIMPLE  | dpm   | eq_ref | PRIMARY   | PRIMARY | 2   | 
syslog.sc.dst_port |1 | |
++-+---++---+-+-++--+-+

I did create indexes on open_dt and close_dt (2 separate indexes).



On 09/08/2011 02:55 PM, Derek Downey wrote:

Correct me if I'm wrong. You're wanting to get all records that have an 
open_date or a close_date between two times.

If that's correct, you might be able to get an index_merge by doing a query 
like:

WHERE ((starting time)<=open_dt<= (ending time)) OR ((starting 
time)<=close_dt<=(ending time))

and creating two indexes (one on 'open_dt' and the other on 'close_dt')

http://dev.mysql.com/doc/refman/5.1/en/index-merge-optimization.html

Regards,
Derek

On Sep 8, 2011, at 2:50 PM, Brandon Phelps wrote:


Andy,

The queries take minutes to run.  MySQL is 5.1.54 and it's running on Ubuntu 
server 11.04.  Unfortunately the machine only has 2GB of RAM but no other major 
daemons are running on the machine.  We are running RAID 1 (mirroring) with 1TB 
drives.  The tables in question here are all MyISAM.  When running with the 
LIMIT 10 my EXPLAIN is:

++-+---++-+-+-++--+-+
| id | select_type | table | type   | possible_keys   | key 
| key_len | ref| rows | Extra   |
++-+---++-+-+-++--+-+
|  1 | SIMPLE  | sc| range  | ndx_open_close_rcvd | ndx_open_close_rcvd 
| 8   | NULL   | 32393316 | Using where; Using filesort |
|  1 | SIMPLE  | spm   | eq_ref | PRIMARY | PRIMARY 
| 2   | syslog.sc.src_port |1 | |
|  1 | SIMPLE  | dpm   | eq_ref | PRIMARY | PRIMARY 
| 2   | syslog.sc.dst_port |1 | |
++-+---++-+-+-++--+-+

When I remove the LIMIT 10 I get:

+-+---++-+-+-++--+-+
| id | select_type | table | type   | possible_keys   | key | key_len | 
ref| rows | Extra   |
++-+---++-+-+-++--+-+
|  1 | SIMPLE  | sc| ALL| ndx_open_close_rcvd | NULL| NULL| 
NULL   | 32393330 | Using where; Using filesort |
|  1 | SIMPLE  | spm   | eq_ref | PRIMARY | PRIMARY | 2   | 
syslog.sc.src_port |1 | |
|  1 | SIMPLE  | dpm   | eq_ref | PRIMARY | PRIMARY | 2   | 
syslog.sc.dst_port |1 | |
++-+---++-+-+-++--+-+

Thanks for all your help thus far.

On 09/08/2011 02:38 PM, Andrew Moore wrote:

I don't think I saw any query timings in the emails (maybe I missed them).

What version of MySQL are you currently using?
What does the explain look like when your remove the limit 10?
Is your server tuned for MyISAM or InnoDB?
What kind of disk setup is in use?
How much me

Re: Query Optimization

[Brandon Phelps]

Mihail,

I have considered this but have not yet determined how best to go about 
partitioning the table.  I don't think partitioning by dst_address or 
src_address would help because most of the queries do not filter on IP address 
(except very specific queries where the end-user is searching the table for 
history on a particular employee).

I could potentially partition the table based on the day of week the connection 
was opened on which may improve performance for a while since this would take 
me from a single 32million record table down to roughly 4.5 million records per 
partition (32 / 7) however we are looking to eventually store up to 2 months 
worth of data in the table, and so far that 32 million records is only for 1 
month, so I estimate another 32 million-ish before the month is out, bringing 
me to roughly 70 million records total (it would be nice if I could store even 
more than 2 months, but given my currently performance dilemma I don't expect 
that to happen).  Also this does not take into account that the end-user will 
often be pulling data for multiple days at a time, meaning that multiple 
partitions in this scheme will need to be accessed anyway.

The only other logical partitioning scheme I can think of would be to partition 
based on dst_port (the port the log relates to) but the majority of records are 
all to port 80 (standard web traffic) so I don't think this would be all that 
helpful.

I have never messed with partitioning so it is totally possible that I am not 
thinking of something, so if you have any ideas on a decent partitioning scheme 
based on my criteria and queries below, please let me know.

Thanks,
Brandon
 


On 09/08/2011 02:47 PM, Mihail Manolov wrote:

If you're running version 5.1+ you may wanna take a look at table partitioning 
options you may have.

On Sep 8, 2011, at 2:27 PM, Brandon Phelps wrote:


Thanks for the reply Andy.  Unfortunately the users will be selecting varying 
date ranges and new data is constantly coming in, so I am not sure how I could 
archive/cache the necessary data that would be any more efficient than simply 
using the database directly.


On 09/08/2011 02:16 PM, Andrew Moore wrote:

Thinking outside the query, is there any archiving that could happen to make
your large tables kinder in the range scan?

Andy

On Thu, Sep 8, 2011 at 7:03 PM, Brandon Phelps   wrote:


On 09/01/2011 01:32 PM, Brandon Phelps wrote:


On 09/01/2011 12:47 PM, Shawn Green (MySQL) wrote:


On 9/1/2011 09:42, Brandon Phelps wrote:


On 09/01/2011 04:59 AM, Jochem van Dieten wrote:

...



WHERE
(open_dt>= '2011-08-30 00:00:00' OR close_dt>= '2011-08-30

00:00:00')

AND (open_dt<= '2011-08-30 12:36:53' OR close_dt<= '2011-08-30

12:36:53')


In that case your logic here simplifies to:
WHERE
open_dt>= '2011-08-30 00:00:00'
AND
close_dt<= '2011-08-30 12:36:53'



Now add an index over open_dt and close_dt and see what happens.



Jochem


Jochem,

I can't really use your WHERE logic because I also need to retrieve
results where the open_dt time is out of the range specified. For
example, a very large file download might span multiple days so given
your logic if the connection was started 2 days ago and I want to pull 1
days worth of connections, I would miss that entry. Basically I want to
SELECT all of the records that were opened OR closed during the
specified time period, ie. if any activity happened between my start and
end dates, I need to see that record.

Any other ideas?



I believe Jochem was on the right track but he got his dates reversed.

Let's try a little ASCII art to show the situation. I will setup a query
window with two markers (s) and (e). Events will be marked by || markers
showing their durations.

a) (s) (e)
b) |---|
c) |---|
d) |---|
e) ||
f) |---|
g) |---|

To describe these situations:
a) is the window for which you want to query (s) is the starting time and
(e) is the ending time for the date range you are interested in.
b) the event starts and stops before your window exists. It won't be part
of your results.
c) the event starts before the window but ends within the window -
include this
d) the event starts and ends within the window - include this
e) the event starts before the window and ends after the window - include
this
f) the event starts inside the window but ends beyond the window -
include this.
g) the event starts and ends beyond the window - exclude this.

In order to get every event in the range of c-f, here is what you need
for a WHERE clause

WHERE start<= (ending time) and end>= (starting time)

Try that and let us know the results.



Thanks Jochem and Shawn, however the following two queries result in the
exact same EXPLAIN output: (I hope the tables don't wrap too early for you)

Old method:
SELECT
sc.open_dt,
sc.close_dt,
sc.protocol,
INET_NTOA(sc.src_address) AS src_address,
sc.src_port,
INET_NTOA(sc.dst_address) AS dst_address,
sc.dst_port,
sc.sent,
sc.rcvd,
spm.desc AS src_port_d

Re: Query Optimization

[Derek Downey]

Correct me if I'm wrong. You're wanting to get all records that have an 
open_date or a close_date between two times.

If that's correct, you might be able to get an index_merge by doing a query 
like:

WHERE ((starting time)<=open_dt<= (ending time)) OR ((starting 
time)<=close_dt<=(ending time))

and creating two indexes (one on 'open_dt' and the other on 'close_dt')

http://dev.mysql.com/doc/refman/5.1/en/index-merge-optimization.html

Regards,
Derek

On Sep 8, 2011, at 2:50 PM, Brandon Phelps wrote:

> Andy,
> 
> The queries take minutes to run.  MySQL is 5.1.54 and it's running on Ubuntu 
> server 11.04.  Unfortunately the machine only has 2GB of RAM but no other 
> major daemons are running on the machine.  We are running RAID 1 (mirroring) 
> with 1TB drives.  The tables in question here are all MyISAM.  When running 
> with the LIMIT 10 my EXPLAIN is:
> 
> ++-+---++-+-+-++--+-+
> | id | select_type | table | type   | possible_keys   | key   
>   | key_len | ref| rows | Extra   |
> ++-+---++-+-+-++--+-+
> |  1 | SIMPLE  | sc| range  | ndx_open_close_rcvd | 
> ndx_open_close_rcvd | 8   | NULL   | 32393316 | Using where; 
> Using filesort |
> |  1 | SIMPLE  | spm   | eq_ref | PRIMARY | PRIMARY   
>   | 2   | syslog.sc.src_port |1 | |
> |  1 | SIMPLE  | dpm   | eq_ref | PRIMARY | PRIMARY   
>   | 2   | syslog.sc.dst_port |1 | |
> ++-+---++-+-+-++--+-+
> 
> When I remove the LIMIT 10 I get:
> 
> +-+---++-+-+-++--+-+
> | id | select_type | table | type   | possible_keys   | key | key_len 
> | ref| rows | Extra   |
> ++-+---++-+-+-++--+-+
> |  1 | SIMPLE  | sc| ALL| ndx_open_close_rcvd | NULL| NULL
> | NULL   | 32393330 | Using where; Using filesort |
> |  1 | SIMPLE  | spm   | eq_ref | PRIMARY | PRIMARY | 2   
> | syslog.sc.src_port |1 | |
> |  1 | SIMPLE  | dpm   | eq_ref | PRIMARY | PRIMARY | 2   
> | syslog.sc.dst_port |1 | |
> ++-+---++-+-+-++--+-+
> 
> Thanks for all your help thus far.
> 
> On 09/08/2011 02:38 PM, Andrew Moore wrote:
>> I don't think I saw any query timings in the emails (maybe I missed them).
>> 
>> What version of MySQL are you currently using?
>> What does the explain look like when your remove the limit 10?
>> Is your server tuned for MyISAM or InnoDB?
>> What kind of disk setup is in use?
>> How much memory is in your machine?
>> 
>> 
>> On Thu, Sep 8, 2011 at 7:27 PM, Brandon Phelps  wrote:
>> 
>>> Thanks for the reply Andy.  Unfortunately the users will be selecting
>>> varying date ranges and new data is constantly coming in, so I am not sure
>>> how I could archive/cache the necessary data that would be any more
>>> efficient than simply using the database directly.
>>> 
>>> 
>>> 
>>> On 09/08/2011 02:16 PM, Andrew Moore wrote:
>>> 
 Thinking outside the query, is there any archiving that could happen to
 make
 your large tables kinder in the range scan?
 
 Andy
 
 On Thu, Sep 8, 2011 at 7:03 PM, Brandon Phelps   wrote:
 
  On 09/01/2011 01:32 PM, Brandon Phelps wrote:
> 
>  On 09/01/2011 12:47 PM, Shawn Green (MySQL) wrote:
>> 
>>  On 9/1/2011 09:42, Brandon Phelps wrote:
>>> 
>>>  On 09/01/2011 04:59 AM, Jochem van Dieten wrote:
 
> ...
>> 
> 
  WHERE
>> (open_dt>= '2011-08-30 00:00:00' OR close_dt>= '2011-08-30
>> 
> 00:00:00')
 
> AND (open_dt<= '2011-08-30 12:36:53' OR close_dt<= '2011-08-30
>> 
> 12:36:53')
 
  In that case your logic here simplifies to:
> WHERE
> open_dt>= '2011-08-30 00:00:00'
> AND
> close_dt<= '2011-08-30 12:36:53'
> 
 
  Now add an index over open_dt and close_dt and see what happens.
> 
 
  Jochem
> 
 
 Jochem,
 
 I can't re

Re: Query Optimization

[Brandon Phelps]

Andy,

The queries take minutes to run.  MySQL is 5.1.54 and it's running on Ubuntu 
server 11.04.  Unfortunately the machine only has 2GB of RAM but no other major 
daemons are running on the machine.  We are running RAID 1 (mirroring) with 1TB 
drives.  The tables in question here are all MyISAM.  When running with the 
LIMIT 10 my EXPLAIN is:

++-+---++-+-+-++--+-+
| id | select_type | table | type   | possible_keys   | key 
| key_len | ref| rows | Extra   |
++-+---++-+-+-++--+-+
|  1 | SIMPLE  | sc| range  | ndx_open_close_rcvd | ndx_open_close_rcvd 
| 8   | NULL   | 32393316 | Using where; Using filesort |
|  1 | SIMPLE  | spm   | eq_ref | PRIMARY | PRIMARY 
| 2   | syslog.sc.src_port |1 | |
|  1 | SIMPLE  | dpm   | eq_ref | PRIMARY | PRIMARY 
| 2   | syslog.sc.dst_port |1 | |
++-+---++-+-+-++--+-+

When I remove the LIMIT 10 I get:

+-+---++-+-+-++--+-+
| id | select_type | table | type   | possible_keys   | key | key_len | 
ref| rows | Extra   |
++-+---++-+-+-++--+-+
|  1 | SIMPLE  | sc| ALL| ndx_open_close_rcvd | NULL| NULL| 
NULL   | 32393330 | Using where; Using filesort |
|  1 | SIMPLE  | spm   | eq_ref | PRIMARY | PRIMARY | 2   | 
syslog.sc.src_port |1 | |
|  1 | SIMPLE  | dpm   | eq_ref | PRIMARY | PRIMARY | 2   | 
syslog.sc.dst_port |1 | |
++-+---++-+-+-++--+-+

Thanks for all your help thus far.

On 09/08/2011 02:38 PM, Andrew Moore wrote:

I don't think I saw any query timings in the emails (maybe I missed them).

What version of MySQL are you currently using?
What does the explain look like when your remove the limit 10?
Is your server tuned for MyISAM or InnoDB?
What kind of disk setup is in use?
How much memory is in your machine?


On Thu, Sep 8, 2011 at 7:27 PM, Brandon Phelps  wrote:


Thanks for the reply Andy.  Unfortunately the users will be selecting
varying date ranges and new data is constantly coming in, so I am not sure
how I could archive/cache the necessary data that would be any more
efficient than simply using the database directly.



On 09/08/2011 02:16 PM, Andrew Moore wrote:


Thinking outside the query, is there any archiving that could happen to
make
your large tables kinder in the range scan?

Andy

On Thu, Sep 8, 2011 at 7:03 PM, Brandon Phelps   wrote:

  On 09/01/2011 01:32 PM, Brandon Phelps wrote:


  On 09/01/2011 12:47 PM, Shawn Green (MySQL) wrote:


  On 9/1/2011 09:42, Brandon Phelps wrote:


  On 09/01/2011 04:59 AM, Jochem van Dieten wrote:



...





  WHERE

(open_dt>= '2011-08-30 00:00:00' OR close_dt>= '2011-08-30


00:00:00')



AND (open_dt<= '2011-08-30 12:36:53' OR close_dt<= '2011-08-30



12:36:53')


  In that case your logic here simplifies to:

WHERE
open_dt>= '2011-08-30 00:00:00'
AND
close_dt<= '2011-08-30 12:36:53'



  Now add an index over open_dt and close_dt and see what happens.




  Jochem




Jochem,

I can't really use your WHERE logic because I also need to retrieve
results where the open_dt time is out of the range specified. For
example, a very large file download might span multiple days so given
your logic if the connection was started 2 days ago and I want to pull
1
days worth of connections, I would miss that entry. Basically I want
to
SELECT all of the records that were opened OR closed during the
specified time period, ie. if any activity happened between my start
and
end dates, I need to see that record.

Any other ideas?


  I believe Jochem was on the right track but he got his dates

reversed.

Let's try a little ASCII art to show the situation. I will setup a
query
window with two markers (s) and (e). Events will be marked by ||
markers
showing their durations.

a) (s) (e)
b) |---|
c) |---|
d) |---|
e) ||
f) |---|
g) |---|

To describe these situations:
a) is the window for which you want to query (s) is the starting time
and
(e) is the ending t

Re: Query Optimization

[Mihail Manolov]

If you're running version 5.1+ you may wanna take a look at table partitioning 
options you may have.

On Sep 8, 2011, at 2:27 PM, Brandon Phelps wrote:

> Thanks for the reply Andy.  Unfortunately the users will be selecting varying 
> date ranges and new data is constantly coming in, so I am not sure how I 
> could archive/cache the necessary data that would be any more efficient than 
> simply using the database directly.
> 
> 
> On 09/08/2011 02:16 PM, Andrew Moore wrote:
>> Thinking outside the query, is there any archiving that could happen to make
>> your large tables kinder in the range scan?
>> 
>> Andy
>> 
>> On Thu, Sep 8, 2011 at 7:03 PM, Brandon Phelps  wrote:
>> 
>>> On 09/01/2011 01:32 PM, Brandon Phelps wrote:
>>> 
 On 09/01/2011 12:47 PM, Shawn Green (MySQL) wrote:
 
> On 9/1/2011 09:42, Brandon Phelps wrote:
> 
>> On 09/01/2011 04:59 AM, Jochem van Dieten wrote:
 ...
>> 
 WHERE
 (open_dt>= '2011-08-30 00:00:00' OR close_dt>= '2011-08-30
>> 00:00:00')
 AND (open_dt<= '2011-08-30 12:36:53' OR close_dt<= '2011-08-30
>> 12:36:53')
>> 
>>> In that case your logic here simplifies to:
>>> WHERE
>>> open_dt>= '2011-08-30 00:00:00'
>>> AND
>>> close_dt<= '2011-08-30 12:36:53'
>> 
>>> Now add an index over open_dt and close_dt and see what happens.
>> 
>>> Jochem
>> 
>> Jochem,
>> 
>> I can't really use your WHERE logic because I also need to retrieve
>> results where the open_dt time is out of the range specified. For
>> example, a very large file download might span multiple days so given
>> your logic if the connection was started 2 days ago and I want to pull 1
>> days worth of connections, I would miss that entry. Basically I want to
>> SELECT all of the records that were opened OR closed during the
>> specified time period, ie. if any activity happened between my start and
>> end dates, I need to see that record.
>> 
>> Any other ideas?
>> 
>> 
> I believe Jochem was on the right track but he got his dates reversed.
> 
> Let's try a little ASCII art to show the situation. I will setup a query
> window with two markers (s) and (e). Events will be marked by || 
> markers
> showing their durations.
> 
> a) (s) (e)
> b) |---|
> c) |---|
> d) |---|
> e) ||
> f) |---|
> g) |---|
> 
> To describe these situations:
> a) is the window for which you want to query (s) is the starting time and
> (e) is the ending time for the date range you are interested in.
> b) the event starts and stops before your window exists. It won't be part
> of your results.
> c) the event starts before the window but ends within the window -
> include this
> d) the event starts and ends within the window - include this
> e) the event starts before the window and ends after the window - include
> this
> f) the event starts inside the window but ends beyond the window -
> include this.
> g) the event starts and ends beyond the window - exclude this.
> 
> In order to get every event in the range of c-f, here is what you need
> for a WHERE clause
> 
> WHERE start<= (ending time) and end>= (starting time)
> 
> Try that and let us know the results.
> 
 
 Thanks Jochem and Shawn, however the following two queries result in the
 exact same EXPLAIN output: (I hope the tables don't wrap too early for you)
 
 Old method:
 SELECT
 sc.open_dt,
 sc.close_dt,
 sc.protocol,
 INET_NTOA(sc.src_address) AS src_address,
 sc.src_port,
 INET_NTOA(sc.dst_address) AS dst_address,
 sc.dst_port,
 sc.sent,
 sc.rcvd,
 spm.desc AS src_port_desc,
 dpm.desc AS dst_port_desc
 FROM firewall_connections AS sc
 LEFT JOIN port_mappings AS spm ON spm.port = sc.src_port
 LEFT JOIN port_mappings AS dpm ON dpm.port = sc.dst_port
 WHERE
 (open_dt>= '2011-08-31 09:53:31' OR close_dt>= '2011-08-31 09:53:31')
 AND (open_dt<= '2011-09-01 09:53:31' OR close_dt<= '2011-09-01
 09:53:31')
 ORDER BY rcvd DESC
 LIMIT 0, 10;
 
 New method with BTREE index on open_dt, close_dt (index name is
 ndx_open_close_dt):
 SELECT
 sc.open_dt,
 sc.close_dt,
 sc.protocol,
 INET_NTOA(sc.src_address) AS src_address,
 sc.src_port,
 INET_NTOA(sc.dst_address) AS dst_address,
 sc.dst_port,
 sc.sent,
 sc.rcvd,
 spm.desc AS src_port_desc,
 dpm.desc AS dst_port_desc
 FROM firewall_connections AS sc
 LEFT JOIN port_mappings AS spm ON spm.port = sc.src_port
 LEFT JOIN port_mappings AS dpm ON dpm.port = sc.dst_port
 WHERE
 open_dt<= '2011-09-01 09:53:31' AND close_dt>= '2011-08-31 09:53:31'
 ORDER BY rcvd DESC
 LIMIT 0, 10;
 
 EXPLAIN output for old method:
 ++---

Re: Query Optimization

[Andrew Moore]

I don't think I saw any query timings in the emails (maybe I missed them).

What version of MySQL are you currently using?
What does the explain look like when your remove the limit 10?
Is your server tuned for MyISAM or InnoDB?
What kind of disk setup is in use?
How much memory is in your machine?


On Thu, Sep 8, 2011 at 7:27 PM, Brandon Phelps  wrote:

> Thanks for the reply Andy.  Unfortunately the users will be selecting
> varying date ranges and new data is constantly coming in, so I am not sure
> how I could archive/cache the necessary data that would be any more
> efficient than simply using the database directly.
>
>
>
> On 09/08/2011 02:16 PM, Andrew Moore wrote:
>
>> Thinking outside the query, is there any archiving that could happen to
>> make
>> your large tables kinder in the range scan?
>>
>> Andy
>>
>> On Thu, Sep 8, 2011 at 7:03 PM, Brandon Phelps  wrote:
>>
>>  On 09/01/2011 01:32 PM, Brandon Phelps wrote:
>>>
>>>  On 09/01/2011 12:47 PM, Shawn Green (MySQL) wrote:

  On 9/1/2011 09:42, Brandon Phelps wrote:
>
>  On 09/01/2011 04:59 AM, Jochem van Dieten wrote:
>>
>>> ...

>>>
>>  WHERE
 (open_dt>= '2011-08-30 00:00:00' OR close_dt>= '2011-08-30

>>> 00:00:00')
>>
>>> AND (open_dt<= '2011-08-30 12:36:53' OR close_dt<= '2011-08-30

>>> 12:36:53')
>>
>>  In that case your logic here simplifies to:
>>> WHERE
>>> open_dt>= '2011-08-30 00:00:00'
>>> AND
>>> close_dt<= '2011-08-30 12:36:53'
>>>
>>
>>  Now add an index over open_dt and close_dt and see what happens.
>>>
>>
>>  Jochem
>>>
>>
>> Jochem,
>>
>> I can't really use your WHERE logic because I also need to retrieve
>> results where the open_dt time is out of the range specified. For
>> example, a very large file download might span multiple days so given
>> your logic if the connection was started 2 days ago and I want to pull
>> 1
>> days worth of connections, I would miss that entry. Basically I want
>> to
>> SELECT all of the records that were opened OR closed during the
>> specified time period, ie. if any activity happened between my start
>> and
>> end dates, I need to see that record.
>>
>> Any other ideas?
>>
>>
>>  I believe Jochem was on the right track but he got his dates
> reversed.
>
> Let's try a little ASCII art to show the situation. I will setup a
> query
> window with two markers (s) and (e). Events will be marked by ||
> markers
> showing their durations.
>
> a) (s) (e)
> b) |---|
> c) |---|
> d) |---|
> e) ||
> f) |---|
> g) |---|
>
> To describe these situations:
> a) is the window for which you want to query (s) is the starting time
> and
> (e) is the ending time for the date range you are interested in.
> b) the event starts and stops before your window exists. It won't be
> part
> of your results.
> c) the event starts before the window but ends within the window -
> include this
> d) the event starts and ends within the window - include this
> e) the event starts before the window and ends after the window -
> include
> this
> f) the event starts inside the window but ends beyond the window -
> include this.
> g) the event starts and ends beyond the window - exclude this.
>
> In order to get every event in the range of c-f, here is what you need
> for a WHERE clause
>
> WHERE start<= (ending time) and end>= (starting time)
>
> Try that and let us know the results.
>
>
 Thanks Jochem and Shawn, however the following two queries result in the
 exact same EXPLAIN output: (I hope the tables don't wrap too early for
 you)

 Old method:
 SELECT
 sc.open_dt,
 sc.close_dt,
 sc.protocol,
 INET_NTOA(sc.src_address) AS src_address,
 sc.src_port,
 INET_NTOA(sc.dst_address) AS dst_address,
 sc.dst_port,
 sc.sent,
 sc.rcvd,
 spm.desc AS src_port_desc,
 dpm.desc AS dst_port_desc
 FROM firewall_connections AS sc
 LEFT JOIN port_mappings AS spm ON spm.port = sc.src_port
 LEFT JOIN port_mappings AS dpm ON dpm.port = sc.dst_port
 WHERE
 (open_dt>= '2011-08-31 09:53:31' OR close_dt>= '2011-08-31 09:53:31')
 AND (open_dt<= '2011-09-01 09:53:31' OR close_dt<= '2011-09-01
 09:53:31')
 ORDER BY rcvd DESC
 LIMIT 0, 10;

 New method with BTREE index on open_dt, close_dt (index name is
 ndx_open_close_dt):
 SELECT
 sc.open_dt,
 sc.close_dt,
 sc.protocol,
 INET_NTOA(sc.src_address) AS src_address,
 sc.src_port,
 INET_NTOA(sc.dst_address) AS dst_address,
 sc.dst_port,
 sc.sent,
 sc.rcvd,
 spm.desc AS src_port_desc,
 dpm.desc AS dst_port_desc
 FROM firewall_connections AS sc
 LEFT J

Re: Query Optimization

[Brandon Phelps]

Thanks for the reply Andy.  Unfortunately the users will be selecting varying 
date ranges and new data is constantly coming in, so I am not sure how I could 
archive/cache the necessary data that would be any more efficient than simply 
using the database directly.


On 09/08/2011 02:16 PM, Andrew Moore wrote:

Thinking outside the query, is there any archiving that could happen to make
your large tables kinder in the range scan?

Andy

On Thu, Sep 8, 2011 at 7:03 PM, Brandon Phelps  wrote:


On 09/01/2011 01:32 PM, Brandon Phelps wrote:


On 09/01/2011 12:47 PM, Shawn Green (MySQL) wrote:


On 9/1/2011 09:42, Brandon Phelps wrote:


On 09/01/2011 04:59 AM, Jochem van Dieten wrote:

...



WHERE
(open_dt>= '2011-08-30 00:00:00' OR close_dt>= '2011-08-30

00:00:00')

AND (open_dt<= '2011-08-30 12:36:53' OR close_dt<= '2011-08-30

12:36:53')


In that case your logic here simplifies to:
WHERE
open_dt>= '2011-08-30 00:00:00'
AND
close_dt<= '2011-08-30 12:36:53'



Now add an index over open_dt and close_dt and see what happens.



Jochem


Jochem,

I can't really use your WHERE logic because I also need to retrieve
results where the open_dt time is out of the range specified. For
example, a very large file download might span multiple days so given
your logic if the connection was started 2 days ago and I want to pull 1
days worth of connections, I would miss that entry. Basically I want to
SELECT all of the records that were opened OR closed during the
specified time period, ie. if any activity happened between my start and
end dates, I need to see that record.

Any other ideas?



I believe Jochem was on the right track but he got his dates reversed.

Let's try a little ASCII art to show the situation. I will setup a query
window with two markers (s) and (e). Events will be marked by || markers
showing their durations.

a) (s) (e)
b) |---|
c) |---|
d) |---|
e) ||
f) |---|
g) |---|

To describe these situations:
a) is the window for which you want to query (s) is the starting time and
(e) is the ending time for the date range you are interested in.
b) the event starts and stops before your window exists. It won't be part
of your results.
c) the event starts before the window but ends within the window -
include this
d) the event starts and ends within the window - include this
e) the event starts before the window and ends after the window - include
this
f) the event starts inside the window but ends beyond the window -
include this.
g) the event starts and ends beyond the window - exclude this.

In order to get every event in the range of c-f, here is what you need
for a WHERE clause

WHERE start<= (ending time) and end>= (starting time)

Try that and let us know the results.



Thanks Jochem and Shawn, however the following two queries result in the
exact same EXPLAIN output: (I hope the tables don't wrap too early for you)

Old method:
SELECT
sc.open_dt,
sc.close_dt,
sc.protocol,
INET_NTOA(sc.src_address) AS src_address,
sc.src_port,
INET_NTOA(sc.dst_address) AS dst_address,
sc.dst_port,
sc.sent,
sc.rcvd,
spm.desc AS src_port_desc,
dpm.desc AS dst_port_desc
FROM firewall_connections AS sc
LEFT JOIN port_mappings AS spm ON spm.port = sc.src_port
LEFT JOIN port_mappings AS dpm ON dpm.port = sc.dst_port
WHERE
(open_dt>= '2011-08-31 09:53:31' OR close_dt>= '2011-08-31 09:53:31')
AND (open_dt<= '2011-09-01 09:53:31' OR close_dt<= '2011-09-01
09:53:31')
ORDER BY rcvd DESC
LIMIT 0, 10;

New method with BTREE index on open_dt, close_dt (index name is
ndx_open_close_dt):
SELECT
sc.open_dt,
sc.close_dt,
sc.protocol,
INET_NTOA(sc.src_address) AS src_address,
sc.src_port,
INET_NTOA(sc.dst_address) AS dst_address,
sc.dst_port,
sc.sent,
sc.rcvd,
spm.desc AS src_port_desc,
dpm.desc AS dst_port_desc
FROM firewall_connections AS sc
LEFT JOIN port_mappings AS spm ON spm.port = sc.src_port
LEFT JOIN port_mappings AS dpm ON dpm.port = sc.dst_port
WHERE
open_dt<= '2011-09-01 09:53:31' AND close_dt>= '2011-08-31 09:53:31'
ORDER BY rcvd DESC
LIMIT 0, 10;

EXPLAIN output for old method:
++-+---+--**--+---**
+--+-+**+--+--**---+
| id | select_type | table | type | possible_keys | key | key_len | ref |
rows | Extra |
++-+---+--**--+---**
+--+-+**+--+--**---+
| 1 | SIMPLE | sc | index | open_dt,ndx_open_close_dt | ndx_rcvd | 4 |
NULL | 10 | Using where |
| 1 | SIMPLE | spm | eq_ref | PRIMARY | PRIMARY | 2 | syslog.sc.src_port |
1 | |
| 1 | SIMPLE | dpm | eq_ref | PRIMARY | PRIMARY | 2 | syslog.sc.dst_port |
1 | |
++-+---+--**--+---**
+--+-+**+--+--**---+

EXPLAIN output for new method with new index:
++-+---+--**--+---**
+--+-+**+--+

Re: Query Optimization

[Andrew Moore]

Thinking outside the query, is there any archiving that could happen to make
your large tables kinder in the range scan?

Andy

On Thu, Sep 8, 2011 at 7:03 PM, Brandon Phelps  wrote:

> On 09/01/2011 01:32 PM, Brandon Phelps wrote:
>
>> On 09/01/2011 12:47 PM, Shawn Green (MySQL) wrote:
>>
>>> On 9/1/2011 09:42, Brandon Phelps wrote:
>>>
 On 09/01/2011 04:59 AM, Jochem van Dieten wrote:
 > > ...

 > > WHERE
 > > (open_dt >= '2011-08-30 00:00:00' OR close_dt >= '2011-08-30
 00:00:00')
 > > AND (open_dt <= '2011-08-30 12:36:53' OR close_dt <= '2011-08-30
 12:36:53')

 > In that case your logic here simplifies to:
 > WHERE
 > open_dt >= '2011-08-30 00:00:00'
 > AND
 > close_dt <= '2011-08-30 12:36:53'

 > Now add an index over open_dt and close_dt and see what happens.

 > Jochem

 Jochem,

 I can't really use your WHERE logic because I also need to retrieve
 results where the open_dt time is out of the range specified. For
 example, a very large file download might span multiple days so given
 your logic if the connection was started 2 days ago and I want to pull 1
 days worth of connections, I would miss that entry. Basically I want to
 SELECT all of the records that were opened OR closed during the
 specified time period, ie. if any activity happened between my start and
 end dates, I need to see that record.

 Any other ideas?


>>> I believe Jochem was on the right track but he got his dates reversed.
>>>
>>> Let's try a little ASCII art to show the situation. I will setup a query
>>> window with two markers (s) and (e). Events will be marked by || markers
>>> showing their durations.
>>>
>>> a) (s) (e)
>>> b) |---|
>>> c) |---|
>>> d) |---|
>>> e) ||
>>> f) |---|
>>> g) |---|
>>>
>>> To describe these situations:
>>> a) is the window for which you want to query (s) is the starting time and
>>> (e) is the ending time for the date range you are interested in.
>>> b) the event starts and stops before your window exists. It won't be part
>>> of your results.
>>> c) the event starts before the window but ends within the window -
>>> include this
>>> d) the event starts and ends within the window - include this
>>> e) the event starts before the window and ends after the window - include
>>> this
>>> f) the event starts inside the window but ends beyond the window -
>>> include this.
>>> g) the event starts and ends beyond the window - exclude this.
>>>
>>> In order to get every event in the range of c-f, here is what you need
>>> for a WHERE clause
>>>
>>> WHERE start <= (ending time) and end >= (starting time)
>>>
>>> Try that and let us know the results.
>>>
>>
>> Thanks Jochem and Shawn, however the following two queries result in the
>> exact same EXPLAIN output: (I hope the tables don't wrap too early for you)
>>
>> Old method:
>> SELECT
>> sc.open_dt,
>> sc.close_dt,
>> sc.protocol,
>> INET_NTOA(sc.src_address) AS src_address,
>> sc.src_port,
>> INET_NTOA(sc.dst_address) AS dst_address,
>> sc.dst_port,
>> sc.sent,
>> sc.rcvd,
>> spm.desc AS src_port_desc,
>> dpm.desc AS dst_port_desc
>> FROM firewall_connections AS sc
>> LEFT JOIN port_mappings AS spm ON spm.port = sc.src_port
>> LEFT JOIN port_mappings AS dpm ON dpm.port = sc.dst_port
>> WHERE
>> (open_dt >= '2011-08-31 09:53:31' OR close_dt >= '2011-08-31 09:53:31')
>> AND (open_dt <= '2011-09-01 09:53:31' OR close_dt <= '2011-09-01
>> 09:53:31')
>> ORDER BY rcvd DESC
>> LIMIT 0, 10;
>>
>> New method with BTREE index on open_dt, close_dt (index name is
>> ndx_open_close_dt):
>> SELECT
>> sc.open_dt,
>> sc.close_dt,
>> sc.protocol,
>> INET_NTOA(sc.src_address) AS src_address,
>> sc.src_port,
>> INET_NTOA(sc.dst_address) AS dst_address,
>> sc.dst_port,
>> sc.sent,
>> sc.rcvd,
>> spm.desc AS src_port_desc,
>> dpm.desc AS dst_port_desc
>> FROM firewall_connections AS sc
>> LEFT JOIN port_mappings AS spm ON spm.port = sc.src_port
>> LEFT JOIN port_mappings AS dpm ON dpm.port = sc.dst_port
>> WHERE
>> open_dt <= '2011-09-01 09:53:31' AND close_dt >= '2011-08-31 09:53:31'
>> ORDER BY rcvd DESC
>> LIMIT 0, 10;
>>
>> EXPLAIN output for old method:
>> ++-+---+--**--+---**
>> +--+-+**+--+--**---+
>> | id | select_type | table | type | possible_keys | key | key_len | ref |
>> rows | Extra |
>> ++-+---+--**--+---**
>> +--+-+**+--+--**---+
>> | 1 | SIMPLE | sc | index | open_dt,ndx_open_close_dt | ndx_rcvd | 4 |
>> NULL | 10 | Using where |
>> | 1 | SIMPLE | spm | eq_ref | PRIMARY | PRIMARY | 2 | syslog.sc.src_port |
>> 1 | |
>> | 1 | SIMPLE | dpm | eq_ref | PRIMARY | PRIMARY | 2 | syslog.sc.dst_port |
>> 1 | |
>> ++-+---+--**--+---**
>> +--+-+**--

Re: Query Optimization

[Brandon Phelps]

On 09/01/2011 01:32 PM, Brandon Phelps wrote:

On 09/01/2011 12:47 PM, Shawn Green (MySQL) wrote:

On 9/1/2011 09:42, Brandon Phelps wrote:

On 09/01/2011 04:59 AM, Jochem van Dieten wrote:
> > ...

> > WHERE
> > (open_dt >= '2011-08-30 00:00:00' OR close_dt >= '2011-08-30
00:00:00')
> > AND (open_dt <= '2011-08-30 12:36:53' OR close_dt <= '2011-08-30
12:36:53')

> In that case your logic here simplifies to:
> WHERE
> open_dt >= '2011-08-30 00:00:00'
> AND
> close_dt <= '2011-08-30 12:36:53'

> Now add an index over open_dt and close_dt and see what happens.

> Jochem

Jochem,

I can't really use your WHERE logic because I also need to retrieve
results where the open_dt time is out of the range specified. For
example, a very large file download might span multiple days so given
your logic if the connection was started 2 days ago and I want to pull 1
days worth of connections, I would miss that entry. Basically I want to
SELECT all of the records that were opened OR closed during the
specified time period, ie. if any activity happened between my start and
end dates, I need to see that record.

Any other ideas?



I believe Jochem was on the right track but he got his dates reversed.

Let's try a little ASCII art to show the situation. I will setup a query window 
with two markers (s) and (e). Events will be marked by || markers showing 
their durations.

a) (s) (e)
b) |---|
c) |---|
d) |---|
e) ||
f) |---|
g) |---|

To describe these situations:
a) is the window for which you want to query (s) is the starting time and (e) 
is the ending time for the date range you are interested in.
b) the event starts and stops before your window exists. It won't be part of 
your results.
c) the event starts before the window but ends within the window - include this
d) the event starts and ends within the window - include this
e) the event starts before the window and ends after the window - include this
f) the event starts inside the window but ends beyond the window - include this.
g) the event starts and ends beyond the window - exclude this.

In order to get every event in the range of c-f, here is what you need for a 
WHERE clause

WHERE start <= (ending time) and end >= (starting time)

Try that and let us know the results.


Thanks Jochem and Shawn, however the following two queries result in the exact 
same EXPLAIN output: (I hope the tables don't wrap too early for you)

Old method:
SELECT
sc.open_dt,
sc.close_dt,
sc.protocol,
INET_NTOA(sc.src_address) AS src_address,
sc.src_port,
INET_NTOA(sc.dst_address) AS dst_address,
sc.dst_port,
sc.sent,
sc.rcvd,
spm.desc AS src_port_desc,
dpm.desc AS dst_port_desc
FROM firewall_connections AS sc
LEFT JOIN port_mappings AS spm ON spm.port = sc.src_port
LEFT JOIN port_mappings AS dpm ON dpm.port = sc.dst_port
WHERE
(open_dt >= '2011-08-31 09:53:31' OR close_dt >= '2011-08-31 09:53:31')
AND (open_dt <= '2011-09-01 09:53:31' OR close_dt <= '2011-09-01 09:53:31')
ORDER BY rcvd DESC
LIMIT 0, 10;

New method with BTREE index on open_dt, close_dt (index name is 
ndx_open_close_dt):
SELECT
sc.open_dt,
sc.close_dt,
sc.protocol,
INET_NTOA(sc.src_address) AS src_address,
sc.src_port,
INET_NTOA(sc.dst_address) AS dst_address,
sc.dst_port,
sc.sent,
sc.rcvd,
spm.desc AS src_port_desc,
dpm.desc AS dst_port_desc
FROM firewall_connections AS sc
LEFT JOIN port_mappings AS spm ON spm.port = sc.src_port
LEFT JOIN port_mappings AS dpm ON dpm.port = sc.dst_port
WHERE
open_dt <= '2011-09-01 09:53:31' AND close_dt >= '2011-08-31 09:53:31'
ORDER BY rcvd DESC
LIMIT 0, 10;

EXPLAIN output for old method:
++-+---++---+--+-++--+-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows 
| Extra |
++-+---++---+--+-++--+-+
| 1 | SIMPLE | sc | index | open_dt,ndx_open_close_dt | ndx_rcvd | 4 | NULL | 
10 | Using where |
| 1 | SIMPLE | spm | eq_ref | PRIMARY | PRIMARY | 2 | syslog.sc.src_port | 1 | |
| 1 | SIMPLE | dpm | eq_ref | PRIMARY | PRIMARY | 2 | syslog.sc.dst_port | 1 | |
++-+---++---+--+-++--+-+

EXPLAIN output for new method with new index:
++-+---++---+--+-++--+-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows 
| Extra |
++-+---++---+--+-++--+-+
| 1 | SIMPLE | sc | index | open_dt,ndx_open_close_dt | ndx_rcvd | 4 | NULL | 
10 | Using where |
| 1 | SIMPLE | spm | eq_ref | PRIMARY | PRIMARY | 2 | syslog.sc.src_port | 1 | |
| 1 | SIMPLE | dpm | eq_ref | PRIMARY | PRIMARY | 2 | syslog.sc.dst_port | 1 | |
++

Triggers - Accessing all NEW data

[Chris Tate-Davies]

Hello. I want to know if there is a special way I can access all the 
data in the NEW/OLD data?


I realise I can access it by referencing NEW.fieldname but I want to 
serialise the NEW object so I can save as a string. Is this possible or 
do I need to write a function?


Thanks, Chris



*Chris Tate-Davies*

*Software Development*
Inflight Productions Ltd
Telephone: 01295 269 680
15 Stukeley Street | London | WC2B 5LT
*Email:*chris.tatedav...@inflightproductions.com 


*Web:*www.inflightproductions.com 





-


Registered Office: 15 Stukeley Street, London WC2B 5LT, England.
Registered in England number 1421223

This message is for the designated recipient only and may contain privileged, 
proprietary, or otherwise private information. If you have received it in 
error, please notify the sender immediately and delete the original. Any other 
use of the email by you is prohibited. Please note that the information 
provided in this e-mail is in any case not legally binding; all committing 
statements require legally binding signatures.


http://www.inflightproductions.com

Re: trying to change wait_timeout

[Andrew Moore]

Check that you're looking at the variable in the GLOBAL scope not the
SESSION scope.

SHOW GLOBAL VARIABLE ...

Andy

On Thu, Sep 8, 2011 at 11:34 AM, Bruce Ferrell wrote:

> On 09/08/2011 02:56 AM, Johan De Meersman wrote:
>
>> - Original Message -
>>
>>> From: "Bruce Ferrell"
>>> To: mysql@lists.mysql.com
>>> Sent: Thursday, 8 September, 2011 3:10:16 AM
>>> Subject: trying to change wait_timeout
>>>
>>> I've read the documentation on MySQL for version 5.1 and it says all
>>> I have to do is to place the following:
>>> wait_timeout=xxx
>>> under [mysqld]
>>>
>> That, and restart the service, of course. You *did* think of restarting
>> the service, I trust? :-p
>>
>> That being said, it is also a dynamic variable, so if you didn't restart,
>> prefer not to restart *and* are certain your config file is correct; you can
>> also do "set global wait_timeout=xxx" to have it take effect immediately for
>> all new sessions. Yes, that means you'll have to disconnect/reconnect to see
>> the change in your own session.
>>
>
> Good question to ask.  Yes, I did restart mysql.  Both before and after
> show variables like 'wait_time%' returns 28800.  Most confusing.
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql?**
> unsub=eroomy...@gmail.com
>
>

Re: trying to change wait_timeout

[Bruce Ferrell]

On 09/08/2011 02:56 AM, Johan De Meersman wrote:

- Original Message -

From: "Bruce Ferrell"
To: mysql@lists.mysql.com
Sent: Thursday, 8 September, 2011 3:10:16 AM
Subject: trying to change wait_timeout

I've read the documentation on MySQL for version 5.1 and it says all
I have to do is to place the following:
wait_timeout=xxx
under [mysqld]

That, and restart the service, of course. You *did* think of restarting the 
service, I trust? :-p

That being said, it is also a dynamic variable, so if you didn't restart, prefer not to 
restart *and* are certain your config file is correct; you can also do "set global 
wait_timeout=xxx" to have it take effect immediately for all new sessions. Yes, that 
means you'll have to disconnect/reconnect to see the change in your own session.


Good question to ask.  Yes, I did restart mysql.  Both before and after 
show variables like 'wait_time%' returns 28800.  Most confusing.




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: trying to change wait_timeout

[Johan De Meersman]

- Original Message -
> From: "Bruce Ferrell" 
> To: mysql@lists.mysql.com
> Sent: Thursday, 8 September, 2011 3:10:16 AM
> Subject: trying to change wait_timeout
> 
> I've read the documentation on MySQL for version 5.1 and it says all
> I have to do is to place the following:
> wait_timeout=xxx
> under [mysqld]

That, and restart the service, of course. You *did* think of restarting the 
service, I trust? :-p

That being said, it is also a dynamic variable, so if you didn't restart, 
prefer not to restart *and* are certain your config file is correct; you can 
also do "set global wait_timeout=xxx" to have it take effect immediately for 
all new sessions. Yes, that means you'll have to disconnect/reconnect to see 
the change in your own session.


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: strange mysql update ..

[Rik Wasmus]

> I fired the update statement in a wrong way ..like this ..
> 
> update user_info set login_date='2011-08-05 04:15:05' and user_id =16078845
> limit 1 ;
> ( I forgot to use where . instead of where I used and )
> update user_info set login_date='2011-08-05 04:15:05' where user_id
> =16078845 limit 1 ; ( this is the query intended )
> 
> after the update ..I got this message ..
> mysql> update user_info set login_date='2011-08-05 04:15:05' and user_id
> =16078845 limit 1;
> Query OK, 1 row affected, 1 warning (0.02 sec)
> Rows matched: 1  Changed: 1  Warnings: 0
> 
> It shows that one record is affected and one row changed ..
> I did show warnings ..the output is like this ..
> 
> | Warning | 1292 | Truncated incorrect DOUBLE value: '2011-08-05 04:15:05'
 
> So my question is what happened exactly ?
> Why no records updated ?

A lot of casting:

(1) login_date='2011-08-05 04:15:05' and user_id =16078845;

And implies boolean, so the result is the either true or false. MySQL doesn't 
like using non-numbers as booleans (the '2011-08-05 04:15:05') , this is the 
double spoken of. 

(2) login_date = false (or true, but that doesn't matter) 

But MySQL doesn't know booleans, to a number it is:

(3) login_date = 0

But the column is a DATETIME (or TIMESTAMP) column, 0 or 1 is an incorrect 
value, cast to:

(4) login_date = -00-00 00:00:00

So, somewhere there's (or was, may be overwritten) a record with that value, 
just 1 due to the limit 1, otherwise, the whole table would have that as a 
login_date (doesn't matter wether it was true or false).


Check out:
DB 5.1.58-1-log:(none)  mysql> SELECT 1 AND 1;
+-+
| 1 AND 1 |
+-+
|   1 |
+-+
1 row in set (0.00 sec)

DB 5.1.58-1-log:(none)  mysql> SELECT 0 AND 1;
+-+
| 0 AND 1 |
+-+
|   0 |
+-+
1 row in set (0.01 sec)

DB 5.1.58-1-log:(none)  mysql> SELECT '1' AND 1;
+---+
| '1' AND 1 |
+---+
| 1 |
+---+
1 row in set (0.03 sec)

DB 5.1.58-1-log:(none)  mysql> SELECT 'a' AND 1;
+---+
| 'a' AND 1 |
+---+
| 0 |
+---+
1 row in set, 1 warning (0.03 sec)

DB 5.1.58-1-log:(none)  mysql> SHOW WARNINGS;
+-+--+---+
| Level   | Code | Message   |
+-+--+---+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'a' |
+-+--+---+
1 row in set (0.01 sec)
-- 
Rik Wasmus

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: strange mysql update ..

[umapathi b]

Here is the o/p after the update ..

   user_id: 16078845
   drivers_license: TEST1140DL
login_date: 2011-06-19 11:20:07
 course_id: 1011
  regulator_id: 10840
 test_info: 
   completion_date: 2011-06-19 11:37:16
print_date: NULL
  password: test1140dl
certificate_number: NULL
 login: test114...@1140dl.com


- Umapathi

On Thu, Sep 8, 2011 at 4:23 AM, Ananda Kumar  wrote:

> Can you lets us know what is the output of
>
> select * from user_info where user_id=16078845;
>
>
> On Thu, Sep 8, 2011 at 1:02 PM, umapathi b  wrote:
>
>> I wanted to change the login_date of one user . The original data of that
>> user is like this ..
>>
>> select * from user_info where user_id = 16078845 \G
>> *** 1. row ***
>>   user_id: 16078845
>>   drivers_license: TEST1140DL
>>login_date: 2011-06-19 11:20:07
>> course_id: 1011
>>  regulator_id: 10840
>> test_info: 
>>   completion_date: 2011-06-19 11:37:16
>>print_date: NULL
>>  password: test1140dl
>> certificate_number: NULL
>> login: test114...@1140dl.com
>>
>> I fired the update statement in a wrong way ..like this ..
>>
>> update user_info set login_date='2011-08-05 04:15:05' and user_id
>> =16078845
>> limit 1 ;
>> ( I forgot to use where . instead of where I used and )
>> update user_info set login_date='2011-08-05 04:15:05' where user_id
>> =16078845 limit 1 ; ( this is the query intended )
>>
>> after the update ..I got this message ..
>> mysql> update user_info set login_date='2011-08-05 04:15:05' and user_id
>> =16078845 limit 1;
>> Query OK, 1 row affected, 1 warning (0.02 sec)
>> Rows matched: 1  Changed: 1  Warnings: 0
>>
>> It shows that one record is affected and one row changed ..
>> I did show warnings ..the output is like this ..
>>
>> mysql> show warnings;
>>
>> +-+--+-+
>> | Level   | Code | Message
>> |
>>
>> +-+--+-+
>> | Warning | 1292 | Truncated incorrect DOUBLE value: '2011-08-05 04:15:05'
>> |
>>
>>
>> +-+--+-+
>>
>> But I could not get any record in the table with the updated login_date ..
>> mysql> select * from user_info where login_date like '2011-08-05%' ;
>> Empty set (0.67 sec)
>>
>> So my question is what happened exactly ?
>> Why no records updated ?
>>
>> Help is highly appreciated in this regard ..
>>
>> - Umapathi
>> umapath...@gmail.com
>>
>
>

Re: strange mysql update ..

[Ananda Kumar]

Can you lets us know what is the output of

select * from user_info where user_id=16078845;

On Thu, Sep 8, 2011 at 1:02 PM, umapathi b  wrote:

> I wanted to change the login_date of one user . The original data of that
> user is like this ..
>
> select * from user_info where user_id = 16078845 \G
> *** 1. row ***
>   user_id: 16078845
>   drivers_license: TEST1140DL
>login_date: 2011-06-19 11:20:07
> course_id: 1011
>  regulator_id: 10840
> test_info: 
>   completion_date: 2011-06-19 11:37:16
>print_date: NULL
>  password: test1140dl
> certificate_number: NULL
> login: test114...@1140dl.com
>
> I fired the update statement in a wrong way ..like this ..
>
> update user_info set login_date='2011-08-05 04:15:05' and user_id =16078845
> limit 1 ;
> ( I forgot to use where . instead of where I used and )
> update user_info set login_date='2011-08-05 04:15:05' where user_id
> =16078845 limit 1 ; ( this is the query intended )
>
> after the update ..I got this message ..
> mysql> update user_info set login_date='2011-08-05 04:15:05' and user_id
> =16078845 limit 1;
> Query OK, 1 row affected, 1 warning (0.02 sec)
> Rows matched: 1  Changed: 1  Warnings: 0
>
> It shows that one record is affected and one row changed ..
> I did show warnings ..the output is like this ..
>
> mysql> show warnings;
>
> +-+--+-+
> | Level   | Code | Message
> |
>
> +-+--+-+
> | Warning | 1292 | Truncated incorrect DOUBLE value: '2011-08-05 04:15:05'
> |
>
>
> +-+--+-+
>
> But I could not get any record in the table with the updated login_date ..
> mysql> select * from user_info where login_date like '2011-08-05%' ;
> Empty set (0.67 sec)
>
> So my question is what happened exactly ?
> Why no records updated ?
>
> Help is highly appreciated in this regard ..
>
> - Umapathi
> umapath...@gmail.com
>

strange mysql update ..

[umapathi b]

I wanted to change the login_date of one user . The original data of that
user is like this ..

select * from user_info where user_id = 16078845 \G
*** 1. row ***
   user_id: 16078845
   drivers_license: TEST1140DL
login_date: 2011-06-19 11:20:07
 course_id: 1011
  regulator_id: 10840
 test_info: 
   completion_date: 2011-06-19 11:37:16
print_date: NULL
  password: test1140dl
certificate_number: NULL
 login: test114...@1140dl.com

I fired the update statement in a wrong way ..like this ..

update user_info set login_date='2011-08-05 04:15:05' and user_id =16078845
limit 1 ;
( I forgot to use where . instead of where I used and )
update user_info set login_date='2011-08-05 04:15:05' where user_id
=16078845 limit 1 ; ( this is the query intended )

after the update ..I got this message ..
mysql> update user_info set login_date='2011-08-05 04:15:05' and user_id
=16078845 limit 1;
Query OK, 1 row affected, 1 warning (0.02 sec)
Rows matched: 1  Changed: 1  Warnings: 0

It shows that one record is affected and one row changed ..
I did show warnings ..the output is like this ..

mysql> show warnings;
+-+--+-+
| Level   | Code | Message |
+-+--+-+
| Warning | 1292 | Truncated incorrect DOUBLE value: '2011-08-05 04:15:05' |

+-+--+-+

But I could not get any record in the table with the updated login_date ..
mysql> select * from user_info where login_date like '2011-08-05%' ;
Empty set (0.67 sec)

So my question is what happened exactly ?
Why no records updated ?

Help is highly appreciated in this regard ..

- Umapathi
umapath...@gmail.com