Design question.
I maintain a little open source project that deals with IDS alert data. I want to add IP reputation to my event queries and I am stuck on how I should implement it. The user will have the option of bringing in lists from different providers and the limit will not be fixed. These lists will be a single column of IP addresses. list 1: IP listing list 2: IP listing list 3: IP listing ... There can, and most likely will be duplication of addresses across the different lists. The number of lists that a host is a member of will be an indication of its reputation. The desired result will be something like: event count | event signature | src ip | country | ip reputation | dst ip | country | ip reputation The lists will be updated once each day or on demand. I already have a mappings table that provides country information for ip's in the event table which is joined during the event queries. The mappings table contains a little under 500,000 addresses and grows slowly - say 50 to 100 addresses / day. As new ip's appear in the event table, they are mapped to a country. Questions: 1) Should I just create a new table for every list the user adds and then do joins on these? 2) Should I put the lists in 1 table somehow? 3) As the lists are done daily, should I just run a midnight task that parses each list and adds the information to the mappings table. I have no idea what the format would look like. I was thinking of creating a varchar and have something like: list1|list2|list9|list20 and then just breaking it out in the code. The entire table would of course need to be scanned each day to check whether or not an address had been taken off a list. (efficiency?) Any comments/suggestions would be greatly appreciated. Thanks. -- Paul Halliday http://www.pintumbler.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Need to remove and reinstall, which files/folders?
I have a customized (i.e.: bugs fixed by a programer) instance of MySQL 5.1 on my Intel Macintosh running OS 10.6.6. MySQL stopped running after upgrading to 10.6 from 10.5. It seems that I should remove 5.1 and re-install 5.5. Is there a complete list of files and folders available to tell me what to delete? Thanks, Glen - Berkeley, CA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Benetl a free ETL tool using MySQL is out in version 3.7
Dear all, Benetl, a free ETL tool for files using MySQL, is out in version 3.7. You can freely download it at: www.benetl.net This version brings new controls and messages in the GUI. This version brings improvements and a bug correction in GUI. You should update. You can learn more about ETL tools at: http://en.wikipedia.org/wiki/Extract,_transform,_load Thanks for your interest. Regards, -- Benoît Carpentier www.benetl.net Founder of Benetl and Java project manager -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Insert data in one table from Another Problem
Here's one option to pivot your results: select record_id ,max(soi) as soi ,max(heading) as heading ,max(description) as description ,max(relloc) as relloc from (select record_id ,if(field_name = 'SOI', field_value, '') as soi ,if(field_name = 'Heading', field_value, '') as heading ,if(field_name = 'Description', field_value, '') as description ,if(field_name = 'RelLoc', field_value, '') as relloc from user_news) s1 group by s1.record_id; -Original Message- From: Adarsh Sharma [mailto:adarsh.sha...@orkash.com] Sent: Wednesday, February 16, 2011 6:33 AM To: mysql@lists.mysql.com Subject: Insert data in one table from Another Problem Dear all, Today I am puzzled around a problem of inserting data into new table in new format. I have a table named *user_news* as : We have four rows with respect to each record_id. fore.g : I have listed main columns as *record_id field_name field_value* 572SOIMedia 572 Heading A senior Police official confirmed the presence of the stone quarry at Jafflong near the India-Bangladesh border 572Description HNLC runs a stone quarry in Jafflong District of Bangladesh. The outfit is also believed to own several betel nut plantations besides running other business in Bangladesh. 572 RelLoc Jafflong 578SOI Media 578 Heading Army Chief General V. K. Singh in Shillong said he was confident that the NDFB would come to the negotiating table if they are handled properly 578Description A school teacher was abducted by unidentified militants in Damas of East Garo Hills District. Army Chief General V. K. Singh in Shillong said he was confident . 578 RelLoc Garo Hills Similarly i have 1000 of rows. Now I create a new table as columns as : *record_id SOI heading Description RelLoc * and its values is as : * * 572 MediaA senior Police official confirmed the presence of the stone quarry at Jafflong near the India-Bangladesh border HNLC runs a stone quarry in Jafflong District of Bangladesh. Jafflong The values in *field_name* becomes four columns in the above table . and their values are the values of f*ield_value *column. The problem is that I want this data now in horizontal form and the data of four rows in one row. That is four rows in one table contributes a single row in *other *table. I try with procedures and cursors but fail to achieve the output. Is it possible in Mysql. Please guide me how to achieve this as I am stuck around it. Thanks Best Regards Adarsh Sharma -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Question about performance
Hi all; I wonder if there is any tool to Performance Tuning querys. In other know if there is any way to kill connections that take x hours dead (for example 1 hour) -- Mit forever My Blog http://www.redcloverbi.wordpress.com My Faborite Webhttp://ocw.mit.edu/OcwWeb/Electrical-Engineering-and-Computer-Science/index.htm http://www.technologyreview.com/
RE: mysql TIME_WAIT
After a TCP connection has been closed, the state changes to TIME_WAIT. If I recall correctly, this is a way to prevent the same port from being immediately reused, and allow any duplicate packets from the previous connection that might still be floating around the network time to expire. In general, the presence of TIME_WAIT isn't something to be too concerned about. If you see a large number of these, it may be because your application is opening a new database connection for each web server request. You might be able to save yourself a bit of overhead by refactoring your application to use a database connection pool. -Travis -Original Message- From: Madan Thapa [mailto:madan.feedb...@gmail.com] Sent: Wednesday, February 16, 2011 3:35 PM To: mysql@lists.mysql.com Subject: mysql TIME_WAIT Hi, When i do a netstat and grep port 3306 , i can see lots of TIME_WAIT.Can you please advise , what might be the issue for so many TIME_WAIT ? Here are the logs and related files: My.CNF on DB Server ## ## [root@sql mysql]# cat /etc/my.cnf [mysqld] safe-show-database skip-locking skip-bdb port= 3306 socket = /var/lib/mysql/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 16M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size = 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 4 max_connections=5000 long_query_time = 20 log-slow-queries=/var/log/mysqld-slow.log [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL safe-updates [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout [root@sql mysql]# NETSTAT on Web Server # I have changed acutal IP to WEBSERVERIP ( for the webserver ) and DBSERVERIP ( for the db server ip ) [root@web temphotos]# netstat -an | grep DBSERVERIP tcp0 0 WEBSERVERIP:57666 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:54315 DBSERVERIP:3306 ESTABLISHED tcp0 0 WEBSERVERIP:53293 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53295 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53305 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53304 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53307 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53306 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53309 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53308 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53311 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53310 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53297 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53296 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53299 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53298 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53301 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53300 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53303 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53302 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53257 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53256 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53259 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53258 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53462 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:56250 DBSERVERIP:3306 ESTABLISHED tcp0 0 WEBSERVERIP:50288 DBSERVERIP:3306 ESTABLISHED tcp0 0 WEBSERVERIP:52286 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53116 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:48573 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:48574 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:48575 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:48568 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:48569 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:48570 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:48571 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:48300 DBSERVERIP:3306 ESTABLISHED tcp0 0 WEBSERVERIP:47460 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:42983 DBSERVERIP:3306
Re: Question about performance
from the mysql console: show processlist this will show you ids of all active connections, even the dead ones then, again form the console kill processid On Thu, Feb 17, 2011 at 3:52 PM, Rafael Valenzuela rav...@gmail.com wrote: Hi all; I wonder if there is any tool to Performance Tuning querys. In other know if there is any way to kill connections that take x hours dead (for example 1 hour) -- Mit forever My Blog http://www.redcloverbi.wordpress.com My Faborite Webhttp://ocw.mit.edu/OcwWeb/Electrical-Engineering-and-Computer-Science/index.htm http://www.technologyreview.com/ -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Question about performance
Hi Michael:
Yeah , i think that i do a shell script.something like that.
require 'mysql'
mysql = Mysql.new(ip, user, pass)
processlist = mysql.query(show full processlist)
killed = 0
processlist.each { | process |
mysql.query(KILL #{process[0].to_i})
}
puts #{Time.new} -- killed: #{killed} connections
2011/2/17 Michael Dykman mdyk...@gmail.com
from the mysql console: show processlist
this will show you ids of all active connections, even the dead ones
then, again form the console kill processid
On Thu, Feb 17, 2011 at 3:52 PM, Rafael Valenzuela rav...@gmail.com
wrote:
Hi all;
I wonder if there is any tool to Performance Tuning querys. In other know
if
there is any way to kill connections that take x hours dead (for example
1
hour)
--
Mit forever
My Blog http://www.redcloverbi.wordpress.com
My Faborite Web
http://ocw.mit.edu/OcwWeb/Electrical-Engineering-and-Computer-Science/index.htm
http://www.technologyreview.com/
--
- michael dykman
- mdyk...@gmail.com
May the Source be with you.
--
Mit forever
My Blog http://www.redcloverbi.wordpress.com
My Faborite
Webhttp://ocw.mit.edu/OcwWeb/Electrical-Engineering-and-Computer-Science/index.htm
http://www.technologyreview.com/
Re: Question about performance
Rafael,
You realize that script will kill perfectly well-behaved queries in
mid-flight? If you have so many dead connections that it is interfering
with operation, you have another problem elsewhere..
- md
On Thu, Feb 17, 2011 at 4:16 PM, Rafael Valenzuela rav...@gmail.com wrote:
Hi Michael:
Yeah , i think that i do a shell script.something like that.
require 'mysql'
mysql = Mysql.new(ip, user, pass)
processlist = mysql.query(show full processlist)
killed = 0
processlist.each { | process |
mysql.query(KILL #{process[0].to_i})
}
puts #{Time.new} -- killed: #{killed} connections
2011/2/17 Michael Dykman mdyk...@gmail.com
from the mysql console: show processlist
this will show you ids of all active connections, even the dead ones
then, again form the console kill processid
On Thu, Feb 17, 2011 at 3:52 PM, Rafael Valenzuela rav...@gmail.com
wrote:
Hi all;
I wonder if there is any tool to Performance Tuning querys. In other
know if
there is any way to kill connections that take x hours dead (for example
1
hour)
--
Mit forever
My Blog http://www.redcloverbi.wordpress.com
My Faborite Web
http://ocw.mit.edu/OcwWeb/Electrical-Engineering-and-Computer-Science/index.htm
http://www.technologyreview.com/
--
- michael dykman
- mdyk...@gmail.com
May the Source be with you.
--
Mit forever
My Blog http://www.redcloverbi.wordpress.com
My Faborite
Webhttp://ocw.mit.edu/OcwWeb/Electrical-Engineering-and-Computer-Science/index.htm
http://www.technologyreview.com/
--
- michael dykman
- mdyk...@gmail.com
May the Source be with you.
Re: Question about performance
I am working with mysql since many yaers and i have never
found e reason to kill braindead connections - what
benefit do you think to have from such actions instead
looking why there are hanging ones?
kill a connection of postfix and some user gets
temorary lookup error, php-scripts are closing
connections after the request
if you have long living connections from php
you have persistent connections active - so
why do you not disable the feature if you do
not like it instead writnign dirty scripts?
Am 17.02.2011 22:16, schrieb Rafael Valenzuela:
Hi Michael:
Yeah , i think that i do a shell script.something like that.
require 'mysql'
mysql = Mysql.new(ip, user, pass)
processlist = mysql.query(show full processlist)
killed = 0
processlist.each { | process |
mysql.query(KILL #{process[0].to_i})
}
puts #{Time.new} -- killed: #{killed} connections
signature.asc
Description: OpenPGP digital signature
