2

I've been trying to work this issue out, been really killing me! We've been experiencing some slow database queries for a Prestashop installation that we host, and a look into the query log revealed that it is fairly full of simple insert queries like this:

INSERT INTO `ps_guest` 
(`id_operating_system`,`id_web_browser`,`id_customer`,`javascript`,
 `screen_resolution_x`,`screen_resolution_y`,`screen_color`,`sun_java`,
 `adobe_flash`,`adobe_director`,`apple_quicktime`,`real_player`,
 `windows_media`,`accept_language`) 
VALUES ('3','1','0','0','0','0','0','0','0','0','0','0','0','');

Since the data in this table is not something that we need to persist for the long term, we changed the table into a MEMORY table.

CREATE TABLE `ps_guest` (
  `id_guest` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `id_operating_system` int(10) unsigned DEFAULT NULL,
  `id_web_browser` int(10) unsigned DEFAULT NULL,
  `id_customer` int(10) unsigned DEFAULT NULL,
  `javascript` tinyint(1) DEFAULT '0',
  `screen_resolution_x` smallint(5) unsigned DEFAULT NULL,
  `screen_resolution_y` smallint(5) unsigned DEFAULT NULL,
  `screen_color` tinyint(3) unsigned DEFAULT NULL,
  `sun_java` tinyint(1) DEFAULT NULL,
  `adobe_flash` tinyint(1) DEFAULT NULL,
  `adobe_director` tinyint(1) DEFAULT NULL,
  `apple_quicktime` tinyint(1) DEFAULT NULL,
  `real_player` tinyint(1) DEFAULT NULL,
  `windows_media` tinyint(1) DEFAULT NULL,
  `accept_language` varchar(8) DEFAULT NULL,
  PRIMARY KEY (`id_guest`),
  KEY `id_customer` (`id_customer`),
  KEY `id_operating_system` (`id_operating_system`)
) ENGINE=MEMORY AUTO_INCREMENT=188 DEFAULT CHARSET=utf8 |

A MEMORY table should be fast in theory right? And most of the time it is, but occasionally that same insert query still shows up in our slow query log.

Since this insert query does block visitor page loads, we hope to minimise/eliminate it completely.

What else should I be looking at? Below is my.cnf

[mysql]

# CLIENT #
port                           = 3306

[mysqld]

# GENERAL #
port                           = 3306
server-id                      = 1
user                           = mysql
default_storage_engine         = InnoDB
innodb_doublewrite             = 1
low_priority_updates           = 0
max_write_lock_count           = 1

# MyISAM #
key_buffer_size                = 8M
myisam_recover                 = FORCE,BACKUP

# SAFETY #
max_allowed_packet             = 16M
max_connect_errors             = 1000000
#skip_name_resolve
#sql_mode                       = ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
sql_mode                       = NO_ENGINE_SUBSTITUTION
sysdate_is_now                 = 1
innodb                         = FORCE
innodb_strict_mode             = 1

# DATA STORAGE #
datadir                        = /var/lib/mysql/data/

# BINARY LOGGING #
log_bin                        = /var/lib/mysql/data/mysql-bin
expire_logs_days               = 7
sync_binlog                    = 0

# CACHES AND LIMITS #
tmp_table_size                 = 32M
max_heap_table_size            = 32M
query_cache_type               = 1
query_cache_size               = 256M
query_cache_limit              = 4M
max_connections                = 40
table_cache                    = 8196
thread_cache_size              = 32
open_files_limit               = 65535
table_definition_cache         = 4096
table_open_cache               = 8196
join_buffer_size               = 4M

# INNODB #
#innodb_flush_method            = O_DIRECT
innodb_io_capacity             = 75
innodb_log_files_in_group      = 2
innodb_log_file_size           = 384M
innodb_log_buffer_size         = 8M
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table          = 1
innodb_buffer_pool_size        = 1536M
innodb_thread_concurrency      = 5

# LOGGING #
log-warnings                   = 2
log_error                      = /var/lib/mysql/data/mysql-error.log
general-log                    = 0
general_log_file               = /var/lib/mysql/data/mysql-general.log
log_queries_not_using_indexes  = 1
long_query_time                = 3
slow_query_log                 = 1
slow_query_log_file            = /var/lib/mysql/data/mysql-slow.log
log_queries_not_using_indexes  = OFF
1

Depending on how many rows are in your table, your problem may stem from the id_customer and id_operating_system indexes.

Try:

DROP INDEX `id_customer` ON `ps_guest`;
DROP INDEX `id_operating_system` on `ps_guest`;
CREATE INDEX `id_customer` USING BTREE ON `ps_guest` (`id_customer`);
CREATE INDEX `id_operating_system` USING BTREE ON `ps_guest` (`id_operating_system`);

MEMORY tables default to creating HASH indexes, which work very differently to the indexes on most other MySQL database engines. These can drastically slow queries down, especially when there are indexes with low cardinality (like your id_operating_system index).

Peter Zaitsev has shown on the MySQL Performance Blog that this can decrease the speed of queries by more than 100 times.

Unfortunately a memory table isn't necessarily fast in theory - there are quite a few caveats.

MySQL documentation suggests:

A typical use case for the MEMORY engine involves these characteristics:

  • Operations involving transient, non-critical data such as session management or caching. When the MySQL server halts or restarts, the data in MEMORY tables is lost.

  • In-memory storage for fast access and low latency. Data volume can fit entirely in memory without causing the operating system to swap out virtual memory pages.

  • A read-only or read-mostly data access pattern (limited updates).

...

Despite the in-memory processing for MEMORY tables, they are not necessarily faster than InnoDB tables on a busy server, for general-purpose queries, or under a read/write workload. In particular, the table locking involved with performing updates can slow down concurrent usage of MEMORY tables from multiple sessions.

|improve this answer|||||
1

Use Btree instead of Hash indexes on the memory table and this will solve your problem.

CREATE INDEX indexname ON yourtable(col1) USING BTREE

Hash indexes are default for memory tables, they are fast for SELECT but slow for INSERT.

|improve this answer|||||
  • i can confirm that this actually yields with ~3x-4x update in insert speed. – kodisha Nov 10 '15 at 13:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.