CODEWITHSUNDEEP
mysqlmysql-pythondatabase-cursor
Jack
Jack

Reputation: 5880

I'm confused about MySQLdb server side cursor and client cursor

I'm using python-mysql(MySQLdb) to query Mysql server. There are two cursor modules: one is client cursor, such as:

cursor = db.cursor(MySQLdb.cursors.DictCursor)

Another one is server side cursor,such as:

cursor = db.cursor(MySQLdb.cursors.SSDictCursor)

The doc says Server side cursor means that Mysql would cache some results in mysql server side and then send them out to the client. I'm so confused about this, let's say, if I wanna kill one mysql server I could just use multiple server side cursors and then mysql will be dead because of memory ran out. Furthermore, does server size cursor make any sense? By default Mysql mechanism is that when mysql retrieved one record it would send it out the client immediately. Does make any sense to cache the results and then send them out?

I really don't known which cursor I should use, client cursor or server side cursor?

Upvotes: 5

Views: 3769

Answers (3)

Norbert
Norbert

Reputation: 6084

A cursor consists out of three parts:

  • A query
  • A query result
  • A pointer to the place until where data has been retrieved.

Depending on the query, the result can either be cached, or be retrieved in parts by the engine:

For example a query result which is usually not cached:

SELECT * FROM sometable;

MySQL (and most other DBMS) will just retrieve a row from the table every time you request a row. It can however use a table lock if you are using InnoDB and ACID compliant transactions in read committed style.

The second scenario is a query from which the result has to be cached:

SELECT * FROM sometable ORDER BY a,b,c;

In this case the MySQL (and again most other DBMS) has to get all the data in the correct order first. For this a temporary table will be created on disk in the #tmp location. This can cause disk full (translated most of the time as out of memory errors) issues and loss of a connection. MySQL however keeps running.

Upvotes: 1

Petro
Petro

Reputation: 816

I'm not the greatest Database Ninja around, but often times things get built into server software that aren't really useful in the general or common cases, but are really, really awesome in that one little corner case.

Nimdil gave you one, but this is another:

http://techualization.blogspot.com/2011/12/retrieving-million-of-rows-from-mysql.html

This person asserts that SScursor is more of an "unbuffered" cursor.

This sort of seems to contradict that:

http://dev.mysql.com/doc/refman/5.7/en/cursor-restrictions.html

Anyway, it sort of seems that the use for Server Side Cursors are when you're dealing with datasets such that your query could overwhelm the client.

Upvotes: 2

nimdil
nimdil

Reputation: 1381

I believe MySQL would rather kill your cursor than crash because of few oversized cursors.

You can think of several scenarios when server side cursor makes sense. For example if you have slow network connection and the cursor is big, you can work on some small part of the data you can get quicker, possibly pass it to other system and then fetch some more. This way the overall speed of solution would be greater.

Other scenario that I can think of is when you have quite powerful database server and rather crappy machine under the client - this way in case of big dataset it would be easier for database to hold the whole set for your client while the client can micromanage memory efficiently.

There are possibly many other scenarios. If you think it doesn't make sense, just don't use it. Not all options are for every setup.

Upvotes: 1

Related Questions