0

I have a server (MS Windows Server 2012 R2 Datacenter 64GB RAM 2TB+ disk space) running mySQL 5.0. When I start the mySQL server, right off the bat it allocates 214,000 handles. Is that normal? I've been looking into this because I am trying to run an application that executes multiple unique queries over thousands of records and it is just crawling.

I have changed query_cache_size from 160M to 0M in the my.ini file as query caching will not benefit this application. Still no change in handles. I'm not sure what else I can do to fix this. Does anyone have any ideas?

The server is: MySQL 5.0.60sp1-enterprise-gpl-nt

There are a ton of options. Here are what I think are the relevant ones (I could be wrong I am not an expert)

[mysqld]
default_storage_engine=InnoDB
innodb_file_per_table
innodb_flush_method=unbuffered 
lower_case_table_names=2
max_allowed_packet=48M
max_heap_table_size=64777216 
max_connections=3010
query_cache_size=0M
table_cache=6020
tmp_table_size=16M
thread_cache_size=64
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
key_buffer_size=20M
read_buffer_size=64K
read_rnd_buffer_size=256K
innodb_additional_mem_pool_size=15M
innodb_flush_log_at_trx_commit=1
innodb_buffer_pool_size=709M
innodb_thread_concurrency=50
eric_the_animal
  • 432
  • 9
  • 19
  • Can I have a reason for the down vote? Something wrong with the question? – eric_the_animal Feb 17 '15 at 20:50
  • 1
    The number is rising. A couple of years ago it was just tens of thousands. I can find various bug reports (even from as early as 2008), and questions on other SE sites, like [this one on ServerFault from 2011](http://serverfault.com/questions/235899/is-it-normal-for-mysqld-to-have-32k-handles). Apparently this number is built up from 3 handles per MyISAM table and 2 handles per cached query (and probably other resources too). For a large database these numbers quickly add up, although I don't think you have 60.000 tables. ;-) – GolezTrol Feb 17 '15 at 20:56
  • What database engine? Configuration (server options)? – Marcus Adams Feb 17 '15 at 21:04
  • I've updated the question with engine and config options. for all I know 200000+ handles is normal and the handles may not even be what is slowing me down. I just thought it looked odd. – eric_the_animal Feb 17 '15 at 21:15
  • Do you use partitions? How many tables? How did you find the used number of handles? – JuniorCompressor Feb 17 '15 at 21:21
  • I'm not sure what you mean by partitions - I didn't set up this server it was done by our IT group. I would approximate about 250 tables across 15 databases. I found the number of handles by looking at Task Manager mysqld-nt.exe process. I'm not running any query currently and it is sitting at 214,000. – eric_the_animal Feb 17 '15 at 21:25
  • Your innodb_thread_concurrency setting seems high. Still, at this point, we'd have to see the queries to eliminate that as an issue with the slowdown. – Marcus Adams Feb 17 '15 at 21:42

0 Answers0