MYSQL huge temp files with android client

Support requests, bug reports, etc. go here. Dedicated servers / VDS hosting only
GabrieleV
Bear Rating Trainee
Bear Rating Trainee
Posts: 3
Joined: 27 Nov 2013, 14:58

MYSQL huge temp files with android client

Postby GabrieleV » 27 Nov 2013, 15:06

Hello,
I noticed that when I try to login with tt-rss android client, on the tt-rss server, mysql starts writing huge temp files to /tmp named like

Code: Select all

-rw-rw----  1 mysql      mysql      810078468 Nov 27 11:51 #sql_2119_0.MYD
-rw-rw----  1 mysql      mysql           2048 Nov 27 11:51 #sql_2119_0.MYI
-rw-rw----  1 mysql      mysql        7881996 Nov 27 11:51 #sql_2119_1.MYD
-rw-rw----  1 mysql      mysql           2048 Nov 27 11:51 #sql_2119_1.MYI


These files quickly fill up all available disk space, and I have to stop apache2 and mysql to clean them up.
It seems that there is some query that is not optimized, as indicated here: temp tables - Why does MySQL produce so many temporary MYD files? - Database Administrators Stack Exchange

Suggestions ?

User avatar
fox
^ me reading your posts ^
Posts: 6318
Joined: 27 Aug 2005, 22:53
Location: Saint-Petersburg, Russia
Contact:

Re: MYSQL huge temp files with android client

Postby fox » 27 Nov 2013, 15:49

I suggest using a database server which doesn't start writing huge files on disk because some query is "not optimized" (whatever that means).

GabrieleV
Bear Rating Trainee
Bear Rating Trainee
Posts: 3
Joined: 27 Nov 2013, 14:58

Re: MYSQL huge temp files with android client

Postby GabrieleV » 27 Nov 2013, 17:59

Fantastic. Never experienced that behaviour with other php application. This is not a mysql problem, it's an application bug.

User avatar
fox
^ me reading your posts ^
Posts: 6318
Joined: 27 Aug 2005, 22:53
Location: Saint-Petersburg, Russia
Contact:

Re: MYSQL huge temp files with android client

Postby fox » 27 Nov 2013, 18:16

Well, to be fair, it's not like anyone here is stopping you from using other applications with your terrible database server. I would actually suggest you doing so and incidentally maybe not posting here.

craywolf
Mr. Awesome
Posts: 97
Joined: 19 Mar 2013, 18:07

Re: MYSQL huge temp files with android client

Postby craywolf » 27 Nov 2013, 18:31

TT-RSS v1.10.5bcb7b6
MySQL 14.14
PHP 5.3.3
CentOS 6.4

Not seeing these temp files. Logged out with the Android client, logged back in, poked around a bit ... nada.

User avatar
fox
^ me reading your posts ^
Posts: 6318
Joined: 27 Aug 2005, 22:53
Location: Saint-Petersburg, Russia
Contact:

Re: MYSQL huge temp files with android client

Postby fox » 27 Nov 2013, 18:49

No you see it's an application bug because of temporary tables which tt-rss doesn't use, and

User avatar
sleeper_service
Bear Rating Overlord
Bear Rating Overlord
Posts: 884
Joined: 30 Mar 2013, 23:50
Location: Dallas, Texas

Re: MYSQL huge temp files with android client

Postby sleeper_service » 27 Nov 2013, 19:12

GabrieleV wrote:Suggestions ?


many people are running this software, one, so far, has complained about this issue.

try doing the investigation suggested in the link that you posted, and figure out what's happening, instead of a "something doesn't work, what's wrong" meaningless post.

ps, seems they're talking a lot about myisam issues, you shouldn't have any myisam tables in your db.

GabrieleV
Bear Rating Trainee
Bear Rating Trainee
Posts: 3
Joined: 27 Nov 2013, 14:58

Re: MYSQL huge temp files with android client

Postby GabrieleV » 27 Nov 2013, 21:41

The generation of the temp files is directly related to the login of the android client. It has never occured before on this machine, and I can reproduce it every time.
I've checked that all the tables used by tt-rss have INNODB engine type. The tt-rss database user has access only to it's own database.
Logging slow queries, I've found that this is one of the queries that creates the problem:

Code: Select all

Count: 3  Time=158.67s (476s)  Lock=0.00s (0s)  Rows=200.0 (600), tt-rss[tt-rss]@localhost

SELECT DISTINCT
  date_entered,
  guid,
  ttrss_entries.id,ttrss_entries.title,
  updated,
  label_cache,
  tag_cache,
  always_display_enclosures,
  site_url,
  note,
  num_comments,
  comments,
  int_id,
  uuid,
  lang,
  hide_images,
  unread,feed_id,marked,published,link,last_read,orig_feed_id,
  last_marked, last_published,
  ttrss_feeds.title AS feed_title,favicon_avg_color,
  content, content AS content_preview,
  author,score
  FROM
  ttrss_entries,ttrss_user_entries
  LEFT JOIN ttrss_feeds ON (feed_id = ttrss_feeds.id)
  WHERE
  ttrss_user_entries.ref_id = ttrss_entries.id AND
  ttrss_user_entries.owner_uid = 'S' AND
  unread = true AND
  true ORDER BY score DESC, date_entered DESC, updated DESC
  LIMIT N OFFSET N


I'm not a database expert. I'm not an expert.

User avatar
fox
^ me reading your posts ^
Posts: 6318
Joined: 27 Aug 2005, 22:53
Location: Saint-Petersburg, Russia
Contact:

Re: MYSQL huge temp files with android client

Postby fox » 27 Nov 2013, 21:56

Maybe you should remove the query, see where it takes you. Experiment.

hrk
Bear Rating Disaster
Bear Rating Disaster
Posts: 75
Joined: 24 Apr 2013, 12:39

Re: MYSQL huge temp files with android client

Postby hrk » 27 Nov 2013, 22:26

In the (unlikely) event that such a query is really the culprit, you can use http://dev.mysql.com/doc/refman/5.5/en/execution-plan-information.html an EXPLAIN statement to check it out.

I used MySQL and the Android client for a very short while, then I moved to PostgreSQL. I even wrote a tool to migrate an existing MySQL instance of ttrss-db to PG without losing any data. It's linked on the forum somewhere (or you can find it on my GitHub: https://github.com/hrk

Edit: when I used MySQL+Android client I had no such issues.

richm
Bear Rating Trainee
Bear Rating Trainee
Posts: 2
Joined: 07 Oct 2013, 18:08

Re: MYSQL huge temp files with android client

Postby richm » 28 Nov 2013, 21:08

I suspect it is a problem with MySQL. I ran explain on the query against my database and the query planner states that it is using a temporary table.

Code: Select all

+----+-------------+--------------------+-------------+-------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------+---------+----------------------------------+------+--------------------------------------------------------------------------------------------------------+
| id | select_type | table              | type        | possible_keys                                                                                                                 | key                                     | key_len | ref                              | rows | Extra                                                                                                  |
+----+-------------+--------------------+-------------+-------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------+---------+----------------------------------+------+--------------------------------------------------------------------------------------------------------+
|  1 | SIMPLE      | ttrss_user_entries | index_merge | ref_id,owner_uid,ttrss_user_entries_owner_uid_index,ttrss_user_entries_ref_id_index,ttrss_user_entries_unread_idx             | ttrss_user_entries_unread_idx,owner_uid | 1,4     | NULL                             |  496 | Using intersect(ttrss_user_entries_unread_idx,owner_uid); Using where; Using temporary; Using filesort |
|  1 | SIMPLE      | ttrss_feeds        | eq_ref      | PRIMARY                                                                                                                       | PRIMARY                                 | 4       | ttrss.ttrss_user_entries.feed_id |    1 |                                                                                                        |
|  1 | SIMPLE      | ttrss_entries      | eq_ref      | PRIMARY                                                                                                                       | PRIMARY                                 | 4       | ttrss.ttrss_user_entries.ref_id  |    1 |                                                                                                        |
+----+-------------+--------------------+-------------+-------------------------------------------------------------------------------------------------------------------------------+-----------------------------------------+---------+----------------------------------+------+--------------------------------------------------------------------------------------------------------+


Given that the query selects the content field twice (as content and content_preview) that would cause the temporary table to be quite large if any of the feed entries has lots of text in it.

I couldn't see any obvious way to make the query not use a temporary table.

I did initially think that adding an extra index to ttrss_user_entries that included `owner_uid` and `ref_id` together but don't think that will work because the ORDER BY is on score from ttrss_user_entries and then date_entered/updated from ttrss_entries.

User avatar
fox
^ me reading your posts ^
Posts: 6318
Joined: 27 Aug 2005, 22:53
Location: Saint-Petersburg, Russia
Contact:

Re: MYSQL huge temp files with android client

Postby fox » 29 Nov 2013, 13:55

Well, fetching content into two variables is not needed anymore (I forgot about it completely), I'll remove it. Not that it somehow should lead to gigabytes of random files but I digress.

JustAMacUser
Bear Rating Overlord
Bear Rating Overlord
Posts: 373
Joined: 20 Aug 2013, 23:13

Re: MYSQL huge temp files with android client

Postby JustAMacUser » 29 Nov 2013, 20:47

richm wrote:Given that the query selects the content field twice (as content and content_preview) that would cause the temporary table to be quite large if any of the feed entries has lots of text in it.

I couldn't see any obvious way to make the query not use a temporary table.

I did initially think that adding an extra index to ttrss_user_entries that included `owner_uid` and `ref_id` together but don't think that will work because the ORDER BY is on score from ttrss_user_entries and then date_entered/updated from ttrss_entries.


Just for the record, temporary tables aren't a bad thing. It happens all the time. The issue is that when MySQL runs out of memory to build and parse the data, it needs to write it to disk. Writing to disk impacts performance. You can increase certain buffers but based on the file sizes being reported it wouldn't really be feasible in this situation. The key here lies in understanding what's causing the huge query, and my initial instinct tells me it has something to do with the types of feeds to which the user is subscribed.

Sorting across columns from separate tables shouldn't be a problem either if both columns are indexed. If they're not, then MySQL has to do a bit more work to sort the data, but that's true of any "order by" operation.


Return to “Support”

Who is online

Users browsing this forum: No registered users and 10 guests