10

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.

Steve

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.

RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
Steve06
  • 285
  • 2
  • 5
  • 10
  • Please show your server load, so check memory, io, cpu loads etc. –  Jul 21 '12 at 10:41
  • I'll run some tests and report back what Windows Task Manager has to say (or would you advise a better tool?) –  Jul 21 '12 at 10:44
  • That should be enough for a first indication to see where your issue is. –  Jul 21 '12 at 10:51
  • just added some stats. –  Jul 21 '12 at 22:10
  • I suggest you also read this as a start for configuring InooDB usage: Innodb Performance Optimization Basics. Your innodb_buffer_pool_size is very low for a 16GB Ram machine. Try making that a 12GB, DBMS's just love RAM, lots of RAM. – ypercubeᵀᴹ Jul 22 '12 at 08:15
  • If the queries don't gain any efficiency, you can try posting an example in a separate question (with the tables' definitions and the EXPLAIN EXTENDED plan.) – ypercubeᵀᴹ Jul 22 '12 at 08:20
  • 2
    In addition you can also try out the Percona wizard to get "recommended" settings for your database server at https://tools.percona.com/wizard – Stephen Senkomago Musoke Jul 22 '12 at 08:23
  • I went through the Percona wizard and found the resulting config file helpful as a very rough guidance. However, it was far from perfect. For example, while it suggested an innodb_buffer_pool_size of 12G like you guys, the max_heap_table_size and tmp_table_size were much too low at only 32M, causing the server to create temporary tables on disk rather than RAM. I pushed both values up to 12G and were thus able to reproduce the previous results achieved with my own ini file. So while Percona hinted me at where I could push some boundaries, there was no speed increase with the queries at hand. – Steve06 Jul 22 '12 at 16:29
  • @Steve06 it seems you have some big and complicated queries (lots of GROUP BY and ORDER BY maybe?), which can benefir from big temp file settings. Can you post an example of a query? – ypercubeᵀᴹ Jul 22 '12 at 19:14
  • Query #1 has involves only ordering by one variable but has inner and left joins across about 20 tables. Query #2 (actually the faster one) involves several group by clauses, joins over 20 tables, including tables created on the fly (via subqueries). I might post one of them in a separate question. In the meantime, if you are anyone else has any further comments on how to best configure MySQL for the above hardware, I'd appreciate you sharing them. – Steve06 Jul 22 '12 at 23:26

4 Answers4

5

Since you are running MySQL 5.5, you may want to consider configuring InnoDB to access multiple cores

Here are the settings you should be using

innodb_thread_concurrency sets the upper bound on number of concurrent threads that InnoDB can hold open. Best round number to set for this is (2 X Number of CPUs) + Number of Disks. UPDATE : As I learned firsthand from the Percona NYC Conference, you should set this to 0 in order to alert InnoDB Storage Engine to find the best number of threads for the environment it is running in.

innodb_concurrency_tickets sets the number of threads that can bypass concurrency checking with impunity. After that limit is reached, thread concurrency checking becomes the norm again.

innodb_commit_concurrency sets the number of concurrent transactions that can be committed. Since the default is 0, not setting this allows any number of transactions to commit simultaneously.

innodb_thread_sleep_delay sets the number of milliseconds an InnoDB thread can be dormant before reentering the InnoDB queue. Default is 10000 (10 sec).

innodb_read_io_threads and innodb_write_io_threads (both since MySQL 5.1.38) allocate the specified number of threads for reads and writes. Default is 4 and maximum is 64.

innodb_replication_delay imposes thread delay on a slave is innodb_thread_concurrency is reached.

Here are my past posts on MySQL 5.5 and activating multiple cores for InnoDB

Community
  • 1
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
2

Percona- Leading MySQL consultant offers a MySQL configuration wizard. It lets you to configure my.cnf/my.ini depending on your system configuration.

Also Percona people have released a book called "High Performance MySQL". The third edition was recently released, and covers tuning in great detail.

Mahesh Patil
  • 3,056
  • 2
  • 16
  • 23
1

Memory usage: see http://mysql.rjweb.org/doc.php/memory (Most tunables won't make enough difference to matter.)

max_heap_table_size=4000M is Dangerously high! If 4 users need such, you are out of RAM and swapping. Swapping hurts performance much more than virtually anything.

Queries taking more than a few seconds: They should be studied for improvement; please provide SHOW CREATE TABLE; SHOW TABLE STATUS; EXPLAIN SELECT

Rick James
  • 78,038
  • 5
  • 47
  • 113
0

You may consider otger options too. Such as PostgreSQL on FreeBSD. But swiching from Windows to Linux will incrrease you performance.

ilhan
  • 3,049
  • 5
  • 20
  • 13