MYSQL 5.5 Performance issue
Our database has 4 years data and most of the time users will be working only on current year data so we decided to partition our large tables.
So we took one table with 104 million rows. This table has a column named 'period' which represents billing period and it is a foriegn key and have indexes.
Copied the original table to a temp table and partitioned the temp table using period column as partition key and created the same indexes which are in original table.
Now i have 2 partitions and 1st partition has 3 years data and 2nd partition has current years data.
The 2nd partition has 8 million rows which is what i am trying to query.
I have a slower query which queries from this large table and join other smaller tables.
This table has index on the field used in where clause.
I ran this query using original table (which is not partitioned) which took 24 seconds for first time and took 12 seconds 2nd time .
Same Query i ran using this temp table (partitioned) which almost took 25 seconds for first time and subsequent runs took same 25 second time.
I also looked at the query plan it is scanning only the current year partition.
How is it possible the partition table is taking more time than the original table. Also i don't have query cache enabled but the query second run is always faster.
When i set the query-cache-type=0 the original table took 35 mins to retrieve the results however the partition table ran in 25 sec.
MYSQL Version - 5.5
Runs on a dedicated linux server.
MySQL Engine - InnoDB
Database Size = 593 GB
Free Space = 58 GB
CPU INFO
-----------
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 8
On-line CPU(s) list: 0-7
Thread(s) per core: 1
Core(s) per socket: 2
Socket(s): 4
NUMA node(s): 1
Vendor ID: GenuineIntel
CPU family: 6
>> free -g
total used free shared buffers cached
Mem: 15 9 6 0 0 1
-/+ buffers/cache: 7 7
Swap: 3 0 3
MYSQL Configuration
-------------------
## InnoDB
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 512M
innodb-flush-log-at-trx-commit = 2
innodb-file-per-table = 1
innodb-buffer-pool-size = 7G
innodb_buffer_pool_instances = 9
innodb_old_blocks_time = 1000
## Cache
thread-cache-size = 50
table-open-cache = 4096
table-definition-cache = 2048
query-cache-type = 1
query-cache-size = 0
query-cache-limit = 1M
## Per-thread Buffers
sort-buffer-size = 1M
read-buffer-size = 1M
read-rnd-buffer-size = 1M
join-buffer-size = 1M
Subject
Views
Written By
Posted
MYSQL 5.5 Performance issue
953
October 13, 2017 12:56PM
518
October 19, 2017 03:21AM
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion
of Oracle or any other party.