List:General Discussion« Previous MessageNext Message »
From:Andy Wallace Date:August 14 2013 5:52pm
Subject:Re: Performance hiccoughs..
View as plain text  
On 8/14/13 10:46 AM, Manuel Arostegui wrote:
>
> 2013/8/14 Andy Wallace <awallace@stripped
> <mailto:awallace@stripped>>
>
>     Hey all -
>
>     We have been focusing on performance in our systems a lot lately, and have made
> some pretty
>     good progress. Upgrading the mySQL engine from 5.1 to 5.5 was eye-opening.
>
>     But there are still issues, and one in particular is vexing. It seems like a
> tuning problem
>     for sure - I notice this even at the command-line interface.  I will have a
> update command:
>
>        update my_table set test_column = 'tester_value' where key_value =
> 'a-test-key';
>
>     key_value is the primary key for my_table, which is an INNODB table, about 50MB,
> 96K rows
>
>
> Can you provide the whole show create table for that table?

It's a big table:

		CREATE TABLE `agent` (
		  `acnt` varchar(20) NOT NULL,
		  `passwd` varchar(20) NOT NULL,
		  `package` char(2) DEFAULT NULL,
		  `data_template` varchar(20) DEFAULT 'NULL',
		  `default_search_type` enum('1','2','3') NOT NULL DEFAULT '1',
		  `status` enum('A','T','P','C','D','X','S') NOT NULL COMMENT 'A=active, T=testdrive,
D=deactivated, P=pending, C=in create state, 
X=expired td,S=Suspended',
		  `flags` varchar(10) DEFAULT NULL COMMENT 'pipe-separated flags',
		  `aliases` varchar(4000) NOT NULL DEFAULT '',
		  `offices` varchar(4000) NOT NULL DEFAULT '',
		  `license_no` varchar(40) NOT NULL DEFAULT '',
		  `agent_code` varchar(20) DEFAULT NULL,
		  `office_code` varchar(20) DEFAULT NULL,
		  `parent_acnt` varchar(20) DEFAULT NULL,
		  `number_of_agentlinks` int(11) DEFAULT NULL,
		  `number_of_emails` int(11) DEFAULT NULL,
		  `name` varchar(60) NOT NULL,
		  `fname` varchar(30) DEFAULT NULL,
		  `lname` varchar(30) DEFAULT NULL,
		  `contact` varchar(80) NOT NULL DEFAULT '',
		  `company` varchar(80) NOT NULL DEFAULT '',
		  `address` varchar(100) NOT NULL DEFAULT '',
		  `city` varchar(80) NOT NULL DEFAULT '',
		  `state` varchar(2) NOT NULL DEFAULT '',
		  `zip` varchar(10) NOT NULL DEFAULT '',
		  `country` varchar(80) NOT NULL DEFAULT '',
		  `phone` varchar(100) NOT NULL DEFAULT '',
		  `fax` varchar(100) DEFAULT NULL,
		  `textline1` varchar(100) NOT NULL DEFAULT '',
		  `textline2` varchar(100) NOT NULL DEFAULT '',
		  `textline3` varchar(100) NOT NULL DEFAULT '',
		  `textline4` varchar(100) NOT NULL DEFAULT '',
		  `domain` varchar(80) NOT NULL DEFAULT '',
		  `email` varchar(80) NOT NULL,
		  `url` varchar(200) NOT NULL DEFAULT '',
		  `state_restriction` varchar(150) NOT NULL DEFAULT '',
		  `county_restriction` varchar(4000) NOT NULL DEFAULT '',
		  `area_restriction` varchar(4000) NOT NULL DEFAULT '',
		  `city_restriction` text,
		  `ht_freq` enum('d','w') NOT NULL DEFAULT 'd',
		  `ht_dow` enum('1','2','3','4','5','6','7') NOT NULL DEFAULT '1',
		  `signup_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
		  `disabled_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
		  `reactivation_date` date DEFAULT NULL,
		  `last_login_date` datetime DEFAULT NULL,
		  `testdrive_expires` date DEFAULT NULL,
		  `no_mls_value` varchar(100) DEFAULT NULL,
		  `internal_account` enum('N','Y') DEFAULT NULL,
		  `sample_account` enum('N','Y') NOT NULL DEFAULT 'N',
		  `is_ppc` enum('YES','NO') NOT NULL DEFAULT 'NO',
		  `is_leadbuilder` enum('YES','NO') NOT NULL DEFAULT 'NO',
		  `website_created` enum('N','Y') NOT NULL DEFAULT 'N',
		  `brand` int(11) NOT NULL DEFAULT '0',
		  `reseller_name` varchar(64) NOT NULL DEFAULT '',
		  `source_tracking` enum('wordpress') DEFAULT NULL,
		  `report_to_mls` enum('N','Y') NOT NULL DEFAULT 'Y',
		  `export_listings_to_parent` enum('N','Y') NOT NULL DEFAULT 'N',
		  `accepts_leads` enum('N','Y') DEFAULT 'Y',
		  `last_full_listing_update` datetime DEFAULT NULL,
		  `last_new_listing_update` datetime DEFAULT NULL,
		  `last_activity_reminder` datetime DEFAULT NULL,
		  `mobile_access_count` int(11) DEFAULT '0',
		  `testdrive_self_extend_count` int(11) DEFAULT '0',
		  `weak_password` enum('N','Y') DEFAULT 'N',
		  `weak_email_password` enum('N','Y') DEFAULT 'N',
		  PRIMARY KEY (`acnt`),
		  KEY `parent_acnt` (`parent_acnt`),
		  KEY `email` (`email`)
		) ENGINE=InnoDB DEFAULT CHARSET=utf8

The update sample translates to:

   update agent set domain = 'some_value.com' where acnt = 'some-account-id';


>     If I run this 10 times with different key values, most of the time, it will
> return pretty
>     much instantaneously. But at least once, it will take 10, 20, 30 seconds to
> return. This
>     affects our applications as well - operations that are generally fast will
> suddenly be
>     very, very slow... and then back to fast.
>
>     OS: SunOS 5.10
>     SQL version: 5.5.33-log MySQL Community Server (GPL)
>     Hardware: Virtual Machine (VMWare), 4 cpus - 16GB RAM
>
>     Tuning section of my.cnf:
>
>              # tuning
>              key_buffer_size=512M
>              max_allowed_packet=16M
>              table_open_cache=512
>              sort_buffer_size=10M
>              read_buffer_size=10M
>              read_rnd_buffer_size=8M
>              myisam_sort_buffer_size=512M
>              thread_cache_size=8
>              query_cache_type=1
>              query_cache_size=1024M
>              query_cache_limit=10M
>
> Have you done tests with query cache disabled? Depending on your workload you might
> get some better performance.

Have not tried disabling it - we have thousands of clients, all editing things, and many
of our
automated processes hit this table as well (updating statuses and dates, etc).

>
>              # 2 x numcpus
>              #thread_concurrency=4
>              #innodb_thread_concurrency=0
>              #innodb_read_io_threads=16
>              #innodb_write_io_threads=16
>              # You can set .._buffer_pool_size up to 50 - 80 %
>              # of RAM but beware of setting memory usage too high
>              innodb_buffer_pool_size=2048M
>
> Why only 2GB if you have 16GB in the machine? If possible try to allocate more memory
> for MySQL.

One thing we are playing with.

>
>              innodb_additional_mem_pool___size = 20M
>              # Set .._log_file_size to 25 % of buffer pool size
>              innodb_log_file_size=100M
>              innodb_log_buffer_size=8M
>              innodb_flush_log_at_trx___commit=1
>
> This can be a performance killer, try to set it to 0 and make sure you understand
> what it means
>
> (http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit)

Interesting, thanks for the reference/


>              innodb_lock_wait_timeout=50
>              innodb=on
>
> Do you have innodb_file_per_table enabled?

Yes, we do.

> Cheers,
> Manuel

Thanks!



-- 
Andy Wallace
iHOUSEweb, Inc.
awallace@stripped
(866) 645-7700 ext 219
--
"Sometimes it pays to stay in bed on Monday, rather than spending the rest of the week
debugging Monday's code."
- Christopher Thompson
Thread
Performance hiccoughs..Andy Wallace14 Aug
  • Re: Performance hiccoughs..Manuel Arostegui14 Aug
    • Re: Performance hiccoughs..Andy Wallace14 Aug