0

I have a MySQL 5.1 server that is struggling. We have too many rows in some of the tables and we do not have enough RAM - currently only 32 GB RAM.

I have RAM begin delivered tomorrow, but I cannot install until Sunday. Between then and now (without deleting rows) I am wanting to understand if there are any tweaks I may be able to consider to slightly improve performance.

The largest problem is we have one table with 32+ million records. Any select against that table is taking quite some time (as you might expect).

My assumption is the machine is heavily utilizing HDD. But I also see high CPU utilization for the mysqld process.

I have checked for fragmented tables and optimized the ones that were fragmented.

The server has 10 HDDs working in a RAID 10 array. So the I/O has read and write speed improvements.

InnoDB Read buffer efficiency: 99.98% (35291508797 hits/ 35299695194 total) InnoDB Write Log efficiency: 81.46% (36334605 hits/ 44602585 total)

Any thoughts or suggestions would be greatly appreciated.

Here is the my.cfg file:

[mysqld]
innodb_file_per_table
skip-external-locking
port                            = 3306
socket                          = /var/lib/mysql/mysql.sock
max_connections                 = 256
key_buffer_size                 = 256M
max_allowed_packet              = 100M
table_open_cache                = 256
sort_buffer_size                = 1M
read_buffer_size                = 1M
read_rnd_buffer_size            = 4M
myisam_sort_buffer_size         = 64M
thread_cache_size               = 8
query_cache_size                = 16M
max_heap_table_size             = 512M
tmp_table_size                  = 512M
wait_timeout                    = 600
general_log_file                = /var/log/mysql/mysql.general.log
general_log                     = 0
log-slow-queries                = /var/log/mysql/mysql-slow.log
long_query_time                 = 60

# setup replication master:
server-id                       = 1
binlog-format                   = mixed
log-bin                         = /var/log/mysql/mysql-bin.log
datadir                         = /var/lib/mysql
innodb_flush_log_at_trx_commit  = 1
sync_binlog                     = 1

# InnoDB tables
innodb_flush_log_at_trx_commit  = 2
innodb_additional_mem_pool_size = 256M
innodb_buffer_pool_size         = 20G
innodb_lock_wait_timeout        = 120
innodb_log_buffer_size          = 32M
innodb_log_file_size            = 1024M

[mysqld_safe]
log-error                       = /var/log/mysqld.log
pid-file                        = /var/run/mysqld/mysqld.pid

[mysqldump]
quick
max_allowed_packet              = 16M

[mysql]
no-auto-rehash
prompt                          =\u:>\\_

[myisamchk]
key_buffer_size                 = 128M
sort_buffer_size                = 128M
read_buffer                     = 2M
write_buffer                    = 2M

[mysqlhotcopy]
interactive-timeout

We have an application that utilizes this database. This app uses a framework that, for page queries, runs a count() query for pagination. Those count() queries for pagination are many of the ones I see with lengthy run times.

Here is the requested information:


SHOW TABLE STATUS:

| Name | Engine | Version | Row_format | Rows     | Avg_row_length | Data_length | Max_data_length   | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation         | Checksum | Create_options | Comment |
| intz | InnoDB | 10      | Compact    | 37508536 |            562 | 21083717632 |                 0 |  15220752384 |   5242880 |       42680391 | 2016-07-26 12:05:22 | NULL                | NULL       | latin1_swedish_ci |     NULL | 

...it looks like we are just over 20 GB.


SELECT STATEMENT:

SELECT 
    COUNT(*) AS count
FROM
   intz AS INTz
WHERE
    ((INTz.call_id_string LIKE '%3145551212%')
  OR (INTz.original_call_id LIKE '%3145551212%')
  OR (INTz.telephone_number LIKE '%3145551212%')
  OR (INTz.id = 3145551212)
  OR (INTz.ani LIKE '%3145551212%')
  OR (INTz.dnis LIKE '%3145551212%')
  OR (INTz.customer_account LIKE '%3145551212%'));

...that select count(*) will take roughly 75 seconds to run.


EXPLAIN SELECT:

+----+-------------+-------------+-------+-----------------+--------------+---------+------+----------+--------------------------+
| id | select_type | table | type  | possible_keys   | key          | key_len | ref  | rows     | Extra                    |
+----+-------------+-------------+-------+-----------------+--------------+---------+------+----------+--------------------------+
|  1 | SIMPLE      | intz  | index | id,search_index | search_index | 313     | NULL | 40707970 | Using where; Using index |
+----+-------------+-------------+-------+-----------------+--------------+---------+------+----------+--------------------------+

I also have an index on those fields:

alter table intz add index search_index (id,call_id_string,original_call_id,telephone_number,ani,dnis,customer_account);
0

High CPU and/or I/O -- Find the slowest queries, provide them, plus EXPLAIN SELECT ... and SHOW CREATE TABLE. This is the most likely way to solve your problem. The fix may be a simple as adding a 'composite' index.

How big is the table? (SHOW TABLE STATUS) If it is less than 20GB, the extra RAM may not help.

The settings are reasonable for 32GB of RAM. Be sure to increase (only) innodb_buffer_pool_size to about 70% of RAM after adding more.

PARTITIONing came with 5.1. However, there are very few cases where it provides any performance benefits. Let's see the queries and schema before embarking blindly on such. BTW, it was not until 5.1.43 that this construct was added: PARTITION BY RANGE(UNIX_TIMESTAMP(col)). More on partitioning.

Addenda

OR and LIKE with leading wildcard == table scan (or index scan in your case). The Index does not help much since it is nearly as big as the table.

Add an extra column (or maybe an extra table) that contains the concatenation of those search columns. Build a FULLTEXT index on it. Then search with MATCH(combined_col) AGAINST (+3145551212 IN BOOLEAN MODE). It will run a thousand times as fast; the "overhead" will vanish. And you will regret buying the extra RAM.

Use it for your query as well as for your count.

But there are limitations (word length, definition of "word", etc).

  • Thanks for the response, Rick James. I added answers to your questions at the bottom of the original post. If you have any suggestions they would be greatly appreciated. – MSF004 Mar 21 '17 at 19:06
0

You need to try to partition this table. I can find documentation for MySQL version 5.6 and 5.7, but not 5.1, so I'm not 100% sure if that option exists in 5.1?

The link for 5.6 shows how to tell if partitioning option is enabled. It also has examples for altering an existing table to create partitions.

I don't know the structure of your table, so I can't give you specific syntax to follow. But generally, you want to try to break the table up into logical groups. So maybe find an indexed date field in your table and partition based on that date:

PARTITION BY RANGE (UNIX_TIMESTAMP(bill_date))(
PARTITION p0 VALUES LESS THAN (UNIX_TIMESTAMP('2013-04-01')), 
PARTITION p1 VALUES LESS THAN (UNIX_TIMESTAMP('2013-07-01')), 
PARTITION p2 VALUES LESS THAN (UNIX_TIMESTAMP('2013-10-01')), 
PARTITION p3 VALUES LESS THAN (UNIX_TIMESTAMP('2014-01-01')));

Hopefully, this points you in the right direction, at least.

Note that your Select, Update, and Insert statements won't need to be modified to use partitions.

One warning, though. If you used the syntax above, you will get errors if you try to insert a row with bill_date > 2014-01-01, since no partition includes that range. So add several partitions ABOVE the current, active, partition. (If you split by years, then add at least 3 or so years above current year.) And maybe even add a last partion that's VALUES GREATER THAN ... so if you forget to add more partitions later, you don't crash the application. If you split the table into partitions based on a hash (see the examples in the links), then this is less of an issue.

  • I can try this on out dev server first - so that is great. But I want to ask if there are any obvious concerns with doing this? Being I am completely unfamiliar with partitioning a DB table (besides what I have read in the past 20 mins). For instance, will we need to re-work our select statements for that table, or is partitioning transparent for SQL statements? Also, any data concerns? – MSF004 Mar 20 '17 at 17:55
  • I haven't worked specifically with MySQL and partitions. I know in Oracle and SQL Server, the Select, Insert, and Update queries are the same, no impact except the partitions help the engine dig through fewer rows for each query. You do need to worry about running out of partitions. Will edit my response to include a note on that. – CaM Mar 20 '17 at 18:04

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.