Mariadb "copying to tmp table" problem on Warmshowers.org - IRC conversation

gistfile1.txt

Could anybody help me with a mariadb/mysql problem "Copying to tmp table" which happens only randomly on my server, but brings all database activities to a halt? I haven't been able to solve this over a couple of years now. https://github.com/rfay/Warmshowers.org/issues/22
[13:26]  <montywi> rfay: looking
[13:26]  <rfay> montywi, much appreciated
[13:26]  <montywi> Which version was this
[13:27]  <rfay> montywi, 5.1, Ubuntu 10.04, 5.1.49-mariadb82
[13:27]  <montywi> The 'copying to tmp table' part is a LOT faster in MariaDB than a MySQL (I have seen > 10x speedup)
[13:28]  <montywi> ok, the speedup is in > 5.1 I think. Checking...
[13:28]  <rfay> It has been better since I upgraded to Mariadb. However, still brings all database-based websites to a halt
[13:28]  <litheum> rfay: any opportunity to improve the queries that are cerating these tmp tables/files?
[13:29]  <litheum> rfay: or locate tmpdir on a faster device? or increase max_heap_table_size and max_tmp_table_size if you are having a lot of temporary tables go to disk?
[13:29]  <rfay> litheum, well, they're standard Drupal, and I haven't seen major improvements in more recent versions. So hard to do, I guess.
[13:30]  <rfay> litheum, thanks for those suggestions. Mostly, tmpfs would be the solution? As many kind-of-knowledgable database people. I feel incompetent around settings like you suggest. Looking at what they're set to...
[13:34]  <rfay> montywi, would be pretty easy for me to upgrade to 5.2, for sure. Doubt it would do any harm at all.
[13:35]  <montywi> rfay: the "copying to tmp table" means that the query is complex and we have to store rows in a in-memory table before we can send them to the client
[13:36]  * maxmad ([email protected]) has joined #maria
[13:36]  <rfay> montywi, the sad thing too is that these queries are *all* basically irrelevant to the operation of the system, and are done during cache rebuilds and the like. 
[13:36]   litheum LizardK|ng
[13:36]  <montywi> so the problem dosn't have anything to do with tmp table as such, but because of the other complexity of the query
[13:37]  <rfay> litheum, max_heap_table_size is 67108864 and max_tmp_table_size does not show up in "show variables"
[13:37]  <montywi> if its about cache rebuild, then the issue is probably fetching rows from disk
[13:38]  <rfay> montywi, I think most of the examples in that issue are rebuilding a search index. Nearly irrelevant to the operation of the site.
[13:38]  <montywi> rfay: not that if you don't get ""copying to tmp table on disk" then this has nothing to do with tmpfs
[13:38]  <rfay> montywi, didn't understand that
[13:39]  <montywi> So there is two cases:
[13:39]  <rfay> montywi, you're saying that putting tmp on a tmpfs partition would not solve the problem..
[13:39]  * cashwilliams ([email protected]) has joined #maria
[13:39]  <montywi> - MariaDB is storing temporary results in a tmp in memory table. In this case tmpfs will not help as nothing is written to disk
[13:39]  <montywi> this is what happens if you have 'copying to tmp table'
[13:40]  <montywi> - If the memory table gets full, then MariaDB copies the data to a on disk tmp table (aria). In this 'show processlist' will show "Copying to tmp table on disk"
[13:41]  <montywi> in the later case, putting tmp tables on a ram disk will help. But it doesn't look that this is your problem...
[13:41]  <montywi> is this the query to fill the cache: ELECT n.nid, n.title, l.comment_count FROM {node} n INNER JOIN {term_node} tn ON tn.vid = n.vid INNER JOIN {term_data} td ON td.tid = tn.tid INNER JOIN {node_comment_statistics} l ON n.nid = l.nid WHERE n.status = 1 AND td.vid = %d ORDER BY n.nid DESC
[13:41]  <montywi> s/ELECT/SELECT
[13:42]  <rfay> montywi, litheum it doesn't *ever* seem to say "on disk"
[13:42]  <montywi> then the issue is not writing to your file system but instead reading data from the storage engine...
[13:42]  <ebergen> is Created_tmp_disk_tables going up?
[13:42]  * archivist has quit (Ping timeout: 240 seconds)
[13:43]  * the_wench has quit (Ping timeout: 245 seconds)
[13:43]  <rfay> ebergen, sorry for the ignorance, but how to I find that out? I don't see it in variables
[13:43]  <ebergen> show status
[13:44]  <rfay> ebergen, Created_tmp_disk_tables = 0 after long runtime.
[13:44]  <ebergen> so it isn't tmpdir
[13:44]  <ebergen> sorry
[13:44]  <rfay> Created_tmp_files = 311 and Created_tmp_tables = 5
[13:44]  <ebergen> show global status; not show status
[13:45]  <rfay> | Created_tmp_disk_tables               | 257669         |
[13:45]  <rfay> | Created_tmp_files                     | 311            |
[13:45]  <rfay> | Created_tmp_tables                    | 1027356        |
[13:45]  <ebergen> so you have some being created
[13:45]  <ebergen> you can look at show session status after running the query to verify that none were created for that query
[13:46]  <rfay> ebergen, I've never been able to make this happen by running the query directly.
[13:46]  <ebergen> show profile will also tell you but that is slightly more complicated
[13:46]  <rfay> Actually, I used to be able to make it happen by running the query under mysql, but haven't under mariadb.
[13:47]  <montywi> rfay: with MariaDB 5.1 and above, you can force any query that creates a on disk table to be written to slow query log
[13:47]  <montywi> see http://kb.askmonty.org/en/slow-query-log-extended-statistics/
[13:47]  <ebergen> cool
[13:48]  <ebergen> the thing that made the biggest difference for temp tables for me was increasing aria_pagecache_buffer_size
[13:49]  <ebergen> for disk tables
[13:50]  <montywi> ebergen: what did you increase it to ?
[13:50]  <ebergen> 128M
[13:51]  <montywi> rfay: the best way to get forward is try to optimize the query
[13:51]  <ebergen> the second biggest difference was moving to tmpfs but I think that may be more ext3s fault
[13:52]  <montywi> rfay: so 25% of your tables are spilling over to disk.  You may be able to get that number smaller by increasing the size of in memory tmp tables (if you have the memory)
[13:52]  <rfay> | maria_pagecache_buffer_size               | 8384512             |
[13:53]  <montywi> only 8M;  Better to increase it to at least 64M
[13:53]  <montywi> will help a LOT!
[13:53]  <rfay> montywi, excellent!
[13:54]  <montywi> rfay: you can also increase "tmp_table_size", which is by default 16M. Note however that this is memory given for every running query that uses tmp table
[13:54]  <montywi> rfay: what do you mean the query is only for filling the cache. Is it not a real query?
[13:55]  <montywi> And fill what cache?  The innodb cache, the file system (OS) cache, the query cache?
[13:55]  <rfay> montywi, I mean it's a background, Drupal query, which is not even critical to site operation. 
[13:55]  <montywi> so what's the purpose of the query?
[13:55]  <rfay> montywi, I should have said it's a query run when doing Drupal's cron routine. 
[13:56]  <rfay> montywi, there are 2 or 3 queries that have been caught in the act, but the ones I've studied most carefully are updating the indexes to Drupal's forums
[13:56]  * archivist ([email protected]) has joined #maria
[13:57]  <rfay> | tmp_table_size | 67108864 |
[13:57]  * the_wench ([email protected]) has joined #maria
[13:57]  <montywi> but just to be clear 'copying to tmp table' is not bad. It always happens when you do a complex order by on a table which is not the first one in explain
[13:58]  * marialog has quit (Remote host closed the connection)
[13:58]  <rfay> Thanks for that
[13:58]  <montywi> so look more at the numbers of rows in explain. If the product of these gets big, then you have a problem
[13:58]  <rfay> I've only seen it be bad :-) My monitoring goes off, all sites on my server are dead, and there are 10-20 queries "copying to tmp table" :-)
[13:59]  <rfay> I had already increased tmp_table_size to 64M apparently
[13:59]  * marialog ([email protected]) has joined #maria
[13:59]  <montywi> in the explain you have given, you have 13*6*1*1*197 = 15366 rows. If you can keep these in memory then things will be fast
[13:59]  * maxmad has quit (Ping timeout: 264 seconds)
[14:00]  <montywi> rfay: the reason things goes bad on linux is usually because you get many things writing to disk at the same time (linux don't like that)
[14:00]  <montywi> how much memory do you have in the machine?
[14:00]  <rfay> montywi, 2G
[14:00]  <rfay> montywi, and it always runs cool
[14:01]  <montywi> simplest way to get more speed would be increasing memory to 4G or 8G and increasing aria_buffer_size to 256M and tmp_table_size to 128M
[14:01]  <rfay> There's really plenty of memory available for reasonable increases.
[14:01]  <montywi> as memory is so cheap nowadays, that's the easiest option to get a lot of speed
[14:02]  <montywi> rfay: all depends on how many concurrent complex queries you have
[14:02]  <rfay> montywi, VM running on Rackspace for a nonprofit, so doubling means doubling cost, unfortunately
[14:02]  <montywi> ok
[14:02]  <rfay> montywi, so maria_buffer_size is per connection and would multiply too much?
[14:02]  <montywi> Another option is to just increase tmp_table_size for the thread that runs the background queries
[14:03]  <montywi> no maria_buffer_size is for everyone. It's allocated at start and will not grow
[14:03]  <rfay> montywi, well, I can certainly increase that then
[14:03]  * cashwilliams has quit (Quit: Leaving.)
[14:04]  <montywi> in memory tmp tables are allocated on demand and grows per connection up to tmp_table_size
[14:04]  <rfay> montywi, you also recommend going to 5.2 or higher?
[14:04]  <montywi> you should try 5.5;  It's stable and it's faster for complex queries
[14:04]  <rfay> montywi, I'll do it then.
[14:04]  <rfay> Should work fine.
[14:05]  <montywi> hope the above helps...
[14:05]  <rfay> montywi, you're absolutely awesome. litheum and ebergen thanks so much for the help!
[14:05]  <rfay> montywi++
[14:05]  <rfay> litheum++
[14:05]  <rfay> ebergen++
[14:06]  <rfay> The best view of all this I've *ever* had. After being frustrated by it forever.