实例详解Linux下MySQL数据库性能调优方法
以下的环境具备一定的代表性,可以说是中小企业一般配置和工作环境。希望通过本文能让大家理解Linux下MySQL数据库性能调优方法。51CTO向您推荐《 MySQL数据库入门与精通教程 》。 硬件准备环境: 硬盘: 16块 SAS 15K RAID5 带512MCache CPU: AMD 4核两颗
以下的环境具备一定的代表性,可以说是中小企业一般配置和工作环境。希望通过本文能让大家理解Linux下MySQL数据库性能调优方法。51CTO向您推荐《MySQL数据库入门与精通教程》。
硬件准备环境:
硬盘: 16块 SAS 15K RAID5 带512MCache
CPU: AMD 4核两颗
内存: 16G
软件环境:
操作系统: RedHat AS4.6 2.6.9-67.ELsmp MySQL: 5.0
下面分别根据不同场景进行性能调优:
使用场景A: 写操作(插入/查询/删除)密集,而且数据容量远远超硬件准备环境:
硬盘: 16块 SAS 15K RAID5 带512MCache
CPU: AMD 4核两颗
内存: 16G
软件环境
操作系统: RedHat AS4.6 2.6.9-67.ELsmp
MySQL: 5.0
下面分别根据不同场景进行性能调优:
使用场景A:
写操作(插入/查询/删除)密集,而且数据容量远远超过内存的大小(最小是200G以上,甚至更大到1.5T以上), 每秒需要以千为单位的事务操作
数据可靠性要求不高,允许丢几条记录(硬件出故障时)
优化方式:
在这种情况下,数据库的Cache基本上命中率是非常低的,对数据库的操作基本上就是对磁盘进行操作, 因此侧重点应该是对磁盘相关优化。
首先是考虑数据库存储引擎,由于涉及插入/删除/查询等操作,而且要支持事务,因此考虑使用InnoDB存储引擎,不考虑MyIsam原因是该存储引擎是表级锁,有数据删除时会非常慢。
设置合适的MySQL 参数,由于有16G内存,因此参数设置如下
<ol class="dp-sql"> <li class="alt"><span><span>innodb_buffer_pool_size=8G </span></span></li> <li><span>innodb_additional_mem_pool_size = 800M, </span></li> <li class="alt"><span>innodb_log_buffer_size = 4M </span></li> <li><span>innodb_log_file_size = 500M </span></li> <li class="alt"><span>innodb_log_files_in_group = 4 </span></li> <li><span>log-bin </span></li> </ol>
因为使用主备复制,因此需要打开Log-Bin,这里就涉及一个sync-bin的概念,缺省情况下,sync-bin 在MySQL中是关闭的,但存在一个风险,因为写日志没有刷新到硬盘中去的话,日志是写在操作系统的文件系统里的Cache中,这样若机器断电,那么日志信息将部分丢失。为了减少数据丢失,我们测试了sync-bin=1,sync-bin=5和sync-bin=20,三种情况,sync-bin=1的情况下,丢失数据最坏概率是丢一个事务的数据,但性能非常差;设置sync-bin=5的情况下,性能可以提高1倍左右;设置sync-bin=20时,性能比sync-bin=5又可以提高1倍,设置大于20的值,基本上相差不大,性能提高不了多少,最坏20个事务数据丢失是在我们的允许范围之内,因此设置20是个合理的值,这个值具体取决于你的系统能够最坏允许丢少的事务数据。
若你的系统对数据丢失不于考虑,可以关闭sync-bin,这时数据刷新到硬盘完全取决于操作系统的配置,相关的配置参数有如下:
<ol class="dp-xml"><li class="alt"><span><span>/proc/sys/vm/dirty_ratio </span></span></li></ol>
这 个参数控制一个进程在文件系统中的文件系统写缓冲区的大小,单位是百分比,表示系统内存的百分比,表示当一个进程中写缓冲使用到系统内存多少的时候,再有 磁盘写操作时开始向磁盘写出数据。增大之会使用更多系统内存用于磁盘写缓冲,也可以极大提高系统的写性能。但是,当你需要持续、恒定的写入场合时,应该降 低其数值,一般缺省是 40。更新方法
echo 30 >/proc/sys/vm/dirty_ratio (或则修改/etc/sysctl.conf文件,增加sys.vm.dirty_ratio=30 重起机器)
<ol class="dp-xml"><li class="alt"><span><span>/proc/sys/vm/dirty_background_ratio </span></span></li></ol>
这个参数控制文件系统的pdflush进程,在何时刷新磁盘。单位是百分比,表示系统总内存的百分比,意思是当磁盘的脏数据缓冲到系统内存多少的时候,pdflush开始把脏数据刷新到磁盘。增大会使用更多系统内存用于磁盘写缓冲,也可以极大提高系统的写性能。但是,当你需要持续、恒定的写入场合时,应该降低其数值,一般缺省是10。
<ol class="dp-xml"><li class="alt"><span><span>/proc/sys/vm/dirty_writeback_centisecs </span></span></li></ol>
Pdflush写后台进程每隔多久被唤醒并执行把脏数据写出到硬盘。单位是 1/100 秒。缺省数值是500,也就是 5 秒。如果你的系统是持续地写入动作,那么实际上还是降低这个数值比较好,这样可以把尖峰的写操作削平成多次写操作。设置方法如下:
<ol class="dp-xml"> <li class="alt"><span><span>echo 200 </span><span class="tag">></span><span>/proc/sys/vm/dirty_writeback_centisecs </span></span></li> <li><span>/proc/sys/vm/dirty_expire_centisecs </span></li> </ol>
这个参数声明Linux内核写缓冲区里面的脏数据多“旧”了之后,pdflush进程就开始考虑写到磁盘中去。单位是 1/100秒。缺省是 30000,也就是 30 秒的数据就算旧了,将会刷新磁盘。对于特别重载的写操作来说,这个值适当缩小也是好的,但也不能缩小太多,因为缩小太多也会导致IO提高太快。建议设置为 1500,也就是15秒算旧。
<ol class="dp-xml"><li class="alt"><span><span>echo 1500 </span><span class="tag">></span><span>/proc/sys/vm/ dirty_expire_centisecs </span></span></li></ol>
因此若没有调整这些参数,全部以缺省值,而且关闭sync-bin的话,那么最多丢失的数据是:
5秒种(dirty_writeback_centisecs)之内的,小于1.6G的数据(dirty_background_ratio,16G/10=1.6G)
当然,实际上5秒之内不太可能写1.6G的数据,因此最坏就是5秒钟之内的数据丢失。因此若要关闭sync-bin,又不想丢失太多数据的话,可以通过调整dirty_writeback_centisecs这个参数,如调整到200(2秒),这样最多就丢2秒钟的数据。又可以提高数据的写能力。
MySQL里还有一个参数可以调整,提高数据库的写能力,那就是
<ol class="dp-xml"><li class="alt"><span><span>innodb_flush_log_at_trx_commit </span></span></li></ol>
这个参数默认是1,即每次事务Commit时,都刷新日志,以免数据丢失。因为我们的系统允许丢失少量数据,因此可以把innodb_flush_log_at_trx_commit设置为2,允许丢失一个事务的数据,经测试,发现2可以提高25%左右的性能。
另外对于文件系统的mount方式,noatime方式也可以提高部分性能(数据库专用的服务器,一般是noatime)
当数据有删除更新操作后,时间长后一般有碎片,导致索引空间不紧凑,占用更多的硬盘空间,因此会导致查询编码,解决办法是定期执行下面的语句:
<ol class="dp-sql"><li class="alt"><span><span class="keyword">ALTER</span><span> </span><span class="keyword">TABLE</span><span> tbl_name ENGINE=INNODB </span></span></li></ol>
另外若sql语句中有sort 和group by之类,需要增大sort_buffer_size
这个参数 是每客户端连接的,当有sort/group查询时,会分配sort_buffer_size大小的内存,因此若连接很多,则要小心;合适的值可以查看 SHOW GLOBAL STATUS里面Sort_merge_passes的信息以及Created_tmp_tables之类信息。

MySQL adalah sistem pengurusan pangkalan data relasi sumber terbuka, terutamanya digunakan untuk menyimpan dan mengambil data dengan cepat dan boleh dipercayai. Prinsip kerjanya termasuk permintaan pelanggan, resolusi pertanyaan, pelaksanaan pertanyaan dan hasil pulangan. Contoh penggunaan termasuk membuat jadual, memasukkan dan menanyakan data, dan ciri -ciri canggih seperti Operasi Join. Kesalahan umum melibatkan sintaks SQL, jenis data, dan keizinan, dan cadangan pengoptimuman termasuk penggunaan indeks, pertanyaan yang dioptimumkan, dan pembahagian jadual.

MySQL adalah sistem pengurusan pangkalan data sumber terbuka yang sesuai untuk penyimpanan data, pengurusan, pertanyaan dan keselamatan. 1. Ia menyokong pelbagai sistem operasi dan digunakan secara meluas dalam aplikasi web dan bidang lain. 2. Melalui seni bina pelanggan-pelayan dan enjin penyimpanan yang berbeza, MySQL memproses data dengan cekap. 3. Penggunaan asas termasuk membuat pangkalan data dan jadual, memasukkan, menanyakan dan mengemas kini data. 4. Penggunaan lanjutan melibatkan pertanyaan kompleks dan prosedur yang disimpan. 5. Kesilapan umum boleh disahpepijat melalui pernyataan yang dijelaskan. 6. Pengoptimuman Prestasi termasuk penggunaan indeks rasional dan pernyataan pertanyaan yang dioptimumkan.

MySQL dipilih untuk prestasi, kebolehpercayaan, kemudahan penggunaan, dan sokongan komuniti. 1.MYSQL Menyediakan fungsi penyimpanan dan pengambilan data yang cekap, menyokong pelbagai jenis data dan operasi pertanyaan lanjutan. 2. Mengamalkan seni bina pelanggan-pelayan dan enjin penyimpanan berganda untuk menyokong urus niaga dan pengoptimuman pertanyaan. 3. Mudah digunakan, menyokong pelbagai sistem operasi dan bahasa pengaturcaraan. 4. Mempunyai sokongan komuniti yang kuat dan menyediakan sumber dan penyelesaian yang kaya.

Mekanisme kunci InnoDB termasuk kunci bersama, kunci eksklusif, kunci niat, kunci rekod, kunci jurang dan kunci utama seterusnya. 1. Kunci dikongsi membolehkan urus niaga membaca data tanpa menghalang urus niaga lain dari membaca. 2. Kunci eksklusif menghalang urus niaga lain daripada membaca dan mengubah suai data. 3. Niat Kunci mengoptimumkan kecekapan kunci. 4. Rekod Rekod Kunci Kunci Rekod. 5. Gap Lock Locks Index Rakaman Gap. 6. Kunci kunci seterusnya adalah gabungan kunci rekod dan kunci jurang untuk memastikan konsistensi data.

Sebab -sebab utama prestasi pertanyaan MySQL yang lemah termasuk tidak menggunakan indeks, pemilihan pelan pelaksanaan yang salah oleh pengoptimasi pertanyaan, reka bentuk jadual yang tidak munasabah, jumlah data yang berlebihan dan persaingan kunci. 1. Tiada indeks menyebabkan pertanyaan perlahan, dan menambah indeks dapat meningkatkan prestasi dengan ketara. 2. Gunakan perintah Jelaskan untuk menganalisis pelan pertanyaan dan cari ralat pengoptimuman. 3. Membina semula struktur meja dan mengoptimumkan keadaan gabungan dapat meningkatkan masalah reka bentuk jadual. 4. Apabila jumlah data adalah besar, pembahagian dan strategi bahagian meja diterima pakai. 5. Dalam persekitaran konkurensi yang tinggi, mengoptimumkan urus niaga dan strategi mengunci dapat mengurangkan persaingan kunci.

Dalam pengoptimuman pangkalan data, strategi pengindeksan hendaklah dipilih mengikut keperluan pertanyaan: 1. Apabila pertanyaan melibatkan pelbagai lajur dan urutan syarat ditetapkan, gunakan indeks komposit; 2. Apabila pertanyaan melibatkan pelbagai lajur tetapi urutan syarat tidak ditetapkan, gunakan pelbagai indeks lajur tunggal. Indeks komposit sesuai untuk mengoptimumkan pertanyaan berbilang lajur, manakala indeks lajur tunggal sesuai untuk pertanyaan tunggal lajur.

Untuk mengoptimumkan pertanyaan perlahan MySQL, SlowQuerylog dan Performance_Schema perlu digunakan: 1. Dayakan SlowQueryLog dan tetapkan ambang untuk merakam pertanyaan perlahan; 2. Gunakan Performance_Schema untuk menganalisis butiran pelaksanaan pertanyaan, cari kesesakan prestasi dan mengoptimumkan.

MySQL dan SQL adalah kemahiran penting untuk pemaju. 1.MYSQL adalah sistem pengurusan pangkalan data sumber terbuka, dan SQL adalah bahasa standard yang digunakan untuk mengurus dan mengendalikan pangkalan data. 2.MYSQL menyokong pelbagai enjin penyimpanan melalui penyimpanan data yang cekap dan fungsi pengambilan semula, dan SQL melengkapkan operasi data yang kompleks melalui pernyataan mudah. 3. Contoh penggunaan termasuk pertanyaan asas dan pertanyaan lanjutan, seperti penapisan dan penyortiran mengikut keadaan. 4. Kesilapan umum termasuk kesilapan sintaks dan isu -isu prestasi, yang boleh dioptimumkan dengan memeriksa penyataan SQL dan menggunakan perintah menjelaskan. 5. Teknik pengoptimuman prestasi termasuk menggunakan indeks, mengelakkan pengimbasan jadual penuh, mengoptimumkan operasi menyertai dan meningkatkan kebolehbacaan kod.


Alat AI Hot

Undresser.AI Undress
Apl berkuasa AI untuk mencipta foto bogel yang realistik

AI Clothes Remover
Alat AI dalam talian untuk mengeluarkan pakaian daripada foto.

Undress AI Tool
Gambar buka pakaian secara percuma

Clothoff.io
Penyingkiran pakaian AI

AI Hentai Generator
Menjana ai hentai secara percuma.

Artikel Panas

Alat panas

Dreamweaver CS6
Alat pembangunan web visual

SecLists
SecLists ialah rakan penguji keselamatan muktamad. Ia ialah koleksi pelbagai jenis senarai yang kerap digunakan semasa penilaian keselamatan, semuanya di satu tempat. SecLists membantu menjadikan ujian keselamatan lebih cekap dan produktif dengan menyediakan semua senarai yang mungkin diperlukan oleh penguji keselamatan dengan mudah. Jenis senarai termasuk nama pengguna, kata laluan, URL, muatan kabur, corak data sensitif, cangkerang web dan banyak lagi. Penguji hanya boleh menarik repositori ini ke mesin ujian baharu dan dia akan mempunyai akses kepada setiap jenis senarai yang dia perlukan.

PhpStorm versi Mac
Alat pembangunan bersepadu PHP profesional terkini (2018.2.1).

ZendStudio 13.5.1 Mac
Persekitaran pembangunan bersepadu PHP yang berkuasa

SublimeText3 Linux versi baharu
SublimeText3 Linux versi terkini