当前位置 : 导入[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.

Asta的使用

—-随着Internet/intranet的发展,多层应用和分布式瘦客户数据库应用程序逐渐得到广泛应用。在多层应用的开发方式中,Delphi提供了Midas构件,第三方提供了Asta、Midware等。经过使用,相比较而言,Asta所提供的构件易学易用方便灵巧,客户端程序无须任何数据库引擎或动态连结库即可方便的实现多层应用,且客户、服务器可实现实时信息传输。可以说,Asta为普通程序员提供了无须专门培训即可成为复杂多层应用程序开发高手的简单途径。
—- 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,则简单客户程序就完成了。
—-运行此程序即可看到数据表格。此客户端程序无须任何动态库即可在客户端运行,是完全的瘦客户。需要注意的是,AstaClientSocket的ConnectAction属性有多种选择,分别规定了客户端到服务器端的连结状况,大家可分别试试看有何结果。至于安全性设置、实时信息传递,可参阅随机帮助文件。

[转]ASTA中间件

        几年前我用MTS的时候就听说ASTA了,后来我们公司也搞与MIDAS和ASTA类似的中间件TM+BSO,现在MTS升华到COM+,我们的TM由于稳定性和效率不够也夭折了,最近一个朋友在一家大型物流软件公司做开发,说还在使用ASTA,去ASTA网站看看,还活得很健壮,支持delphi2006、SOAP,还搞什么ASTA Wireless。
朋友说他们用ASTA最主要是节省数据库连接数量,减少数据库连接购买费用,再有就是要向客户吹嘘3-Tier,根本没有写什么Business Object,执行速度也是很慢。
我下了ASTA3版本看了看,可能他比MIDAS招人喜欢在这几个地方:
1、资料打包速度比MIDAS快,都用ADO引擎,22万笔资料,TDataSetProvider打包要980秒,ASTA只要377秒;(实际上有更绝的,直接把ADODataSet存到_Stream送到客户端,只要14秒)
2、 中间层服务器内置了资料元件(DataObject),客户端传入SQL语句就可查询出资料。这在很多刚搞3-tier的人看来认为很方便,因为他们按Borland的范例,客户端有多少DataSet,服务器就会有多少DataSet和相同数量的DataSetProvider,常常光处理资料就要在服务器大动干戈,还不要谈Business Function。
3、纯Delphi源码的服务器,可以自己增加些必须的画面,比如参数设定、日志管理、运行状况监控等等,这在COM+或别的中间件是无能为力的。
有几个疑问的地方:
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运算。浏览显示?学网页分页分批显示咯。

获取PE文件的导出函数列表

uses ImageHlp;

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

lpFileBase := MapViewOfFile(hFileMapping, FILE_MAP_READ, 0, 0, 0);
if lpFileBase = nil then
begin
CloseHandle(hFileMapping);
CloseHandle(hFile);
Exit;
end;

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
begin
UnmapViewOfFile(lpFileBase);
CloseHandle(hFileMapping);
CloseHandle(hFile);
Exit;
end;

pImg_Export_Dir := PImageExportDirectory(
pImg_NT_Header.OptionalHeader.DataDirectory[IMAGE_DIRECTORY_ENTRY_EXPORT].
VirtualAddress);
if not Assigned(pImg_Export_Dir) then
begin

天才女孩妙语集

我认识的女孩里有一个优点,那就是智力超群。她有着超常的判断力和逻辑思维能力,常作惊人之语,让我佩服得五体投地。生活虽然平淡,但她的妙语时不时能给我一点意想不到的惊喜,为了和大家分享我的惊喜,特地总结了一下她比较著名的言论,举出最让我佩服的十大例子:

1.有一段时间我打她呼机她总是不回电,问她,她说:“我把呼机关了。”“为什么要把呼机关掉?”“公司常打我呼机叫我加班,我关了呼机,他们就呼不到我了。”

2.后来她买了一个手机,有一次她出门忘了带手机,从街上打公用电话给我:“老公快帮我给小芳打个电话,告诉她到麦当劳门口等我。”“你自己为什么不打?”“我手机没带呀!”

3.有一次看到地摊上卖莲蓬,老婆问:“怎么卖?”答:“一块一个。”“这么贵啊,三块两个卖不卖?”

4.有一次我和她出门,两个人只带了一把家门钥匙,她要去菜场,让我先回家,把钥匙留给她。我问她:“为什么要把钥匙留给你啊?”她说:“这样等会儿我回家,你就不用下来给我开门了。”

5.有一次听录音机,她一不小心按了录音按钮,我叫道:“快停掉,你把带子上东西抹掉了!”她不去按录音机却上来捂我的嘴,事后她解释道:“只要我们不发声音,带子上的内容不就抹不掉了。”

6.看电视新闻看到一半,她说:“快帮我按一下暂停,我要去洗手间!”

7.她说:“唉,明天又停电,什么事也做不成,只好躺在床上看一天电视了。”

8.唱卡拉OK,她用电视机摇控器上的选台按钮来选歌曲,都按出电视新闻来了,她还不明白:“为什么王菲的歌要配警察破案的画面?”

9.两个人搬电脑,她说:“这么重啊,你为什么搬之前不先删掉点东西?”

10.最后我把上面写的给她看,本以为她会不好意思笑的,不料她看了两遍,抬起头迷惑不解地问我:“怎么了,我说错什么了吗?”