mysql performance tuning, write, get db into ram

Question

I am trying to improve the performance of a website by optimizing the Mysql server. In addition to general optimization I have a feeling that some write operations take an unacceptably long time.

The server is a Mac os x server 10.5.8 (I think this is a 32 bit OS) with 2x2.8 GHz Quad-core intel xeon and 8 GB ram.

There are 2 InnoDB tables that are rarely used and the other 45 tables are MyISAM.

The entire database is 1.2 GB which includes a 400 MB log table that is only written to and never read by the application.

I would assume that having the entire database in RAM would improve performance. Also I would assume that tolerating some data loss in case of system failure could improve the write performance, but I do not know how to configure it to take advantage of this. The data that could be lost is not critical and can be recreated by a user if lost.

The site has about 1 or 2 simultaneous visitors.

I have run some iterations of the mysqltuner.pl. Here are the current results:

 >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.92-log
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[!!] InnoDB is enabled but isn't being used
[OK] Total fragmented tables: 0

-------- Security Recommendations  -------------------------------------------
ERROR 1142 (42000) at line 1: SELECT command denied to user ''@'localhost' for table 'user'
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 16m 57s (4K q [4.126 qps], 122 conn, TX: 6M, RX: 1M)
[--] Reads / Writes: 97% / 3%
[--] Total buffers: 922.0M global + 12.4M per thread (100 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 2.1G (26% of installed RAM)
[OK] Slow queries: 0% (0/4K)
[OK] Highest usage of available connections: 2% (2/100)
[!!] Cannot calculate MyISAM index size - re-run script as root user
[OK] Query cache efficiency: 42.5% (1K cached / 3K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 36 sorts)
[OK] Temporary tables created on disk: 8% (6 on disk / 68 total)
[OK] Thread cache hit rate: 98% (2 created / 122 connections)
[OK] Table cache hit rate: 53% (58 open / 109 opened)
[OK] Open file limit used: 4% (103/2K)
[OK] Table locks acquired immediately: 100% (3K immediate / 3K locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Add skip-innodb to MySQL configuration to disable InnoDB
    MySQL started within last 24 hours - recommendations may be inaccurate

and here is my my.cnf

# The MySQL server
[mysqld]
port            = 3306
socket          = /var/mysql/mysql.sock
skip-locking
key_buffer = 384M
max_allowed_packet = 1M
table_cache = 1024
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 512M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
skip-thread-priority
log_slow_queries = 1
long_query_time = 1

Before restarting mysql I had 3 slow queries of perhaps 100K queries, but where do I find the log with these queries?

//

score 2 · Accepted Answer · answered Feb 09 '12 at 13:04

You said that you "have a feeling that some write operations take an unacceptably long time". What gives you this feeling? Have you tried to measure it at all?

If you're on OS X, dtrace is a wonderful tool for measuring this sort of thing. Some people have done some of the work for you. At the very least, use vmstat, iostat or iotop to determine whether you are seeing significant slowdowns in disk timings.

Your query_cache_size is probably too large. Writes have to invalidate all entries for that table from the query cache. The larger the query cache is, the longer this takes and this is probably something that is making your writes slower. The manual recommends "tens of megabytes". You should change it down in steps and measure the performance after each change to determine how big yours should be.

The Table locks acquired immediately: 100% (3K immediate / 3K locks) value suggests that MyISAM table locking is not a problem for you. This can become a significant problem with MyISAM tables that experience a higher ratio of writes to reads.

Queries that write to the disk are included in the slow query tracking so if you are seeing slow queries for INSERT, UPDATE, REPLACE, DELETE, TRUNCATE, etc that would be a hint that you have a problem.

The slow query log can be a file or a table or both. Since you don't seem to have specified a location for it, it should use the defaults. From the part of the manual on the slow query log and the part on log locations:

If you specify no name for the slow query log file, the default name is host_name-slow.log. The server creates the file in the data directory unless an absolute path name is given to specify a different directory.

RolandoMySQLDBA · Answer 2 · 2012-02-09T18:49:14.260

Given these factors you mentioned

MySQL 5.0.92
32-bit OS

This quickly rules out using InnoDB since MySQL 5.0 does not have InnoDB that can engage multiple cores (oldest version of MySQL that has InnoDB to engage multiple cores is 5.1.38 InnoDB plug-in).

You cannot have the entire database in RAM because MyISAM only caches indexes. However, you can load all the MyISAM indexes into the key buffer.

The first thing you need to do is compute the correctly sized key_buffer_size. Here is a query to compute that for you:

SELECT CONCAT(ROUND(KBS/POWER(1024, 
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999), 
SUBSTR(' KMG',IF(PowerOf1024<0,0, 
IF(PowerOf1024>3,0,PowerOf1024))+1,1)) 
recommended_key_buffer_size FROM 
(SELECT LEAST(POWER(2,31),KBS1) KBS 
FROM (SELECT SUM(index_length) KBS1 
FROM information_schema.tables 
WHERE engine='MyISAM' AND 
table_schema NOT IN ('information_schema','mysql')) AA ) A, 
(SELECT 2 PowerOf1024) B;

Please note that the recommendation will cap itself at 2G if the sum of all MyISAM indexes exceeds 2G. Also note this: the SQL statement has the clause (SELECT 2 PowerOf1024) B. This will output the recommendation in MB. Using (SELECT 1 PowerOf1024) B outputs in KB. Using (SELECT 3 PowerOf1024) B outputs in GB.

OK you now know what size to make the MyISAM Key Buffer. How do you load it?

Run this:

SELECT DISTINCT CONCAT('SELECT ',ndxcollist,' FROM ',
db,'.',tb,' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache
FROM (SELECT engine,table_schema db,table_name tb,index_name,
GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist
FROM (SELECT B.engine,A.table_schema,A.table_name,A.index_name,
A.column_name,A.seq_in_index
FROM information_schema.statistics A INNER JOIN
(SELECT engine,table_schema,table_name FROM information_schema.tables
WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) B
USING (table_schema,table_name)
WHERE A.index_type <> 'FULLTEXT'
ORDER BY table_schema,table_name,index_name,seq_in_index) A
GROUP BY table_schema,table_name,index_name) AA
ORDER BY db,tb;

This query will show you every query you need to run to force all MYI index pages into the key buffer. Output this query to a script and run that output:

SQLSTMT="SELECT DISTINCT CONCAT('SELECT ',ndxcollist,' FROM ',db,'.',tb,' ORDER BY ',ndxcollist,';') SelectQueryToLoadCache FROM (SELECT engine,table_schema db,table_name tb,index_name,GROUP_CONCAT(column_name ORDER BY seq_in_index) ndxcollist FROM (SELECT B.engine,A.table_schema,A.table_name,A.index_name,A.column_name,A.seq_in_index FROM information_schema.statistics A INNER JOIN (SELECT engine,table_schema,table_name FROM information_schema.tables WHERE engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) B USING (table_schema,table_name)  WHERE A.index_type <> 'FULLTEXT' ORDER BY table_schema,table_name,index_name,seq_in_index) A GROUP BY table_schema,table_name,index_name) AA ORDER BY db,tb;"
mysql -u... -p.... -AN -e"${SQLSTMT}" > MyISAMIndexPreload.sql
mysql -u... -p.... -A < MyISAMIndexPreload.sql

Give it a Try !!!

score 1 · Answer 3 · edited Apr 13 '17 at 12:43

1

If you have 8Gb of RAM it doesn't mean that put your whole DB in RAM.Os uses some memory for internal operations also.

I would suggest some setting in my.cnf they may work for you

Increase your `key_buffer` from 384M to some high value may be to 512MB as you have sufficien RAM

Size of the Key Buffer, used to cache index blocks for MyISAM tables. Do not set it larger than 30% of your available memory, as some memory is also required by the OS to cache rows. Even if you're not using MyISAM tables, you should still set it to 8-64M as it will also be used for internal temporary disk tables.

set table_open_cache  to 2048

UPDATE

For proper configuring your my.cnf please visit optimizating my.cnf

Try it it may help you...

edited Apr 13 '17 at 12:43

Community

1

answered Feb 09 '12 at 11:17

Abdul Manaf

248
1
8

I have changed only the things which you have provided in your my.cnf you can also tune the server with various other variables it depend on your requirements. – Abdul Manaf Feb 09 '12 at 11:18
If you need fully optimized my.cnf let me know. – Abdul Manaf Feb 09 '12 at 11:19
I disagree on the long_query_time. He said that this value catches 3 queries in 100,000. That's perfectly fine. If anything, I would set it lower. – Ladadadada Feb 09 '12 at 12:30
@Ladadadada : agreed to u sorry for mistake.. – Abdul Manaf Feb 09 '12 at 12:53
While looking at the manual, I realised that he can't set it any lower until he upgrades to the 5.1 branch. – Ladadadada Feb 09 '12 at 13:11

mysql performance tuning, write, get db into ram

3 Answers3