将SQL结果尽快转换为JSON。

huangapple
go评论124阅读模式
英文:

SQL result to JSON as fast as possible

问题

我正在尝试将Go内置的SQL结果转换为JSON。我正在使用goroutines，但遇到了问题。

基本问题：

有一个非常大的数据库，大约有20万个用户，我必须通过TCP套接字在基于微服务的系统中为它们提供服务。从数据库获取用户花费了20毫秒，但使用当前解决方案将这批数据转换为JSON花费了10秒。这就是为什么我想使用goroutines的原因。

使用Goroutines的解决方案：

  1. func getJSON(rows *sql.Rows, cnf configure.Config) ([]byte, error) {
  2.     log := logan.Log{
  3.         Cnf: cnf,
  4.     }
  6.     cols, _ := rows.Columns()
  8.     defer rows.Close()
  10.     done := make(chan struct{})
  11.     go func() {
  12.         defer close(done)
  13.         for result := range resultChannel {
  14.             results = append(
  15.                 results,
  16.                 result,
  17.             )
  18.         }
  19.     }()
  21.     wg.Add(1)
  22.     go func() {
  23.         for rows.Next() {
  24.             wg.Add(1)
  25.             go handleSQLRow(cols, rows)
  26.         }
  27.         wg.Done()
  28.     }()
  30.     go func() {
  31.         wg.Wait()
  32.         defer close(resultChannel)
  33.     }()
  35.     <-done
  37.     s, err := json.Marshal(results)
  38.     results = []resultContainer{}
  39.     if err != nil {
  40.         log.Context(1).Error(err)
  41.     }
  42.     rows.Close()
  43.     return s, nil
  44. }
  46. func handleSQLRow(cols []string, rows *sql.Rows) {
  47.     defer wg.Done()
  48.     result := make(map[string]string, len(cols))
  49.     fmt.Println("asd -> " + strconv.Itoa(counter))
  50.     counter++
  51.     rawResult := make([][]byte, len(cols))
  52.     dest := make([]interface{}, len(cols))
  54.     for i := range rawResult {
  55.         dest[i] = &rawResult[i]
  56.     }
  57.     rows.Scan(dest...) // GET PANIC
  58.     for i, raw := range rawResult {
  59.         if raw == nil {
  60.             result[cols[i]] = ""
  61.         } else {
  62.             fmt.Println(string(raw))
  63.             result[cols[i]] = string(raw)
  64.         }
  65.     }
  66.     resultChannel <- result
  67. }

这个解决方案给我一个恐慌，错误信息如下：

  1. panic: runtime error: invalid memory address or nil pointer dereference
  2. [signal SIGSEGV: segmentation violation code=0x1 addr=0x0 pc=0x45974c]
  4. goroutine 408 [running]:
  5. panic(0x7ca140, 0xc420010150)
  6.     /usr/lib/golang/src/runtime/panic.go:500 +0x1a1
  7. database/sql.convertAssign(0x793960, 0xc420529210, 0x7a5240, 0x0, 0x0, 0x0)
  8.     /usr/lib/golang/src/database/sql/convert.go:88 +0x1ef1
  9. database/sql.(*Rows).Scan(0xc4203e4060, 0xc42021fb00, 0x44, 0x44, 0x44, 0x44)
  10.     /usr/lib/golang/src/database/sql/sql.go:1850 +0xc2
  11. github.com/PumpkinSeed/zerodb/operations.handleSQLRow(0xc420402000, 0x44, 0x44, 0xc4203e4060)
  12.     /home/loow/gopath/src/github.com/PumpkinSeed/zerodb/operations/operations.go:290 +0x19c
  13. created by github.com/PumpkinSeed/zerodb/operations.getJSON.func2
  14.     /home/loow/gopath/src/github.com/PumpkinSeed/zerodb/operations/operations.go:258 +0x91
  15. exit status 2

当前的解决方案虽然有效，但花费太多时间：

  1. func getJSON(rows *sql.Rows, cnf configure.Config) ([]byte, error) {
  2.     log := logan.Log{
  3.         Cnf: cnf,
  4.     }
  5.     var results []resultContainer
  6.     cols, _ := rows.Columns()
  7.     rawResult := make([][]byte, len(cols))
  8.     dest := make([]interface{}, len(cols))
  10.     for i := range rawResult {
  11.         dest[i] = &rawResult[i]
  12.     }
  14.     defer rows.Close()
  16.     for rows.Next() {
  18.         result := make(map[string]string, len(cols))
  19.         rows.Scan(dest...)
  20.         for i, raw := range rawResult {
  21.             if raw == nil {
  23.                 result[cols[i]] = ""
  25.             } else {
  26.                 result[cols[i]] = string(raw)
  27.             }
  28.         }
  30.         results = append(results, result)
  31.     }
  32.     s, err := json.Marshal(results)
  33.     if err != nil {
  34.         log.Context(1).Error(err)
  35.     }
  36.     rows.Close()
  37.     return s, nil
  38. }

问题：

为什么goroutine解决方案会给我一个错误，而不是明显的恐慌，因为前面大约200个goroutine都正常运行？

更新：

原始工作解决方案的性能测试：

  1. INFO[0020] setup taken -> 3.149124658s                   file=operations.go func=operations.getJSON line=260 service="Database manager" ts="2017-04-02 19:45:27.132881211 +0100 BST"
  2. INFO[0025] toJSON taken -> 5.317647046s                  file=operations.go func=operations.getJSON line=263 service="Database manager" ts="2017-04-02 19:45:32.450551417 +0100 BST"

将SQL映射为JSON花费了3秒，将JSON转换为JSON花费了5秒。

英文:

I'm trying to transform the Go built-in sql result to JSON. I'm using goroutines for that but I got problems.

The base problem:

There is a really big database with around 200k user and I have to serve them through tcp sockets in a microservice based system. To get the users from the database spent 20ms but transform this bunch of data to JSON spend 10 seconds with the current solution. This is why I want to use goroutines.

Solution with Goroutines:

  1. func getJSON(rows *sql.Rows, cnf configure.Config) ([]byte, error) {
  2. log := logan.Log{
  3. Cnf: cnf,
  4. }
  5. cols, _ := rows.Columns()
  6. defer rows.Close()
  7. done := make(chan struct{})
  8. go func() {
  9. defer close(done)
  10. for result := range resultChannel {
  11. results = append(
  12. results,
  13. result,
  14. )
  15. }
  16. }()
  17. wg.Add(1)
  18. go func() {
  19. for rows.Next() {
  20. wg.Add(1)
  21. go handleSQLRow(cols, rows)
  22. }
  23. wg.Done()
  24. }()
  25. go func() {
  26. wg.Wait()
  27. defer close(resultChannel)
  28. }()
  29. &lt;-done
  30. s, err := json.Marshal(results)
  31. results = []resultContainer{}
  32. if err != nil {
  33. log.Context(1).Error(err)
  34. }
  35. rows.Close()
  36. return s, nil
  37. }
  38. func handleSQLRow(cols []string, rows *sql.Rows) {
  39. defer wg.Done()
  40. result := make(map[string]string, len(cols))
  41. fmt.Println(&quot;asd -&gt; &quot; + strconv.Itoa(counter))
  42. counter++
  43. rawResult := make([][]byte, len(cols))
  44. dest := make([]interface{}, len(cols))
  45. for i := range rawResult {
  46. dest[i] = &amp;rawResult[i]
  47. }
  48. rows.Scan(dest...) // GET PANIC
  49. for i, raw := range rawResult {
  50. if raw == nil {
  51. result[cols[i]] = &quot;&quot;
  52. } else {
  53. fmt.Println(string(raw))
  54. result[cols[i]] = string(raw)
  55. }
  56. }
  57. resultChannel &lt;- result
  58. }

This solution give me a panic with the following message:

  1. panic: runtime error: invalid memory address or nil pointer dereference
  2. [signal SIGSEGV: segmentation violation code=0x1 addr=0x0 pc=0x45974c]
  3. goroutine 408 [running]:
  4. panic(0x7ca140, 0xc420010150)
  5. /usr/lib/golang/src/runtime/panic.go:500 +0x1a1
  6. database/sql.convertAssign(0x793960, 0xc420529210, 0x7a5240, 0x0, 0x0, 0x0)
  7. /usr/lib/golang/src/database/sql/convert.go:88 +0x1ef1
  8. database/sql.(*Rows).Scan(0xc4203e4060, 0xc42021fb00, 0x44, 0x44, 0x44, 0x44)
  9. /usr/lib/golang/src/database/sql/sql.go:1850 +0xc2
  10. github.com/PumpkinSeed/zerodb/operations.handleSQLRow(0xc420402000, 0x44, 0x44, 0xc4203e4060)
  11. /home/loow/gopath/src/github.com/PumpkinSeed/zerodb/operations/operations.go:290 +0x19c
  12. created by github.com/PumpkinSeed/zerodb/operations.getJSON.func2
  13. /home/loow/gopath/src/github.com/PumpkinSeed/zerodb/operations/operations.go:258 +0x91
  14. exit status 2

The current solution which is working but spend too much time:

  1. func getJSON(rows *sql.Rows, cnf configure.Config) ([]byte, error) {
  2. log := logan.Log{
  3. Cnf: cnf,
  4. }
  5. var results []resultContainer
  6. cols, _ := rows.Columns()
  7. rawResult := make([][]byte, len(cols))
  8. dest := make([]interface{}, len(cols))
  9. for i := range rawResult {
  10. dest[i] = &amp;rawResult[i]
  11. }
  12. defer rows.Close()
  13. for rows.Next() {
  14. result := make(map[string]string, len(cols))
  15. rows.Scan(dest...)
  16. for i, raw := range rawResult {
  17. if raw == nil {
  18. result[cols[i]] = &quot;&quot;
  19. } else {
  20. result[cols[i]] = string(raw)
  21. }
  22. }
  23. results = append(results, result)
  24. }
  25. s, err := json.Marshal(results)
  26. if err != nil {
  27. log.Context(1).Error(err)
  28. }
  29. rows.Close()
  30. return s, nil
  31. }

Question:

Why the goroutine solution give me an error, where it is not an obvious panic, because the first ~200 goroutine running properly?!

UPDATE

Performance test for the original working solution:

  1. INFO[0020] setup taken -&gt; 3.149124658s                   file=operations.go func=operations.getJSON line=260 service=&quot;Database manager&quot; ts=&quot;2017-04-02 19:45:27.132881211 +0100 BST&quot;
  2. INFO[0025] toJSON taken -&gt; 5.317647046s                  file=operations.go func=operations.getJSON line=263 service=&quot;Database manager&quot; ts=&quot;2017-04-02 19:45:32.450551417 +0100 BST&quot;

The sql to map is 3 sec and to json is 5 sec.

答案1

得分: 0

Go协程在像JSON编组这样的CPU绑定操作上不会提高性能。你需要的是一个更高效的JSON编组器。虽然我没有使用过，但有一些可用的选项。简单地在Google上搜索“更快的JSON编组”将会得到很多结果。一个受欢迎的选项是ffjson。我建议从那里开始。

英文:

Go routines won't improve performance on CPU-bound operations like JSON marshaling. What you need is a more efficient JSON marshaler. There are some available, although I haven't used any. A simple Google search for 'faster JSON marshaling' will turn up many results. A popular one is ffjson. I suggest starting there.

huangapple
  • 本文由 发表于 2017年4月2日 22:52:17
  • 转载请务必保留本文链接：https://go.coder-hub.com/43169737.html
  • go
  • goroutine
  • json
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定