bitsCN.com
Hello, I'm a Sun Solaris sys admin for a start-upcompany. I've got the UNIX background, but now I'mhaving to learn PostgreSQL to support it on ourservers :)Server Background:Solaris 10 x86PostgreSQL 8.0.3Dell PowerEdge 2650 w/4gb ram.This is running JBoss/Apache as well (I KNOW the badjuju of running it all on one box, but it's all wehave currently for this project). I'm dedicating 1gbfor PostgreSQL alone.So, far I LOVE it compared to MySQL it's solid.The only things I'm kind of confused about (and I'vebeen searching for answers on lot of good perf docs,but not too clear to me) are the following:1.) shared_buffers I see lot of reference to makingthis the size of available ram (for the DB). However,I also read to make it the size of pgdata directory.I notice when I load postgres each daemon is using theamount of shared memory (shared_buffers). Our currentdataset (pgdata) is 85mb in size. So, I'm curiousshould this size reflect the pgdata or the 'actual'memory given?I currently have this at 128mb
You generally want shared_buffers to be no more than 10% of availableram. Postgres expects the OS to do it's own caching. 128M/4G = 3% seemsreasonable to me. I would certainly never set it to 100% of ram.
2.) effective_cache_size - from what I read this isthe 'total' allowed memory for postgresql to usecorrect? So, if I am willing to allow 1GB of memoryshould I make this 1GB?
This is the effective amount of caching between the actual postgresbuffers, and the OS buffers. If you are dedicating this machine topostgres, I would set it to something like 3.5G. If it is a mixedmachine, then you have to think about it.This does not change how postgres uses RAM, it changes how postgresestimates whether an Index scan will be cheaper than a Sequential scan,based on the likelihood that the data you want will already be cached inRam.If you dataset is only 85MB, and you don't think it will grow, youreally don't have to worry about this much. You have a very small database.
3.) max_connections, been trying to figure 'how' todetermine this #. I've read this is buffer_size+500kper a connection.ie. 128mb(buffer) + 500kb = 128.5mb per connection?
Max connections is just how many concurrent connections you want toallow. If you can get away with lower, do so. Mostly this is to preventconnections * work_mem to get bigger than your real working memory andcausing you to swap.
I was curious about 'sort_mem' I can't find referenceof it in the 8.0.3 documentation, has it been removed?
sort_mem changed to work_mem in 8.0, same thing with vacuum_mem ->maintenance_work_mem.
work_mem and max_stack_depth set to 4096maintenance_work_mem set to 64mb
Depends how much space you want to give per connection. 4M is prettysmall for a machine with 4G of RAM, but if your DB is only 85M it mightbe plenty.work_mem is how much memory a sort/hash/etc will use before it spills todisk. So look at your queries. If you tend to sort most of your 85M dbin a single query, you might want to make it a little bit more. But ifall of your queries are very selective, 4M could be plenty.I would make maintenance_work_mem more like 512M. It is only used forCREATE INDEX, VACUUM, etc. Things that are not generally done by morethan one process at a time. And it's nice for them to have plenty ofroom to run fast.
Thanks for any help on this. I'm sure bombardment ofnewbies gets old :)-William
Good luck,JohnbitsCN.com

MySQL數據庫升級的步驟包括:1.備份數據庫,2.停止當前MySQL服務,3.安裝新版本MySQL,4.啟動新版本MySQL服務,5.恢復數據庫。升級過程需注意兼容性問題,並可使用高級工具如PerconaToolkit進行測試和優化。

MySQL備份策略包括邏輯備份、物理備份、增量備份、基於復制的備份和雲備份。 1.邏輯備份使用mysqldump導出數據庫結構和數據,適合小型數據庫和版本遷移。 2.物理備份通過複製數據文件,速度快且全面,但需數據庫一致性。 3.增量備份利用二進制日誌記錄變化,適用於大型數據庫。 4.基於復制的備份通過從服務器備份,減少對生產系統的影響。 5.雲備份如AmazonRDS提供自動化解決方案,但成本和控制需考慮。選擇策略時應考慮數據庫大小、停機容忍度、恢復時間和恢復點目標。

MySQLclusteringenhancesdatabaserobustnessandscalabilitybydistributingdataacrossmultiplenodes.ItusestheNDBenginefordatareplicationandfaulttolerance,ensuringhighavailability.Setupinvolvesconfiguringmanagement,data,andSQLnodes,withcarefulmonitoringandpe

在MySQL中優化數據庫模式設計可通過以下步驟提升性能:1.索引優化:在常用查詢列上創建索引,平衡查詢和插入更新的開銷。 2.表結構優化:通過規範化或反規範化減少數據冗餘,提高訪問效率。 3.數據類型選擇:使用合適的數據類型,如INT替代VARCHAR,減少存儲空間。 4.分區和分錶:對於大數據量,使用分區和分錶分散數據,提升查詢和維護效率。

tooptimizemysqlperformance,lofterTheSeSteps:1)inasemproperIndexingTospeedUpqueries,2)使用ExplaintplaintoAnalyzeandoptimizequeryPerformance,3)ActiveServerConfigurationStersLikeTlikeTlikeTlikeIkeLikeIkeIkeLikeIkeLikeIkeLikeIkeLikeNodb_buffer_pool_sizizeandmax_connections,4)

MySQL函數可用於數據處理和計算。 1.基本用法包括字符串處理、日期計算和數學運算。 2.高級用法涉及結合多個函數實現複雜操作。 3.性能優化需避免在WHERE子句中使用函數,並使用GROUPBY和臨時表。

MySQL批量插入数据的高效方法包括:1.使用INSERTINTO...VALUES语法,2.利用LOADDATAINFILE命令,3.使用事务处理,4.调整批量大小,5.禁用索引,6.使用INSERTIGNORE或INSERT...ONDUPLICATEKEYUPDATE,这些方法能显著提升数据库操作效率。

在MySQL中,添加字段使用ALTERTABLEtable_nameADDCOLUMNnew_columnVARCHAR(255)AFTERexisting_column,刪除字段使用ALTERTABLEtable_nameDROPCOLUMNcolumn_to_drop。添加字段時,需指定位置以優化查詢性能和數據結構;刪除字段前需確認操作不可逆;使用在線DDL、備份數據、測試環境和低負載時間段修改表結構是性能優化和最佳實踐。


熱AI工具

Undresser.AI Undress
人工智慧驅動的應用程序,用於創建逼真的裸體照片

AI Clothes Remover
用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool
免費脫衣圖片

Clothoff.io
AI脫衣器

Video Face Swap
使用我們完全免費的人工智慧換臉工具,輕鬆在任何影片中換臉!

熱門文章

熱工具

Atom編輯器mac版下載
最受歡迎的的開源編輯器

EditPlus 中文破解版
體積小,語法高亮,不支援程式碼提示功能

SublimeText3 Mac版
神級程式碼編輯軟體(SublimeText3)

Safe Exam Browser
Safe Exam Browser是一個安全的瀏覽器環境,安全地進行線上考試。該軟體將任何電腦變成一個安全的工作站。它控制對任何實用工具的訪問,並防止學生使用未經授權的資源。

SAP NetWeaver Server Adapter for Eclipse
將Eclipse與SAP NetWeaver應用伺服器整合。