On Wed, May 19, 2004 at 11:36:38AM -0400, Brent Baisley wrote: > I wouldn't upgrade until you know where the bottleneck is (CPU, disk, > network, or RAM). Since you are using "professional" software, I > wouldn't try to change the queries. Have you made changes to your > my.cnf file? Since you have enough ram to hold all the data, ram is > probably not your bottleneck. The question is whether you have your > system configured to use it.
Here is the uncommented parts in the /etc/my.cnf: [client] port = 3306 socket = /tmp/mysql.sock [mysqld] port = 3306 socket = /tmp/mysql.sock key_buffer = 384M skip-locking set-variable = max_connections=3500 max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 32M thread_concurrency = 2 server-id = 1 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M > I forget if phpbb and phpnuke use PEAR::DB to access the underlying > database. If they do, there is a certain amount of overhead involved > that you just can't get around without changing the database access > code to use "native" calls. Are your database and web server on the > same machine (a security no-no, but performance yes-yes)? If they are > on separate machines, make sure you have a fast connection between > them, at least 100MB and make sure it's full duplex. > I think they don't use it, The database on a separate server, with a 1000MB LAN connection, Same for the 2 webservers. I verified this now. > > On May 19, 2004, at 10:34 AM, Mohammed Sameer wrote: > > >On Wed, May 19, 2004 at 08:47:28AM -0400, Brent Baisley wrote: > >>Something that small shouldn't really need optimizing. What is the > >>size > >>of your data (mb?, gb?) and what does your query look like? If you are > >>doing a wild card search on a large text field without a full text > >>index, then those times may be the best you're going to get. Many > >>times > >>it's about optimizing your query statement or you indexes rather than > >>MySQL settings. > >mainly we are using 2 databases: > >675M for phpbb and 77M for phpnuke. That's not much. > >Ok, It seems that the actual queries performed by phpbb are optimized. > > > >>For instance, I have a test machine (Mac G4 400Mhz, 512MB, MySQL > >>4.0.18) that does a three table join with each table having just over > >>100,000 rows. My initial query took about 12 seconds, but changing my > >>query statement around a little I got it to just under 1 second. Which > >>is pretty good considering the hardware it's on. > >>What does you explain for the query look like? It's probably doing a > >>full table scan, which means you're bottleneck is the disk. > >> > > > >but the server is running on a duel P III with 2 GB RAM and a RAID > >controller. > >Do you think there is much to be done, or shall I look for better > >hardware/or clustering ? > > > >>On May 19, 2004, at 3:26 AM, Mohammed Sameer wrote: > >> > >>>Hi, > >>>I'm running a MySQL server on a duel P III 1G, with 2 GB RAM. > >>>MySQL 4.0.18 compiled from source. > >>> > >>>We have 2 webservers running apache, And this is the backend database > >>>server. > >>>The server is really slow. > >>>a select on a table with 138,247 rows takes about 1.6 - 2+ seconds, I > >>>have about 50% CPU idle. > >>> > >>>using MyISAM table types. > >>> > >>>sure I can provide any statistics about the running server. > >>>Do you think that switching to innodb'd help ? Is it a problem with > >>>Debian woody ?? > >>>Any suggestions are welcomed. > >>> > >>>Many thanks! > >>> > >>>-- > >>>---------------- > >>>-- Katoob Main Developer > >>>Linux registered user #224950, ICQ #58475622 > >>>-- > >>>Don't send me any attachment in Micro$oft (.DOC, .PPT) format please > >>>Read http://www.fsf.org/philosophy/no-word-attachments.html > >>>Preferable attachments: .PDF, .HTML, .TXT > >>>Thanx for adding this text to Your signature > >>>-- > >>>-----BEGIN GEEK CODE BLOCK----- > >>>Version: 3.1 > >>>GCM/IT d-(++)@ s+(++):->+++ a-- C+++$>++++ UL+++$>++++ P+++$>+++++ > >>>L+++(++++)$>+++++ E>+++ W++?>$ N+>+++ o? K-? !w++ !O !M !V !PS@ !PE@ > >>>Y+ PGP=+++ > >>>t? 5? !X R? tv-- b+@ DI D+ G-- e++>+++ h-->++ !r y? > >>>------END GEEK CODE BLOCK------ > >>> > >>-- > >>Brent Baisley > >>Systems Architect > >>Landover Associates, Inc. > >>Search & Advisory Services for Advanced Technology Environments > >>p: 212.759.6400/800.759.0577 > >> > > > >-- > >---------------- > >-- Katoob Main Developer > >Linux registered user #224950, ICQ #58475622 > >-- > >Don't send me any attachment in Micro$oft (.DOC, .PPT) format please > >Read http://www.fsf.org/philosophy/no-word-attachments.html > >Preferable attachments: .PDF, .HTML, .TXT > >Thanx for adding this text to Your signature > >-- > >-----BEGIN GEEK CODE BLOCK----- > >Version: 3.1 > >GCM/IT d-(++)@ s+(++):->+++ a-- C+++$>++++ UL+++$>++++ P+++$>+++++ > >L+++(++++)$>+++++ E>+++ W++?>$ N+>+++ o? K-? !w++ !O !M !V !PS@ !PE@ > >Y+ PGP=+++ > >t? 5? !X R? tv-- b+@ DI D+ G-- e++>+++ h-->++ !r y? > >------END GEEK CODE BLOCK------ > > > -- > Brent Baisley > Systems Architect > Landover Associates, Inc. > Search & Advisory Services for Advanced Technology Environments > p: 212.759.6400/800.759.0577 > -- ---------------- -- Katoob Main Developer Linux registered user #224950, ICQ #58475622 -- Don't send me any attachment in Micro$oft (.DOC, .PPT) format please Read http://www.fsf.org/philosophy/no-word-attachments.html Preferable attachments: .PDF, .HTML, .TXT Thanx for adding this text to Your signature -- -----BEGIN GEEK CODE BLOCK----- Version: 3.1 GCM/IT d-(++)@ s+(++):->+++ a-- C+++$>++++ UL+++$>++++ P+++$>+++++ L+++(++++)$>+++++ E>+++ W++?>$ N+>+++ o? K-? !w++ !O !M !V !PS@ !PE@ Y+ PGP=+++ t? 5? !X R? tv-- b+@ DI D+ G-- e++>+++ h-->++ !r y? ------END GEEK CODE BLOCK------
signature.asc
Description: Digital signature