2

I have a MySql database that is used to store events from an app, since we created it, we only inserted and selected data, we've never deleted any rows. I'm not a DB admin and there's no DB admin in my organization so please bear with me if I'm missing something basic. The database has a single table like this one:

CREATE TABLE `eventlogs` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `LogType` int(11) NOT NULL,
  `ProductId` longtext,
  `Username` varchar(128) CHARACTER SET utf8 DEFAULT NULL,
  `ClientVersion` longtext,
  `Message` longtext,
  `Referrer` longtext,
  `UserAgent` longtext,
  `CreatedDate` datetime NOT NULL,
  PRIMARY KEY (`Id`),
  KEY `IX_LogType` (`LogType`),
  KEY `IX_CreatedDate` (`CreatedDate`),
  KEY `IX_Username` (`Username`)
) ENGINE=InnoDB AUTO_INCREMENT=180712975 DEFAULT CHARSET=latin1;

This used to work very well but it reached a point where it's almost impossible to run any query, they take more than 15 minutes and sometimes even more! This is the typical query we run:

SELECT * FROM customily_logs.eventlogs
    WHERE CreatedDate > '2020-06-01'
      and Username = 'myuser'
      and LogType = 3

And this is the execution plan from the query:

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "14073888.06"
    },
    "table": {
      "table_name": "eventlogs",
      "access_type": "index_merge",
      "possible_keys": [
        "IX_LogType",
        "IX_CreatedDate",
        "IX_Username"
      ],
      "key": "intersect(IX_Username,IX_LogType)",
      "key_length": "387,4",
      "rows_examined_per_scan": 15809639,
      "rows_produced_per_join": 7904819,
      "filtered": "50.00",
      "cost_info": {
        "read_cost": "12492924.16",
        "eval_cost": "1580963.90",
        "prefix_cost": "14073888.06",
        "data_read_per_join": "3G"
      },
      "used_columns": [
        "Id",
        "LogType",
        "ProductId",
        "Username",
        "ClientVersion",
        "Message",
        "Referrer",
        "UserAgent",
        "CreatedDate"
      ],
      "attached_condition": "((`customily_logs`.`eventlogs`.`LogType` = 3) and (`customily_logs`.`eventlogs`.`CreatedDate` > '2020-06-01') and (`customily_logs`.`eventlogs`.`Username` = 'myuser'))"
    }
  }
}

This is MySQL 5.7.29 on a Windows Server 2012 R2 x64. The table has 177 million rows and according to MySql Workbench, it has 65.6 GiB of data and 8.8 GiB of indexes. The Windows machine is an EC2 instance on AWS with 32 cores, 128 GB of RAM, and 2 TB of EBS storage with 9000 IOPS, although it's shared with other running apps, the CPU usage is rarely over 50% and there's always around 50 GB of free RAM. These are the server settings in my.ini:

[client]

port=3307

[mysql]
no-beep

[mysqld]

port=3307

datadir=C:/ProgramData/MySQL/MySQL Server 5.7/Data

default-storage-engine=INNODB

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

log-output=FILE

general-log=0

general_log_file="WIN-2BKHQL88U78.log"

slow-query-log=1

slow_query_log_file="WIN-2BKHQL88U78-slow.log"

long_query_time=10

log-error="WIN-2BKHQL88U78.err"

relay_log="WIN-2BKHQL88U78-relay"

server-id=1

report_port=3307

lower_case_table_names=1

secure-file-priv="C:/ProgramData/MySQL/MySQL Server 5.7/Uploads"

max_connections=151

table_open_cache=2000

tmp_table_size=3G

thread_cache_size=10

myisam_max_sort_file_size=100G

myisam_sort_buffer_size=4G

key_buffer_size=8M

read_buffer_size=64K

read_rnd_buffer_size=256K

innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size=1M

innodb_buffer_pool_size = 24G

innodb_log_file_size=48M

innodb_thread_concurrency=33

innodb_autoextend_increment=64

innodb_buffer_pool_instances=8

innodb_concurrency_tickets=5000

innodb_old_blocks_time=1000

innodb_open_files=300

innodb_stats_on_metadata=0

innodb_file_per_table=1

innodb_checksum_algorithm=0

back_log=80

flush_time=0

join_buffer_size=256K

max_allowed_packet=4M

max_connect_errors=100

open_files_limit=4161

sort_buffer_size=256K

table_definition_cache=1400

binlog_row_event_max_size=8K

sync_master_info=10000

sync_relay_log=10000

sync_relay_log_info=10000

I tried increasing the innodb_buffer_pool_size to 32 GB but it didn't help. I also run ANALYZE which finished almost instantly, and I checked for fragmentation in the database but it seems to be really low (checked it with this). I'm hoping to reduce the query time to 1 minute or less but I don't know if it's unrealistic. Can the usage of many longtext columns affect the query time?

Thank you very much for your help!

3
  • 1
    intersect is a clue that you need a "composite" index, as suggested by danblack. – Rick James Jun 17 at 15:41
  • Additional information request. Post on pastebin.com and share the links. From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; F) complete www.MySQLTuner.pl (perl) report or similar after 24h uptime for server workload tuning analysis to provide suggestions. – Wilson Hauck Jun 17 at 15:47
  • 1
    For Windows version of MySQLTuner, github.com/pmachapman/mysqltuner – Wilson Hauck Jun 17 at 15:49
5

Good news is that you need a better use of multiple-column indexes to better correspond to your query (ref: How MySQL uses indexes).

By using your constant ref values, (the WHERE criteria is =), Username and LogType before the range CreatedDate if you create the combined index this will help with the query.

ALTER TABLE eventlogs
DROP INDEX IX_Username,
ADD INDEX IX_Username_LogType_CreatedDate(Username,LogType,CreatedDate),
ALGORITHM=INPLACE,
LOCK=NONE;

I've replaced the existing index because a similar sized index with the same prefix is equivalent.

With this <1 minute queries should be possible if the data set size returned isn't huge.

longtext columns won't be having much of an effect here unless there are a lot of rows that exceed ~8k in size (i.e. stored off row - ref) and this would affect the time of the query based on number of rows returned rather than the search time (as indexes aren't longtext).

4
  • 2
    Partitioning is rarely useful. A "cookbook" for indexing: mysql.rjweb.org/doc.php/index_cookbook_mysql – Rick James Jun 17 at 15:43
  • Thank you! I'm trying this but it's been 3 hours and the index is still rebuilding, will come back with the results :) – Andres Jun 18 at 23:14
  • 1
    Worked perfectly! Queries are SUPER fast now, thank you so much! – Andres Jun 21 at 13:32
  • Glad to hear it. If this was a common query you'll probably see savings in RAM/CPU that you can recoop with a smaller AWS instance. I should of mentioned innodb_io_capacity can be tuned up to the available part of the 9000 IOPs available (it probably would of make the alter table significantly faster). Welcome to DBA stack exchange, please continue the good questions and we'll do our best to answer them. – danblack Jun 22 at 1:10

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.