I'm running a MySQL 5.5 server on my workstation for scientific data analysis and wonder how to configure MySQL in order to get the most out of it performance-wise. The types of query that I typically run involve joins of 10-20 tables and can run for quite long, one to several minutes being no exception at all. Only very few users access the database at the same time (5 being the maximum). I moved the server from a Lenovo Thinkpad T61 with a 2.2 GHz Dual Core and 4 GB of RAM to the following brand-new machine with hand-selected components:
- Intel i7 3770, 4x 3.4 GHz (running @ 4x3.7 GHz)
- Z77 chipset
- 16 GB of DDR3 1600 RAM
- Windows 7 Prof 64-bit
- Windows and MySQL server run on a Intel 520 series SSD drive.
First tests (running the same query on both machines) showed a definitive improvement in speed for the new one, but the queries still take a lot of time and I had expected more of a boost. The queries in question are fairly well optimized, i.e. all tables have proper key that are also being used as of "explain extended".
Now to my current MySQL settings: First I should mention that I moved from MyISAM to Innodb long time ago.
Some of my my.ini tweaks (i.e. departures from default settings):
# Maximum size for internal (in-memory) temporary tables. If a table # grows larger than this value, it is automatically converted to disk # based table This limitation is for a single table. There can be many # of them. #tmp_table_size=35M tmp_table_size=4000M max_heap_table_size=4000M # InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and # row data. The bigger you set this the less disk I/O is needed to # access data in tables. On a dedicated database server you may set this # parameter up to 80% of the machine physical memory size. Do not set it # too large, though, because competition of the physical memory may # cause paging in the operating system. Note that on 32bit systems you # might be limited to 2-3.5G of user level memory per process, so do not # set it too high. #innodb_buffer_pool_size=96M innodb_buffer_pool_size=800M general-log expire_logs_days = 60 general_log_file = "F:/my_query_mysql.log" log-output = TABLE optimizer_search_depth = 0 #meant to cure the "statistics state" bug in some queries
I'd like to know whether somebody would suggest changes to the above numbers or even further settings that I do not know of.
I'd appreciate any helpful remark.
EDIT: I have two queries involving joins across 10-20 tables and ran them on my Lenovo notebook and the new PC. Query #1 took 3m36s on the new machine vs 9m11s on the laptop; Query #2 took 22.5s on the workstation vs 48.5s on the laptop. So the execution speed was improved by roughly the factor 2-2.5. On the workstation, not even 50% of the RAM was used. The average CPU load across the four cores (as reported by Windows Task Manager) was only about 13%. The load on a per-core basis (as reported by Core Temp) was about 25-40% for ONE core, while it was <=10% for the others, indicating that MySQL does not make use of multiple cores for a single query.