当前位置 : 导入[WP]

标签 导入[WP] 下的文章

What to tune in MySQL Server after installation

My favorite question during Interview for people to work as MySQL DBAs or be involved with MySQL Performance in some way is to ask them what should be tuned in MySQL Server straight after installation, assuming it was installed with default settings.

I’m surprised how many people fail to provide any reasonable answer to this question, and how many servers are where in wild which are running with default settings.

Even though you can tune quite a lot of variables in MySQL Servers only few of them are really important for most common workload. After you get these settings right other changes will most commonly offer only incremental performance improvements.

[b]key_buffer_size[/b] - Very important if you use MyISAM tables. Set up to 30-40% of available memory if you use MyISAM tables exclusively. Right size depends on amount of indexes, data size and workload - remember MyISAM uses OS cache to cache the data so you need to leave memory for it as well, and data can be much larger than indexes in many cases. Check however if all of key_buffer is used over time - it is not rare to see key_buffer being set to 4G while combined size of .MYI files is just 1GB. This would be just a waste. If you use few MyISAM tables you’ll want to keep it lower but still at least 16-32Mb so it is large enough to accommodate indexes for temporary tables which are created on disk.

[b]innodb_buffer_pool_size[/b] This is very important variable to tune if you’re using Innodb tables. Innodb tables are much more sensitive to buffer size compared to MyISAM. MyISAM may work kind of OK with default key_buffer_size even with large data set but it will crawl with default innodb_buffer_pool_size. Also Innodb buffer pool caches both data and index pages so you do not need to leave space for OS cache so values up to 70-80% of memory often make sense for Innodb only installations. Same rules as for key_buffer apply - if you have small data set and it is not going to grow dramatically do not oversize innodb_buffer_pool_size you might find better use for memory available.

[b]innodb_additional_pool_size[/b] This one does not really affect performance too much, at least on OS with decent memory allocators. Still you might want to have it 20MB (sometimes larger) so you can see how much memory Innodb allocates for misc needs.

[b]innodb_log_file_size[/b] Very important for write intensive workloads especially for large data sets. Larger sizes offer better performance but increase recovery times so be careful. I normally use values 64M-512M depending on server size.

[b]innodb_log_buffer_size[/b] Default for this one is kind of OK for many workloads with medium write load and shorter transactions. If you have update activity spikes however or work with blobs a lot you might want to increase it. Do not set it too high however as it would be waste of memory - it is flushed every 1 sec anyway so you do not need space for more than 1 sec worth of updates. 8MB-16MB are typically enough. Smaller installations should use smaller values.

[b]innodb_flush_logs_at_trx_commit[/b] Crying about Innodb being 100 times slower than MyISAM ? You probably forgot to adjust this value. Default value of 1 will mean each update transaction commit (or each statement outside of transaction) will need to flush log to the disk which is rather expensive, especially if you do not have Battery backed up cache. Many applications, especially those moved from MyISAM tables are OK with value 2 which means do not flush log to the disk but only flush it to OS cache. The log is still flushed to the disk each second so you normally would not loose more than 1-2 sec worth of updates. Value 0 is a bit faster but is a bit less secure as you can lose transactions even in case MySQL Server crashes. Value 2 only cause data loss with full OS crash.

[b]table_cache[/b] - Opening tables can be expensive. For example MyISAM tables mark MYI header to mark table as currently in use. You do not want this to happen so frequently and it is typically best to size your cache so it is large enough to keep most of your tables open. It uses some OS resources and some memory but for modern hardware it is typically not the problem. 1024 is good value for applications with couple hundreds tables (remember each connection needs its own entry) if you have many connections or many tables increase it larger. I’ve seen values over 100.000 used.

[b]thread_cache[/b] Thread creation/destructions can be expensive, which happen at each connect/disconnect. I normally set this value to at least 16. If application has large jumps in amount of concurrent connections and I see fast growth of
Threads_Created variable I boost it higher. The goal is not to have threads created in normal operation.

[b]query_cache[/b] If your application is read intensive and you do not have application level caches this can be great help. Do not set it too large as it may slow things down as its maintenance may get expensive. Values from 32M to 512M normally make sense. Check it however after a while and see if it is well used. For certain workloads cache hit ratio is lower than would justify having it enabled.

Note: as you can see all of these are global variables. These variables depend on hardware and mix of storage engines, while per session variables are typically workload specific. If you have simple queries there is no reason to increase sort_buffer_size even if you have 64GB of memory to waste. Furthermore doing so may decrease performance.
I normally leave per session variable tuning to second step after I can analyze workload.

P.S Note MySQL distribution contains bunch of sample my.cnf files which may be great templates to use. Typically they would already be much better than defaults if you chose correct one.


—- 1. 认识Asta
—-象普通的Delphi构件一样,在Delphi环境下安装Asta即可看到该页标上有AstaClientSocket、AstaClienDataset 、AstaServerSocket 、AstaDataSet等构件,其中关键是AstaClientSocket 、AstaClienDataset、AstaServerSocket。其中,AstaClient由一个AstaClientSocket和至少一个AstaClienDataset组成。AstaServer的核心是AstaServerSocket。Database可以是现在流行的任何数据库产品,如Sybase、Informix 、MS-SQLserver等。
—- 2. 如何实现多层应用
—- Asta本身带有两个例子服务器程序,一个是BDEserver,另一个是ODBCserver。这两个服务器程序设计的很完善,稍加改造(汉化)即可直接使用。我们使用BDE服务器。 本例以Paradox本地数据库为例,说明如何开发客户端软件。
—- 2.1 运行BDE服务器程序,出现数据连结窗口,弹开组合框选择别名BDEDEMOS,确定。
—- 2.2进入Delphi环境,建立新应用netapp.dpr,窗体上分别放置AstaClientSocket1、AstaClienDataset1。AstaClientSocket1的Adress属性填入服务器程序所在机器的IP地址。若服务器程序在同一台机器上,则IP地址默认为127.0.0.1。Port属性与服务器程序端口一致(若服务器程序未作改动此值为9000,当然也可选用其他数值)。AstaClienDataset1的属性与一般数据表Ttable相同,首先选择DataBasename属性,然后选取tablename。最后把Active 设为true(相当于执行了SQL语句:Select * from[tablename])。本例中tablename选取employee.db。
—- 3. 把结果表现出来
—- 同一般数据库应用程序一样,在窗体上放入Datasource1,其Dataset属性设为AstaClientSocket1,放入DBGrid,其Datasource设为 Datasource1,则简单客户程序就完成了。


        几年前我用MTS的时候就听说ASTA了,后来我们公司也搞与MIDAS和ASTA类似的中间件TM+BSO,现在MTS升华到COM+,我们的TM由于稳定性和效率不够也夭折了,最近一个朋友在一家大型物流软件公司做开发,说还在使用ASTA,去ASTA网站看看,还活得很健壮,支持delphi2006、SOAP,还搞什么ASTA Wireless。
朋友说他们用ASTA最主要是节省数据库连接数量,减少数据库连接购买费用,再有就是要向客户吹嘘3-Tier,根本没有写什么Business Object,执行速度也是很慢。
2、 中间层服务器内置了资料元件(DataObject),客户端传入SQL语句就可查询出资料。这在很多刚搞3-tier的人看来认为很方便,因为他们按Borland的范例,客户端有多少DataSet,服务器就会有多少DataSet和相同数量的DataSetProvider,常常光处理资料就要在服务器大动干戈,还不要谈Business Function。
1、ASTA也说Thin Client,客户端不装数据库引擎就是瘦客户端?瘦了多少,九牛一毛。那些Form无论包在bpl还是dll里都是要安装或下载到客户端去的。(除非都动态生成Form,嗯,难度大…)
2、 数据库连接真能省下来?我们知道一个TADOConnection就是一个连接,一个Session,服务器必须为每个并发的使用创建一个TADOConnection,如何省?除非ASTA在服务器限制了TADOConnection的创建数量,超过就等待。(像SQLServer的连接授权是认Session还是Workstation?)因此,要省连接除非减少并发数量,就是你的服务器Data Object和EntityObject规划得比较好,在最迟的时间以最短的时间访问数据库,因为服务器元件是无状态的,可以用完就给别人用,而C/S模式下一个客户端的Session是要一直连接的。(补记:SQLServe有两种授权模式,对设备和对CPU,对设备就是Workstation,一台PC无论开几个TADOConnection连接都只算一个,对CPU指服务器的CPU,workstation就是可以无限多。因此,有了中间层,就只需要购买中间层服务器的授权,可以大大节省授权费用。)
3、速度问题,3-tier当然不能一次把很大资料送到客户端去,送去干嘛?运算?先在服务器的Entity Object运算。浏览显示?学网页分页分批显示咯。


uses ImageHlp;

function GetDLLFileExports(
szFileName: PChar;
mStrings: TStrings
): Boolean;
hFileMapping: THANDLE;
lpFileBase: Pointer;
pImg_DOS_Header: PImageDosHeader;
pImg_NT_Header: PImageNtHeaders;
pImg_Export_Dir: PImageExportDirectory;
ppdwNames: ^PDWORD;
szFunc: PChar;
i: Integer;
Result := False;
if not Assigned(mStrings) then Exit;
hFile := CreateFile(szFileName, GENERIC_READ, FILE_SHARE_READ, nil,
if(hFile = INVALID_HANDLE_VALUE) then Exit;
hFileMapping := CreateFileMapping(hFile, nil, PAGE_READONLY, 0, 0, nil);
if hFileMapping = 0 then

lpFileBase := MapViewOfFile(hFileMapping, FILE_MAP_READ, 0, 0, 0);
if lpFileBase = nil then

pImg_DOS_Header := PImageDosHeader(lpFileBase);
pImg_NT_Header := PImageNtHeaders(
Integer(pImg_DOS_Header) + Integer(pImg_DOS_Header._lfanew));

if IsBadReadPtr(pImg_NT_Header, SizeOf(IMAGE_NT_HEADERS)) or
(pImg_NT_Header.Signature <> IMAGE_NT_SIGNATURE) then

pImg_Export_Dir := PImageExportDirectory(
if not Assigned(pImg_Export_Dir) then