Severe performance issues

[MicroDroid]

Issue Description

What are you doing?

Running a web server that does 1 SQL query and returns results:

const tag = await models.tag.findOne({
	where: {name: ctx.params.name},
});

What do you expect to happen?

3500+ req/s

What is actually happening?

460 req/s

Additional context

1. I tried patching the native Promise to add some extra functions that behave like Bluebird, and I was able to go up to ~830 req/s by ditching out Bluebird entirely.
2. I did a benchmark to my webserver with solely Redis query, it can do up to ~3500 req/s on a single CPU core.
3. I benchmarked mysql2, it can do up to 108,000 req/s - which is pretty much however fast is my running MariaDB server.

With that in mind, I believe sequelize does introduce extremely awkward procedures that bring the speed down to 430 req/s - note that it should ideally do 50,000+ queries/s assuming the webserver in front of it is ditched away.

Environment

• Sequelize version: 4.44.3
• Node.js version: 12.11.1
• Operating System: Arch Linux

Issue Template Checklist

How does this problem relate to dialects?

• I think this problem happens regardless of the dialect.
• I think this problem happens only for the following dialect(s):
• I don't know, I was using PUT-YOUR-DIALECT-HERE, with connector library version XXX and database version XXX

Would you be willing to resolve this issue by submitting a Pull Request?

• Yes, I have the time and I know how to start.
• Yes, I have the time but I don't know how to start, I would need guidance.
• No, I don't have the time, although I believe I could do it if I had the time...
• No, I don't have the time and I wouldn't even know how to start.

[knoxcard2]

Do you have sql connection pooling turned on?

  const Sequelize = require('sequelize'),
    sequelize = new Sequelize(process.env.db_name, process.env.db_user, process.env.db_pass, {
    dialect: 'mariadb',
    dialectOptions: {
      socketPath: process.env.db_socket,
      timezone: process.env.db_timezone
    },
    pool: {
      min: 0,
      max: 5,
      idle: 10000
    },
    define: {
      charset: 'utf8',
      timestamps: false,
      hooks: {
        afterCreate: (model, fn) => {
          formatCols(model)
        },
        afterUpdate: (model, fn) => {
          formatCols(model)
        }
      }
    },
    benchmark: false,
    logging: false
  })

[MicroDroid]

@knoxcard2 yes

[knoxcard2]

Are you using mysql2 or mariadb?

npm

npm uninstall mysql2
npm install mariadb

yarn

yarn remove mysql2
yarn add mariadb

[MicroDroid]

@knoxcard2 Sorry for the delay.

I had to upgrade to sequelize v5, and then switched to mariadb, now it's about ~360 req/s.

[knoxcard2]

Did you set a db index on the name column?'
CREATE INDEX name ON tag(name)

[MicroDroid]

I have not, however, the query that sequelize is producing, the MySQL server can process at around 100,000 req/s.

Also this affects all sorts of queries, not a specific one.

[knoxcard2]

@MicroDroid - please post your code....another problem is the way you run the db query. It is better to wait for the previous query to finish on a callback and the run the next for INSERTS in some cases.

[knoxcard2]

@MicroDroid - Also, are you running update/insert codes one at a time? The bulkCreate() method allows bulk insert/update lightning fast. Below is a pseudocode for a fast bulk update based on the unique column 'url_slug'.

[loop]
menu_urls.push({
	processed: 'N',
	menu_url: menu_url,
	url_slug: url_slug,
	location: ''
})
[/end loop]

app.get('dispensaries').bulkCreate(menu_urls, {
	updateOnDuplicate: ['url_slug']
}).then(() => {
	app.get('dispensaries').destroy({
		where: {
			processed: 'Y'
		}
	}).then(() => {
		console.log('all done!')
	})
})

[MicroDroid]

@knoxcard2 The queries I am making are SELECT ones. Anyhow, I make an SQL query based on an incoming HTTP request, thus bulk operations do not apply.

That, and they're technically another thing, bulk operation benchmarks with or without sequelize will both be faster, but they do not apply to my usage.

Here's my code:

// ...
async get(ctx) {
	const tag = await models.tag.findOne({
		where: { name: ... },
	});

	if (!tag)
		throw {status: 404, message: 'Not found'};

	// return response immediately
}
// ...

[MicroDroid]

What I'm trying to convey is that the exact identical query, regardless of indices or any other optimization, are far, far slower when done through sequelize.

Like I said initially, the issue lies within Bluebird being slow and other code issues.

[kroleg]

@MicroDroid how do you benchmark? I am curious because i am also having performance issues with sequelize (but i use postgres)
I benchmark with vegeta and here is a repo which compares pg and sequelize performance (you'll need to change sql query if you want to try it)

[MicroDroid]

I benchmark with koa as web server + ApacheBench and compare performance when including a query or not

[sushantdhiman]

We have removed bluebird, that should help #12140

[kroleg]

@sushantdhiman do you need help with removing remains of Bluebird from code and docs?

[sushantdhiman]

@jedwards1211 is working on tests (converting them to async) @kroleg you can help us with documentation cleanup :)

[SimonSchick]

Sorry, already done #12167