Re: crashed tables after copy
I'm experiencing the same problem. Everytime when I shut down mysql and use myisamchk to do the maintenance, I'll get a few tables crashed. In phpmyadmin they showed a status of "in use". I have to repair them to get the db working. Rainer - Original Message - From: "Brian Moon" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Wednesday, October 29, 2003 11:16 PM Subject: crashed tables after copy > Over the last two days, we have upgraded the OS on our two database > servers. We have one that handles the database for our content and the > other handles the database for our forums. > > For completeness sake, they were both running RH 7.1 with kernels > patched for ReiserFS. The data was stored on a ReiserFS, Raid 5 > partition. Both databases were running 4.0.15a. > > Here is the problem we had. > > We shut down server A cleanly and rsync'd the database dir over to > server B. Shortly after doing this, we started getting 145 errors on > some of the data we had just moved. We stopped mysql, repaired > (myisamchk -of) the tables and got going again. Shortly after that, the > tables were crashed again. We then repaired again, dumped to a .sql > file, then recreated the tables from scratch. From that point on, no > problems. > > We upgraded server A to RH 9. We then moved all the data from server B > over to server A using rsync. Again, shortly after, crashed tables. > repair, no good. dumped the tables in question, rebuilt from scratch, > no problem. > > So, is there something I did that could have caused this? It was not > the same tables each time. While I don't plan on moving the data around > any time soon, I do need to set up a slave and don't want to screw > anything up. I normally rsync or tar up the data on the master as LOAD > DATA FROM MASTER has not proven reliable over WAN for me. > > -- > Brian Moon, dealnews.com, Inc. > dealnews.com News on computer hardware bargains > dealmac.com The latest Mac bargains > Comparison shop @ dealram.com dealcam.com dealink.com > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql Performance Question
10 Query SELECT p.*, t.post_text FROM xoops_bb_posts p, xoops_bb_posts_ 408517 xoops 192.168.0.1 baby_kingd10 Query SELECT p.*, t.post_text FROM xoops_bb_posts p, xoops_bb_posts_ 408519 xoops 192.168.0.1 baby_kingd10 Query SELECT p.*, t.post_text FROM xoops_bb_posts p, xoops_bb_posts_ 408581 xoops 192.168.0.1 baby_kingd10 Query SELECT p.*, t.post_text FROM xoops_bb_posts p, xoops_bb_posts_ 408604 xoops 192.168.0.1 baby_kingd10 Query SELECT uname FROM xoops_users WHERE uid = 793 408605 xoops 192.168.0.1 baby_kingd10 Query SELECT * FROM xoops_users WHERE uid=3089 408490 xoops 192.168.0.1 baby_kingd11 Query SELECT p.*, t.post_text FROM xoops_bb_posts p, xoops_bb_posts_ 408492 xoops 192.168.0.1 baby_kingd11 Query SELECT p.*, t.post_text FROM xoops_bb_posts p, xoops_bb_posts_ 408497 xoops 192.168.0.1 baby_kingd11 Query SELECT p.*, t.post_text FROM xoops_bb_posts p, xoops_bb_posts_ 408592 xoops 192.168.0.1 baby_kingd11 Query SELECT t.*, u.uname, u2.uname as last_poster, p.post_time as l 408599 xoops 192.168.0.1 baby_kingd11 Query SELECT t.*, u.uname, u2.uname as last_poster, p.post_time as l 408600 xoops 192.168.0.1 baby_kingd11 Query SELECT t.*, u.uname, u2.uname as last_poster, p.post_time as l 408602 xoops 192.168.0.1 baby_kingd11 Query SELECT * FROM xoops_users WHERE uid=3819 TOP -- 12:10:01 up 2 days, 8:52, 1 user, load average: 4.15, 6.79, 7.02 40 processes: 38 sleeping, 2 running, 0 zombie, 0 stopped CPU0 states: 67.2% user 25.4% system0.0% nice 0.0% iowait 6.4% idle CPU1 states: 10.0% user 7.3% system0.0% nice 0.0% iowait 82.2% idle Mem: 2064500k av, 1130168k used, 934332k free, 0k shrd, 62876k buff 933640k actv, 49892k in_d, 26704k in_c Swap: 2040244k av, 36k used, 2040208k free 684320k cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND 3138 mysql 15 0 264M 264M 2232 S99.9 13.0 1792m 0 mysqld-max SAR -- 07:00:00 HK all 0.66 0.00 0.47 98.87 07:10:00 HK all 5.04 0.00 1.89 93.06 07:20:00 HK all 4.85 0.00 2.23 92.92 07:30:00 HK all 2.25 0.00 1.15 96.60 07:40:00 HK all 2.88 0.00 1.34 95.78 07:50:00 HK all 2.00 0.00 1.12 96.88 08:00:00 HK all 4.09 0.00 1.86 94.05 08:10:00 HK all 4.90 0.00 2.38 92.72 08:20:00 HK all 3.40 0.00 1.73 94.88 08:30:00 HK all 2.65 0.00 1.42 95.92 08:40:00 HK all 4.25 0.00 1.98 93.77 08:50:00 HK all 5.92 0.00 2.74 91.34 09:00:00 HK all 7.57 0.00 3.36 89.07 09:10:00 HK all 12.44 0.00 5.50 82.06 09:20:00 HK all 32.86 0.00 14.31 52.84 09:30:00 HK all 24.03 0.00 9.57 66.40 09:40:00 HK all 31.26 0.00 13.83 54.91 09:50:00 HK all 43.56 0.00 21.61 34.83 10:00:03 HK all 42.48 0.00 19.74 37.78 10:10:00 HK all 38.89 0.00 19.31 41.80 10:10:00 HK CPU %user %nice %system %idle 10:20:00 HK all 42.50 0.00 20.21 37.29 10:30:00 HK all 35.91 0.00 16.64 47.45 10:40:00 HK all 46.26 0.00 22.03 31.72 10:50:00 HK all 43.28 0.00 19.37 37.35 11:00:00 HK all 35.22 0.00 16.42 48.36 11:10:00 HK all 33.16 0.00 15.55 51.29 11:20:00 HK all 36.43 0.00 16.64 46.93 11:30:00 HK all 35.75 0.00 16.84 47.41 11:40:00 HK all 39.29 0.00 18.52 42.20 11:50:00 HK all 38.25 0.00 19.21 42.54 12:00:01 HK all 57.51 0.00 28.40 14.09 12:10:00 HK all 50.69 0.00 26.55 22.76 Average: all 15.37 0.00 7.33 77.30 - Original Message - From: "David Griffiths" <[EMAIL PROTECTED]> To: "Rainer Sip" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Wednesday, October 22, 2003 3:11 AM Subject: Re: Mysql Performance Question > You need to figure out what's slowing down your application. It could be > expensive queries which in turn could be caused by missing indexes. It could > be that the machine is too slow or the configuration of MySQL is > sub-optimal. > > In this case, the machine looks fine. > > I can't comment on queries or indexes. > > Your tuning, I can. > > set-variable= key_buffer=512M >
Re: Mysql Performance Question
Thanks a lot guys. Haydies: >Just out of wondering, are you using PHP and if so do you use mysql_pconnect >rather then mysql_connect because that would really speed things up. I tired pconnect before but it didn't help but using up all the available memory. It speeds up things until the disks started swapping, which happened in a few hours after the server was up and running. Gabriel: >Can you make changes to the site's software? Perhaps more aggressive >caching would help. It looks like Xoops is using PHP, so you could try >using something like php memcache (http://lucifer.intercosmos.net/) or >Turck MMCache (http://sourceforge.net/projects/turck-mmcache) or any >other PHP cache / accelerator product. I'm now using PHPA to cache the scripts. >Will Xoops take advantage of InnoDB's transactional capabilities? I probably don't need the transactional capabilities of innodb. I'm just thinking the row level locking of innodb will bring some more speed to the server. >Can you add more RAM? What type of disks are you using in the RAID >setup? Is it hardware or software RAID? Due to limited budget I'm unable to add more memory in the short future (really don't want to replace 4 512MB modules with 1G modules). In fact there are always about 5-800MB of unused/buffered memory. The machine is equipped with hardware raid, and 4 19GB SCSI disks. Cheers, Rainer - Original Message - From: "Rainer Sip" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, October 21, 2003 5:48 PM Subject: Mysql Performance Question I'm running a community site (Xoops) on Mysql 4.0.14. I found that the speed of my site is slow during peak hours, when there are 450 concurrent uers hanging on the site. Mytop showed that the queries per second maxed at 500. I believe this could be higher, provided that I have it running on a dedicated machine. I also noticed the load average is very high (12+ during peak hours) In the mid run I'm planning to mirgrate to innodb for higher concurrency (I'm currently using myisam). However, I'm seeking suggestions in fine tuning the parameters. The machine is a Dell PowerEdge 4400 with 2 Xeon 1G processors and 2GB of memory. There are 4 disks running raid 0+1. Attached the my.cnf for your easy review. Thanks a lot in advance. Cheers, Rainer [client] socket=/var/lib/mysql/mysql.sock [client] socket=/var/lib/mysql/mysql.sock [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock skip-locking set-variable= key_buffer=512M set-variable= max_allowed_packet=1M set-variable= table_cache=1024 set-variable= sort_buffer=16M set-variable= join_buffer=8M set-variable= record_buffer=8M set-variable= thread_cache=512 set-variable= max_connections=500 set-variable= tmp_table_size=128M set-variable= thread_concurrency=4 set-variable= myisam_sort_buffer_size=512M set-variable= query_cache_size=6M #set-variable = max_connect_errors=1000 #set-variable = back_log=100 #log-bin skip-innodb [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [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=256M set-variable= sort_buffer=124M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=8M set-variable= write_buffer=4M [mysqlhotcopy] interactive-timeout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysql Performance Question
I'm running a community site (Xoops) on Mysql 4.0.14. I found that the speed of my site is slow during peak hours, when there are 450 concurrent uers hanging on the site. Mytop showed that the queries per second maxed at 500. I believe this could be higher, provided that I have it running on a dedicated machine. I also noticed the load average is very high (12+ during peak hours) In the mid run I'm planning to mirgrate to innodb for higher concurrency (I'm currently using myisam). However, I'm seeking suggestions in fine tuning the parameters. The machine is a Dell PowerEdge 4400 with 2 Xeon 1G processors and 2GB of memory. There are 4 disks running raid 0+1. Attached the my.cnf for your easy review. Thanks a lot in advance. Cheers, Rainer [client] socket=/var/lib/mysql/mysql.sock [client] socket=/var/lib/mysql/mysql.sock [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock skip-locking set-variable= key_buffer=512M set-variable= max_allowed_packet=1M set-variable= table_cache=1024 set-variable= sort_buffer=16M set-variable= join_buffer=8M set-variable= record_buffer=8M set-variable= thread_cache=512 set-variable= max_connections=500 set-variable= tmp_table_size=128M set-variable= thread_concurrency=4 set-variable= myisam_sort_buffer_size=512M set-variable= query_cache_size=6M #set-variable = max_connect_errors=1000 #set-variable = back_log=100 #log-bin skip-innodb [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [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=256M set-variable= sort_buffer=124M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=8M set-variable= write_buffer=4M [mysqlhotcopy] interactive-timeout
Re: Socket Missing
Hi there, This is my first message to this mailing list. I just upgraded Mysql from 3.23.54 to 4.0.14. Everything were fine after the upgrade, but I experienced something strange: - When I telnet to the localhost, the response says 3.23.54 is running. - However, when I do a mysqladmin -V, it shows 4.0.14 - I tried to log into mysql, the connection refused and there was a error 2002. Of course, the socket is missing. - When I locate the mysql.sock file, it showed that the file is under /var/lib/mysql, when I cd to that directory, no socket file can be found. Fortunately the server is still up and running. However I'm confused with the installation. Could someone give me a hand. Thanks a lot in advance. Rainer
