What is the fastest way to log data to a MySQL sever within a PHP script? -

preamble:

this post not how use php , mysql, or how write script logs information database. question meant work out best solution logging information on fastest way mysql database using php script! it's micro-improvements. thank you!

---

situation:

i've got php script working on server, delivers content fast customers. mysql server available on machine too, weapon of choice. track information requests. therefore, need log information somehow, , think best solution here flat database table information can stored.

but need keep time low possible log have @ least no impact on response time, on many simultaneous requests. system has between 100k , 1m requests per day. of them between working hours (8 - 18 o'clock). actual response time ~3-5ms, 1ms mean increase of 20%.

the database table i've created flat , has no extras. index on table on id column, primary field auto_increment, because have unique identifier further jobs (later on more this). post , further examples, assume table structure this:

| id | d1  | d2  | d3  | |----|-----|-----|-----| | 1  | foo | bar | baz | | 2  | foo | bar | baz | | 3  | foo | bar | baz | | ... 

the processing of recorded data done script later. there no need further actions data, it's storage itself. table grow 3m rows.

---

database thoughts:

first of asked myself right database engine. first thought was, memory fastest, lose entries whenever server going down (i got weekly maintenance window installing updates , restarting system too). should not ever happen. came myisam , innodb. 1 take?

so made simple benchmark, see if there big differences between these 2 engines. i've created 3 tables, each engine on development machine , created simple script, calculating times.

create table `log_myisam` (     `id` int(10) unsigned not null auto_increment,     `d1` varchar(3) not null,     `d2` varchar(3) not null,     `d3` varchar(3) not null,     primary key (`id`) ) engine=myisam;  create table `log_innodb` (     `id` int(10) unsigned not null auto_increment,     `d1` varchar(3) not null,     `d2` varchar(3) not null,     `d3` varchar(3) not null,     primary key (`id`) ) engine=innodb;  create table `log_memory` (     `id` int(10) unsigned not null auto_increment,     `d1` varchar(3) not null,     `d2` varchar(3) not null,     `d3` varchar(3) not null,     primary key (`id`) ) engine=memory; 

my test script enters 1,000,000 flat lines tables. afterwards calculate average out of consumed time. here's benchmark script:

foreach( array("myisam", "innodb", "memory") $table ) {     $query = "insert log_{$table} set d1='foo',d2='bar',d3='baz'";      $start = microtime(true);     for( $i = 0; $i < 1000000; $i++ ) {         $sql->query($query);     }     $end = microtime(true);      $results[$table] = $end - $start; } 

as expected, memory table far fastest one. myisam every time faster innodb. makes sense me, because myisam leaks support things foreign keys , transactions, there less functional overhead in engine.

what surprised me fact, memory table twice size other tables. @ point i'm not sure why. results:

                  | innodb    | myisam    | memory    | |-----------------|-----------|-----------|-----------| | time insert | 133.92 s  | 101.00 s  | 79.351 s  | | avg. per entry  | 0.1392 ms | 0.1010 ms | 0.0794 ms | | time saved in % | 0.0000 %  | 24.585 %  | 21.436 %  | | table size      | 35.6   mb | 29.9   mb | 55.9   mb | 

but far know, myisam locks table while executing insert. problematic on many simultaneous requests. don't know how benchmark this.

another question me is, how index of id column affect run time. helpful or slow down time. let benchmark script run again, after removed primary index , auto_increment option id column.

                  | innodb    | myisam    | memory    | |-----------------|-----------|-----------|-----------| | time id    | 133.92 s  | 101.00 s  | 79.351 s  | | avg. id    | 0.1392 ms | 0.1010 ms | 0.0794 ms | |-----------------|-----------|-----------|-----------| | time without id | 131.88 s  | 91.868 s  | 73.014 s  | | avg. without id | 0.1319 ms | 0.0919 ms | 0.0701 ms | 

myisam seems take advantage of dropping index. range of 2 results not width spreaded expected.

---

query thoughts:

the query has been kept simple. not know how improve further.

insert log_myisam set     d1 = 'foo',     d2 = 'bar',     d3 = 'baz' 

---

php script thoughts:

one thing cost time, connection itself. because of that, go persistent connection. i've used mysqli of course. there difference between procedural or oop usage? i've made simple benchmark again.

$startprocedual = microtime(true); for( $i = 0; $i < 1000; $i++ ) {     $sql = mysqli_connect('localhost', 'root', '', 'benchmark');     mysqli_close($sql);     unset($sql); } $endprocedual = microtime(true);  $startoop = microtime(true); for( $i = 0; $i < 1000; $i++ ) {     $sql = new mysqli('localhost', 'root', '', 'benchmark');     $sql->close();     unset($sql); } $endoop = microtime(true); 

without persistent connection difference quite visible! oop style bit faster, , 1.000 connections.

procedural: 0.326150 s oop:        0.256580 s 

with persistent connection enabled, both versions same. whole connection time dropped 1 third of normal one. seems best way go persistent connection here.

procedural: 0.093201 s oop:        0.092088 s 

---

my temporary conclusion:

actually i'm @ logging time of 0.204 ms (as avg. of 100.000 inserts).

as following:

• use myisam because it's best mix of speed, security , size
• try drop id column because of index more speed
• the query not improved anymore (imo)
• use persistent connection mysqli oop style

but there open questions me. did made right decisions? myisam blocking execution? there way use memory? persistent connection have side effects, slowing down through higher memory usage? ...

i appreciate ideas or tips faster logging. maybe i'm totally wrong @ points. please let me know if have experiences kind of things.

thanks in advance!

regardless of engine, , glad not touch hot potato, fastest way far use load data infile csv files create. so, csv created in append mode traffic comes in. have mechanism close 1 version down, new incrementor, , start fresh. perhaps hourly. files may when done

/tmp/csvfiles/traffic_20160725_00.csv ... /tmp/csvfiles/traffic_20160725_23.csv 

that got 24 hours of traffic. upload described above, whenever feel it, rest assured of 2 things:

• you have backup
• you outperform insert call (by factors blow mind away).

an added bonus csv, let's call them text files, pretty ready rock , roll no sql solution when decide might belong anyway.

note: big fan of events. have 3 links off profile page them examples. however, events , stored procs forbidden use load data infile. have separate agents that. agents nice because on time naturally add different functionality them. combo of events , agents, there never need use cron or other o/s schedulers.

the takeway: never use insert this.