Slow query using between
| iprange |1 | startIpNum | A | 82350 | NULL | NULL | YES | BTREE | | | hn_iprange | 1 | iprange |2 | endIpNum| A | 82350 | NULL | NULL | YES | BTREE | | +++--+--+- +---+-+--++--+ +-+ 3 rows in set (0.00 sec) T1 has 20,029 rows T2 has 82350 rows Many thanks to anyone who can offer any insight to this quandary. Cheers James Riordon Hosting | Webdesign | PHP | DTP http://www.outofcontrol.ca
Re: Server comparison running Mysql
On Sunday, October 6, 2002, at 01:53 AM, James Riordon wrote: Have you tried monitoring server B via 'top' or something and see what processes are taking up CPU time and/or swapping. Hello, I have monitored top for a bit. It is definitely MySQL-Max that is taking up the CPU and placing the load on the server. I have run strace on some of the pids and see nothing normal. I have attached here one of the listings from processlist just in case you see something: All right, after some more testing I have done the following: RECAP I have two servers, server A and server B. Server A has dual 1GHz PIII chips with 1GB ram and 2 ide drives running Ensim 3.0.4, php 4.1.2 and mysql-max 3.23.49a. Server B is a single processor 1.7Ghz PIV with 1.5GB of RAM and 1 ide drive running Ensim 3.1.1, php 4.1.2 and mysql-max 3.23.49a. I have one site on Server A which runs well and the server load is consistently under 1. When I moved that site to the new server it spiraled and crashed within seconds with high loads. After some investigation I thought it was MySQL that was the problem. Reason being is that I get tons of MySQL children or connections but they never close. Then I did the following. I recreated the site on Server B. In the config files on Server B I pointed the MySQL connect to Server A. I then redirected all traffic from Server A to Server B. So now Server A is handling all the mysql stuff as it was before, but all the httpd stuff is on Server B. Now for the strange thing, Server A crashes and burns. Yes, the dual processor machine now dies after about 2-3 minutes with load averages as high as 30 or more! All from mysql-max. So, I am thinking that it is no longer MySQL that is the problem. I would think from this test that it is either PHP or Apache that is opening connections with MySQL and then not releasing them. I am using connect() and not pconnect(), and I have disabled persistent connections in php.ini. My httpd.conf settings and my.cnf settings are below. Sorry if this sounds confusing but it is hard to describe it all clearly. Can anyone give me some suggestions of things I can look at on the new server that might be causing MySQL to open all these connections. I have checked the process list and I get a lot of process that just sit there and do nothing for over a minute. Almost like they are used once and then sit there until they die. Here are my config settings (same on both servers so I only include them once here): my.cnf - set-variable = max_connections=200 set-variable = interactive_timeout=100 set-variable = wait_timeout=100 set-variable = thread_cache_size=256 set-variable = connect_timeout=120 set-variable = myisam_sort_buffer_size=32M set-variable = key_buffer=16M set-variable = join_buffer=3M set-variable = record_buffer=3M set-variable = sort_buffer=5M set-variable = table_cache=1024 skip-locking Apache httpd.conf - Timeout 300 KeepAlive On MaxKeepAliveRequests 300 KeepAliveTimeout 30 MinSpareServers 32 MaxSpareServers 64 StartServers 5 MaxClients 256 #MaxRequestsPerChild 30 Many many thanks. James - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Server comparison running Mysql
At 11:12 -0300 10/06/2002, I whispered: Maybe P4 1,7 mhz performs marginally better than a P3/1000Mhz, but ... My webserver have a P3/450Mhz with 384 MB Ram and a 10 Gb IDE drive :P I cant seem to get above 1.0 load average on the machine on normal use (I have average 10-30 people online simultaneously 24 hours a day). The machine runs Apache/Php/MySQL. The only time I've reached 80-90% CPU load and more than 1.0 load average was during stresstesting, where I let 500 simulated visitors connect to 3 pages each every 20 seconds. The webserver, of course, turned into a turtle, which is natural. But still, seeing a 1.7Ghz P4 with those load averages, thats not something I find normal - compared to my P3/450... Cheers Rob. I have to admit that I don't find it normal either. Although not really an upgrade, more exactly a 'move' to new server, I would think that with my current load average of 0.4 on the old dual machine, the new machine would be able to handle the load abeit with an increased load. I am checking out some other things people have me to look at both on the list and off this list, and will report back when I am finished. Our ISP is dog slow tonight making work kind of boring. More tomorrow. Thanks by the way to everyone who has given some good suggestions. James - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Server comparison running Mysql
At 10:42 -0500 10/07/2002, gerald_clark was thought to have said : You don't give operating system versions for the two machines. Right you are: Server A Redhat 7.1 PHP 4.2.1 MySQL-Max 3.23.49a 2.4 (2.4.9-12) Server B Redhat 7.2 PHP 4.2.2 MySQL-Max 3.23.52 (Just upgraded again) 2.4 (2.4.9-34) I have also tried a machine with of similar build to Server B with a freshly built kernel 2.4.18-10. Same results as Server B with the 2.4.9-34 kernel. James James Riordon wrote: Hi, I have two servers. Server A: Dual 1GHz processors 1GB of RAM MySQL-Max 3.23.49a PHP 4.2.1 Server B: Single 1.7GHz processors 1.5GB of RAM MySQL-Max 3.23.49a PHP 4.2.2 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Re: Fwd: Re: Server comparison running Mysql
At 15:53 +0100 10/06/2002, Andrew Braithwaite was thought to have said : Hi James, What kind of disks are in the old system and the new system (scsi or ide, any raid, what rpm do they have etc..?) I ask this because high cpu levels can be a symptom of a diskbound server... Try running the following: iostat -x 5 5 vmstat 5 5 Let me know I'll try to help. sqlquery As requested, here is are the results fro vmstat and iostat from the new slow server and the old dual zippy server both under the exact same load less than 2 minutes apart. Single Processor iostat --- Linux 2.4.9-34 () 10/08/2002 avg-cpu: %user %nice%sys %idle 1.180.050.40 98.37 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util hda3.53 9.33 0.53 3.57 32.40 103.3533.15 0.03 215.68 42.90 1.76 hda1 0.00 0.00 0.00 0.000.000.0017.98 0.00 525.42 468.64 0.00 hda2 0.00 0.00 0.00 0.000.000.00 5.00 0.00 150.00 150.00 0.00 hda3 3.52 9.33 0.53 3.57 32.39 103.3433.15 0.03 215.66 42.89 1.76 avg-cpu: %user %nice%sys %idle 0.000.000.40 99.60 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util hda0.00 3.00 0.00 1.000.00 32.0032.00 0.00 0.00 0.00 0.00 hda1 0.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.00 0.00 hda2 0.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.00 0.00 hda3 0.00 3.00 0.00 1.000.00 32.0032.00 0.00 0.00 0.00 0.00 avg-cpu: %user %nice%sys %idle 0.000.000.20 99.80 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util hda0.00 0.40 0.00 1.800.00 17.60 9.78 0.00 0.00 0.00 0.00 hda1 0.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.00 0.00 hda2 0.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.00 0.00 hda3 0.00 0.40 0.00 1.800.00 17.60 9.78 0.00 0.00 0.00 0.00 avg-cpu: %user %nice%sys %idle 7.600.007.20 85.20 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util hda0.00 0.20 0.00 0.600.006.4010.67 0.00 0.00 0.00 0.00 hda1 0.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.00 0.00 hda2 0.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.00 0.00 hda3 0.00 0.20 0.00 0.600.006.4010.67 0.00 0.00 0.00 0.00 avg-cpu: %user %nice%sys %idle 6.200.006.60 87.20 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util hda0.00 7.60 0.00 9.600.00 139.2014.50 0.00 0.00 0.00 0.00 hda1 0.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.00 0.00 hda2 0.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.00 0.00 hda3 0.00 7.60 0.00 9.600.00 139.2014.50 0.00 0.00 0.00 0.00 Single Processor vmstat --- procs memoryswap io system cpu r b w swpd free buff cache si sobibo incs us sy id 0 0 0 21544 71820 234956 1007796 0 01652 4456 1 0 13 0 0 0 21544 71812 234956 1007796 0 0 016 11223 0 1 99 0 0 0 21544 71812 234956 1007796 0 0 0 9 10720 0 0 100 0 0 0 21544 71812 234956 1007796 0 0 0 3 11018 0 0 100 0 0 0 21544 71480 234956 1007796 0 0 070 131 116 14 14 73 Dual Processor iostat - avg-cpu: %user %nice%sys %idle 0.380.052.292.60 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util hdc7.19 3.02 1.24 5.548.25 10.1720.19 0.03 21.87 0.69 0.05 hdc1 0.00 0.00 0.00 0.000.010.0040.91 0.00 1291.58 1281.52 0.00 hdc5 7.19 3.02 1.24 5.548.24 10.1720.19 0.03 21.84 0.69 0.05 hdc6 0.00 0.00 0.00 0.000.000.00 8.00 0.00 250.00 250.00 0.00 avg-cpu: %user %nice%sys %idle 22.200.00 11.20 66.60 Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util hdc0.00 0.00 0.00 0.400.003.20 8.00 0.00 0.00 0.00 0.00 hdc1 0.00 0.00 0.00 0.000.000.00 0.00 0.00 0.00 0.00 0.00 hdc5 0.00 0.00 0.00 0.400.003.20 8.00 0.00 0.00 0.00 0.00 hdc6 0.00 0.00 0.00 0.000.000.00 0.00
RE: Re: Server comparison running Mysql
At 11:17 -0500 10/06/2002, I whispered: -Original Message- From: Robert H.R. Restad [mailto:[EMAIL PROTECTED]] Sent: Sunday, October 06, 2002 9:04 AM To: James Riordon; [EMAIL PROTECTED] Subject: RES: Re: Server comparison running Mysql Another thing... I noticed once when I moved a website from one server to another, that when exporting/importing the MySQL databases, there was a few errors which theoretically shouldnt have been possible to get. Illegal/duplicate indexes etc. I had to override and supress error messages to actually be allowed to import the database at all. (Even if the original database worked - appearantly - perfect on the original webserver) How did you move the tables over? I've never had a problem moving databases between machines when I use mysqldump like mysqldump -C --add-drop-table blah --databases DB1 DB2 DB3|rsh newhost mysql. I moved the databases over as such mysqldump --add-drop-table DB1 Then on the new machine I rebuild the index via OPTIMIZE TABLE tablename; REPAIR TABLE tablename QUICK; for each table. Perhaps i did something wrong? sql, select - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Fwd: Re: Fwd: Re: Server comparison running Mysql
Since it seems that your new box is limited by the CPU, the conclusion
seems that your old 1GHz biprocessor had more horsepower than the new
single 1.7GHz processor.
Have you replaced a dual PentiumIII by a single PentiumIV ?
Some benchmarks show that a 1.7GHz PIV performs only marginally better
than a 1GHz PIII
(check http://www17.tomshardware.com/cpu/00q4/001120/index.html).
Hope this helps
--
Joseph Bueno
Although this was the sort of answer I was suspecting, I was hoping
no one would actually give it to me :-) I think i have come close to
exhausting all my possibilities through mysql at this point. Someone
suggested the Kernel, but I fear it is the lack of an extra processor.
J
James Riordon wrote:
Have you tried monitoring server B via 'top' or something and see what
processes are taking up CPU time and/or swapping.
Hello,
I have monitored top for a bit. It is definitely MySQL-Max that
is taking up the CPU and placing the load on the server. I have run
strace on some of the pids and see nothing normal. I have attached
here one of the listings from processlist just in case you see
something:
+-+--+---+---+-+--+--+--+
| Id | User | Host | db| Command |
Time | State| Info |
+-+--+---+---+-+--+--+--+
| 509 | {user} | localhost | {database} | Query
| 0| Sorting result | SELECT sid,title,hits from stories
WHERE uid 0 and Hits 0 ORDER BY Hits desc LIMIT 10|
| 513 | {user} | localhost | {database} | Sleep
| 7| | |
| 516 | {user} | localhost | {database} | Query
| 1| Sending data | SELECT COUNT(*) FROM comments WHERE
sid = 'ical' |
| 521 | {user} | localhost | {database} | Sleep
| 5| | |
| 523 | {user} | localhost | {database} | Sleep
| 2| | |
| 525 | {user} | localhost | {database} | Sleep
| 0| | |
| 526 | {user} | localhost | {database} | Query
| 1| Sorting result | SELECT UNIX_TIMESTAMP(date) AS day
FROM comments WHERE sid = '20021001061844504' ORDER BY date desc |
| 529 | {user} | localhost | {database} | Sleep
| 1| | |
| 531 | {user} | localhost | {database} | Query
| 1| Sorting result | SELECT UNIX_TIMESTAMP(date) AS day
FROM comments WHERE sid = '20021001061844504' ORDER BY date desc |
| 534 | {user} | localhost | {database} | Query
| 1| Sorting result | SELECT UNIX_TIMESTAMP(date) AS day
FROM comments WHERE sid = '20021002055217828' ORDER BY date desc |
| 535 | {user} | localhost | {database} | Query
| 0| Sorting result | SELECT UNIX_TIMESTAMP(date) AS day
FROM comments WHERE sid = '20021001061844504' ORDER BY date desc |
| 538 | {user} | localhost | {database} | Query
| 2| Sorting result | SELECT UNIX_TIMESTAMP(date) AS day
FROM comments WHERE sid = '20021002055217828' ORDER BY date desc |
| 541 | {user} | localhost | {database} | Query
| 1| Sorting result | SELECT UNIX_TIMESTAMP(date) AS day
FROM comments WHERE sid = '20021001061844504' ORDER BY date desc |
| 542 | {user} | localhost | {database} | Query
| 2| Sorting result | SELECT UNIX_TIMESTAMP(date) AS day
FROM comments WHERE sid = '20021001061844504' ORDER BY date desc |
| 544 | {user} | localhost | {database} | Init DB
| 1| Writing to net | |
| 553 | {user} | localhost | {database} | Query
| 0| Sorting result | SELECT UNIX_TIMESTAMP(date) AS day
FROM comments WHERE sid = '20021002062208512' ORDER BY date desc |
| 556 | {user} | localhost | {database} | Query
| 1| | SELECT imageurl FROM topics WHERE
tid = 'apps' |
| 557 | {user} | localhost | {database} | Query
| 0| Sorting result | SELECT UNIX_TIMESTAMP(date) AS day
FROM comments WHERE sid = '20021002063014627' ORDER BY date desc |
| 561 | {user} | localhost | {database} | Query
| 2| Sorting result | SELECT UNIX_TIMESTAMP(date) AS day
FROM comments WHERE sid = '20021002062208512' ORDER BY date desc |
| 562 | {user} | localhost | {database} | Query
| 0| Sorting result | SELECT UNIX_TIMESTAMP(date) AS day
Server comparison running Mysql
Hi, I have two servers. Server A: Dual 1GHz processors 1GB of RAM MySQL-Max 3.23.49a PHP 4.2.1 Server B: Single 1.7GHz processors 1.5GB of RAM MySQL-Max 3.23.49a PHP 4.2.2 Server A has a site that uses about 75GB of bandwidth per month all pulled through PHP and MySQL. Load average is 0.4 over time. Processlist is never above 2-5 process showing at a time. All in all a very nice server. When I switch the site to Server B - same database, same program, same my.cnf, my load average on Server B jumps to 25-35, the process list jumps to 30-50 processes, the server comes to a grinding hault and then sits there, all within 30-60 seconds of redirecting the site. I thought Server B would be able to happily handle the large load but it apparently is not. I am trying to figure out it Server B just can't handle the load, or if there is a problem somewhere. Can anyone give me their experienced thoughts on which I might be experiencing? Any suggested things I should try? I am kind of in a bind here as I wish to get rid of the Dual processor machine for the colo it is in is quite expensive and the bandwidth is quite low on a monthly basis. The new colo does not seem to offer dual processor machines. Time is of the essence for me so any thoughts or suggestions would be greatfully accepted. Thanks James - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Fwd: Re: Server comparison running Mysql
| |
|
| 575 | {user} | localhost | {database} | Query |
2| Sorting result | SELECT UNIX_TIMESTAMP(date) AS day FROM
comments WHERE sid = '20021002063853736' ORDER BY date desc |
| 576 | {user} | localhost | {database} | Sleep |
4| |
|
| 577 | {user} | localhost | {database} | Sleep |
4| |
|
| 583 | {user} | localhost | {database} | Sleep |
3| |
|
| 584 | {user} | localhost | {database} | Sleep |
3| |
|
| 586 | {user} | localhost | {database} | Query |
4| Copying to tmp table | SELECT distinct *, count(*) as dups,
comments.cid,comments.sid,stories.sid,stories.title,max(UNIX_TI |
| 587 | {user} | localhost | {database} | Query |
2| Copying to tmp table | SELECT distinct *, count(*) as dups,
comments.cid,comments.sid,stories.sid,stories.title,max(UNIX_TI |
| 588 | {user} | localhost | {database} | Query |
2| Sorting result | SELECT UNIX_TIMESTAMP(date) AS day FROM
comments WHERE sid = '20021002063853736' ORDER BY date desc |
| 590 | {user} | localhost | {database} | Query |
3| Copying to tmp table | SELECT distinct *, count(*) as dups,
comments.cid,comments.sid,stories.sid,stories.title,max(UNIX_TI |
| 591 | {user} | localhost | {database} | Query |
3| Copying to tmp table | SELECT distinct *, count(*) as dups,
comments.cid,comments.sid,stories.sid,stories.title,max(UNIX_TI |
| 592 | root | localhost | | Query | 0
| | show processlist
|
| 594 | {user} | localhost | {database} | Query |
0| Sorting result | SELECT *,UNIX_TIMESTAMP(date) AS day
FROM stories where UNIX_TIMESTAMP(date) 1033794000 ORDER BY U |
| 595 | {user} | localhost | | Connect |
| login|
|
| 598 | {user} | localhost | {database} | Sleep |
0| |
|
| 599 | {user} | localhost | {database} | Sleep |
0|
|
|
| 600 | {user} | localhost | | Connect |
| login|
|
| 601 | unauthenticated user | localhost | | Connect |
| login|
|
+-+--+---+---+-+--+--+--+
Here is my /etc/my.cnf too:
[mysqld]
port= 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
set-variable= key_buffer=256M
set-variable= max_allowed_packet=1M
set-variable= table_cache=512
set-variable= sort_buffer=1M
set-variable= record_buffer=1M
set-variable= myisam_sort_buffer_size=64M
set-variable= thread_cache=64
set-variable= wait_timeout=30
set-variable= max_connections=350
set-variable= max_connect_errors=25
# Try number of CPU's*2 for thread_concurrency
set-variable= thread_concurrency=8
#log-bin
#server-id = 1
# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname
[mysqldump]
quick
set-variable= max_allowed_packet=16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
set-variable= key_buffer=128M
set-variable= sort_buffer=128M
set-variable= read_buffer=2M
set-variable= write_buffer=2M
[myisamchk]
set-variable= key_buffer=128M
set-variable= sort_buffer=128M
set-variable= read_buffer=2M
set-variable= write_buffer=2M
[mysqlhotcopy]
interactive-timeout
Thanks
James
Bhavin.
- Original Message -
From: James Riordon [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Saturday, October 05, 2002 9:31 PM
Subject: Server comparison running Mysql
Hi,
I have two servers.
Server A:
Dual 1GHz processors
1GB of RAM
MySQL-Max 3.23.49a
PHP 4.2.1
Server B:
Single 1.7GHz processors
1.5GB of RAM
MySQL-Max 3.23.49a
PHP 4.2.2
Server A has a site that uses about 75GB of bandwidth per
month all pulled through PHP and MySQL. Load average is 0.4 over
time. Processlist is never above 2-5 process showing at a time. All
in all a very nice server.
When I switch the site to Server B - same database, same
program, same my.cnf, my load average on Server B jumps to 25-35, the
process list jumps to 30-50 processes, the server comes to a grinding
hault and then sits there, all within 30-60 seconds of redirecting
the site.
I thought Server B would be able to happily handle the large
load but it apparently is not.
I am trying to figure out it Server B just can't handle the
load
Re: Adding an index to a table
At 15:07 +0800 03/28/2002, Sammy Lau was thought to have said : ?http://www.mysql.com/doc/C/R/CREATE_INDEX.html Granted, I should have looked before I spoke for the first question. 2 - Do I have to do anything special like stop requests to the server while I add the index. 3 - How safe is this to do? I do not see the answer for #2 and #3 in the recommended reading. Can anyone comment on those questions, or should I just backup and dive on in? Many thanks. James - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Adding an index to a table
Hi, I have a MySQL database that currently is used by about 8000 users. I need to add an index to one of the fields in one of the tables to help speed up a particular query being made to the MySQL database. I am a bit nervous to do so in case I screw up. Mind you I have backed up the databases all ready. 1 - What command do I issue to add a simple index to one field in a table. 2 - Do I have to do anything special like stop requests to the server while I add the index. 3 - How safe is this to do? Many thanks. James - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Tweaking MySQL on a RaQ (or any server) Help please.
At 9:07 -0500 02/07/2002, Ken Menzel was thought to have said : Many of the questions on this list are answered by people like myself who are users of MySQL. We are busy trying to run our own servers and put food on the table. That being said, you are wondering why your question did not get an answer, it may be the length of the message and the not-very-specific question. I will keep this in mind for my next barrage of questions GRIN Unix System Administration Handbook, Prentice Hall MySQL, the manual is an excellent source of information. MySQL by Paul Dubois from New Riders is a standard Core MySQL by Leon Atkinson Prentice Hall (Just out this year) Of the above books I have them all with the exception of the last. I will get it and read it too. Oh, I have read quite extensively the MySQL manual and it is quite enlightening. I feel I have covered all the material necessary, but something is nagging me, screaming out Yo, over here buddy, tune me too. i just can't put my finger on that one little nagging voice in my head. FROWN Also there are web resources: http://www.mysql.com/portal/books http://www.faqts.com/knowledge_base/index.phtml/fid/52/ Articles on MySQL in the portal section of the web site: http://www.mysql.com/portal/development/html/development-61-1.html Thanks. I will add these to my list and take a look soon. The MySQL support contract is by far the best money I have ever spent on software! I will look into this. Thanks again. Best of luck and I hope this helped, It most certainly is a help. Everything I read on this list seems to allow my capabilities of MySQL grow in leaps and bounds. Hope I did not come off sounding to whiny about not getting an answer the first time round. Overworked these days...oh, and under paid. Again, many thanks to all those on the list. James - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Tweaking MySQL on a RaQ (or any server) Help please.
the problems. From random 'top' viewings, I see that I have on average 5-10 mysqld running around 50-75% CPU usage (total) and about 56MB of RAM each. Here is a typical view of the top of my 'top', so to speak: 8754 mysql 11 0 62456 60M 1684 R 0 23.3 12.0 0:09 mysqld 8453 mysql 19 0 62456 60M 1684 R 0 18.3 12.0 0:35 mysqld 7618 mysql 10 0 62456 60M 1684 R 0 6.7 12.0 1:32 mysqld 7619 mysql 12 0 62456 60M 1684 R 0 6.3 12.0 1:25 mysqld 8299 mysql 1 0 62456 60M 1684 S 0 4.3 12.0 0:42 mysqld 8298 mysql 0 0 62456 60M 1684 S 0 4.1 12.0 0:29 mysqld 12991 httpd 2 0 15284 14M 11680 S 0 2.7 2.8 1:14 httpd 12987 httpd 0 0 15544 14M 11660 S 0 2.0 2.9 1:14 httpd 7833 mysql 0 0 62456 60M 1684 S 0 1.8 12.0 1:17 mysqld 12853 httpd 0 0 14780 14M 11624 S 0 1.6 2.7 1:20 httpd 13042 httpd 0 0 14804 14M 11672 S 0 1.6 2.7 1:20 httpd 12665 httpd 0 0 14716 14M 11668 S 0 1.5 2.7 1:11 httpd There, that is about it for now. I have done my homework and have spent many many hours study the list, the documentation, searching the 'Net' for more information, and I have not come up with a way to make our machine more efficient. So, if anyone can suggest even the most obvious of things to look at then I would be forever grateful. James Riordon SysAdmin (at least for now...) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Tweaking MySQL my.cnf
Hi Again, I have honed this question pretty well I think (MySQL 3.23.37): I am using large-my.cnf as my my.cnf file. It is for servers with 512 MB of RAM from what I read. On our server that has 512MB of RAM I notice in top the following: 10244 mysql 16 0 147M 147M 1684 S 0 16.7 29.2 0:08 mysqld 10260 mysql 14 0 148M 148M 1684 R 0 8.0 29.4 0:10 mysqld All instances of mysqld are using between 146-154MB of RAM each. This seems awefully large to me. Is this correct or should I be reducing the amount of RAM each child gets. James - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Tweaking MySQL on a RaQ (or any server) Help please.
At 9:07 -0500 02/07/2002, Ken Menzel was thought to have said : Many of the questions on this list are answered by people like myself who are users of MySQL. We are busy trying to run our own servers and put food on the table. That being said, you are wondering why your question did not get an answer, it may be the length of the message and the not-very-specific question. I will keep this in mind for my next barrage of questions GRIN Unix System Administration Handbook, Prentice Hall MySQL, the manual is an excellent source of information. MySQL by Paul Dubois from New Riders is a standard Core MySQL by Leon Atkinson Prentice Hall (Just out this year) Of the above books I have them all with the exception of the last. I will get it and read it too. Oh, I have read quite extensively the MySQL manual and it is quite enlightening. I feel I have covered all the material necessary, but something is nagging me, screaming out Yo, over here buddy, tune me too. i just can't put my finger on that one little nagging voice in my head. FROWN Also there are web resources: http://www.mysql.com/portal/books http://www.faqts.com/knowledge_base/index.phtml/fid/52/ Articles on MySQL in the portal section of the web site: http://www.mysql.com/portal/development/html/development-61-1.html Thanks. I will add these to my list and take a look soon. The MySQL support contract is by far the best money I have ever spent on software! I will look into this. Thanks again. Best of luck and I hope this helped, It most certainly is a help. Everything I read on this list seems to allow my capabilities of MySQL grow in leaps and bounds. Hope I did not come off sounding to whiny about not getting an answer the first time round. Overworked these days...oh, and under paid. Again, many thanks to all those on the list. James - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Tweaking MySQL my.cnf
Hi Again, I have honed this question pretty well I think (MySQL 3.23.37): I am using large-my.cnf as my my.cnf file. It is for servers with 512 MB of RAM from what I read. On our server that has 512MB of RAM I notice in top the following: 10244 mysql 16 0 147M 147M 1684 S 0 16.7 29.2 0:08 mysqld 10260 mysql 14 0 148M 148M 1684 R 0 8.0 29.4 0:10 mysqld All instances of mysqld are using between 146-154MB of RAM each. This seems awefully large to me. Is this correct or should I be reducing the amount of RAM each child gets. James - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Tweaking MySQL on a RaQ (or any server) Help please.
the problems. From random 'top' viewings, I see that I have on average 5-10 mysqld running around 50-75% CPU usage (total) and about 56MB of RAM each. Here is a typical view of the top of my 'top', so to speak: 8754 mysql 11 0 62456 60M 1684 R 0 23.3 12.0 0:09 mysqld 8453 mysql 19 0 62456 60M 1684 R 0 18.3 12.0 0:35 mysqld 7618 mysql 10 0 62456 60M 1684 R 0 6.7 12.0 1:32 mysqld 7619 mysql 12 0 62456 60M 1684 R 0 6.3 12.0 1:25 mysqld 8299 mysql 1 0 62456 60M 1684 S 0 4.3 12.0 0:42 mysqld 8298 mysql 0 0 62456 60M 1684 S 0 4.1 12.0 0:29 mysqld 12991 httpd 2 0 15284 14M 11680 S 0 2.7 2.8 1:14 httpd 12987 httpd 0 0 15544 14M 11660 S 0 2.0 2.9 1:14 httpd 7833 mysql 0 0 62456 60M 1684 S 0 1.8 12.0 1:17 mysqld 12853 httpd 0 0 14780 14M 11624 S 0 1.6 2.7 1:20 httpd 13042 httpd 0 0 14804 14M 11672 S 0 1.6 2.7 1:20 httpd 12665 httpd 0 0 14716 14M 11668 S 0 1.5 2.7 1:11 httpd There, that is about it for now. I have done my homework and have spent many many hours study the list, the documentation, searching the 'Net' for more information, and I have not come up with a way to make our machine more efficient. So, if anyone can suggest even the most obvious of things to look at then I would be forever grateful. James Riordon SysAdmin (at least for now...) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Tweaking MySQL on a RaQ
2.7 1:11 httpd There, that is about it for now. I have done my homework and have spent many many hours study the list, the documentation, searching the 'Net' for more information, and I have not come up with a way to make our machine more efficient. So, if anyone can suggest even the most obvious of things to look at then I would be forever grateful. James Riordon SysAdmin (at least for now...) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Bytes sent vs. Bandwidth used
At 22:35 -0800 01/21/2002, I whispered: On Mon, Jan 21, 2002 at 11:02:47PM -0500, James Riordon wrote: I have our new client up and running and have come upon another question. When I get extended-status I get the following: | Bytes_received | 53553199 | | Bytes_sent | 664736462 | SNIP Is Apache/PHP on the same server as MySQL? Yes. We have Apache, PHP and MySQL all running on the same server. Our actual throughput yesterday actually turned out to be closer to 350MB but the MySQL extended-status Bytes_Sent showed around 700MB by the end of the day. Is this normal? Why does it work this way. Many thanks James - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Bytes sent vs. Bandwidth used
Hi All, I have our new client up and running and have come upon another question. When I get extended-status I get the following: | Bytes_received | 53553199 | | Bytes_sent | 664736462 | If I am correct this mean about 664MB sent to the client from MySQL. The problem is that the entire server is only showing abount 128MB of bandwidth usage for the same time period. So my question is: What exactly does Bytes_sent refer to. Is this a server internal usage, or is my bandwidth usage for MySQL not showing up properly in my stats? Or is it possible that alot of the information being sent from MySQL to PHP for processing is simply not getting used in the final output. If this is the case then would that not show an in-efficient use of queries? Many thanks in advance for any help. James Riordon System Administrator Amigo-3 Interactive Inc. http://www.amigo-3.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL and PHP on a RaQ Server
Hi, This is my first post to this list so please be patient on this one. I was told that this might be the place to get an answer to this question, although not entirely a MySQL Question. So, any suggestions or help would appreciated. We have a RaQ4i 400MHz server with 512MB of RAM running PHP4.03p1 and MySQL 3.23.43 if memory serves correctly. We are looking at bringing on a client who uses 1 MySQL database and PHP to host a news site similar to Slashdot, the OpenSource app he uses is called GeekLog, if anyone has heard of it. He gets about 5000 unique visitors a day and about 10 page views per day. He uses about 30GB of bandwidth a month. I cannot say how many actual queries the databases gets unfortunately. Most (30-40%) of the traffic appears to be around mid-day My question is - what is the opinion that our server can handle this site. In mind and experience there should be no problem with a site as this. Again, I realize that this is partially off topic, but it would also be nice to know of various server configurations that work with MySQL for people who are making custom installations with varying demands and configurations. Does such a place exist. Many thanks in advance for any help that anyone on the list can offer. James Riordon System Administrator Amigo-3 Interactive Inc. http://www.amigo-3.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
