Hello,
I have a question about MySQL memory usage, and a question about performance:
I'm running a large-ish 64-bit MySQL 5.1.45 instance with 4 databases on Windows Server 2008. The server is an 8 core DL380 with 32GB of RAM, and we were expecting to dedicate approx. 12 GB of this to MySQL.
The disks attached to the server are striped members of a high performance fiber SAN, so disk throughput is essentially optimal.
The schema in three of the databases is identical, including a view in each one that has some JOINS to about 15 small tables in the fourth DB.
The first database has ~13 million rows, the second database has ~4 million rows, and the third database has ~200k rows, each stored in only one table. Tables in the 4th DB are less than 1000 rows, but there are at least 30 of them.
All of the tables are MyISAM.
On disk, the first database appears to occupy about a 4GB footprint. I would therefore expect the MySQL instance use at least 4GB of RAM on the server, but this isn't nearly the case--It appears to be using only about 1.9GB. We do semi-frequent table scans of this information, so I do know that each row has been read at least 5 or 10 times since this instance was restarted.
At a high level, I would expect that there is a huge performance lift associated with storing most of this data in some sort of memory based cache--I know MS SQL Server does something like this out of the box, and I expected the same general concept to be operating behind the scenes in MySQL. That doesn't appear to be the case, however. I'm not sure if it's not implemented, or if I've configured the wrong settings.
Can you take a look at our my.ini (below) and let me know if I've approached this with the correct settings?
Do we need to be doing something with
myisam_use_mmap
to make MySQL cache table data more aggressively?
My second question is this:
Table scan operations on this server (through the VIEW that joins the large table to about 15 small tables) are horrendous.
Issuing a
SELECT * FROM viewname
on the ~200k row table takes over 50 minutes to start returning data! The larger tables are even worse.
SHOW PROCESSLIST
indicates that these queries are spending nearly 100% of their time in the "Sending Data" state (which is hard to believe, since no data is getting sent for the majority of the query's lifecycle).
I'm hoping to address at least part of this performance issue with an answer to my first question, since I know that the view is sub-optimal, both from a mySQL optimizer perspective and from a programmatic perspective. Any other ideas though?
Thanks for your time,
Paul B. Davis
Here's the my.ini for this instance:
[mysqld]
port=3306
socket=G:\mysqltmp\mysql.sock
lower_case_table_names=1
max_user_connections=95
max_connections=100
table_cache=1200
max_connect_errors=999999
default-storage-engine=myisam
key_buffer_size=8G
max_allowed_packet=1M
table_open_cache=512
sort_buffer_size=6M
read_buffer_size=6M
read_rnd_buffer_size=8M
myisam_sort_buffer_size=64M
query_cache_size=32M
thread_cache_size=16
thread_concurrency=2
log-error=G:\mysql\log\mysql.log
general_log=1
general_log_file=G:\mysql\log\mysql-generalquery.log
slow_query_log
slow_query_log_file=G:\mysql\log\mysql-slowquery.log
long_query_time=5
binlog-do-db=adjunction_web_0
slave-skip-errors=all
#skip-slave-start
server-id=158
innodb_file_per_table
innodb_buffer_pool_size=512M
relay-log=SQLDW0-relay-bin
relay-log-index=SQLDW0-relay-bin.index
#innodb_data_home_dir = C:\mysql\data/
#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
#innodb_log_group_home_dir = C:\mysql\data/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 384M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 100M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50
[client]
socket = /tmp/mysql.sock