MYSQL Using 100% CPU

[imranlink]

Hi,

I am using one computer as Web server and other for Mysql Database server(Please view there below Specification).

Mysql server CPU shows me 50% usage for any single request from web Server and 100% for two or more reqests (threads from Web server having PHP scripts to select data from tables. Select queries only select data from single table so have no joins from different databases.). I also got an error message when there where 5 threads running.

Can u please help me how can i Optimized MYSQL Server for better performance?

Warning: mysql_pconnect(): Can't create a new thread (errno 12). If you are not out of available memory, you can consult the manual for a possible OS-dependent

---

my.ini

[mysqld]
basedir=C:/mysql
datadir=C:/mysql/data
max_allowed_packet=16MB
ft_min_word_len=2
key_buffer=300MB
key_buffer_size=300MB
query_cache_size=300MB
table_cache=200MB
sort_buffer=300MB
sort_buffer_size=300MB
read_buffer_size=300MB
record_buffer=300MB
binlog_cache_size=300MB
bulk_insert_buffer_size=300MB
join_buffer_size=300MB
query_cache_limit=300MB
query_cache_type=1

[WinMySQLadmin]
Server=C:/mysql/bin/mysqld-nt.exe

---

My SQL Server [System Summary]

Item Value
OS Name Microsoft Windows 2000 Server
Version 5.0.2195 Service Pack 4 Build 2195
OS Manufacturer Microsoft Corporation
System Manufacturer Intel
System Model LM440GX+ DP
System Type X86-based PC
Processor x86 Family 6 Model 7 Stepping 3 GenuineIntel ~497 Mhz
Processor x86 Family 6 Model 7 Stepping 3 GenuineIntel ~497 Mhz
BIOS Version L440GX+ PhoenixBIOS L440GX+ Production Release 11.2
Windows Directory C:\WINNT
System Directory C:\WINNT\system32
Boot Device \Device\Harddisk0\Partition1
Total Physical Memory 1,048,044 KB
Available Physical Memory 624,620 KB
Total Virtual Memory 6,189,980 KB
Available Virtual Memory 3,687,588 KB
Page File Space 5,141,936 KB
Page File C:\pagefile.sys

---

WEB Server [System Summary]

Item Value
OS Name Microsoft Windows 2000 Server
Version 5.0.2195 Service Pack 4 Build 2195
OS Manufacturer Microsoft Corporation
System Manufacturer Intel
System Model LM440GX+ DP
System Type X86-based PC
Processor x86 Family 6 Model 7 Stepping 3 GenuineIntel ~497 Mhz
Processor x86 Family 6 Model 7 Stepping 3 GenuineIntel ~497 Mhz
BIOS Version L440GX+ PhoenixBIOS L440GX+ Production Release 11.1
Windows Directory C:\WINNT
System Directory C:\WINNT\system32
Boot Device \Device\Harddisk0\Partition1
Total Physical Memory 1,048,044 KB
Available Physical Memory 448,608 KB
Total Virtual Memory 4,044,688 KB
Available Virtual Memory 2,804,364 KB
Page File Space 2,996,644 KB
Page File C:\pagefile.sys

---

Any Help will be appricated.

[bastien]

Assuming you are running IIS as the webserver, it might be that you have a limit on the number of concurrent connections allowed by IIS itself...You'll need to loook at the properties of the webserver itself

Running at 100% for a little while is nothing, we oftern spike 100% on the CPU with intensive queries...consider tuning the sql to reduce the load on the system (alter the queries, add indeces, break the queries into parts etc)

[imranlink]

First of all thank you for replying me, I am greatfull for your kind help.

Well, My database's size is = 8 GB
MYSQL Server: 4.0.18-nt
Database size if big due to lot of Blob images stored inside it for each web site.
I have unlimited connections in IIS. Any further help?

[bastien]

Storing images in db is always kinda dicey...and as you have found out, creating high loads on the system....

I tend to prefer storing images on the file system and storing the path/image name as text...simpler to handle, smaller loads etc