All that I find on the Internet for advice on tuning MySQL for performance is dealing with production databases that have a large number of connections and many repetitive queries. This is not my workload; instead, I am researching data with MySQL, where I am the only user, the data does not change very often (only bulk import), and the number of connections that I could have at any given time is <20. Available my data is quite large (several hundred concerts, tables with 50 mm rows with a bunch of rows in them), but the queries that I write are rarely executed more than a few times.
I have O'Reilly Schwartz et al. a book about MySQL, and it was a godsend for understanding how to make certain things (like indexes) useful to me. However, I feel much less comfortable with server settings for such a workload, as I can find some examples on the Internet. Below are the non-reserve parameters (MySQL 5.5, Ubuntu) that I run with:
max_heap_table_size=32G
tmp_table_size=32G
join_buffer_size=6G
innodb_buffer_pool_size=10G
innodb_buffer_pool_instances=2
sort_buffer_size=100M
My server is multi-core (quad seems to be wasted on MySQL, but sometimes I hit a couple of requests right away) 32 GB of RAM. Now it seems that MySQL is limited to 12 GB of RAM, probably due to size innodb_buffer_pool. I installed it tmp_table_sizeand it was heap sizejust fantastic, because I made several requests, where I kept a lot in my memory.
MySQL ? , innodb?