Bug #47821 Can't create/write to file (Errcode: 22)
Submitted: 5 Oct 2009 5:54 Modified: 6 Oct 2009 11:32
Reporter: Aniket Samudra Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S2 (Serious)
Version:5.1.36 OS:Windows (64-bit mysql)
Assigned to: CPU Architecture:Any
Tags: error code 22, performance

[5 Oct 2009 5:54] Aniket Samudra
Description:
We are doing some benchmarking on mysql-64 bit 5.1.36 version. To do so, we have a multithreaded C# app that gives SELECT query calls to mysql.

To be precise when threads are reached to 685, the client script exits with following error -

Unhandled Exception:  Unhandled Exception: Unhandled Exception:   MySql.Data.My
qlClient.MySqlException: Can't create/write to file 'C:\Windows\TEMP\#sqlc78_2a
_2.MYD' (Errcode: 22)
  at MySql.Data.MySqlClient.MySqlStream.OpenPacket()
  at MySql.Data.MySqlClient.NativeDriver.ReadResult(UInt64& affectedRows, Int64
 lastInsertId)
  at MySql.Data.MySqlClient.MySqlDataReader.GetResultSet()
  at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
  at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior
  at ThreadTest.MyJob.runme()
  at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, C
ntextCallback callback, Object state)

How to repeat:
Here is a sample query we are using to give multi-threaded hits to mysql

SELECT i.id, score, i.s3_id, item_type, address_line, city, state, zip, country, title, 0, lat, lon, 0, ratings, reviews, 0 as brelevance, 0 as relevance, (select count(distinct fs3id) from geoindex.frlinks where frlinks.id=i.id) as ud, phone, category, t.activity, tags, capability, abi, site, r.relevance as rrelevance FROM geoindex.item i, geoindex.terms t left join geoindex.relevance r on (t.id = r.id and  r.activity = 0) WHERE i.id=t.id  AND i.item_type in ('Entity') AND (i.lon >= -118.923615757735 and i.lon <= -117.232491237666 and i.lat >= 33.4501415131841 and i.lat <= 34.4403145980438) AND ((t.keywords = 'supplies')) order by brelevance desc, rrelevance desc, reviews desc LIMIT 100;

There is no error in the script as till 685 threads we are getting the results. Can you let us know what's wrong there?

My.ini details -
tmpdir="C:/temp"
basedir="C:/Program Files/MySQL/mysql64bit/"
datadir="C:/Program Files/MySQL/mysql64bit/Data/"
default-storage-engine=INNODB
max_connections=1200
query_cache_size=201M
table_cache= 4096 
tmp_table_size= 1M
max_heap_table_size = 4096M
thread_cache_size=64
key_buffer_size=1008M
sort_buffer_size=200M
innodb_file_per_table
[5 Oct 2009 5:56] Aniket Samudra
After changing the tmpdir in My.ini, the path in the exception changes. We have searched the bugs database. For ErrorCode 13 we've to change/reset the permissions, although this is error-code 22, still we checked the permissions and available disk-space for tmpdir are correct
[5 Oct 2009 6:14] Valeriy Kravchuk
If each of your threads is doing some select that access (several? 3 in your example) MyISAM tables, then 685 of them may need too many open file handles for current implementation on Windows (2048). Check http://dev.mysql.com/doc/refman/5.1/en/limits-windows.html. 

Try with SELECT that accesses one table only, and you should be able to get more concurrent threads.
[5 Oct 2009 8:26] Aniket Samudra
Thanks a lot for a prompt reply.

So does this mean we hit a bottleneck here? The only solution is to optimize the query to open lesser number of tables that will allow us to use more threads correct?

or is there any other parameter or change to 64-bit o/s will resolve the issue. Let me know so that i can close the defect then.
[5 Oct 2009 11:55] MySQL Verification Team
See bug: http://bugs.mysql.com/bug.php?id=24509.
[5 Oct 2009 16:24] Valeriy Kravchuk
You can reduce number of on-disk temporary tables needed also, by setting some more reasonable values in my.ini instead of:

tmp_table_size= 1M
max_heap_table_size = 4096M

I'd suggest to start with: 

tmp_table_size= 16M
max_heap_table_size = 16M

But whatever you do you should just remember that in current MySQL 5.x versions on Windows you can not have more than 2048 files handles open by all running threads of mysqld.exe at any given time. This is a documented limitation.

The problem is fixed in a newer codebase (see that bug Miguel mentioned), but is still present in 5.1.x.
[6 Oct 2009 11:32] Aniket Samudra
We are satisfied with the replies by you guys and this helps us on our tasks. We are closing the bug now.