Mysql – thesql performance tuning, write, get db into ram

database-performancemac-osxmac-osx-servermyisamMySQL

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 2×2.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?

Best Answer

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.

Related Solutions

Ubuntu – MySQL: How to get the “Maximum possible memory usage” down

You have a server with 256M, but you can't use all of that -- remember there's some OS overhead. Add to that with the fact you're over committing as other folks have mentioned and you'll definitely thrash here. 256M is only enough for a small DB, 20 connections is a lot with what you've got configured.

1) reduce your max connections to 4 (you're using 3 out of 20)

2) optimize your query cache better; 8M is really large, and 64M total is a lot based on your hits/prunes; try a 4/32 combo and see how it goes. Really I think a 2/24 combo would work for you.

3) you have no sorts requiring temp tables, why is that max_heap_table_size verb in there? Comment that out, use the defaults

4) do you actually have 128 tables? Try cutting that table_cache in half to 64 or 48

5) reduce thread_cache_size to 4

6) optimize those tables to reduce fragmenting

Those are some things to start with. It looks like you threw a bunch of numbers in a config without any actual profiling to know what you needed and have created a mess; if all else fails go back to the defaults and get rid of your custom settings and start over using some performance tuning guides you can find on Google. Get the output of SHOW VARIABLES and SHOW STATUS, find any one of a bajillion tuning guides and plug in your actual, real numbers into their equations and that'll tell you the exact-ish numbers you need to put in your config file.

MySQL DB causing high IO running Zabbix server

innodb_buffer_pool_size > 1G, because your dataset size is 1,8GB.

To decrease number of reads you need to increase innodb_buffer_pool_size. To decrease number of writes you need to edit your zabbix templates (disabling some unnecessary items like free inodes, increase intervals between checks).

You have Reads / Writes ratio at 57% / 43%, so enabling Query Cache will not help (it may make things worse, because writes to tables invalidates cache).

Think about increasing tmp_table_size and max_heap_table_size to avoid creating tmp tables on disk (13% of tmp tables). Temporary tables are in MB? it is count? If it is counter, its too high.

Decrease number of connection to 50 (your highest number was 33).

innodb_support_xa = false
innodb_buffer_pool_size = 256M # It depends how many memory is available to MySQL, more is better.
innodb_flush_log_at_trx_commit = 0 # disable writing to logs on every commit and disable fsync on each write
innodb_max_dirty_pages_pct = 90 # avoid flushing dirty pages to disk
innodb_flush_method = O_DIRECT # direct access to disk without OS cache
thread_cache_size = 4
query_cache_size = 0
table_cache = 80? # a little more than number_of_tables_in_zabbix_database

Usefull link about InnoDB optimization.

Best Answer

Related Solutions

Ubuntu – MySQL: How to get the “Maximum possible memory usage” down

MySQL DB causing high IO running Zabbix server

Related Topic