Stop, move files, edit my.

 
Ailes: What would be the best approach for me?

Fansler: Dr-007: why between? you say avoid collision. avoid collision with what?

Darbouze: WHERE INET_ATON”193.235.19.255″ BETWEEN INET_ATO****tart AND INET_ATONipEnd;

Mullice: Oops, i thought it was one rule

Fansler: Probably you . yes that

Crummett: Avoid collision with other ip ranges

Piceno: But the INET_ATON, is it fast?

Soland: Http://stackoverflow.com/questions/24179437/mysql-check-if-an-ip-address-is-in-range

Washburn: This ring0 onthere says in a comment; Very heavy since no index will be used. Better to have IPs as numerical values in the DB, then create an index on them

Moeller: But jaydee says “No. Use the INET_ATON function to do the conversion.”

Fansler: INET_ATON should be quite fast

Fansler: So you are storing IP addresses in the database as strings?

Howick: But i’m planning to do this

Morrish: It keeps it all readable

Scobee: Onthere they say to convert the IP to a bigint

Fansler: I would convert the dotted ntoation to a number and store the nunber in the db

Fansler: Then convert only when you are showing it to a person

Fansler: The number means that the index is useful.

Eichelmann: I know indexed tables are faster, if you mean that

Fansler: Numbers can be compared. dotted notation cannot be compared,

Fansler: 010.001.10.11 is equal to 10.1.10.11, but they don’t compare the same.

Kruppenbacher: I’m googling for the opposite of INET ATON now

Albertsen: For easy back conversion while selecting to show

Stamey: Allright, thanks! ill implement that

Terpening: Better just do it the right way inmediately

Yanosky: Hi. I have a “cat dump.sql mysql” containing a table with ~23Mio rows. Dump takes only about 5mins. But when sourcing there are frequent and long “stop” w/o any CPU or IO load on the system. Any ideas?

Deason: Asdfoo: did you “show processlist” during a stall?

Montgomery: Asdfoo: See http://hashmysql.org/wiki/InnoDB_Import_Speed for common approaches to speed up imports using the InnoDB engine

Tiedeman: Deason: yes, show processlist just shown an “insert into . values .” which is hanging way too long around.

Deason: Asdfoo: and the state?

Deason: Asdfoo: the values change each time you run “show processlist” not full processlist, too much to read

Newton: Fansler, nice. now its pretty easy to add a bunch of ips to a range aswell.

Lanterman: Deason: state is “update”. the values did not change for very long.

Penttila: Asdfoo: what bothers me is that it stops generating system load for long periods.

Deason: Asdfoo: sounds like it might be an i/o stall, did you check the innodb_log_file_size already?

Long: Deason: i’m just reading the page you have provided.

Harrower: Deason: i/o stall – you mean on the FS level? like writebuffer flush?

Cornet: Deason: innodb_log_file_size is 5242880.

Deason: Asdfoo: and there is the problem most likely. which version of mysql?

Burdess: Deason: shall I increase the innodb_log_file_size? permanently or just for this import?

Deason: Asdfoo: you need to stop mysqld, mv ib_logfile* out of datadir, then increase innodb_log_file_size to 256M in my.cnf, then start mysqld

Kelemen: Deason: ok. thanks a lot.

Deason: Http://pastie.org/private/hhzeb1tmysnqsrlstldfya hm, guess i broke something

Egerton: The log sequence number in ibdata files does not match

Frierson: The log sequence number in the ib_logfiles!

Deason: Not usually, did you stop mysqld first?

Fullard: Stop, move files, edit my.cnf, start again.