MySQL performing too badly under heavy load - urgent hlp needed
Hello all, Stuck up with a major problem. Urgent hlp required MySQL seems to be performing too bad during heavy load on the server. Queries which normally take around 5 secs to complete are taking more than 1000 secs to complete during load. What could be the reason. Show processlist shows many process in sending data state. All tables are of INNODB type. But we are not running any transactions as yet. The server is clogged due to many httpd requests (150 Max). All the httpd requests are in W state ( means sending response ). What could be causing this. Is it MySQL or is it Apache... Any suggestions would help... Thanks, Ratheesh K J
Re: MySQL performing too badly under heavy load - urgent hlp needed
I doubt apache is to blame. 5 seconds for a query on a website is extremely slow, so if that is your normal results, then you have a problem there already. I've been building database driven websites for around 11 years and i don't think i can remember a single time i went into production with a single query that was slower than 0.1 second, unless it was something very rarely used for administration purposes. From the top of my head i'd say your problem is either a) poor datamodel design which forces slow queries b) poor usage of indexes in the database (use explain to check) c) non-optimized configuration of the server (have you tuned the server parameters to the way you use the server?) d) insufficient hardware for your needs e) any combination of the above Ratheesh K J wrote: Hello all, Stuck up with a major problem. Urgent hlp required MySQL seems to be performing too bad during heavy load on the server. Queries which normally take around 5 secs to complete are taking more than 1000 secs to complete during load. What could be the reason. Show processlist shows many process in sending data state. All tables are of INNODB type. But we are not running any transactions as yet. The server is clogged due to many httpd requests (150 Max). All the httpd requests are in W state ( means sending response ). What could be causing this. Is it MySQL or is it Apache... Any suggestions would help... Thanks, Ratheesh K J -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL performing too badly under heavy load - urgent hlp needed
At 09:38 AM 7/27/2006, Ratheesh K J wrote: Hello all, Stuck up with a major problem. Urgent hlp required MySQL seems to be performing too bad during heavy load on the server. Queries which normally take around 5 secs to complete are taking more than 1000 secs to complete during load. What could be the reason. Show processlist shows many process in sending data state. All tables are of INNODB type. But we are not running any transactions as yet. The server is clogged due to many httpd requests (150 Max). All the httpd requests are in W state ( means sending response ). What could be causing this. Is it MySQL or is it Apache... Any suggestions would help... Thanks, Ratheesh K J So, what have you tried? Give us some information -- knowing which version you are running would be a good start. Are the queries slow if run from the command line? Are there enough threads in Apache? Have you rebuilt your indexes? Dropped them and replaced. Run optimize database? Done a dump and restore? Miles Thompson -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.1.394 / Virus Database: 268.10.4/401 - Release Date: 7/26/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL performing too badly under heavy load - urgent hlp needed
On Thursday 27 July 2006 17:00, Martin Jespersen wrote: I doubt apache is to blame. 5 seconds for a query on a website is extremely slow, so if that is your normal results, then you have a problem there already. I've been building database driven websites for around 11 years and i don't think i can remember a single time i went into production with a single query that was slower than 0.1 second, unless it was something very rarely used for administration purposes. From the top of my head i'd say your problem is either a) poor datamodel design which forces slow queries b) poor usage of indexes in the database (use explain to check) c) non-optimized configuration of the server (have you tuned the server parameters to the way you use the server?) d) insufficient hardware for your needs e) any combination of the above f) not enough memory, that forces major swapping activity -Stathis Ratheesh K J wrote: Hello all, Stuck up with a major problem. Urgent hlp required MySQL seems to be performing too bad during heavy load on the server. Queries which normally take around 5 secs to complete are taking more than 1000 secs to complete during load. What could be the reason. Show processlist shows many process in sending data state. All tables are of INNODB type. But we are not running any transactions as yet. The server is clogged due to many httpd requests (150 Max). All the httpd requests are in W state ( means sending response ). What could be causing this. Is it MySQL or is it Apache... Any suggestions would help... Thanks, Ratheesh K J -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL performing too badly under heavy load - urgent hlp needed
Do a show status and check on what mysql is doing. I would start by looking at: threads_created - if this is high, increase your thread_cache_size. This means MySQL is busy creating and destroying threads instead of reusing them. This can take a toll on the OS. Opened_tables - if this number is high/climbing, MySQL is buys opening and closing tables, which means your table_cache is probably too low. Compare open_tables to table_cache, open_tables should be lower. Show variables will help you see your current settings. Some things can be changed on the fly, like the thread cache, so can can do some things without taking MySQL down. - Original Message - From: Ratheesh K J [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, July 27, 2006 8:38 AM Subject: MySQL performing too badly under heavy load - urgent hlp needed Hello all, Stuck up with a major problem. Urgent hlp required MySQL seems to be performing too bad during heavy load on the server. Queries which normally take around 5 secs to complete are taking more than 1000 secs to complete during load. What could be the reason. Show processlist shows many process in sending data state. All tables are of INNODB type. But we are not running any transactions as yet. The server is clogged due to many httpd requests (150 Max). All the httpd requests are in W state ( means sending response ). What could be causing this. Is it MySQL or is it Apache... Any suggestions would help... Thanks, Ratheesh K J -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication doesn't work under heavy load
Hi,
I just noticed that replication on my servers failed when they were
under heavy load. I have 1 master and 2 slaves.
For example, I have table for all incoming messages.
message_inbox | CREATE TABLE `message_inbox` (
`member_id` mediumint(8) unsigned NOT NULL default '0',
`message_id` int(10) unsigned NOT NULL default '0',
`new` enum('y','n','replied') NOT NULL default 'y',
`datetime` datetime default NULL,
KEY `idx_1` (`member_id`,`new`),
KEY `idx_2` (`member_id`,`datetime`)
) TYPE=InnoDB
This table has about 2789678 rows.
I have a cron running this every night.
DELETE message_inbox FROM message_inbox LEFT JOIN message ON message.id
= message_inbox.message_id WHERE message.id IS NULL
Message table also has about the same amount of rows.
I notice that when the cron is running, the changes made to this table
don't replicate properly to slave DB. Anyone has same experience? I
checked 'show slave status' but no Last_error or Last_errno was found.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication doesn't work under heavy load
On Monday 04 October 2004 01:18 am, Batara Kesuma wrote: I have a cron running this every night. DELETE message_inbox FROM message_inbox LEFT JOIN message ON message.id = message_inbox.message_id WHERE message.id IS NULL Message table also has about the same amount of rows. I notice that when the cron is running, the changes made to this table don't replicate properly to slave DB. Anyone has same experience? I checked 'show slave status' but no Last_error or Last_errno was found. Define properly? Jeff pgpLp7xvfln8d.pgp Description: PGP signature
Re: MySQL 4.0.13 Memory problem under heavy load
I would run the DB on a RAMDisk http://www.ibiblio.org/mdw/linuxfocus/English/November1999/article124.html Anyone else? Martin - Original Message - From: Kayra Otaner [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, August 20, 2003 3:56 PM Subject: MySQL 4.0.13 Memory problem under heavy load Hi all, I want to get your opinions on how to increase available/free memory and performance on a heavy volume database server. I have MySQL 4.0.13 running on RH 7.2 replicated to another RH 7.2 using same MySQL version. Recently our master database server (2 AMD Cpu + 2Gb memory + 2Gb swap space) started to suffer from memory outages because of heavy load. During day available free memory is changing from 200Mb to 5Mb and when available memory reaches to 5Mb MySQL starts to give 'Too many connections' messages. Db server is working with 45-70 query/second and more than 25,712 connection per hour. There are active 10-13 threads serving databases. To increase available free memory I've did the following : 1- Optimized all tables 2- Removed unneccessary/old indexes 3- Moved unused databases to replication server 4- Increased key_buffer_size from 8Mb to 128Mb 5- Have increased max_connection from 100 to 150 6- Have increased thread_cache to 5 This changes helped a bit but still memory is a problem for MySQL. What should I do to prevent 'too many connections' messages and have more memory available on database servers? Should I remove more indexes from tables? Should I increase key_buffer_size to 256Mb or more? Key_buffer_size doesn't look like a problem since key efficiency looks 100% most of the time. Thank you for your time Kayra Otaner -- 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 4.0.13 Memory problem under heavy load
In the last episode (Aug 20), Kayra Otaner said: I want to get your opinions on how to increase available/free memory and performance on a heavy volume database server. I have MySQL 4.0.13 running on RH 7.2 replicated to another RH 7.2 using same MySQL version. Recently our master database server (2 AMD Cpu + 2Gb memory + 2Gb swap space) started to suffer from memory outages because of heavy load. During day available free memory is changing from 200Mb to 5Mb and when available memory reaches to 5Mb MySQL starts to give 'Too many connections' messages. Db server is working with 45-70 query/second and I don't think those two are related. After an hour of operation, it's expected to have zero Free memory; unix uses unused memory as disk cache, so Free memory is wasted memory. More important questions are: Are you swapping? and What is the current memory usage of mysql? -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.0.13 Memory Problem on heavy load
Hi all, I want to get your opinions on how to increase available/free memory and performance on a heavy volume database server. I have MySQL 4.0.13 running on RH 7.2 replicated to another RH 7.2 using same MySQL version. Recently our master database server (2 AMD Cpu + 2Gb memory + 2Gb swap space) started to suffer from memory outages because of heavy load. During day available free memory is changing from 200Mb to 5Mb and when available memory reaches to 5Mb MySQL starts to give 'Too many connections' messages. Db server is working with 45-70 query/second and more than 25,712 connection per hour. There are active 13-18 threads serving databases. To increase available free memory I've did following : 1- Optimized all tables 2- Removed unneccessary/old indexes 3- Moved unused databases to replication server 4- Increased key_buffer_size from 8Mb to 16Mb This changes helped a bit but still memory is a problem for MySQL. Average MySQL thread used to consume 100-120 Mb memory before changes, now it is consuming 60-70Mb per thread. What should I do to prevent 'too many connections' messages and have more memory available on database servers? Should I remove more indexes from tables? Should I increase key_buffer_size to 32Mb or more? Key_buffer_size doesn't look like a problem since key efficiency looks 100% most of the time. Thank you for your time Kayra Otaner - This mail sent through IMP: http://horde.org/imp/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 4.0.13 Memory problem under heavy load
Keep in mind that Linux will allocate nearly all of its free RAM to the buffer cache as the kernel opens and reads files to increase filesystem performance (cat /proc/meminfo and look at the buffers row) So, lack of free RAM may not mean what you think it means. As for the too many connections issues, take a look at the connections table (mysqladmin proc, or show processlist from the mysql command prompt). You may need to raise the connection limit even further. --Michael -Original Message- From: Kayra Otaner [mailto:[EMAIL PROTECTED] Sent: Wednesday, August 20, 2003 3:57 PM To: [EMAIL PROTECTED] Subject: MySQL 4.0.13 Memory problem under heavy load Hi all, I want to get your opinions on how to increase available/free memory and performance on a heavy volume database server. I have MySQL 4.0.13 running on RH 7.2 replicated to another RH 7.2 using same MySQL version. Recently our master database server (2 AMD Cpu + 2Gb memory + 2Gb swap space) started to suffer from memory outages because of heavy load. During day available free memory is changing from 200Mb to 5Mb and when available memory reaches to 5Mb MySQL starts to give 'Too many connections' messages. Db server is working with 45-70 query/second and more than 25,712 connection per hour. There are active 10-13 threads serving databases. To increase available free memory I've did the following : 1- Optimized all tables 2- Removed unneccessary/old indexes 3- Moved unused databases to replication server 4- Increased key_buffer_size from 8Mb to 128Mb 5- Have increased max_connection from 100 to 150 6- Have increased thread_cache to 5 This changes helped a bit but still memory is a problem for MySQL. What should I do to prevent 'too many connections' messages and have more memory available on database servers? Should I remove more indexes from tables? Should I increase key_buffer_size to 256Mb or more? Key_buffer_size doesn't look like a problem since key efficiency looks 100% most of the time. Thank you for your time Kayra Otaner -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.0.13 Memory problem under heavy load
Hi all, I want to get your opinions on how to increase available/free memory and performance on a heavy volume database server. I have MySQL 4.0.13 running on RH 7.2 replicated to another RH 7.2 using same MySQL version. Recently our master database server (2 AMD Cpu + 2Gb memory + 2Gb swap space) started to suffer from memory outages because of heavy load. During day available free memory is changing from 200Mb to 5Mb and when available memory reaches to 5Mb MySQL starts to give 'Too many connections' messages. Db server is working with 45-70 query/second and more than 25,712 connection per hour. There are active 10-13 threads serving databases. To increase available free memory I've did the following : 1- Optimized all tables 2- Removed unneccessary/old indexes 3- Moved unused databases to replication server 4- Increased key_buffer_size from 8Mb to 128Mb 5- Have increased max_connection from 100 to 150 6- Have increased thread_cache to 5 This changes helped a bit but still memory is a problem for MySQL. What should I do to prevent 'too many connections' messages and have more memory available on database servers? Should I remove more indexes from tables? Should I increase key_buffer_size to 256Mb or more? Key_buffer_size doesn't look like a problem since key efficiency looks 100% most of the time. Thank you for your time Kayra Otaner -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Server Crashes under heavy load
We are hosting a high volume site that gets about 1 million page views a day on RedHat 7.3. We currently have 3 load balanced servers on the front end accessing a MySQL server on the back end. The MySQL servers is dual P3 1ghz with 1 GB of RAM and when the MySQL queries hit about 50 per second, the DB crashes and the servers is useless unless you reset the DB. Memory is only at about 50% usage, but the CPU skyrockets to 100%. The only solution we can think of is to throw a huge server at the backend (i.e. 4-8 processor Compaq 8500) and keep RH 7.3 or switch to Windows 2000 Advanced Server and cluster a few dual P3 servers together. Any other solutions to make MySQL handle a high volume site? CS - 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: MySQL Server Crashes under heavy load
We have a site with a similar architecture: - 6 load balanced front-ends - 1 dedicated database server (Dual P3 1.4GHz, 2GbRAM, RH7.2) we serve around 3 million pages/day (all pages are dynamiquely generated, each page needs an average of 15 SQL queries). What we have done: - audit ALL SQL queries and make sure that they are optimised (all selects use indexes,...) - optimise mysqld configuration (tune key_buffer_size, ..) - setup replication : each front-end is a mysql slave server and replicate most used tables. - modify the site so that heaviest SELECT queries are run by each front-end on local slave. Result: - on main database server: 300queries/s average (~700q/s peak) load : 0.2 average, ~0.7 peak - on each front-end : 30q/s average (60q/s peak) We could run more requests on the slaves but since the master server load is so low, we have postponed those optimisations. I think you really should audit your queries first. From my experience and what other users have reported on this list, you should expect to be able to run several hundred queries/s with the kind of hardware you are using. Hope this helps -- Joseph Bueno Chavvon Smith wrote: We are hosting a high volume site that gets about 1 million page views a day on RedHat 7.3. We currently have 3 load balanced servers on the front end accessing a MySQL server on the back end. The MySQL servers is dual P3 1ghz with 1 GB of RAM and when the MySQL queries hit about 50 per second, the DB crashes and the servers is useless unless you reset the DB. Memory is only at about 50% usage, but the CPU skyrockets to 100%. The only solution we can think of is to throw a huge server at the backend (i.e. 4-8 processor Compaq 8500) and keep RH 7.3 or switch to Windows 2000 Advanced Server and cluster a few dual P3 servers together. Any other solutions to make MySQL handle a high volume site? CS - 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: MySQL Server Crashes under heavy load
Hi Chavvon, we are running a high volume site with currenty 1.3 Million Page Views daily. mysql query average is at 300. We are using the latest stable versions of mysql, apache and php. the server has 2 GB of RAM and 4 XEON 2.4 GZ processors and the load varies between 1 and 2 at peek times. although this box is serving blazing fast even at peak times i think you should cluster if you expect that the hits increase... best regards, philipp - Original Message - From: Joseph Bueno [EMAIL PROTECTED] To: Chavvon Smith [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, January 17, 2003 6:45 PM Subject: Re: MySQL Server Crashes under heavy load We have a site with a similar architecture: - 6 load balanced front-ends - 1 dedicated database server (Dual P3 1.4GHz, 2GbRAM, RH7.2) we serve around 3 million pages/day (all pages are dynamiquely generated, each page needs an average of 15 SQL queries). What we have done: - audit ALL SQL queries and make sure that they are optimised (all selects use indexes,...) - optimise mysqld configuration (tune key_buffer_size, ..) - setup replication : each front-end is a mysql slave server and replicate most used tables. - modify the site so that heaviest SELECT queries are run by each front-end on local slave. Result: - on main database server: 300queries/s average (~700q/s peak) load : 0.2 average, ~0.7 peak - on each front-end : 30q/s average (60q/s peak) We could run more requests on the slaves but since the master server load is so low, we have postponed those optimisations. I think you really should audit your queries first. From my experience and what other users have reported on this list, you should expect to be able to run several hundred queries/s with the kind of hardware you are using. Hope this helps -- Joseph Bueno Chavvon Smith wrote: We are hosting a high volume site that gets about 1 million page views a day on RedHat 7.3. We currently have 3 load balanced servers on the front end accessing a MySQL server on the back end. The MySQL servers is dual P3 1ghz with 1 GB of RAM and when the MySQL queries hit about 50 per second, the DB crashes and the servers is useless unless you reset the DB. Memory is only at about 50% usage, but the CPU skyrockets to 100%. The only solution we can think of is to throw a huge server at the backend (i.e. 4-8 processor Compaq 8500) and keep RH 7.3 or switch to Windows 2000 Advanced Server and cluster a few dual P3 servers together. Any other solutions to make MySQL handle a high volume site? CS - 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
duplicate inserts during heavy load
Hi we have what we think is a bug regarding an insert (or an update) during times of heavy load on the mysql server. Our application should perform an insert in one table and updates on two other tables but the insert is performed up to 9 times. I'm not entierly sure that the problem is NOT in out application (PHP CGI which is run via crontab) but I checked it 2 times and 2 collegues checked it too seperatly so it should be okay. It can't be a problem with slow queries because we log them and non of the affected tables show up in the log. The job that is responsible for the high load on the server is a mysqlhotcopy FROM the database where the affected tables reside in to a new DB which isn't used at that time. There is much work and time needed to replicate the error so I would like to know beforehand what I can do to get logs or anything else to track down that problem. Any help or suggestions would be appreciated. Regards -- Stefan Immel |N|O|C Network Operation Center -+-+-+--- | Grove Auf der Stuecke 6Tel. +49 2773-8167-0 35708 Haiger / Germany Fax +49 2773-8167-20 -- mailto:[EMAIL PROTECTED] http://www.grove.de There is always hope, only because it is the one thing nobody's figured out how to kill yet. ~ Galen, Crusade Racing The Night -- http://www.nocr2.de - NOC R2 die Lösung für den IT-Workflow -- - 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
SOS. Problems updating under heavy load.
Hi, I am using MySQL 3.23.41 and JDBC to support some servers. When I stress the servers I get several errors like java.sql.SQLException: General error: Table testtab was not locked with LOCK TABLES I get this error on some of the insert attempts, and on half of the update attempts. I also tried with row locking with get_lock (with the primary key as argument), but this didn't help much. Please, if someone knows a remedy for this, I would be very glad to know about it. Thanks - 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
innodb producing strange error on a heavy-load system
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 hi, any ideas what could be causing this? this happens when apache has reached its max_client limit. using 3.23.45 011127 18:16:30 InnoDB: Started /home/attila/mysql-max-3.23.45-pc-linux-gnu-i686/bin/mysqld: ready for connecti 011127 18:35:45 read_const: Got error 146 when reading table ./h2_2/users 011127 18:35:48 read_const: Got error 146 when reading table ./h2_23/users InnoDB: Warning: a long semaphore wait: - --Thread 12976157 has waited at btr0cur.c line 348 for 127.00 seconds the semap X-lock on RW-latch at 50adcbb8 created in file buf0buf.c line 348 a writer (thread id 12976157) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file ../include/btr0btr.ic line 28 Last time write locked in file btr0pcur.c line 228 InnoDB: Warning: a long semaphore wait: - --Thread 13377598 has waited at ../include/btr0btr.ic line 28 for 121.00 second S-lock on RW-latch at 50adcbb8 created in file buf0buf.c line 348 a writer (thread id 12976157) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file ../include/btr0btr.ic line 28 Last time write locked in file btr0pcur.c line 228 InnoDB: ## Starts InnoDB Monitor for 30 secs to print diagnostic info: thanks, Attila ... There are many kinds of people in the world. Are you one of them? - --- Public key: http://civ.hu/attila.asc -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE8A9NADeyfLhmXxQwRAsXdAJ9ZH4It71cQTM1RAOekt4Yo4LC5NgCeOOce /0MvcStxUa459lRpNWLQvfg= =HEaZ -END PGP SIGNATURE- - 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 mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
heavy load configuration
I have a Linux box running mysql and apache, and we are expecting quite a load on the 1st of June. We have an application form whose data will be written into a db on the same server and I would like to know what I can do to make sure things go smooth. The machine is an AMD 500, with 320MB RAM, 256MB SWAP a 20GB IDE with the OS on and a 18,2GB SCSI 1rpm for backup and location of /var/lib/mysql in order to facilitate high speed writes of the tables to disk. How will I know if my server is up to it, or should I rather say, how many consecutive users will this box be able to handle? The line shouldn't be a problem, I think it sits on a couple of 100 MB/s line. Thanks - 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: heavy load configuration
You should get an identical 20gig IDE drive to 'mirror' the O/S partitions, and since you have a SCSI setup for the data areas, you should get at least 2 more of those, and 'stripe' the data across them (or at least get a second drive to 'mirror' those partitions as well).Though 'mirroring' will only give you a performance boost (up to 2 times) for 'reads', it will protect against a full failure (especially if you ARE going to be that busy) if one of the drives fails... A 3+ drive 'stripe' will give you a great 'write' performance increase, but won't protect the data against drive failure. (if you have a lot of money to spend, get 5 more drives, and stripe the data across 3 of them, and 'mirror' them to the other 3 drives... If you have a REAL lot of money to spend, put the 2nd 3 SCSI drives on a different SCSI controller) - Original Message - From: P.Agenbag [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, May 26, 2001 9:34 AM Subject: heavy load configuration I have a Linux box running mysql and apache, and we are expecting quite a load on the 1st of June. We have an application form whose data will be written into a db on the same server and I would like to know what I can do to make sure things go smooth. The machine is an AMD 500, with 320MB RAM, 256MB SWAP a 20GB IDE with the OS on and a 18,2GB SCSI 1rpm for backup and location of /var/lib/mysql in order to facilitate high speed writes of the tables to disk. How will I know if my server is up to it, or should I rather say, how many consecutive users will this box be able to handle? The line shouldn't be a problem, I think it sits on a couple of 100 MB/s line. Thanks - 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: heavy load configuration
At 06:34 PM 5/26/2001 +0200, P.Agenbag wrote: How will I know if my server is up to it, or should I rather say, how many consecutive users will this box be able to handle? The line shouldn't be a problem, I think it sits on a couple of 100 MB/s line. Well.. this is one topic I sorta specialize in. Off the bat you look like you have a machine that should be able to handle quite a load without barfing. The two areas I'd be interested in getting more metrics on would be your RAM utilization and a profile of your I/O to and from the disks where you have your tablespace. Without those metrics any advise I would give you is just crystal gazing. In the area of RAM I normally put as much RAM in a machine that can until it is can't take any more if I am running a database that is expected to deal with large queries or lots of small ones. Again, without some sort of metric to work with in terms of what your queries look like and what you actually mean by high traffic. Disk drives I like to put on some sort of RAID when I am thinking of either high throughput or a need for reliablity. Hot swap drives in a hardware raid box are the best way to go. IMHO and AFAIR Raid-5 is to be avoided for databases with a high degree of read-modify-write transactions built into the application or even just high write. With RAID-5 you pay a write penalty in terms of performance because of the fact that parity calculations take some overhead. The exception to this rule is where you have large caches front ending your RAID box. Even here there is cause for concern in the reliability arena as there have been known to be problems with data getting corrupted in a database when cache was not properly destaged after a write. I could on forever on this subject, but I'll stop here. -+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+ Peter L. Berghold[EMAIL PROTECTED] Schooner Technology ConsultingCELL: (732) 539-7920 Unix Professional Services: Sun/Solaris, Perl, Perl/CGI, mod_perl - 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
ARCHIVING UNDER HEAVY LOAD
Hello! There's a need to design a system (something like online forum) that will be storing huge amount of small messages in database. There must be fast access only to messages for the last 3 days (guess this means that main table with indexes on it must be kept small?), while other messages can be stored in rarely accessed archive (different table?). What's the best practice for implementing archiving in MySQL? In Oracle there's an ability to "switch off" part of the table and then move it to archive in background mode. What about MySQL? Thanks in advance, Alex P.S. This is the second time I post this question, sorry, problems with mail account prevented me from reading the replies. The only one I got was abount using MERGE tables which are still beta. - 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: ARCHIVING UNDER HEAVY LOAD
On Mon, Mar 19, 2001 at 04:06:29PM +0300, Anatoly Chubais wrote: Hello! There's a need to design a system (something like online forum) that will be storing huge amount of small messages in database. There must be fast access only to messages for the last 3 days (guess this means that main table with indexes on it must be kept small?), while other messages can be stored in rarely accessed archive (different table?). What's the best practice for implementing archiving in MySQL? In Oracle there's an ability to "switch off" part of the table and then move it to archive in background mode. What about MySQL? See my message to Nathan with: Message-ID: [EMAIL PROTECTED] and the related thread for the discussion of a similar topic. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 328-7878Fax: (408) 530-5454 Cell: (408) 439-9951 - 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
Heavy Load
hey, i'm running serveral mysql servers on powerful machines - dual processor, 2 GB memory, etc... Most of the databases store temporary data only. I need the maximum performance from these servers and have a lot of connections to the database. My problem is this - whenever the number of queries sent to the database increases, or the CPU usage increases under heavy load, mysql crashes. This has been a consistent behaviour. The system is under testing phase and the servers just give up under a reasonably heavy load. This is the my.cnf file - [mysqld] skip-locking set-variable= key_buffer=640M set-variable= max_allowed_packet=10M set-variable= table_cache=640 set-variable= sort_buffer=6M set-variable= record_buffer=6M set-variable= thread_cache=16 set-variable= thread_concurrency=16 set-variable= myisam_sort_buffer_size=64M log-bin server-id = 1 set-variable= max_connections=2000 set-variable= max_connect_errors=1 set-variable= back_log=2900 set-variable= connect_timeout=15 set-variable= wait_timeout=57600 set-variable= interactive_timeout=57600 [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash [isamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M 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=2M set-variable= write_buffer=2M According to me, the database should become slow under heavy load - it should not just crash. The backtrace is also incomplete. And it is consistent for every crash. Any ideas why this could be happening? I'm using PHP to access the database on RH 6.2 with kernel 2.4.2. I'm using latest versions of everything. Thanks, Vinod Do You Yahoo!? Get your free @yahoo.co.in address at http://mail.yahoo.co.in - 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
ARCHIVING UNDER HEAVY LOAD
Hello! There's a need to design a system (something like online forum) that will be storing huge amount of small messages in database. There must be fast access only to messages for the last 3 days (guess this means that main table with indexes on it must be kept small?), while other messages can be stored in rarely accessed archive (different table?). What's the best practice for implementing archiving in MySQL? In Oracle there's an ability to "switch off" part of the table and then move it to archive in background mode. What about MySQL? Thanks in advance, Alex - 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
ARCHIVING UNDER HEAVY LOAD
Hello! There's a need to design a system (something like online forum) that will be storing huge amount of small messages in database. There must be fast access only to messages for the last 3 days (guess this means that main table with indexes on it must be kept small?), while other messages can be stored in rarely accessed archive (different table?). What's the best practice for implementing archiving in MySQL? In Oracle there's an ability to "switch off" part of the table and then move it to archive in background mode. What about MySQL? Thanks in advance, Alex - 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: ARCHIVING UNDER HEAVY LOAD
On Fri, Mar 16, 2001 at 12:17:41PM +0300, wrote: Hello! There's a need to design a system (something like online forum) that will be storing huge amount of small messages in database. There must be fast access only to messages for the last 3 days (guess this means that main table with indexes on it must be kept small?), while other messages can be stored in rarely accessed archive (different table?). What's the best practice for implementing archiving in MySQL? In Oracle there's an ability to "switch off" part of the table and then move it to archive in background mode. What about MySQL? Take a look at MERGE tables in the manual. With some Perl or other scripting, you can create a table for each day which you use to INSERT new messages. Create a MERGE table on the tables for the last three days which you use for your SELECTs. You can keep a separate MERGE table on the older tables. You might want to combine some per-day tables into a larger (per week or month) table before creating a MERGE table on them, because MySQL will have less tables to open then, which makes it faster. You also may want to optimize the older tables to speed things up. Regards, Fred. -- Fred van Engen XO Communications B.V. email: [EMAIL PROTECTED] Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands - 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
