Easing The Load of tt-rss on Mysql?

Support requests, bug reports, etc. go here. Dedicated servers / VDS hosting only
mvtango
Bear Rating Trainee
Bear Rating Trainee
Posts: 4
Joined: 31 Jul 2013, 14:33

Easing The Load of tt-rss on Mysql?

Postby mvtango » 31 Jul 2013, 14:49

Hello there,

everything's up and running. But the database is spinning a lot. There are some indications that this might be an index issue. See my diagnosis below. At the bottom, there's the result of mysqldump -d for the database. Thanks for your help!

M

Trouble:

* Thousands of Innodb_buffer_pool_reads with only one user reading on tt-rss. (http://dev.mysql.com/doc/refman/5.5/en/ ... pool_reads)

* Thousands of Heandler_read_rnd and millions of Handler_read_rnd_next with only one user reading.
http://dev.mysql.com/doc/refman/5.5/en/ ... d_rnd_next

Code: Select all


- MySQL dump 10.13  Distrib 5.5.32, for debian-linux-gnu (x86_64)
--
-- Host: sql    Database: virtel_selfoss
-- ------------------------------------------------------
-- Server version       5.5.32-0ubuntu0.12.04.1

/*!40101 SET @[email protected]@CHARACTER_SET_CLIENT */;
/*!40101 SET @[email protected]@CHARACTER_SET_RESULTS */;
/*!40101 SET @[email protected]@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @[email protected]@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @[email protected]@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @[email protected]@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `ttrss_access_keys`
--

DROP TABLE IF EXISTS `ttrss_access_keys`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ttrss_access_keys` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `access_key` varchar(250) NOT NULL,
  `feed_id` varchar(250) NOT NULL,
  `is_cat` tinyint(1) NOT NULL DEFAULT '0',
  `owner_uid` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `owner_uid` (`owner_uid`),
  CONSTRAINT `ttrss_access_keys_ibfk_1` FOREIGN KEY (`owner_uid`) REFERENCES `ttrss_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `ttrss_archived_feeds`
--

DROP TABLE IF EXISTS `ttrss_archived_feeds`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ttrss_archived_feeds` (
  `id` int(11) NOT NULL,
  `owner_uid` int(11) NOT NULL,
  `title` varchar(200) NOT NULL,
  `feed_url` text NOT NULL,
  `site_url` varchar(250) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `owner_uid` (`owner_uid`),
  CONSTRAINT `ttrss_archived_feeds_ibfk_1` FOREIGN KEY (`owner_uid`) REFERENCES `ttrss_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `ttrss_cat_counters_cache`
--

DROP TABLE IF EXISTS `ttrss_cat_counters_cache`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ttrss_cat_counters_cache` (
  `feed_id` int(11) NOT NULL,
  `owner_uid` int(11) NOT NULL,
  `value` int(11) NOT NULL DEFAULT '0',
  `updated` datetime NOT NULL,
  KEY `ttrss_cat_counters_cache_owner_uid_idx` (`owner_uid`),
  CONSTRAINT `ttrss_cat_counters_cache_ibfk_1` FOREIGN KEY (`owner_uid`) REFERENCES `ttrss_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `ttrss_counters_cache`
--

DROP TABLE IF EXISTS `ttrss_counters_cache`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ttrss_counters_cache` (
  `feed_id` int(11) NOT NULL,
  `owner_uid` int(11) NOT NULL,
  `value` int(11) NOT NULL DEFAULT '0',
  `updated` datetime NOT NULL,
  KEY `ttrss_counters_cache_feed_id_idx` (`feed_id`),
  KEY `ttrss_counters_cache_owner_uid_idx` (`owner_uid`),
  KEY `ttrss_counters_cache_value_idx` (`value`),
  CONSTRAINT `ttrss_counters_cache_ibfk_1` FOREIGN KEY (`owner_uid`) REFERENCES `ttrss_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `ttrss_enclosures`
--

DROP TABLE IF EXISTS `ttrss_enclosures`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ttrss_enclosures` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `content_url` text NOT NULL,
  `content_type` varchar(250) NOT NULL,
  `post_id` int(11) NOT NULL,
  `title` text NOT NULL,
  `duration` text NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ttrss_enclosures_post_id_idx` (`post_id`),
  CONSTRAINT `ttrss_enclosures_ibfk_1` FOREIGN KEY (`post_id`) REFERENCES `ttrss_entries` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=38563 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `ttrss_entries`
--

DROP TABLE IF EXISTS `ttrss_entries`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ttrss_entries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` text NOT NULL,
  `guid` varchar(255) NOT NULL,
  `link` text NOT NULL,
  `updated` datetime NOT NULL,
  `content` longtext NOT NULL,
  `content_hash` varchar(250) NOT NULL,
  `cached_content` longtext,
  `no_orig_date` tinyint(1) NOT NULL DEFAULT '0',
  `date_entered` datetime NOT NULL,
  `date_updated` datetime NOT NULL,
  `num_comments` int(11) NOT NULL DEFAULT '0',
  `plugin_data` longtext,
  `comments` varchar(250) NOT NULL DEFAULT '',
  `author` varchar(250) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `ttrss_entries_date_entered_index` (`date_entered`),
  KEY `ttrss_entries_guid_index` (`guid`),
  KEY `ttrss_entries_updated_idx` (`updated`),
  KEY `date_updated` (`date_updated`)
) ENGINE=InnoDB AUTO_INCREMENT=107831 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `ttrss_entry_comments`
--

DROP TABLE IF EXISTS `ttrss_entry_comments`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ttrss_entry_comments` (
  `id` int(11) NOT NULL,
  `ref_id` int(11) NOT NULL,
  `owner_uid` int(11) NOT NULL,
  `private` tinyint(1) NOT NULL DEFAULT '0',
  `date_entered` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `ref_id` (`ref_id`),
  KEY `owner_uid` (`owner_uid`),
  CONSTRAINT `ttrss_entry_comments_ibfk_1` FOREIGN KEY (`ref_id`) REFERENCES `ttrss_entries` (`id`) ON DELETE CASCADE,
  CONSTRAINT `ttrss_entry_comments_ibfk_2` FOREIGN KEY (`owner_uid`) REFERENCES `ttrss_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `ttrss_error_log`
--

DROP TABLE IF EXISTS `ttrss_error_log`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ttrss_error_log` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `owner_uid` int(11) DEFAULT NULL,
  `errno` int(11) NOT NULL,
  `errstr` text NOT NULL,
  `filename` text NOT NULL,
  `lineno` int(11) NOT NULL,
  `context` text NOT NULL,
  `created_at` datetime NOT NULL,
  PRIMARY KEY (`id`),
  KEY `owner_uid` (`owner_uid`),
  CONSTRAINT `ttrss_error_log_ibfk_1` FOREIGN KEY (`owner_uid`) REFERENCES `ttrss_users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=208 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `ttrss_feed_categories`
--

DROP TABLE IF EXISTS `ttrss_feed_categories`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ttrss_feed_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `owner_uid` int(11) NOT NULL,
  `title` varchar(200) NOT NULL,
  `collapsed` tinyint(1) NOT NULL DEFAULT '0',
  `order_id` int(11) NOT NULL DEFAULT '0',
  `parent_cat` int(11) DEFAULT NULL,
  `view_settings` varchar(250) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `parent_cat` (`parent_cat`),
  KEY `owner_uid` (`owner_uid`),
  CONSTRAINT `ttrss_feed_categories_ibfk_1` FOREIGN KEY (`parent_cat`) REFERENCES `ttrss_feed_categories` (`id`) ON DELETE SET NULL,
  CONSTRAINT `ttrss_feed_categories_ibfk_2` FOREIGN KEY (`owner_uid`) REFERENCES `ttrss_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=67 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `ttrss_feedbrowser_cache`
--

DROP TABLE IF EXISTS `ttrss_feedbrowser_cache`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ttrss_feedbrowser_cache` (
  `feed_url` text NOT NULL,
  `site_url` text NOT NULL,
  `title` text NOT NULL,
  `subscribers` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `ttrss_feeds`
--

DROP TABLE IF EXISTS `ttrss_feeds`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ttrss_feeds` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `owner_uid` int(11) NOT NULL,
  `title` varchar(200) NOT NULL,
  `cat_id` int(11) DEFAULT NULL,
  `feed_url` text NOT NULL,
  `icon_url` varchar(250) NOT NULL DEFAULT '',
  `update_interval` int(11) NOT NULL DEFAULT '0',
  `purge_interval` int(11) NOT NULL DEFAULT '0',
  `last_updated` datetime DEFAULT '0000-00-00 00:00:00',
  `last_error` varchar(250) NOT NULL DEFAULT '',
  `favicon_avg_color` varchar(11) DEFAULT NULL,
  `site_url` varchar(250) NOT NULL DEFAULT '',
  `auth_login` varchar(250) NOT NULL DEFAULT '',
  `auth_pass` varchar(250) NOT NULL DEFAULT '',
  `parent_feed` int(11) DEFAULT NULL,
  `private` tinyint(1) NOT NULL DEFAULT '0',
  `rtl_content` tinyint(1) NOT NULL DEFAULT '0',
  `hidden` tinyint(1) NOT NULL DEFAULT '0',
  `include_in_digest` tinyint(1) NOT NULL DEFAULT '1',
  `cache_images` tinyint(1) NOT NULL DEFAULT '0',
  `hide_images` tinyint(1) NOT NULL DEFAULT '0',
  `cache_content` tinyint(1) NOT NULL DEFAULT '0',
  `auth_pass_encrypted` tinyint(1) NOT NULL DEFAULT '0',
  `last_viewed` datetime DEFAULT NULL,
  `last_update_started` datetime DEFAULT NULL,
  `always_display_enclosures` tinyint(1) NOT NULL DEFAULT '0',
  `update_method` int(11) NOT NULL DEFAULT '0',
  `order_id` int(11) NOT NULL DEFAULT '0',
  `mark_unread_on_update` tinyint(1) NOT NULL DEFAULT '0',
  `update_on_checksum_change` tinyint(1) NOT NULL DEFAULT '0',
  `strip_images` tinyint(1) NOT NULL DEFAULT '0',
  `view_settings` varchar(250) NOT NULL DEFAULT '',
  `pubsub_state` int(11) NOT NULL DEFAULT '0',
  `favicon_last_checked` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `parent_feed` (`parent_feed`),
  KEY `ttrss_feeds_owner_uid_index` (`owner_uid`),
  KEY `ttrss_feeds_cat_id_idx` (`cat_id`),
  CONSTRAINT `ttrss_feeds_ibfk_1` FOREIGN KEY (`owner_uid`) REFERENCES `ttrss_users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `ttrss_feeds_ibfk_2` FOREIGN KEY (`cat_id`) REFERENCES `ttrss_feed_categories` (`id`) ON DELETE SET NULL,
  CONSTRAINT `ttrss_feeds_ibfk_3` FOREIGN KEY (`parent_feed`) REFERENCES `ttrss_feeds` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=1292 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `ttrss_filter_actions`
--

DROP TABLE IF EXISTS `ttrss_filter_actions`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ttrss_filter_actions` (
  `id` int(11) NOT NULL,
  `name` varchar(120) NOT NULL,
  `description` varchar(250) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  UNIQUE KEY `description` (`description`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `ttrss_filter_types`
--

DROP TABLE IF EXISTS `ttrss_filter_types`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ttrss_filter_types` (
  `id` int(11) NOT NULL,
  `name` varchar(120) NOT NULL,
  `description` varchar(250) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  UNIQUE KEY `description` (`description`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `ttrss_filters2`
--

DROP TABLE IF EXISTS `ttrss_filters2`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ttrss_filters2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `owner_uid` int(11) NOT NULL,
  `match_any_rule` tinyint(1) NOT NULL DEFAULT '0',
  `enabled` tinyint(1) NOT NULL DEFAULT '1',
  `inverse` tinyint(1) NOT NULL DEFAULT '0',
  `title` varchar(250) NOT NULL DEFAULT '',
  `order_id` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `owner_uid` (`owner_uid`),
  CONSTRAINT `ttrss_filters2_ibfk_1` FOREIGN KEY (`owner_uid`) REFERENCES `ttrss_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `ttrss_filters2_actions`
--

DROP TABLE IF EXISTS `ttrss_filters2_actions`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ttrss_filters2_actions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `filter_id` int(11) NOT NULL,
  `action_id` int(11) NOT NULL DEFAULT '1',
  `action_param` varchar(250) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `filter_id` (`filter_id`),
  KEY `action_id` (`action_id`),
  CONSTRAINT `ttrss_filters2_actions_ibfk_1` FOREIGN KEY (`filter_id`) REFERENCES `ttrss_filters2` (`id`) ON DELETE CASCADE,
  CONSTRAINT `ttrss_filters2_actions_ibfk_2` FOREIGN KEY (`action_id`) REFERENCES `ttrss_filter_actions` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `ttrss_filters2_rules`
--

DROP TABLE IF EXISTS `ttrss_filters2_rules`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ttrss_filters2_rules` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `filter_id` int(11) NOT NULL,
  `reg_exp` varchar(250) NOT NULL,
  `inverse` tinyint(1) NOT NULL DEFAULT '0',
  `filter_type` int(11) NOT NULL,
  `feed_id` int(11) DEFAULT NULL,
  `cat_id` int(11) DEFAULT NULL,
  `cat_filter` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `filter_id` (`filter_id`),
  KEY `filter_type` (`filter_type`),
  KEY `feed_id` (`feed_id`),
  KEY `cat_id` (`cat_id`),
  CONSTRAINT `ttrss_filters2_rules_ibfk_1` FOREIGN KEY (`filter_id`) REFERENCES `ttrss_filters2` (`id`) ON DELETE CASCADE,
  CONSTRAINT `ttrss_filters2_rules_ibfk_2` FOREIGN KEY (`filter_type`) REFERENCES `ttrss_filter_types` (`id`) ON DELETE CASCADE,
  CONSTRAINT `ttrss_filters2_rules_ibfk_3` FOREIGN KEY (`feed_id`) REFERENCES `ttrss_feeds` (`id`) ON DELETE CASCADE,
  CONSTRAINT `ttrss_filters2_rules_ibfk_4` FOREIGN KEY (`cat_id`) REFERENCES `ttrss_feed_categories` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `ttrss_labels2`
--

DROP TABLE IF EXISTS `ttrss_labels2`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ttrss_labels2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `owner_uid` int(11) NOT NULL,
  `caption` varchar(250) NOT NULL,
  `fg_color` varchar(15) NOT NULL DEFAULT '',
  `bg_color` varchar(15) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `owner_uid` (`owner_uid`),
  CONSTRAINT `ttrss_labels2_ibfk_1` FOREIGN KEY (`owner_uid`) REFERENCES `ttrss_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `ttrss_linked_feeds`
--

DROP TABLE IF EXISTS `ttrss_linked_feeds`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ttrss_linked_feeds` (
  `feed_url` text NOT NULL,
  `site_url` text NOT NULL,
  `title` text NOT NULL,
  `created` datetime NOT NULL,
  `updated` datetime NOT NULL,
  `instance_id` int(11) NOT NULL,
  `subscribers` int(11) NOT NULL,
  KEY `instance_id` (`instance_id`),
  CONSTRAINT `ttrss_linked_feeds_ibfk_1` FOREIGN KEY (`instance_id`) REFERENCES `ttrss_linked_instances` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `ttrss_linked_instances`
--

DROP TABLE IF EXISTS `ttrss_linked_instances`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ttrss_linked_instances` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `last_connected` datetime NOT NULL,
  `last_status_in` int(11) NOT NULL,
  `last_status_out` int(11) NOT NULL,
  `access_key` varchar(250) NOT NULL,
  `access_url` text NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `access_key` (`access_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `ttrss_plugin_storage`
--

DROP TABLE IF EXISTS `ttrss_plugin_storage`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ttrss_plugin_storage` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `owner_uid` int(11) NOT NULL,
  `content` longtext NOT NULL,
  PRIMARY KEY (`id`),
  KEY `owner_uid` (`owner_uid`),
  CONSTRAINT `ttrss_plugin_storage_ibfk_1` FOREIGN KEY (`owner_uid`) REFERENCES `ttrss_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `ttrss_prefs`
--

DROP TABLE IF EXISTS `ttrss_prefs`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ttrss_prefs` (
  `pref_name` varchar(250) NOT NULL,
  `type_id` int(11) NOT NULL,
  `section_id` int(11) NOT NULL DEFAULT '1',
  `access_level` int(11) NOT NULL DEFAULT '0',
  `def_value` text NOT NULL,
  PRIMARY KEY (`pref_name`),
  KEY `type_id` (`type_id`),
  KEY `section_id` (`section_id`),
  CONSTRAINT `ttrss_prefs_ibfk_1` FOREIGN KEY (`type_id`) REFERENCES `ttrss_prefs_types` (`id`),
  CONSTRAINT `ttrss_prefs_ibfk_2` FOREIGN KEY (`section_id`) REFERENCES `ttrss_prefs_sections` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `ttrss_prefs_sections`
--

DROP TABLE IF EXISTS `ttrss_prefs_sections`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ttrss_prefs_sections` (
  `id` int(11) NOT NULL,
  `order_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `ttrss_prefs_types`
--

DROP TABLE IF EXISTS `ttrss_prefs_types`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ttrss_prefs_types` (
  `id` int(11) NOT NULL,
  `type_name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `ttrss_sessions`
--

DROP TABLE IF EXISTS `ttrss_sessions`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ttrss_sessions` (
  `id` varchar(250) NOT NULL,
  `data` text,
  `expire` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `expire` (`expire`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `ttrss_settings_profiles`
--

DROP TABLE IF EXISTS `ttrss_settings_profiles`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ttrss_settings_profiles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(250) NOT NULL,
  `owner_uid` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `owner_uid` (`owner_uid`),
  CONSTRAINT `ttrss_settings_profiles_ibfk_1` FOREIGN KEY (`owner_uid`) REFERENCES `ttrss_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `ttrss_tags`
--

DROP TABLE IF EXISTS `ttrss_tags`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ttrss_tags` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `owner_uid` int(11) NOT NULL,
  `tag_name` varchar(250) NOT NULL,
  `post_int_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `post_int_id` (`post_int_id`),
  KEY `owner_uid` (`owner_uid`),
  CONSTRAINT `ttrss_tags_ibfk_1` FOREIGN KEY (`post_int_id`) REFERENCES `ttrss_user_entries` (`int_id`) ON DELETE CASCADE,
  CONSTRAINT `ttrss_tags_ibfk_2` FOREIGN KEY (`owner_uid`) REFERENCES `ttrss_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=242714 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `ttrss_user_entries`
--

DROP TABLE IF EXISTS `ttrss_user_entries`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ttrss_user_entries` (
  `int_id` int(11) NOT NULL AUTO_INCREMENT,
  `ref_id` int(11) NOT NULL,
  `uuid` varchar(200) NOT NULL,
  `feed_id` int(11) DEFAULT NULL,
  `orig_feed_id` int(11) DEFAULT NULL,
  `owner_uid` int(11) NOT NULL,
  `marked` tinyint(1) NOT NULL DEFAULT '0',
  `published` tinyint(1) NOT NULL DEFAULT '0',
  `tag_cache` text NOT NULL,
  `label_cache` text NOT NULL,
  `last_read` datetime DEFAULT NULL,
  `score` int(11) NOT NULL DEFAULT '0',
  `note` longtext,
  `last_marked` datetime DEFAULT NULL,
  `last_published` datetime DEFAULT NULL,
  `unread` tinyint(1) NOT NULL DEFAULT '1',
  PRIMARY KEY (`int_id`),
  KEY `orig_feed_id` (`orig_feed_id`),
  KEY `ttrss_user_entries_owner_uid_index` (`owner_uid`),
  KEY `ttrss_user_entries_ref_id_index` (`ref_id`),
  KEY `ttrss_user_entries_feed_id` (`feed_id`),
  KEY `ttrss_user_entries_unread_idx` (`unread`),
  CONSTRAINT `ttrss_user_entries_ibfk_1` FOREIGN KEY (`ref_id`) REFERENCES `ttrss_entries` (`id`) ON DELETE CASCADE,
  CONSTRAINT `ttrss_user_entries_ibfk_2` FOREIGN KEY (`feed_id`) REFERENCES `ttrss_feeds` (`id`) ON DELETE CASCADE,
  CONSTRAINT `ttrss_user_entries_ibfk_3` FOREIGN KEY (`orig_feed_id`) REFERENCES `ttrss_archived_feeds` (`id`) ON DELETE SET NULL,
  CONSTRAINT `ttrss_user_entries_ibfk_4` FOREIGN KEY (`owner_uid`) REFERENCES `ttrss_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=107831 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `ttrss_user_labels2`
--

DROP TABLE IF EXISTS `ttrss_user_labels2`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ttrss_user_labels2` (
  `label_id` int(11) NOT NULL,
  `article_id` int(11) NOT NULL,
  KEY `label_id` (`label_id`),
  KEY `article_id` (`article_id`),
  CONSTRAINT `ttrss_user_labels2_ibfk_1` FOREIGN KEY (`label_id`) REFERENCES `ttrss_labels2` (`id`) ON DELETE CASCADE,
  CONSTRAINT `ttrss_user_labels2_ibfk_2` FOREIGN KEY (`article_id`) REFERENCES `ttrss_entries` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `ttrss_user_prefs`
--

DROP TABLE IF EXISTS `ttrss_user_prefs`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ttrss_user_prefs` (
  `owner_uid` int(11) NOT NULL,
  `pref_name` varchar(250) DEFAULT NULL,
  `value` longtext NOT NULL,
  `profile` int(11) DEFAULT NULL,
  KEY `profile` (`profile`),
  KEY `ttrss_user_prefs_owner_uid_index` (`owner_uid`),
  KEY `ttrss_user_prefs_pref_name_idx` (`pref_name`),
  CONSTRAINT `ttrss_user_prefs_ibfk_1` FOREIGN KEY (`profile`) REFERENCES `ttrss_settings_profiles` (`id`) ON DELETE CASCADE,
  CONSTRAINT `ttrss_user_prefs_ibfk_2` FOREIGN KEY (`owner_uid`) REFERENCES `ttrss_users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `ttrss_user_prefs_ibfk_3` FOREIGN KEY (`pref_name`) REFERENCES `ttrss_prefs` (`pref_name`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `ttrss_users`
--

DROP TABLE IF EXISTS `ttrss_users`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ttrss_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `login` varchar(120) NOT NULL,
  `pwd_hash` varchar(250) NOT NULL,
  `last_login` datetime DEFAULT NULL,
  `access_level` int(11) NOT NULL DEFAULT '0',
  `theme_id` int(11) DEFAULT NULL,
  `email` varchar(250) NOT NULL DEFAULT '',
  `full_name` varchar(250) NOT NULL DEFAULT '',
  `email_digest` tinyint(1) NOT NULL DEFAULT '0',
  `last_digest_sent` datetime DEFAULT NULL,
  `salt` varchar(250) NOT NULL DEFAULT '',
  `created` datetime DEFAULT NULL,
  `twitter_oauth` longtext,
  `otp_enabled` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `login` (`login`),
  KEY `theme_id` (`theme_id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `ttrss_version`
--

DROP TABLE IF EXISTS `ttrss_version`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `ttrss_version` (
  `schema_version` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET [email protected]_TIME_ZONE */;

/*!40101 SET [email protected]_SQL_MODE */;
/*!40014 SET [email protected]_FOREIGN_KEY_CHECKS */;
/*!40014 SET [email protected]_UNIQUE_CHECKS */;
/*!40101 SET [email protected]_CHARACTER_SET_CLIENT */;
/*!40101 SET [email protected]_CHARACTER_SET_RESULTS */;
/*!40101 SET [email protected]_COLLATION_CONNECTION */;
/*!40111 SET [email protected]_SQL_NOTES */;

-- Dump completed on 2013-07-31 12:46:11
edit: some people really should learn how to use the code bits by themselves --sleeper

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

Re: Easing The Load of tt-rss on Mysql?

Postby fox » 31 Jul 2013, 14:59

That's a nice wall of text.

User avatar
blainemono
Gaping Anus of Eternity
Gaping Anus of Eternity
Posts: 246
Joined: 05 Jun 2009, 18:13

Re: Easing The Load of tt-rss on Mysql?

Postby blainemono » 31 Jul 2013, 15:06

See my diagnosis above:

mvtango
Bear Rating Trainee
Bear Rating Trainee
Posts: 4
Joined: 31 Jul 2013, 14:33

Re: Easing The Load of tt-rss on Mysql?

Postby mvtango » 31 Jul 2013, 15:27

Update: Millions of Handler_read_rnd_next happen while the update demon is running as well.

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

Re: Easing The Load of tt-rss on Mysql?

Postby fox » 31 Jul 2013, 15:57

It is as if the application is using the database :ponder:

mvtango
Bear Rating Trainee
Bear Rating Trainee
Posts: 4
Joined: 31 Jul 2013, 14:33

Re: Easing The Load of tt-rss on Mysql?

Postby mvtango » 31 Jul 2013, 17:12

Not quite - if the database was properly indexed, these events wouldn't occur. To quote from the docs:

"this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have."

That would be my point - any suggestions as to which indexes to add?

M

mvtango
Bear Rating Trainee
Bear Rating Trainee
Posts: 4
Joined: 31 Jul 2013, 14:33

Re: Easing The Load of tt-rss on Mysql?

Postby mvtango » 31 Jul 2013, 17:24

More indicators:

Select_full_join is 336 " If this value is not 0, you should carefully check the indexes of your tables."
Select_range_check 241 " (If this is not equal to 0, you should very carefully check the indexes of your tables.) "

Both quotes from the docs.

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

Re: Easing The Load of tt-rss on Mysql?

Postby fox » 31 Jul 2013, 21:01

I'm sure it would very easy for you to figure out since you know so much about indexes and whatnot.

feader
Bear Rating Master
Bear Rating Master
Posts: 160
Joined: 26 Dec 2012, 20:03

Re: Easing The Load of tt-rss on Mysql?

Postby feader » 31 Jul 2013, 22:35

If you want to ease MySQL's load, you can use PgSQL.

YEEEEEAAAAAAAAAAAAAAAAAAAAAAAA­AAAAAAAAH

(SCNR, but if you don't give any reasonable details about what you think should change, this is one of the more meaningful answers you will get here.)

xtaz
Bear Rating Master
Bear Rating Master
Posts: 174
Joined: 24 Dec 2009, 16:48

Re: Easing The Load of tt-rss on Mysql?

Postby xtaz » 01 Aug 2013, 11:55

I've always been a MySQL user (and in the last year MariaDB), but have become increasingly disillusioned with it. All these types of threads on this forum made me decide to install and learn PgSQL last night. After spending an hour learning how it works got tt-rss running on it pretty easily and things do seem a bit more responsive. The server is using way less memory and disk I/O has dropped to virtually nothing. Seems a no-brainer when you actually try it out. Now I think I'll spend some time converting my website from using MySQLi code to using PgSQL via PDO.

lyda
Bear Rating Trainee
Bear Rating Trainee
Posts: 7
Joined: 25 Jun 2013, 18:05

Re: Easing The Load of tt-rss on Mysql?

Postby lyda » 01 Aug 2013, 12:56

Is it difficult to migrate from mysql to postgres for tt-rss?

lyda
Bear Rating Trainee
Bear Rating Trainee
Posts: 7
Joined: 25 Jun 2013, 18:05

Re: Easing The Load of tt-rss on Mysql?

Postby lyda » 01 Aug 2013, 13:01

lyda wrote:Is it difficult to migrate from mysql to postgres for tt-rss?


And to answer my own question, this thread discusses how to do it: viewtopic.php?f=1&t=330

As for the original poster's question I'm assuming enabling the slow query log and then running EXPLAINs on the frequent denizens of it would be a way to start looking to add indexes. Then it's just a matter of submitting patches to the schema.

I can give it a go with my current install when I upgrade to 1.9.

xtaz
Bear Rating Master
Bear Rating Master
Posts: 174
Joined: 24 Dec 2009, 16:48

Re: Easing The Load of tt-rss on Mysql?

Postby xtaz » 01 Aug 2013, 13:40

lyda wrote:Is it difficult to migrate from mysql to postgres for tt-rss?


I personally just exported the feeds and settings from the OPML export, installed a brand new tt-rss schema, and imported the OPML in again. Probably quicker and easier than playing around trying to export data. Obviously it means all my feeds become unread again but that's not a big deal. Just clicked mark all as read and go from there.

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

Re: Easing The Load of tt-rss on Mysql?

Postby hrk » 01 Aug 2013, 15:58

You can use the tool I wrote https://github.com/hrk/tt-rss-mysql2pgsql, documented in the following thread http://tt-rss.org/forum/viewtopic.php?f=1&t=2116&p=11508&hilit=postgres#p12388 at the end of page 3.

Create a new database in PgSQL, make sure the user is enabled, execute the TT-RSS schema as you normally would to set up a new instance.
Stop the tt-rss service if it is running, or the cron-job or whatever you use to update your (mysql) instance.

Run the tool (requires Java).

When it completes, edit config.php in the TT-RSS folder and set up pgsql as database engine. Login and be happy.


Return to “Support”

Who is online

Users browsing this forum: No registered users and 10 guests