>
> > We are on a quest to improve the overall performance of our database.
> It's
> > generally
> > working pretty well, but we periodically get big slowdowns for no
> apparent
> > reason. A
> > prime example today - in the command line interface to the DB, I tried to
> > update one
> > record, and got:
> >
> > mysql> update agent set number_of_emails = 5 where acnt = 'AR287416';
> > Query OK, 1 row affected (36.35 sec)
> > Rows matched: 1 Changed: 1 Warnings: 0
> >
> > 36 seconds to update one table? The primary key is `acnt`. If I run the
> > same (basic)
>
Check if there is some DELETE running for the selected table.
If there is a DELETE involving whole table it might be locking up database.
Look into mysql-slow.log
Try to optimize your application queries with EXPLAIN.
>[!!] InnoDB data size / buffer pool: 7.8G/5.5G
>Variables to adjust: innodb_buffer_pool_size (>= 7G)
>2 GB innodb_buffer_pool is a joke for a dataset of 33 GB
>that leads in permanently I/O on mixed load at the chances
>are high that there are times where nothing needed to
>operate is in the buffer_pool and on concurrent load
>mysqld ends in repeatly swap data in and out of the pool
>at least all repeatly accessed tables should fit
>permanently in the buffer
it depends on the load and how much data you're acquiring.
if you have 33GB in total, but only using few same tables in total size of
less than 2GB at the same time it would work just fine.
for example I have 136GB of data, but my buffer is only about 10Gb, but
most of the queries work just fine (I'm using it for mostly read-only
things).
but ofc, you need to check your system usage, if mysqld swaps its a bad
thing and most likely you need to either upgrade your hardware or consider
checking your data architecture (i.e. use LIMIT for quieries, add more
indexes, split large tables for a smaller ones which you really update or
store large data in mongodb etc).
> > command again a few seconds later, I get:
> >
> > mysql> update agent set number_of_emails = 15 where acnt =
> 'AR287416';
> > Query OK, 1 row affected (0.00 sec)
> > Rows matched: 1 Changed: 1 Warnings: 0
> >
> > Why would we be getting such huge variations? We're running Solaris 10 on
> > i386, with
> > 4 processors and 16GB of memory, MySQL 5.1.46-log. We are working out a
> > plan to upgrade
> > to MySQL 5.6, but I certainly don't want to depend on that upgrade to
> > solve all performance
> > problems.
> >
> > 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
> > `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,
> > `fname` varchar(30) DEFAULT NULL,
> > `lname` varchar(30) DEFAULT NULL,
> >
> > <<whole bunch of other fields>>
> >
> > PRIMARY KEY (`acnt`),
> > KEY `parent_acnt` (`parent_acnt`),
> > KEY `status` (`status`),
> > KEY `email` (`email`)
> > ) ENGINE=InnoDB DEFAULT CHARSET=utf8
> >
> >
> > --
> > 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
> >
> >
>
