5.1.68-cll - MySQL Community Server on CentOS
The system has 32GB of RAM.
I increased innodb_buffer_pool_size from 10240M to 15360M (10GB -> 15GB).
Time taken for a series of identical operations increased from 720 to 822 seconds (14% increase).
This was the result only a single test at each setting. But 4 previous tests performed a few months ago resulted in times between 726 and 740s.
I just tried running it again with 8GB, and the time taken was 719s.
Why would more memory result in a slower process?
EDIT: More details on process
The process that I'm testing involves emptying some tables and rebuilding them from data from existing tables. I'm not sure if it's using SELECT INSERT
or if it's SELECT
ing the data, then using PHP to create long INSERT
statements. If that matters then I can find out.
There are no schema definition changes being made.
Here is the output of numactl --hardware
while the server is relatively idle:
root@server [~]# numactl --hardware
available: 1 nodes (0)
node 0 cpus: 0 1 2 3 4 5 6 7
node 0 size: 32740 MB
node 0 free: 6216 MB
node distances:
node 0
0: 10
And free -m
root@server [~]# free -m
total used free shared buffers cached
Mem: 32081 25864 6216 0 2591 12791
-/+ buffers/cache: 10482 21599
Swap: 15994 16 15977
Edit by RolandoMySQLDBA
Please run this query
SELECT
InnoDBSpace / POWER(1024,1) InnoDB_KB,
InnoDBSpace / POWER(1024,2) InnoDB_MB,
InnoDBSpace / POWER(1024,3) InnoDB_GB
FROM
(
SELECT SUM(data_length+index_length) InnoDBSpace
FROM information_schema.tables
WHERE ENGINE='InnoDB'
) A;
RESULT:
InnoDB_KB InnoDB_MB InnoDB_GB
8413536 8216.34375 8.02377319335938
and this one
SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool_pages%';
RESULT:
Innodb_buffer_pool_pages_data
410035
Innodb_buffer_pool_pages_dirty
204
Innodb_buffer_pool_pages_flushed
826954
Innodb_buffer_pool_pages_free
99231
Innodb_buffer_pool_pages_misc
15022
Innodb_buffer_pool_pages_total
524288
During running of process:
root@server [~]# numactl --hardware
available: 1 nodes (0)
node 0 cpus: 0 1 2 3 4 5 6 7
node 0 size: 32740 MB
node 0 free: 5461 MB
node distances:
node 0
0: 10
and:
total used free shared buffers cached
Mem: 32081 26658 5423 0 2603 12948
-/+ buffers/cache: 11106 20975
Swap: 15994 16 15977
numactl --hardware
and post the output.free -m