Re: MySQL Optimal Configuration
"G Ramon Gomez" <[email protected]>
| Newsgroups | gmane.comp.security.ids.prelude.user |
|---|---|
| Message-ID | <622FD37285F1584386F5F83D9D233C9C02AC98AC@SMF-ENTXM-001.sac.ragingwire.net> |
The issue is probably only partially due to your MySQL configuration, and mostly due to using the Open Source Prelude database (which is just plain bad). Consider using Prelude-XLR instead. - Ramon -----Original Message----- From: [email protected] [mailto:[email protected]] On Behalf Of Adriel Desautels Sent: Wednesday, June 25, 2008 10:00 AM To: [email protected] Subject: [prelude-user] MySQL Optimal Configuration Greetings, I'm looking for some people that are running prelude on a fairly busy network. I'm particularly interested in their mysql configurations and how they have their systems setup. Anyone willing to share? My reason for asking is that we are experiencing latency that appears to be attributed to the database. This latency is up to 100 seconds for a refresh of the console, and IMHO thats far too much. Below is a report from our test SQL server as a demonstration of the latency: __ Key _________________________________________________________________ Buffer used 6.00k of 768.00M %Used: 0.00 <--- OK Current 86.56M %Usage: 11.27 Write hit 0.00% Read hit 75.00% __ Questions ___________________________________________________________ Total 4.09k 102.3/s DMS 4.06k 101.6/s %Total: 99.24 <--- HIGH Com_ 27 0.7/s 0.66 COM_QUIT 2 0.1/s 0.05 QC Hits 1 0.0/s 0.02 +Unknown 1 0.0/s 0.02 Slow 100 s 0 0/s 0.00 %DMS: 0.00 Log: ON DMS 4.06k 101.6/s 99.24 SELECT 3.77k 94.2/s 92.04 92.74 <-- HIGH INSERT 294 7.3/s 7.18 7.24 UPDATE 1 0.0/s 0.02 0.02 REPLACE 0 0/s 0.00 0.00 DELETE 0 0/s 0.00 0.00 Com_ 27 0.7/s 0.66 commit 12 0.3/s 0.29 begin 12 0.3/s 0.29 show_status 1 0.0/s 0.02 __ SELECT and Sort _____________________________________________________ Scan 3 0.1/s %SELECT: 0.08 <--- OK Range 978 24.4/s 25.96 Full join 0 0/s 0.00 Range check 0 0/s 0.00 Full rng join 0 0/s 0.00 Sort scan 270 6.8/s Sort range 0 0/s Sort mrg pass 0 0/s __ Query Cache _________________________________________________________ Memory usage 1.64M of 32.00M %Used: 5.11 <--- OK Block Fragmnt 19.96% Hits 1 0.0/s Inserts 3.74k 93.4/s Insrt:Prune 3.74k:1 93.4/s Hit:Insert 0.00:1 __ Table Locks _________________________________________________________ Waited 0 0/s %Total: 0.00 <-- OK Immediate 5.44k 136.0/s __ Tables ______________________________________________________________ Open 47 of 1800 %Cache: 2.61 <-- OK Opened 41 1.0/s __ Connections _________________________________________________________ Max used 3 of 200 %Max: 1.50 <-- OK Total 4 0.1/s __ Created Temp ________________________________________________________ Disk table 0 0/s Table 272 6.8/s Size: 32.0M <-- OK File 0 0/s __ Threads _____________________________________________________________ Running 1 of 2 Cached 0 of 0 %Hit: 0 <-- OK Created 4 0.1/s Slow 0 0/s __ Aborted _____________________________________________________________ Clients 0 0/s Connects 0 0/s __ Bytes _______________________________________________________________ Sent 1.47M 36.8k/s Received 773.04k 19.3k/s __ InnoDB Buffer Pool __________________________________________________ Usage 49.78M of 1.00G %Used: 4.86 <-- OK Read hit 99.74% Pages Free 62.35k %Total: 95.14 Data 2.82k 4.30 %Drty: 0.00 Misc 368 0.56 Latched 0 0.00 Reads 910.58k 22.8k/s From file 2.40k 60.0/s 0.26 Ahead Rnd 5 0.1/s Ahead Sql 3 0.1/s Writes 1.05k 26.2/s Flushes 136 3.4/s Wait Free 0 0/s __ InnoDB Lock _________________________________________________________ Waits 0 0/s <-- OK Current 0 Time acquiring Total 0 ms Average 0 ms Max 0 ms __ InnoDB Data, Pages, Rows ____________________________________________ Data Reads 2.47k 61.7/s Writes 154 3.9/s fsync 32 0.8/s Pending Reads 0 Writes 0 fsync 0 Pages Created 1 0.0/s Read 2.75k 68.8/s Written 136 3.4/s Rows Deleted 0 0/s Inserted 294 7.3/s Read 343.48k 8.6k/s Updated 0 0/s -- Regards, Adriel T. Desautels Chief Technology Officer Netragard, LLC. Office : 617-934-0269 Mobile : 617-633-3821 http://www.linkedin.com/pub/1/118/a45 Join the Netragard, LLC. Linked In Group: http://www.linkedin.com/e/gis/48683/0B98E1705142 --------------------------------------------------------------- Netragard, LLC - http://www.netragard.com - "We make IT Safe" Penetration Testing, Vulnerability Assessments, Website Security Netragard Whitepaper Downloads: ------------------------------- Choosing the right provider : http://tinyurl.com/2ahk3j Three Things you must know : http://tinyurl.com/26pjsn _______________________________________________ Prelude-user site list [email protected] http://lists.prelude-ids.org/mailman/listinfo/prelude-user