c++ - Huge performance difference query mysql database with same golang snippet -
i reimplement project golang recently. project implemented c++. when finished code , have performance test. i'm shocked result. when query database c++, can 130 million rows result in 5 mins. golang, it's 45 mins. when separate code project , build code snippet, it's finished in 2mins. why have huge difference performance result?
my code snippet : https://gist.github.com/pyanfield/2651d23311901b33c5723b7de2364148
package main import ( "database/sql" "fmt" "runtime" "strconv" "time" _ "github.com/go-sql-driver/mysql" ) func main() { runtime.gomaxprocs(runtime.numcpu()) // defer profile.start(profile.cpuprofile, profile.profilepath(".")).stop() dbread, err := connectdb("test:test@tcp(127.0.0.1:3306)/test_oltp?charset=utf8&readtimeout=600s&writetimeout=600s") if err != nil { fmt.printf("error happend when connecting db. %s\n", err.error()) return } defer dbread.close() dbread.setmaxidleconns(0) dbread.setmaxopenconns(100) query := fmt.sprintf("where company_id in (11,22,33,44,55,66,77,88,99,00,111,222,333,4444,555,666,777,888,999)") relations := getrelations(dbread, query) } func connectdb(addr string) (*sql.db, error) { db, err := sql.open("mysql", addr) if err != nil { return nil, err } if err = db.ping(); err != nil { return nil, err } return db, nil } type relation struct { childid int64 parentid int64 } func getrelations(db *sql.db, string)[]relation { begin := time.now() var err error var rows *sql.rows query := fmt.sprintf("select `child_id`, `parent_id` `test_relations` %s", where) rows, err = db.query(query) if err != nil { fmt.println("query error:", err.error()) return nil } defer rows.close() columns, err := rows.columns() buffer := make([]sql.rawbytes, len(columns)) scanargs := make([]interface{}, len(buffer)) := range scanargs { scanargs[i] = &buffer[i] } relations := []relation{} relation := relation{} rows.next() { if err = rows.scan(scanargs...); err != nil { fmt.println("scan:", err.error()) return nil } relation.parentid, _ = strconv.parseint(string(buffer[1]), 10, 64) relation.childid, _ = strconv.parseint(string(buffer[0]), 10, 64) relations = append(relations, relation) } if err = rows.err(); err != nil { fmt.println("next error:", err.error()) return nil } fmt.printf(">>> getrelations cost: %s\n", time.since(begin).string()) // output :>>> getrelations cost:1m45.791047s return relations // len(relations): 131123541 } update: go version 1.6. cpu profile got below: code snippet profile top20:
75.67s of 96.82s total (78.16%) dropped 109 nodes (cum <= 0.48s) showing top 20 nodes out of 82 (cum >= 12.04s) flat flat% sum% cum cum% 11.85s 12.24% 12.24% 11.85s 12.24% runtime.memmove 10.28s 10.62% 22.86% 20.01s 20.67% runtime.mallocgc 5.82s 6.01% 28.87% 5.82s 6.01% strconv.parseuint 5.79s 5.98% 34.85% 5.79s 5.98% runtime.futex 3.42s 3.53% 38.38% 10.28s 10.62% github.com/go-sql-driver/mysql.(*buffer).readnext 3.42s 3.53% 41.91% 6.38s 6.59% runtime.scang 3.37s 3.48% 45.39% 36.97s 38.18% github.com/go-sql-driver/mysql.(*textrows).readrow 3.37s 3.48% 48.87% 3.37s 3.48% runtime.memclr 3.20s 3.31% 52.18% 3.20s 3.31% runtime.heapbitssettype 3.02s 3.12% 55.30% 7.36s 7.60% database/sql.convertassign 2.96s 3.06% 58.36% 3.02s 3.12% runtime.(*mspan).sweep.func1 2.53s 2.61% 60.97% 2.53s 2.61% runtime._externalcode 2.39s 2.47% 63.44% 2.96s 3.06% runtime.readgstatus 2.24s 2.31% 65.75% 8.06s 8.32% strconv.parseint 2.21s 2.28% 68.03% 5.24s 5.41% runtime.heapbitssweepspan 2.15s 2.22% 70.25% 7.68s 7.93% runtime.rawstring 2.06s 2.13% 72.38% 3.18s 3.28% github.com/go-sql-driver/mysql.readlengthencodedstring 1.95s 2.01% 74.40% 12.23s 12.63% github.com/go-sql-driver/mysql.(*mysqlconn).readpacket 1.83s 1.89% 76.29% 79.42s 82.03% main.relations 1.81s 1.87% 78.16% 12.04s 12.44% runtime.slicebytetostring the project cpu profile top20:
(pprof) top20 38.71mins of 42.82mins total (90.40%) dropped 334 nodes (cum <= 0.21mins) showing top 20 nodes out of 76 (cum >= 1.35mins) flat flat% sum% cum cum% 12.02mins 28.07% 28.07% 12.48mins 29.15% runtime.addspecial 5.95mins 13.89% 41.96% 15.08mins 35.21% runtime.pcvalue 5.26mins 12.29% 54.25% 5.26mins 12.29% runtime.readvarint 2.60mins 6.08% 60.32% 7.87mins 18.37% runtime.step 1.98mins 4.62% 64.94% 19.45mins 45.43% runtime.gentraceback 1.65mins 3.86% 68.80% 1.65mins 3.86% runtime/internal/atomic.xchg 1.57mins 3.66% 72.46% 2.93mins 6.84% runtime.(*mspan).sweep 1.52mins 3.54% 76.01% 1.78mins 4.15% runtime.findfunc 1.41mins 3.30% 79.31% 1.42mins 3.31% runtime.markrootspans 1.13mins 2.64% 81.95% 1.13mins 2.64% runtime.(*fixalloc).alloc 0.64mins 1.50% 83.45% 0.64mins 1.50% runtime.duffcopy 0.46mins 1.08% 84.53% 0.46mins 1.08% runtime.findmoduledatap 0.44mins 1.02% 85.55% 0.44mins 1.02% runtime.fastrand1 0.42mins 0.97% 86.52% 15.49mins 36.18% runtime.funcspdelta 0.38mins 0.89% 87.41% 36.02mins 84.13% runtime.mallocgc 0.30mins 0.7% 88.12% 0.78mins 1.83% runtime.scanobject 0.26mins 0.6% 88.72% 0.32mins 0.74% runtime.stkbucket 0.26mins 0.6% 89.32% 0.26mins 0.6% runtime.memmove 0.23mins 0.55% 89.86% 0.23mins 0.55% runtime.heapbitsforobject 0.23mins 0.53% 90.40% 1.35mins 3.15% runtime.lock
i got answer , want share it. caused mistake. ago, tried add memory profile , set runtime. memprofilerate=1 in init method. forgot reset reasonable value. ignored method when checked code every time. after removing setting project, returns normal, , spend 5~6mins query these 130m datas. speed pretty close c++ version. advise please when set runtime.memprofilerate=1 unless make sure want that, , remember reset back.
Comments
Post a Comment