On 8/14/13 10:46 AM, Manuel Arostegui wrote:
2013/8/14 Andy Wallace <awall...@ihouseweb.com <mailto:awall...@ihouseweb.com>>
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.
awall...@ihouseweb.com
(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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
