I'm having a rather large table (350MB, 1.3M rows) and there are dozens of queries arriving at this table every second. Most queries are super-fast thanks to indexes - but some others aren't.

Obviously I'm using indexes for the most common queries on this table; but I always hesitated to add indexes for queries which are only performed every few seconds or even minutes. They are just a minority compared to the more important queries.

As far as I know, adding more indexes makes inserting new rows and updating existing data slower (and UPDATE/DELETE/INSERT queries happen very frequently for this table); but adding indexes also makes normal SELECT queries slower, since MySQL has to find the perfect index first and has to check more index possibilities. Obviously, the slightly slower SELECT statements wouldn't be a problem; but I'm more worried about the UPDATE queries.
But on the other hand: I don't want these rare queries to take 1+ seconds, either.

Is there anything else I could do?
These performance heavy queries are creating temp tables and I thought about increasing the tmp_table_size (it's currently 16MB and I'm having 6+GB of unused RAM space). But then I read that this makes things even worse.

Join/Sort buffer sizes are already at 128/256MB
(Now that I think about it: These two variables are lower than the table size itself. That might be a problem; though I don't want to increase variables without knowing exactly what I'm doing.)

closed as off-topic by Mark Storey-Smith, Paul White, billinkc, RLF, Max Vernon Nov 26 '14 at 16:34

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Mark Storey-Smith, billinkc, Max Vernon
If this question can be reworded to fit the rules in the help center, please edit the question.

  • 5
    I wouldn't call statements "which are only performed every few seconds" rare. The are executed often enough to warrant an index. I have never seen a DBMS where adding an index would make the statement slower because parsing (the step where the best index is searched for) became an issue. When it comes to performance don't just assume things. Test them. Your concern about the DML is a valid one though, but I have seen only a few systems where adding an index really made the DML so bad that it had to be removed. But again: you have to test this. – a_horse_with_no_name Nov 24 '14 at 23:02
  • 1
    A table of 350M is not large and will fit inside memory many times over in a modern system. If your query is causing temporary tables it's important to know why. Group by and Order by aggregations and sorting can be optimised for and knowing the biology of a composite key might show how you can refine your indexing strategy so that your access is optimised. Now for your configuration; you've shown that you're changing variables blindly without the knowledge of their effects. I would recommend a visit to tools.percona.com/wizard and ensure your config is sane. – eroomydna Nov 24 '14 at 23:45
  • I think your join and sort buffers are very, very high. If any query needs that much memory, you should consider rewriting or breaking down the query into many parts. – Manny Calavera Nov 25 '14 at 0:40
  • If you worry so much about index pick time use hint. – Antonios Nov 25 '14 at 1:35
  • @a_horse_with_no_name: Well then what about queries that are only performed every minute or every five minutes? There are queries which happen more often by about tenthousand-times, including DMLs. ... And my table has many different columns and there are quite a few different "rare" queries which sort data by many different column combinations. Indexing ALL of them would most likely make important DMLs slower. And I don't feel like indexing everything is always the right choice. – Tobias Baumeister Nov 25 '14 at 11:04

I wrote some posts about RAM disk and temp tables

The basic idea would be to set set tmp_table_size and max_heap_table_size to the bare minimum value so that move temp tables to the RAM disk quickly.

This would only help if your queries produce temp tables and you do not wish to tune tables and indexes. However, it is imperative to tuning queries and use indexes to produce the smallest possible temp tables before tuning the tmp_table_size and max_heap_table_size options or even go the route of the RAM Disk.

Nevertheless, since you have 16GB on the server, you could set up good 2G RAM disk, configure good settings for join, sort and read buffers per connection, and sufficient RAM left for the OS.

UPDATE 2014-11-25 11:09 EST

Your last comment

I'm wondering what's the difference between temp tables on a RAM Disk and temp tables in "memory"? I think that's the same, isn't it? Therefore it makes no difference wheter I set tmp_table_size & max_heap_table_size to a high or a low value - RAM is used in both situations. Or am I wrong here?

It makes a big difference. Why ?

  • Thank you very much. Indeed I am already using a RAM Disk for my mysql temp dir. However, I'm wondering what's the difference between temp tables on a RAM Disk and temp tables in "memory"? I think that's the same, isn't it? Therefore it makes no difference wheter I set tmp_table_size & max_heap_table_size to a high or a low value - RAM is used in both situations. Or am I wrong here? – Tobias Baumeister Nov 25 '14 at 11:00
  • @TobiasBaumeister, tmp tables on "disk" (even if it's a RAM disk or tmpfs) uses the MyISAM storage engine by default. You can also put a limit on the size of the RAM disk. Whereas tmp tables in memory use the MEMORY (aka HEAP) storage engine, and can use up to tmp_table_size memory per thread. Also, MEMORY storage engine does not support TEXT/BLOB columns. – Bill Karwin Nov 25 '14 at 18:42
  • @RolandoMySQLDBA, have you gotten improvement from putting tmpdir on a tmpfs? I have tried it and found little to no improvement. The overhead of creating temp tables seems to be the bottleneck. The savings by using tmpfs isn't enough to fix performance issues. I suspect temp tables on disk use the filesystem cache and end up living in system memory anyway. – Bill Karwin Nov 25 '14 at 18:45
  • 1
    @BillKarwin you are right. It's just really band-aid to get around not rewriting queries and creating supporting indexes for them. I have had past clients that wanted this and were satisfied with some performance improvements. Of course, those clients had like 192GB RAM and more. RAM disks are not really for small to midsize DB Servers. – RolandoMySQLDBA Nov 25 '14 at 18:58

Not the answer you're looking for? Browse other questions tagged or ask your own question.