Super active table optimization

I have an innodb table with 200 million rows and growing.
It is a highly active table with tons of inserts and updates at all times.
I notice a select query I test on that table is 0.01 seconds or less
when all the inserts/updates are OFF.
But when I throttle up the writes to the table, the select sql then
takes like 1-3 full seconds or more.
This is a serious bottleneck in our application.
Any basic innodb wisdom for this scenario?

+-----------+---------------------+------+-----+---------+-- -----+
| Field | Type | Null | Key | Default | Extra |
+-----------+---------------------+------+-----+---------+-- -----+
| id | bigint(20) unsigned | NO | PRI | 0 | |
| clock | int(11) | NO | PRI | 0 | |
| type | int(11) | NO | PRI | 3 | |
| num | int(11) | NO | | 0 | |
| value_min | double(20,4) | YES | | NULL | |
| value_avg | double(20,4) | YES | | NULL | |
| value_max | double(20,4) | YES | | NULL | |
+-----------+---------------------+------+-----+---------+-- -----+


# cat /etc/my.cnf|grep -i innodb
default-storage-engine=InnoDB
innodb_data_file_path = ibdata1:256M:autoextend
innodb_buffer_pool_size = 768M
innodb_additional_mem_pool_size = 32M
innodb_log_file_size = 192M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 2
innodb_lock_wait_timeout = 50
innodb_flush_method=O_DIRECT
innodb_table_locks=0;
innodb_use_legacy_cardinality_algorithm=0;

16G memory
16G swap
8 CPU

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Super active table optimization

--20cf30549f7dace866049ca5856a
Content-Type: text/plain; charset=UTF-8

1. Which version of MySQL? Judging from your my.cnf it's not MariaDB 5.2 or
Percona Server? They have very good enhancement, particularly in the high
concurrency innodb atmosphere. InnoDB is a lot more tunabl;e in those
versions. Switching from a stock mysql version is adrop-in replacement, so
if tested properly should be a very simple operation.

2. Try makign all fields NOT NULL, that should increase performance as well,
although I'm not sure how much that would help.

On Sat, Feb 19, 2011 at 16:59, Bryan Cantwell wrote:

> # cat /etc/my.cnf|grep -i innodb
>
Just because i like nagging: uuoc :)


> innodb_buffer_pool_size = 768M
>
With 16G RAM you should set this as high as you can, up to 60-75% of your
memory. This of course depends on the storage engine the rest of your db
uses. Aim to be mostly InnoDB and you can set this very high


> innodb_flush_log_at_trx_commit = 2
>
Have you read the consequences of setting this to 2? Look up the manual and
make an infromed decision as it can severely affect what happens if your
server dies.


> innodb_flush_method=O_DIRECT
>
This depends on what kind of hardware you have. With a RAID array with write
cache, you can set it to O-DIRECT. In other environments it can adversely
affect performance.


> 16G swap

This is insane :) If MySQL starts swapping, you're dead in the water anyway.
Typically, anything over 1G or so is too much.


> 8 CPU
>
How many cores? The more cores, the more use you will get from using one of
the non-stock MySQL versions, as they have been optimised for concurrency
and are much more tunable.

Shameless plug: if you want more on-topic help, you should look into letting
us (I work for Open Query) help you. We can then research your whole
environment and have much better advice that is not limited to this scope.
Other companies that do similar services include Percona, SkySQL and
FromDual to name a few :)

Have fun!

--
Walter Heck
Engineer @ Open Query (http://openquery.com)
Exceptional services for MariaDB and MySQL at a fixed budget
--
Follow @openquery on Twitter to stay up to date

--20cf30549f7dace866049ca5856a--

Re: Super active table optimization

--------------enig83E976E40EDF23809D6EA75A
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Am 19.02.2011 16:59, schrieb Bryan Cantwell:
> I have an innodb table with 200 million rows and growing.

more interesting is the db-size!

> innodb_buffer_pool_size =3D 768M

roo small, in the best case it is as large as the database
or so large as pissoble to avoid swapping

> innodb_log_file_size =3D 192M

1/4 of buffer_pool is ok but if your buffer-pool is too small
it is a little difficult to increase this, i would never set
this lower than 512 MB which is also ok for larger databases

this are our innodb settings for a dbmail-instance
and mysql 5.5 and we are speaking about 15 GB data
and 3 Mio. rows, the backend is a san-storage

in this setup we can increase up to 1.000 inserts per second

innodb_buffer_pool_size =3D 5120M
innodb_buffer_pool_instances =3D 5
innodb_purge_threads =3D 1
innodb_max_purge_lag =3D 200000
innodb_max_dirty_pages_pct =3D 60
innodb_additional_mem_pool_size =3D 64M
innodb_log_file_size =3D 512M
innodb_log_buffer_size =3D 256M
innodb_thread_concurrency =3D 32
innodb_thread_sleep_delay =3D 10
innodb_flush_log_at_trx_commit =3D 2
innodb_support_xa =3D 1
innodb_lock_wait_timeout =3D 50
innodb_table_locks =3D 0
innodb_file_format =3D barracuda
innodb_file_per_table =3D 1
innodb_open_files =3D 600
innodb_io_capacity =3D 800
innodb_read_io_threads =3D 8
innodb_write_io_threads =3D 8
transaction-isolation =3D READ-COMMITTED


--------------enig83E976E40EDF23809D6EA75A
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/

iEYEARECAAYFAk1f+ysACgkQhmBjz394AnnVJQCghjRSOhqkPS2zBqmYftCz 8nYg
mNMAn0eHGg0auEgiWWCDjtyOUw7l7DFH
=83LC
-----END PGP SIGNATURE-----

--------------enig83E976E40EDF23809D6EA75A--