Golang DB.SetMaxIdleConns方法代碼示例

func (s *sphinxPools) newConn() (*SphinxDB, error) {
	var (
		err error
		db  *sql.DB
		o   orm.Ormer
	)

	if db, err = sql.Open("sphinx", "root:[email protected]("+setting.SphinxHost+")/?loc=UTC"); err != nil {
		return nil, err
	}

	if err = db.Ping(); err != nil {
		db.Close()
		return nil, err
	}

	db.SetMaxIdleConns(1)
	db.SetMaxOpenConns(2)

	o, err = orm.NewOrmWithDB("sphinx", "sphinx", db)
	if err != nil {
		return nil, err
	}

	sdb := &SphinxDB{
		alive: true,
		pools: s,
		db:    db,
		orm:   o,
	}
	return sdb, nil
}

func postgresSetup(db *sql.DB) error {
	var err error

	db.SetMaxIdleConns(4)
	db.SetMaxOpenConns(8)

	_, err = db.Exec(`CREATE TABLE IF NOT EXISTS Devices (
		DeviceID CHAR(63) NOT NULL PRIMARY KEY,
		Seen TIMESTAMP NOT NULL
	)`)
	if err != nil {
		return err
	}

	var tmp string
	row := db.QueryRow(`SELECT 'DevicesDeviceIDIndex'::regclass`)
	if err = row.Scan(&tmp); err != nil {
		_, err = db.Exec(`CREATE INDEX DevicesDeviceIDIndex ON Devices (DeviceID)`)
	}
	if err != nil {
		return err
	}

	row = db.QueryRow(`SELECT 'DevicesSeenIndex'::regclass`)
	if err = row.Scan(&tmp); err != nil {
		_, err = db.Exec(`CREATE INDEX DevicesSeenIndex ON Devices (Seen)`)
	}
	if err != nil {
		return err
	}

	_, err = db.Exec(`CREATE TABLE IF NOT EXISTS Addresses (
		DeviceID CHAR(63) NOT NULL,
		Seen TIMESTAMP NOT NULL,
		Address VARCHAR(2048) NOT NULL
	)`)
	if err != nil {
		return err
	}

	row = db.QueryRow(`SELECT 'AddressesDeviceIDSeenIndex'::regclass`)
	if err = row.Scan(&tmp); err != nil {
		_, err = db.Exec(`CREATE INDEX AddressesDeviceIDSeenIndex ON Addresses (DeviceID, Seen)`)
	}
	if err != nil {
		return err
	}

	row = db.QueryRow(`SELECT 'AddressesDeviceIDAddressIndex'::regclass`)
	if err = row.Scan(&tmp); err != nil {
		_, err = db.Exec(`CREATE INDEX AddressesDeviceIDAddressIndex ON Addresses (DeviceID, Address)`)
	}
	if err != nil {
		return err
	}

	return nil
}

// Init must be called once before DB is used.
//
// Since it used glog, make sure it is also called after flag.Parse is called.
func (c *DatabaseConfig) NewVersionedDB() (*VersionedDB, error) {
	if err := c.validate(); err != nil {
		return nil, err
	}

	// If there is a connection string then connect to the MySQL server.
	// This is for testing only. In production we get the relevant information
	// from the metadata server.
	var err error
	var DB *sql.DB = nil

	glog.Infoln("Opening SQL database.")
	DB, err = sql.Open(DEFAULT_DRIVER, c.MySQLString())
	if err != nil {
		return nil, fmt.Errorf("Failed to open connection to SQL server: %v", err)
	}

	glog.Infoln("Sending Ping.")
	if err := DB.Ping(); err != nil {
		return nil, fmt.Errorf("Failed to ping SQL server: %v", err)
	}

	// As outlined in this comment:
	// https://github.com/go-sql-driver/mysql/issues/257#issuecomment-48985975
	// We can remove this once we have determined it's not necessary.
	DB.SetMaxIdleConns(0)
	DB.SetMaxOpenConns(200)

	result := &VersionedDB{
		DB:             DB,
		migrationSteps: c.MigrationSteps,
	}

	// Make sure the migration table exists.
	if err := result.checkVersionTable(); err != nil {
		return nil, fmt.Errorf("Attempt to create version table returned: %v", err)
	}
	glog.Infoln("Version table OK.")

	// Ping the database occasionally to keep the connection fresh.
	go func() {
		c := time.Tick(1 * time.Minute)
		for _ = range c {
			if err := result.DB.Ping(); err != nil {
				glog.Warningln("Database failed to respond:", err)
			}
			glog.Infof("db: Successful ping")
		}
	}()

	return result, nil
}

func NewConnection(cfg Config) (*gorp.DbMap, error) {
	u, err := url.Parse(cfg.DSN)
	if err != nil {
		return nil, fmt.Errorf("parse DSN: %v", err)
	}
	var (
		db      *sql.DB
		dialect gorp.Dialect
	)
	switch u.Scheme {
	case "postgres":
		db, err = sql.Open("postgres", cfg.DSN)
		if err != nil {
			return nil, err
		}
		db.SetMaxIdleConns(cfg.MaxIdleConnections)
		db.SetMaxOpenConns(cfg.MaxOpenConnections)
		dialect = gorp.PostgresDialect{}
	case "sqlite3":
		db, err = sql.Open("sqlite3", u.Host)
		if err != nil {
			return nil, err
		}
		if u.Host == ":memory:" {
			// NOTE(ericchiang): sqlite3 coordinates concurrent clients through file locks.
			// In memory databases do not support concurrent calls. Limit the number of
			// open connections to 1.
			//
			// See: https://www.sqlite.org/faq.html#q5
			db.SetMaxOpenConns(1)
		}
		dialect = gorp.SqliteDialect{}
	default:
		return nil, errors.New("unrecognized database driver")
	}

	dbm := gorp.DbMap{Db: db, Dialect: dialect}

	for _, t := range tables {
		tm := dbm.AddTableWithName(t.model, t.name).SetKeys(t.autoinc, t.pkey...)
		for _, unique := range t.unique {
			cm := tm.ColMap(unique)
			if cm == nil {
				return nil, fmt.Errorf("no such column: %q", unique)
			}
			cm.SetUnique(true)
		}
	}
	return &dbm, nil
}

func InitDB(cfg *Config) (err error) {
	if cfg.DBType == "sqlite" {
		cfg.DBType = "sqlite3"
	}
	dsn := cfg.DSN()
	if dsn == "" {
		return fmt.Errorf("unsupported database: `%s`", cfg.DBType)
	}

	var raw_db *sql.DB
	if raw_db, err = sql.Open(cfg.DBType, dsn); err != nil {
		return
	}
	if cfg.MaxIdleConns > 0 {
		raw_db.SetMaxIdleConns(cfg.MaxIdleConns)
	}
	if cfg.MaxOpenConns > 0 {
		raw_db.SetMaxOpenConns(cfg.MaxOpenConns)
	}

	var dialect gorp.Dialect
	switch cfg.DBType {
	case "mysql":
		dialect = gorp.MySQLDialect{Engine: "InnoDB", Encoding: "UTF-8"}
	case "postgres":
		dialect = gorp.PostgresDialect{}
	case "sqlite3":
		dialect = gorp.SqliteDialect{}
	}

	db = &gorp.DbMap{Db: raw_db, Dialect: dialect}
	if cfg.SqlTrace {
		db.TraceOn("[db]", logger{})
	}
	_tableLk.Lock()
	_tableFreezed = true
	_tableLk.Unlock()

	for _, table := range _tables {
		name, primary, model := table.Meta()
		t := db.AddTableWithName(model, name)
		if primary != "" {
			t.SetKeys(true, primary)
		}
	}
	return nil
}

func InitMysqlConnPool() error {
	var err error
	var conn *sql.DB
	conn, err = sql.Open("mysql", fmt.Sprintf("%s:%[email protected](%s)/%s?charset=utf8&parseTime=true&loc=Local",
		GlobalConfig.MYSQL_USER, GlobalConfig.MYSQL_PASSWORD, GlobalConfig.MYSQL_ADDR, GlobalConfig.MYSQL_DBNAME))
	if err != nil {
		return err
	}
	err = conn.Ping()
	if err != nil {
		return err
	}
	conn.SetMaxIdleConns(GlobalConfig.MYSQL_MAX_IDLE_CONN)
	conn.SetMaxOpenConns(GlobalConfig.MYSQL_MAX_CONN)
	mydb = &db{conn}
	return nil
}

func (dbSeq *SequenceDB) Open() (err error) {
	var db *sql.DB
	db, err = sql.Open("mysql", conf.Conf.SequenceDB.DSN)
	if err != nil {
		log.Printf("sequence db open error. %v", err)
		return err
	}

	err = db.Ping()
	if err != nil {
		log.Printf("sequence db ping error. %v", err)
		return err
	}

	db.SetMaxIdleConns(conf.Conf.SequenceDB.MaxIdleConns)
	db.SetMaxOpenConns(conf.Conf.SequenceDB.MaxOpenConns)

	dbSeq.db = db
	return nil
}

func Db() (*sql.DB, error) {
	var (
		db  *sql.DB
		err error
	)

	config, err := BuildFromFile("../config/database.yml")
	if err != nil {
		log.Fatal(err)
	}

	db, err = sql.Open("postgres", "user="+config.DbUser+" password="+config.DbPassword+" dbname="+config.DbName+" sslmode=disable")
	if err != nil {
		return nil, err
	}

	db.SetMaxIdleConns(10)
	db.SetMaxOpenConns(20)

	return db, nil
}

func (this *Conns) initSql(host, port, dbname, user, pass string, maxOpenConns, maxIdleConns int) (*sql.DB, error) {
	slimSqlLog("Init", "user:"+user+" pass:"+pass+" host:"+host+" port:"+port+" dbname:"+dbname)
	var sqlDB *sql.DB
	var err error
	sqlDB, err = sql.Open("mysql", user+":"+pass+"@tcp("+host+":"+port+")/"+dbname+"?charset=utf8")
	if err != nil {
		return nil, err
	}
	if maxOpenConns > 0 {
		sqlDB.SetMaxOpenConns(maxOpenConns)
	} else {
		sqlDB.SetMaxOpenConns(2000)
	}
	if maxIdleConns > 0 {
		sqlDB.SetMaxIdleConns(maxIdleConns)
	} else {
		sqlDB.SetMaxIdleConns(1000)
	}
	err = sqlDB.Ping()
	if err != nil {
		return nil, err
	}
	return sqlDB, nil
}

func main() {
	debugserver.AddFlags(flag.CommandLine)
	lagerflags.AddFlags(flag.CommandLine)
	etcdFlags := AddETCDFlags(flag.CommandLine)
	encryptionFlags := encryption.AddEncryptionFlags(flag.CommandLine)

	flag.Parse()

	cfhttp.Initialize(*communicationTimeout)

	logger, reconfigurableSink := lagerflags.New("bbs")
	logger.Info("starting")

	initializeDropsonde(logger)

	clock := clock.NewClock()

	consulClient, err := consuladapter.NewClientFromUrl(*consulCluster)
	if err != nil {
		logger.Fatal("new-consul-client-failed", err)
	}

	serviceClient := bbs.NewServiceClient(consulClient, clock)

	maintainer := initializeLockMaintainer(logger, serviceClient)

	_, portString, err := net.SplitHostPort(*listenAddress)
	if err != nil {
		logger.Fatal("failed-invalid-listen-address", err)
	}
	portNum, err := net.LookupPort("tcp", portString)
	if err != nil {
		logger.Fatal("failed-invalid-listen-port", err)
	}

	_, portString, err = net.SplitHostPort(*healthAddress)
	if err != nil {
		logger.Fatal("failed-invalid-health-address", err)
	}
	_, err = net.LookupPort("tcp", portString)
	if err != nil {
		logger.Fatal("failed-invalid-health-port", err)
	}

	registrationRunner := initializeRegistrationRunner(logger, consulClient, portNum, clock)

	var activeDB db.DB
	var sqlDB *sqldb.SQLDB
	var sqlConn *sql.DB
	var storeClient etcddb.StoreClient
	var etcdDB *etcddb.ETCDDB

	key, keys, err := encryptionFlags.Parse()
	if err != nil {
		logger.Fatal("cannot-setup-encryption", err)
	}
	keyManager, err := encryption.NewKeyManager(key, keys)
	if err != nil {
		logger.Fatal("cannot-setup-encryption", err)
	}
	cryptor := encryption.NewCryptor(keyManager, rand.Reader)

	etcdOptions, err := etcdFlags.Validate()
	if err != nil {
		logger.Fatal("etcd-validation-failed", err)
	}

	if etcdOptions.IsConfigured {
		storeClient = initializeEtcdStoreClient(logger, etcdOptions)
		etcdDB = initializeEtcdDB(logger, cryptor, storeClient, serviceClient, *desiredLRPCreationTimeout)
		activeDB = etcdDB
	}

	// If SQL database info is passed in, use SQL instead of ETCD
	if *databaseDriver != "" && *databaseConnectionString != "" {
		var err error
		connectionString := appendSSLConnectionStringParam(logger, *databaseDriver, *databaseConnectionString, *sqlCACertFile)

		sqlConn, err = sql.Open(*databaseDriver, connectionString)
		if err != nil {
			logger.Fatal("failed-to-open-sql", err)
		}
		defer sqlConn.Close()
		sqlConn.SetMaxOpenConns(*maxDatabaseConnections)
		sqlConn.SetMaxIdleConns(*maxDatabaseConnections)

		err = sqlConn.Ping()
		if err != nil {
			logger.Fatal("sql-failed-to-connect", err)
		}

		sqlDB = sqldb.NewSQLDB(sqlConn, *convergenceWorkers, *updateWorkers, format.ENCRYPTED_PROTO, cryptor, guidprovider.DefaultGuidProvider, clock, *databaseDriver)
		err = sqlDB.SetIsolationLevel(logger, sqldb.IsolationLevelReadCommitted)
		if err != nil {
			logger.Fatal("sql-failed-to-set-isolation-level", err)
		}

		err = sqlDB.CreateConfigurationsTable(logger)
		if err != nil {
			logger.Fatal("sql-failed-create-configurations-table", err)
//.........這裏部分代碼省略.........

	logger = lagertest.NewTestLogger("sql-db")

	fakeDriver = &fakesqldriverfakes.FakeDriver{}
	fakeConn = &fakesqldriverfakes.FakeConn{}
	fakeTx = &fakesqldriverfakes.FakeTx{}

	fakeDriver.OpenReturns(fakeConn, nil)

	fakeConn.BeginReturns(fakeTx, nil)

	guid, err := uuid.NewV4()
	Expect(err).NotTo(HaveOccurred())
	driverName := fmt.Sprintf("fake-%s", guid)

	sql.Register(driverName, fakeDriver)

	db, err = sql.Open(driverName, "")
	Expect(err).NotTo(HaveOccurred())
	db.SetMaxIdleConns(0)
	Expect(db.Ping()).NotTo(HaveOccurred())

	encryptionKey, err := encryption.NewKey("label", "passphrase")
	Expect(err).NotTo(HaveOccurred())
	keyManager, err := encryption.NewKeyManager(encryptionKey, nil)
	Expect(err).NotTo(HaveOccurred())
	cryptor = encryption.NewCryptor(keyManager, rand.Reader)
	serializer = format.NewSerializer(cryptor)

	sqlDB = sqldb.NewSQLDB(db, 5, 5, format.ENCRYPTED_PROTO, cryptor, fakeGUIDProvider, fakeClock, sqldb.MySQL)
})

func (a *Attacker) hit(tr Targeter, tm time.Time) *Result {
	var (
		res = Result{Timestamp: tm}
		tgt Target
		err error
	)

	defer func() {
		res.Latency = time.Since(tm)
		if err != nil {
			res.Error = err.Error()
		}
	}()

	if err = tr(&tgt); err != nil {
		return &res
	}

	req, err := tgt.Query()
	if err != nil {
		return &res
	}

	/*
		a.cnn, err = sql.Open("mysql", a.dsn)
		if err != nil {
			res.Code = 500
			res.Error = err.Error()
			return &res
		}
		defer a.cnn.Close()
		a.cnn.SetMaxIdleConns(a.maxIdleConns)
		a.cnn.SetMaxOpenConns(a.maxOpenConns)
	*/
	//log.Printf("query:%s", req)

	var r *sql.Rows
	if a.persistent {
		r, err = a.cnn.Query(req)
	} else {
		var cnn *sql.DB
		cnn, err = sql.Open("mysql", a.dsn)
		cnn.SetMaxIdleConns(0)
		cnn.SetMaxOpenConns(1)
		cnn.SetConnMaxLifetime(1 * time.Nanosecond)
		if err != nil {
			res.Code = 500
			res.Error = err.Error()
			return &res
		}
		r, err = cnn.Query(req)
	}
	if err != nil {
		// ignore redirect errors when the user set --redirects=NoFollow
		if a.redirects == NoFollow && strings.Contains(err.Error(), "stopped after") {
			err = nil
		}
		return &res
	}
	defer r.Close()
	num := 0
	for r.Next() {
		num++
		/*
			var id interface{}
				if err := r.Scan(&id); err != nil {
					res.Code = 501
					res.Error = err.Error()
					res.Error = fmt.Sprintf("row scan err:%s: query:%s", err.Error(), req)
					return &res
				}
		*/
	}
	//fmt.Fprintf(os.Stderr, "%d,", num)
	res.BytesIn = 0
	res.BytesOut = 0

	err = r.Err()

	if err == nil {
		if num == 0 {
			res.Code = 201
		} else {
			res.Code = 200
		}
	} else {
		res.Code = 500
		res.Error = fmt.Sprintf("%s: query:%s", err.Error(), req)
	}
	return &res
}