Re: monitoring mysql performance
Am 02.11.2017 um 20:09 schrieb Miguel González: I have a VPS Web server (Apache 2.4/PHP 7.x + Varnish 4.1) running with MySQL 5.6. I have 20 Gb of RAM. I serve Wordpress sites mainly all using innodb. So I´m thinking of ways of improving MySQL performance and of course for that, you need to measure. Currently I´m checking, there are no slow queries and read and writes perform well. I have enabled slow queries logging but the truth is that most queries logged are not slow, they are just slow when the web server load is too high, so when you try to check those queries at other time, there isn´t anything wrong with them so what evidence do you have that it's the database server at all? mysqltuner would be a good start for config hints in that case but mostly the webserver load is high because wordpress, joomla and all that crap is terrible inefficient even with PHP7 and opcache enabled and so you need caching in the application - not query results, whole content parts like navigation and so on - parts of the page which don#t change every time and are shared between different pages -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
monitoring mysql performance
Hi, I have a VPS Web server (Apache 2.4/PHP 7.x + Varnish 4.1) running with MySQL 5.6. I have 20 Gb of RAM. I serve Wordpress sites mainly all using innodb. So I´m thinking of ways of improving MySQL performance and of course for that, you need to measure. Currently I´m checking, there are no slow queries and read and writes perform well. I have enabled slow queries logging but the truth is that most queries logged are not slow, they are just slow when the web server load is too high, so when you try to check those queries at other time, there isn´t anything wrong with them. So I migrated from MySQL 5.5 to 5.6 and enabled query cache. I´ve heard you can use memcached for caching reads and writes but not sure about this. So I started to look into graphical open source tools to measure my server (mostly MySQL) performance before doing any change. Sincerely, I´m overwhelmed on the possibilities, kibana, grafana, etc and I don´t know which one to choose. For starters I was thinking of setting up a virtual machine on my laptop and configure one of those and get the data from the server (not sure if this is feasible without loosing information when the VM is not running). What do you guys recommend me? Thanks! Miguel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
ANN: MySQL Performance Monitoring
Greetings MySQL users -- If MySQL monitoring is something you are doing with one specific tool -- or not at all -- then you might find this blog post on MySQL Performance Monitoring in SPM worth a read: http://wp.me/pwdA7-Xo We frequently hear from organizations across industries who tackle performance monitoring with a mish-mash of different monitoring and alerting tools cobbled together in an uneasy coexistence that is often far from seamless. Think Ganglia+Nagios. A single tool like SPM takes all that hassle away and makes it easy and comprehensive in one step. Here's more info on what SPM monitors: http://sematext.com/spm/index.html Happy monitoring! Mick Emmett Sematext Group, Inc. *http://sematext.com/ http://sematext.com/*
Re: sync_binlog=0 affects MySQL performance but sync_binlog=1 works well
Am 29.11.2012 03:29, schrieb Dehua Yang: Finally , when the sync_binlog=0 , the commit statement disappear in the slow.log in the subject you say exactly the opposite and if something disappears in the slow.log it is good - so how should we help? signature.asc Description: OpenPGP digital signature
Odd MySQL performance behaviour
[Process:] Importing delimited text files from a Windows based server to a MySQL 5.1.41 instance (multiple databases) on a single Ubuntu 10.04.2 host. The process is initiated on the Windows server via the MySQL exe using ‘load data local infile’. There are 20 databases total and we import 15 files per database – 1 file per table. All tables use the MyISAM engine. Prior to each import, we truncate the destination tables. All processing is done sequentially. [Issues:] The process will run fine for about 2 weeks then continues to increase significantly in overall processing time. For example, we’ll see a 35 minute run-time for 2 weeks, then the next day its 55 minutes, then 80 minutes, etc, then without any changes, it will drop back to 35 after about a week (length of times vary.) It does not seem to be the result of any other processing requirements as the server is basically dedicated to MySQL only and there are no manually added CRON tasks. Even the truncate table steps appear to be affected. An comparison of ‘show status’ on a normal day vs a slow day does not appear to show any major issues other than a high volume of aborted_connects but this variable seems to increase regularly, outside of the actual import processing window. Not sure if this is some sort of MySQL resource buildup or something related to the OS. Any input would be greatly appreciated.
Re: Odd MySQL performance behaviour
maybe its is obvoius but did you look at the statistics ? did you try optimize table ? re, wh Am 18.07.2011 18:40, schrieb A F: [Process:] Importing delimited text files from a Windows based server to a MySQL 5.1.41 instance (multiple databases) on a single Ubuntu 10.04.2 host. The process is initiated on the Windows server via the MySQL exe using ‘load data local infile’. There are 20 databases total and we import 15 files per database – 1 file per table. All tables use the MyISAM engine. Prior to each import, we truncate the destination tables. All processing is done sequentially. [Issues:] The process will run fine for about 2 weeks then continues to increase significantly in overall processing time. For example, we’ll see a 35 minute run-time for 2 weeks, then the next day its 55 minutes, then 80 minutes, etc, then without any changes, it will drop back to 35 after about a week (length of times vary.) It does not seem to be the result of any other processing requirements as the server is basically dedicated to MySQL only and there are no manually added CRON tasks. Even the truncate table steps appear to be affected. An comparison of ‘show status’ on a normal day vs a slow day does not appear to show any major issues other than a high volume of aborted_connects but this variable seems to increase regularly, outside of the actual import processing window. Not sure if this is some sort of MySQL resource buildup or something related to the OS. Any input would be greatly appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
If this is a dedicated MySQL box, i would increase the InnoDB buffer pool setting in my.cnf to at least 8GB, 12 would be even better (you did say you have 16GB of ram in the machine I believe). Also, what is the output of: show status like '%tmp%'; JW On Wed, Sep 22, 2010 at 8:01 PM, Vokern vok...@gmail.com wrote: 2010/9/23 Johnny Withers joh...@pixelated.net Can you show us the output of: show status like '%innodb%' JW Sure. mysql show status like '%innodb%'; +---++ | Variable_name | Value | +---++ | Innodb_buffer_pool_pages_data | 262143 | | Innodb_buffer_pool_pages_dirty| 7219 | | Innodb_buffer_pool_pages_flushed | 376090524 | | Innodb_buffer_pool_pages_free | 0 | | Innodb_buffer_pool_pages_misc | 1 | | Innodb_buffer_pool_pages_total| 262144 | | Innodb_buffer_pool_read_ahead_rnd | 385466 | | Innodb_buffer_pool_read_ahead_seq | 1304599| | Innodb_buffer_pool_read_requests | 19253892075| | Innodb_buffer_pool_reads | 142749467 | | Innodb_buffer_pool_wait_free | 0 | | Innodb_buffer_pool_write_requests | 3491971805 | | Innodb_data_fsyncs| 32809939 | | Innodb_data_pending_fsyncs| 0 | | Innodb_data_pending_reads | 0 | | Innodb_data_pending_writes| 0 | | Innodb_data_read | 4013196644352 | | Innodb_data_reads | 147753642 | | Innodb_data_writes| 440467519 | | Innodb_data_written | 12643997136896 | | Innodb_dblwr_pages_written| 376090524 | | Innodb_dblwr_writes | 5464581| | Innodb_log_waits | 6599 | | Innodb_log_write_requests | 490350909 | | Innodb_log_writes | 201315186 | | Innodb_os_log_fsyncs | 13605257 | | Innodb_os_log_pending_fsyncs | 0 | | Innodb_os_log_pending_writes | 0 | | Innodb_os_log_written | 319623115776 | | Innodb_page_size | 16384 | | Innodb_pages_created | 6050545| | Innodb_pages_read | 244945432 | | Innodb_pages_written | 376090524 | | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 594325 | | Innodb_row_lock_time_avg | 154| | Innodb_row_lock_time_max | 27414 | | Innodb_row_lock_waits | 3857 | | Innodb_rows_deleted | 2170086| | Innodb_rows_inserted | 550876090 | | Innodb_rows_read | 15529216710| | Innodb_rows_updated | 142880071 | +---++ 42 rows in set (0.00 sec) -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: document for mysql performance improvement
Hi, If its an IO problem the first and easiest thing to do is (probably) look at your disk subsystem. You can easily achieve higher disk IO by increasing the number of disks and implementing something like RAID1+0. Or you can be logical about it and try to determine whether the IO performance is a symptom or a cause. If there are queries that don't have good indexes, add correct indexes is a smarter solution than add disks. Indeed, even the IO usage can be a red herring. I suggest a more systematic approach to the problem, such as Method R. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
Can you show us the output of: show status like '%innodb%' JW On Tue, Sep 21, 2010 at 10:11 PM, vokern vok...@gmail.com wrote: And this is the innodb file size, does this matter for performance? $ du -h ibdata* 11G ibdata1 11G ibdata2 11G ibdata3 59G ibdata4 2010/9/22 vokern vok...@gmail.com: This is piece of the setting in my.cnf: set-variable = innodb_buffer_pool_size=4G set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_flush_log_at_trx_commit=2 set-variable = innodb_data_file_path=ibdata1:10G;ibdata2:10G;ibdata3:10G;ibdata4:10G:autoextend key_buffer = 1024M sort_buffer = 1M read_buffer = 1M max_allowed_packet = 1M thread_stack= 192K thread_cache_size = 8 max_heap_table_size = 64M myisam-recover = BACKUP max_connections= 800 query_cache_limit = 1M query_cache_size= 16M the disk: # fdisk -l Disk /dev/sda: 598.0 GB, 597998698496 bytes 255 heads, 63 sectors/track, 72702 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes Disk identifier: 0x0004158f from iostat -x: Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.79 309.57 31.06 50.98 1306.74 2860.71 50.80 0.293.59 0.97 7.93 dm-0 0.00 0.000.560.42 4.49 3.40 8.00 0.33 338.96 1.14 0.11 The db is still slow. Thanks for the future helps. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: document for mysql performance improvement
2010/9/23 Johnny Withers joh...@pixelated.net Can you show us the output of: show status like '%innodb%' JW Sure. mysql show status like '%innodb%'; +---++ | Variable_name | Value | +---++ | Innodb_buffer_pool_pages_data | 262143 | | Innodb_buffer_pool_pages_dirty| 7219 | | Innodb_buffer_pool_pages_flushed | 376090524 | | Innodb_buffer_pool_pages_free | 0 | | Innodb_buffer_pool_pages_misc | 1 | | Innodb_buffer_pool_pages_total| 262144 | | Innodb_buffer_pool_read_ahead_rnd | 385466 | | Innodb_buffer_pool_read_ahead_seq | 1304599| | Innodb_buffer_pool_read_requests | 19253892075| | Innodb_buffer_pool_reads | 142749467 | | Innodb_buffer_pool_wait_free | 0 | | Innodb_buffer_pool_write_requests | 3491971805 | | Innodb_data_fsyncs| 32809939 | | Innodb_data_pending_fsyncs| 0 | | Innodb_data_pending_reads | 0 | | Innodb_data_pending_writes| 0 | | Innodb_data_read | 4013196644352 | | Innodb_data_reads | 147753642 | | Innodb_data_writes| 440467519 | | Innodb_data_written | 12643997136896 | | Innodb_dblwr_pages_written| 376090524 | | Innodb_dblwr_writes | 5464581| | Innodb_log_waits | 6599 | | Innodb_log_write_requests | 490350909 | | Innodb_log_writes | 201315186 | | Innodb_os_log_fsyncs | 13605257 | | Innodb_os_log_pending_fsyncs | 0 | | Innodb_os_log_pending_writes | 0 | | Innodb_os_log_written | 319623115776 | | Innodb_page_size | 16384 | | Innodb_pages_created | 6050545| | Innodb_pages_read | 244945432 | | Innodb_pages_written | 376090524 | | Innodb_row_lock_current_waits | 0 | | Innodb_row_lock_time | 594325 | | Innodb_row_lock_time_avg | 154| | Innodb_row_lock_time_max | 27414 | | Innodb_row_lock_waits | 3857 | | Innodb_rows_deleted | 2170086| | Innodb_rows_inserted | 550876090 | | Innodb_rows_read | 15529216710| | Innodb_rows_updated | 142880071 | +---++ 42 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
document for mysql performance improvement
Hello, We are using mysql-5.1 with innodb engine for a web 2.0 application. But we found that the performance is not that good, i.e, the IO load sometime is high, the query is timeout. We run ubuntu server Linux, with apt-get for installing mysql. So is there any good document for improving mysql performance? Thanks. Regards. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
Yes we have changed some arguments in my.cnf like key_buffer_size, sort_buffer_size etc. 2010/9/21 Machiel Richards machiel.richa...@gmail.com: Good day There is quite a lot of documentation available for MySQL performance management. However, a quick question on this matter. Have you changed any of the default buffer and cache sizes as yet? Regards Machiel -Original Message- From: vokern vok...@gmail.com To: mysql@lists.mysql.com Subject: document for mysql performance improvement Date: Tue, 21 Sep 2010 20:37:49 +0800 Hello, We are using mysql-5.1 with innodb engine for a web 2.0 application. But we found that the performance is not that good, i.e, the IO load sometime is high, the query is timeout. We run ubuntu server Linux, with apt-get for installing mysql. So is there any good document for improving mysql performance? Thanks. Regards. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
Yep. There's rather extensive documentation on http://www.mysql.com. You'll need to read it and compare to the metrics you're taking off your own server, draw conclusions and apply them to your setup. You *are* pulling metrics, aren't you, and not hoping for some magic wand to make it all happen ? On Tue, Sep 21, 2010 at 2:37 PM, vokern vok...@gmail.com wrote: Hello, We are using mysql-5.1 with innodb engine for a web 2.0 application. But we found that the performance is not that good, i.e, the IO load sometime is high, the query is timeout. We run ubuntu server Linux, with apt-get for installing mysql. So is there any good document for improving mysql performance? Thanks. Regards. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: document for mysql performance improvement
If its an IO problem the first and easiest thing to do is (probably) look at your disk subsystem. You can easily achieve higher disk IO by increasing the number of disks and implementing something like RAID1+0. What is your current disk configuration? Andy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
2010/9/21 a.sm...@ukgrid.net: If its an IO problem the first and easiest thing to do is (probably) look at your disk subsystem. You can easily achieve higher disk IO by increasing the number of disks and implementing something like RAID1+0. What is your current disk configuration? The disk is exactly Raid10. The CPU is two 2.5G*4, totally 16G memory. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: document for mysql performance improvement
I find this quite good http://www.mysqlperformanceblog.com/ Send your my.cnf and maybe we could look at it and pick anything that would help. Jangita | +254 76 918383 | MSN Y!: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -Original Message- From: vokern [mailto:vok...@gmail.com] Sent: 21 September 2010 2:38 PM To: mysql@lists.mysql.com Subject: document for mysql performance improvement Hello, We are using mysql-5.1 with innodb engine for a web 2.0 application. But we found that the performance is not that good, i.e, the IO load sometime is high, the query is timeout. We run ubuntu server Linux, with apt-get for installing mysql. So is there any good document for improving mysql performance? Thanks. Regards. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jang...@jangita.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
Quoting vokern vok...@gmail.com: The disk is exactly Raid10. The CPU is two 2.5G*4, totally 16G memory. And how many disks do you have, and what type (SATA/SAS/FC etc) what RPM? To improve IO you can add more disks, or upgrade to faster disks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
Thank you all for the kind helps. I will check them and if still have problems I will come back. 2010/9/21 Machiel Richards machiel.richa...@gmail.com: http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/ http://www.mysql.com/why-mysql/performance/ http://www.debianhelp.co.uk/mysqlperformance.htm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
2010/9/21 a.sm...@ukgrid.net: Quoting vokern vok...@gmail.com: The disk is exactly Raid10. The CPU is two 2.5G*4, totally 16G memory. And how many disks do you have, and what type (SATA/SAS/FC etc) what RPM? To improve IO you can add more disks, or upgrade to faster disks. Two disks with SAS driver, 15K rpm. BTW, we are running a TTServer before mysql for caching the query, is this better for performance? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
Quoting vokern vok...@gmail.com: Two disks with SAS driver, 15K rpm. Ok so you have fast disks, but with only 2 disks it is normal you will be quite restricted by DISK IO. By adding more in multiples of 2 and stripping across all you achieve RAID1+0 and higher max IO... Andy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
The disk is exactly Raid10. The CPU is two 2.5G*4, totally 16G memory. Two disks with SAS driver, 15K rpm. RAID-10 with 2 disks? or do you mean RAID0 or RAID1??? Can't see how you would get RAID10, minimum of 6 disks for that, no? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
Mysql tuner is a very useful tool to pull metrics http://blog.mysqltuner.com/ On 09/21/2010 05:48 AM, Jangita wrote: I find this quite good http://www.mysqlperformanceblog.com/ Send your my.cnf and maybe we could look at it and pick anything that would help. Jangita | +254 76 918383 | MSN Y!: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -Original Message- From: vokern [mailto:vok...@gmail.com] Sent: 21 September 2010 2:38 PM To: mysql@lists.mysql.com Subject: document for mysql performance improvement Hello, We are using mysql-5.1 with innodb engine for a web 2.0 application. But we found that the performance is not that good, i.e, the IO load sometime is high, the query is timeout. We run ubuntu server Linux, with apt-get for installing mysql. So is there any good document for improving mysql performance? Thanks. Regards. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
Quoting Johan De Meersman vegiv...@tuxera.be: Your raid controller is lying to you - you can't have RAID10 with just two disks :-p Don't worry about that, though - it's a good enough config. Good enough? If he is genuinely saturating the disk with IO (as he states the problem is IO) then it isnt good enough. But perhaps that needs a bit more analysis before we really know... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
On Tue, Sep 21, 2010 at 4:03 PM, a.sm...@ukgrid.net wrote: Quoting Johan De Meersman vegiv...@tuxera.be: Your raid controller is lying to you - you can't have RAID10 with just two disks :-p Don't worry about that, though - it's a good enough config. Good enough? If he is genuinely saturating the disk with IO (as he states the problem is IO) then it isnt good enough. But perhaps that needs a bit more analysis before we really know... It takes quite some doing to fill a 15krpm RAID-1 with IO, all the way through all the controller, OS and SQL caches. A lot more investigating is needed before concluding that the symptom is indeed the cause. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: document for mysql performance improvement
Also, mailing list doesn't want to distribute attachments :-) Here's a link to the metrics view I was on about earlier: http://www.tuxera.be/mysqlstats.zip On Tue, Sep 21, 2010 at 4:08 PM, Johan De Meersman vegiv...@tuxera.bewrote: On Tue, Sep 21, 2010 at 4:03 PM, a.sm...@ukgrid.net wrote: Quoting Johan De Meersman vegiv...@tuxera.be: Your raid controller is lying to you - you can't have RAID10 with just two disks :-p Don't worry about that, though - it's a good enough config. Good enough? If he is genuinely saturating the disk with IO (as he states the problem is IO) then it isnt good enough. But perhaps that needs a bit more analysis before we really know... It takes quite some doing to fill a 15krpm RAID-1 with IO, all the way through all the controller, OS and SQL caches. A lot more investigating is needed before concluding that the symptom is indeed the cause. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: document for mysql performance improvement
Hi, Check your slow queries first. Large full scans can cause unwanted disk io. Do you use MyISAM or InnoDB? From your status, you seem to have intensive MyISAM locking. Peter On 09/21/2010 04:10 PM, Johan De Meersman wrote: Also, mailing list doesn't want to distribute attachments :-) Here's a link to the metrics view I was on about earlier: http://www.tuxera.be/mysqlstats.zip On Tue, Sep 21, 2010 at 4:08 PM, Johan De Meersmanvegiv...@tuxera.bewrote: On Tue, Sep 21, 2010 at 4:03 PM,a.sm...@ukgrid.net wrote: Quoting Johan De Meersmanvegiv...@tuxera.be: Your raid controller is lying to you - you can't have RAID10 with just two disks :-p Don't worry about that, though - it's a good enough config. Good enough? If he is genuinely saturating the disk with IO (as he states the problem is IO) then it isnt good enough. But perhaps that needs a bit more analysis before we really know... It takes quite some doing to fill a 15krpm RAID-1 with IO, all the way through all the controller, OS and SQL caches. A lot more investigating is needed before concluding that the symptom is indeed the cause. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
This is piece of the setting in my.cnf: set-variable = innodb_buffer_pool_size=4G set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_flush_log_at_trx_commit=2 set-variable = innodb_data_file_path=ibdata1:10G;ibdata2:10G;ibdata3:10G;ibdata4:10G:autoextend key_buffer = 1024M sort_buffer = 1M read_buffer = 1M max_allowed_packet = 1M thread_stack= 192K thread_cache_size = 8 max_heap_table_size = 64M myisam-recover = BACKUP max_connections= 800 query_cache_limit = 1M query_cache_size= 16M the disk: # fdisk -l Disk /dev/sda: 598.0 GB, 597998698496 bytes 255 heads, 63 sectors/track, 72702 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes Disk identifier: 0x0004158f from iostat -x: Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.79 309.57 31.06 50.98 1306.74 2860.71 50.80 0.293.59 0.97 7.93 dm-0 0.00 0.000.560.42 4.49 3.40 8.00 0.33 338.96 1.14 0.11 The db is still slow. Thanks for the future helps. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: document for mysql performance improvement
And this is the innodb file size, does this matter for performance? $ du -h ibdata* 11G ibdata1 11G ibdata2 11G ibdata3 59G ibdata4 2010/9/22 vokern vok...@gmail.com: This is piece of the setting in my.cnf: set-variable = innodb_buffer_pool_size=4G set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_flush_log_at_trx_commit=2 set-variable = innodb_data_file_path=ibdata1:10G;ibdata2:10G;ibdata3:10G;ibdata4:10G:autoextend key_buffer = 1024M sort_buffer = 1M read_buffer = 1M max_allowed_packet = 1M thread_stack = 192K thread_cache_size = 8 max_heap_table_size = 64M myisam-recover = BACKUP max_connections = 800 query_cache_limit = 1M query_cache_size = 16M the disk: # fdisk -l Disk /dev/sda: 598.0 GB, 597998698496 bytes 255 heads, 63 sectors/track, 72702 cylinders Units = cylinders of 16065 * 512 = 8225280 bytes Disk identifier: 0x0004158f from iostat -x: Device: rrqm/s wrqm/s r/s w/s rsec/s wsec/s avgrq-sz avgqu-sz await svctm %util sda 0.79 309.57 31.06 50.98 1306.74 2860.71 50.80 0.29 3.59 0.97 7.93 dm-0 0.00 0.00 0.56 0.42 4.49 3.40 8.00 0.33 338.96 1.14 0.11 The db is still slow. Thanks for the future helps. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL Performance with large data
Hi, I am using MySQL 5.0.45 in production environment. One of my tables (using MyISAM Engine) is expected to have around 4 billion records and each record will have 1867 bytes of data. All fields in this table are of character data type. I have 8 GB RAM on the server, RAID 5 with 750 GB storage space available and quad core processor. My question is whether MySQL will be able to handle queries on this amount of data? What all things I need to consider here? Thank you.
Re: MySQL Performance with large data
The amount and type of data is less the issue than the amount and type of queries is :-) The machine you've described should be able to handle quite a bit of load, though, if well-tuned. On Tue, Nov 24, 2009 at 4:45 PM, Manish Ranjan (Stigasoft) manish.ran...@stigasoft.com wrote: Hi, I am using MySQL 5.0.45 in production environment. One of my tables (using MyISAM Engine) is expected to have around 4 billion records and each record will have 1867 bytes of data. All fields in this table are of character data type. I have 8 GB RAM on the server, RAID 5 with 750 GB storage space available and quad core processor. My question is whether MySQL will be able to handle queries on this amount of data? What all things I need to consider here? Thank you.
RE: MySQL Performance with large data
Thank you Johan. The table will be read only. There will be two steps - first to get the count using search conditions and then to get data from some columns based on those search conditions. The fields will be indexed as per search requirements. _ From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De Meersman Sent: Tuesday, November 24, 2009 9:56 PM To: Manish Ranjan (Stigasoft) Cc: mysql@lists.mysql.com Subject: Re: MySQL Performance with large data The amount and type of data is less the issue than the amount and type of queries is :-) The machine you've described should be able to handle quite a bit of load, though, if well-tuned. On Tue, Nov 24, 2009 at 4:45 PM, Manish Ranjan (Stigasoft) manish.ran...@stigasoft.com wrote: Hi, I am using MySQL 5.0.45 in production environment. One of my tables (using MyISAM Engine) is expected to have around 4 billion records and each record will have 1867 bytes of data. All fields in this table are of character data type. I have 8 GB RAM on the server, RAID 5 with 750 GB storage space available and quad core processor. My question is whether MySQL will be able to handle queries on this amount of data? What all things I need to consider here? Thank you.
Re: MySQL Performance with large data
First off, for 4.000.000.000 records at 1867 byte per record, you're gonna need more storage than that (over 1.6 terabyte if I did my maths right) , unless you're using compressed tables - then your requirements will strongly depend on the actual data: text may easily compress to a factor ten, images (blobs?) almost not. Compressed tables will also speed up your I/O, in exchange for some more CPU load. On such a dataset, table scans are going to be geologically slow, so yes, good indexes will be your saviour :-) For speed, I'd also recommend that you get a RAID-10 setup. Go for a maximum amount of spindles, too - some form of SAN or locally-attached storage boxes with (relatively) small-capacity high-rpm disks. On Tue, Nov 24, 2009 at 5:39 PM, Manish Ranjan (Stigasoft) manish.ran...@stigasoft.com wrote: Thank you Johan. The table will be read only. There will be two steps - first to get the count using search conditions and then to get data from some columns based on those search conditions. The fields will be indexed as per search requirements. _ From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De Meersman Sent: Tuesday, November 24, 2009 9:56 PM To: Manish Ranjan (Stigasoft) Cc: mysql@lists.mysql.com Subject: Re: MySQL Performance with large data The amount and type of data is less the issue than the amount and type of queries is :-) The machine you've described should be able to handle quite a bit of load, though, if well-tuned. On Tue, Nov 24, 2009 at 4:45 PM, Manish Ranjan (Stigasoft) manish.ran...@stigasoft.com wrote: Hi, I am using MySQL 5.0.45 in production environment. One of my tables (using MyISAM Engine) is expected to have around 4 billion records and each record will have 1867 bytes of data. All fields in this table are of character data type. I have 8 GB RAM on the server, RAID 5 with 750 GB storage space available and quad core processor. My question is whether MySQL will be able to handle queries on this amount of data? What all things I need to consider here? Thank you.
Re: MySQL Performance with large data
I second that RAID 10 with as many spindles as you can get recommendation.. for any kind of load, even read-only load, you are going to need it. Also, that 8G of RAM is paltry for the kind of dataset you propose. As already noted, the particulars will come down to the types and frequency of the queries (not to mention expected performance targets) but 4x64 CPUs churning that kind of data could really take advantage of a lot more RAM. - michael dykman On Tue, Nov 24, 2009 at 12:25 PM, Johan De Meersman vegiv...@tuxera.be wrote: First off, for 4.000.000.000 records at 1867 byte per record, you're gonna need more storage than that (over 1.6 terabyte if I did my maths right) , unless you're using compressed tables - then your requirements will strongly depend on the actual data: text may easily compress to a factor ten, images (blobs?) almost not. Compressed tables will also speed up your I/O, in exchange for some more CPU load. On such a dataset, table scans are going to be geologically slow, so yes, good indexes will be your saviour :-) For speed, I'd also recommend that you get a RAID-10 setup. Go for a maximum amount of spindles, too - some form of SAN or locally-attached storage boxes with (relatively) small-capacity high-rpm disks. On Tue, Nov 24, 2009 at 5:39 PM, Manish Ranjan (Stigasoft) manish.ran...@stigasoft.com wrote: Thank you Johan. The table will be read only. There will be two steps - first to get the count using search conditions and then to get data from some columns based on those search conditions. The fields will be indexed as per search requirements. _ From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De Meersman Sent: Tuesday, November 24, 2009 9:56 PM To: Manish Ranjan (Stigasoft) Cc: mysql@lists.mysql.com Subject: Re: MySQL Performance with large data The amount and type of data is less the issue than the amount and type of queries is :-) The machine you've described should be able to handle quite a bit of load, though, if well-tuned. On Tue, Nov 24, 2009 at 4:45 PM, Manish Ranjan (Stigasoft) manish.ran...@stigasoft.com wrote: Hi, I am using MySQL 5.0.45 in production environment. One of my tables (using MyISAM Engine) is expected to have around 4 billion records and each record will have 1867 bytes of data. All fields in this table are of character data type. I have 8 GB RAM on the server, RAID 5 with 750 GB storage space available and quad core processor. My question is whether MySQL will be able to handle queries on this amount of data? What all things I need to consider here? Thank you. -- - michael dykman - mdyk...@gmail.com May you live every day of your life. Jonathan Swift Larry's First Law of Language Redesign: Everyone wants the colon. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
mysql performance issue. pls help..
HI experts I am facing performance issue for last couple of months. it taking more time to execute query.. developers created tables on myIsam and Innodb. i have a doubts whether we can use both storage same time.. i have 2 thinks in my minds to check this performance. 1. to check innodb and myisam db memory pools. 2. to check any index are required. based on the above to points i plan to investigate. so please help I am not familiar with mysql and first time iam working with developers. can any help to fix the problem. please assist me what i have to check now?. Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com
Re: mysql performance issue. pls help..
hi all this is my innodb preference. i need to change anything for increasing the db performance.. innodb_additional_mem_pool_size -- 1048576 innodb_autoextend_increment -- 8 innodb_buffer_pool_awe_mem_mb -- 0 innodb_buffer_pool_size -- 8388608 innodb_checksums-- ON innodb_commit_concurrency -- 0 innodb_concurrency_tickets -- 500 innodb_data_file_path -- ibdata1:10M:autoextend innodb_data_home_dir-- innodb_doublewrite -- ON innodb_fast_shutdown-- 1 innodb_file_io_threads -- 4 innodb_file_per_table -- OFF innodb_flush_log_at_trx_commit -- 1 innodb_flush_method -- innodb_force_recovery -- 0 innodb_lock_wait_timeout-- 50 innodb_locks_unsafe_for_binlog -- OFF innodb_log_arch_dir -- innodb_log_archive -- OFF innodb_log_buffer_size -- 1048576 innodb_log_file_size-- 5242880 innodb_log_files_in_group -- 2 innodb_log_group_home_dir -- ./ innodb_max_dirty_pages_pct -- 90 innodb_max_purge_lag-- 0 innodb_mirrored_log_groups -- 1 innodb_open_files -- 300 innodb_support_xa -- ON innodb_sync_spin_loops -- 20 innodb_table_locks -- ON innodb_thread_concurrency -- 8 innodb_thread_sleep_delay -- 1 interactive_timeout -- 28800 thanks in advance.. Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com On Wed, Nov 18, 2009 at 11:50 AM, F.A.I.Z.A.L sac.fai...@gmail.com wrote: HI experts I am facing performance issue for last couple of months. it taking more time to execute query.. developers created tables on myIsam and Innodb. i have a doubts whether we can use both storage same time.. i have 2 thinks in my minds to check this performance. 1. to check innodb and myisam db memory pools. 2. to check any index are required. based on the above to points i plan to investigate. so please help I am not familiar with mysql and first time iam working with developers. can any help to fix the problem. please assist me what i have to check now?. Best Regards Faizal S GSM : 9840118673 Blog: http://oradbapro.blogspot.com
MySQL University session on February 5: MySQL Performance and Scalability Project - Issues and Opportunities
MySQL Performance and Scalability Project - Issues and Opportunities http://forge.mysql.com/wiki/MySQL_Performance_and_Scalability_Project_-_Issues_and_Opportunities Next Thursday (February 5th), we're continuing our series of sessions on MySQL performance measuring and improvements with Allan Packer's presentation titled MySQL Performance and Scalability Project - Issues and Opportunities. Allan works in the Performance and Applications Engineering department at Sun Microsystems, so again, expect to get some deep insights into the inner workings of the MySQL Server. Allan is based in Australia, so note that this session will take place in the morning (Europe) or evening (APAC), respectively. For MySQL University sessions, point your browser to this page: http://webmeeting.dimdim.com/portal/JoinForm.action?confKey=mysqluniversity You need a browser with a working Flash plugin. You may register for a Dimdim account, but you don't have to. MySQL University is a free educational online program for engineers/developers. MySQL University sessions are open to anyone, not just Sun employees. Sessions are recorded (slides and audio), so if you can't attend the live session you can look at the recording anytime after the session. Here's the schedule for the upcoming weeks (see http://forge.mysql.com/wiki/MySQL_University for a better format of this list): February 5, 200908:00 UTC / 8:00 GMT / 9:00 CET / 11:00 MDT (Moscow) / 13:30 IST (India) / 16:00 CST (Beijing) / 17:00 JST (Tokyo) / 19:00 EDT (Melbourne) MySQL Performance and Scalability Project - Issues and Opportunities Allan Packer February 12, 2008 14:00 UTC / 8am CST (Central) / 9am EST (Eastern) / 14:00 GMT / 15:00 CET / 17:00 MDT (Moscow) Using DTrace with MySQL MC Brown February 19, 2009 14:00 UTC / 8am CST (Central) / 9am EST (Eastern) / 14:00 GMT / 15:00 CET / 17:00 MDT (Moscow) Developing MySQL on Solaris MC Brown Trond Norbye February 26, 2009 14:00 UTC / 8am CST (Central) / 9am EST (Eastern) / 14:00 GMT / 15:00 CET / 17:00 MDT (Moscow) Backing up MySQL using file system snapshotsLenz Grimmer March 5, 2009 14:00 UTC / 8am CST (Central) / 9am EST (Eastern) / 14:00 GMT / 15:00 CET / 17:00 MDT (Moscow)Good Coding Style Konstantin Osipov March 12, 2009 14:00 UTC / 8am CST (Central) / 9am EST (Eastern) / 14:00 GMT / 15:00 CET / 17:00 MDT (Moscow) MySQL and ZFS MC Brown The session address (Dimdim URL) for all sessions is: http://webmeeting.dimdim.com/portal/JoinForm.action?confKey=mysqluniversity Please bookmark this address, since it will remain valid for all future MySQL University sessions. Remember, though, that the meeting room will open only 15 minutes before the session starts. Dimdim is the conferencing system we're using for MySQL University sessions. It provides integrated voice streaming, chat, whiteboard, session recording (slides and voice), and more. All you need to do to attend MySQL University sessions is point your browser to the address given above. All MySQL University sessions are recorded, that is, slides and voice can be viewed as a Flash file (.flv). You can find those recordings on the respective MySQL University session pages which are listed on the MySQL University home page: http://forge.mysql.com/wiki/MySQL_University Cheers, Stefan -- *** Sun Microsystems GmbHStefan Hinz Sonnenallee 1Manager Documentation, Database Group 85551 Kirchheim-Heimstetten Phone: +49-30-82702940 Germany Fax: +49-30-82702941 http://www.sun.de/mysql mailto: stefan.h...@sun.com Amtsgericht Muenchen: HRB161028 Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering *** -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Improve MYSQL performance on large database
Hello, Here, you can get some help: http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/ Regards. Salah NAIT-MOULOUD www.echovox.com | www.m-boost.com On Sat, Nov 8, 2008 at 7:08 AM, Guillermo Nardoni [EMAIL PROTECTED]wrote: Hello everyone, good evening. This is my first time posting here and it is just for asking...!!! Well, we are building a project for our customer wich is basically a searcher, google style. We use mnoGoSearch as indexer, 3.2.27-mysql, to more specific.!. We already try lots of version even compiling from source to discard indexer as a problem. The database, wich is about 20 gb and increasing!!!, is distributed among 5 server, 4 of 5 has SATA disks, 2 gb RAM, the 5th, is 512 mb ram and 80 gb SCSI 3 disk with adaptec 2940U as host scsi adapter. Well, after lots of testing, if you enter on the site, aka: www.lomejordetodo.com.ar, and type a text to search to. it takes about 5 minutues if query has lots of result, like linux kernel query. We read lots of posting, messages and googling but we couldn't solve the problem at all!. If anyone here has o had the same problem I'l give lots of thanks if you could show me the path to investigate. Regards Guillermo Nardoni Rosario - Argentina. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Improve MYSQL performance on large database
Hello everyone, good evening. This is my first time posting here and it is just for asking...!!! Well, we are building a project for our customer wich is basically a searcher, google style. We use mnoGoSearch as indexer, 3.2.27-mysql, to more specific.!. We already try lots of version even compiling from source to discard indexer as a problem. The database, wich is about 20 gb and increasing!!!, is distributed among 5 server, 4 of 5 has SATA disks, 2 gb RAM, the 5th, is 512 mb ram and 80 gb SCSI 3 disk with adaptec 2940U as host scsi adapter. Well, after lots of testing, if you enter on the site, aka: www.lomejordetodo.com.ar, and type a text to search to. it takes about 5 minutues if query has lots of result, like linux kernel query. We read lots of posting, messages and googling but we couldn't solve the problem at all!. If anyone here has o had the same problem I'l give lots of thanks if you could show me the path to investigate. Regards Guillermo Nardoni Rosario - Argentina. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL performance
Hi, If you would like to improve the performance of your MySQL environment, you may consider the following information tobe of interest: The latest series of Dolphin Express drivers for Linux now also includes support for accelerated intra-node socket communications. Through the combination of socket loopback support and native capabilities in Dolphin's interconnect hardware, applications running on an SMP/multi core system will achieve up to 10x better latency and 6x better throughput for local communication vs standard Linux. These developments come on the heels of Dolphin's recent announcement of an independent MySQL benchmark which showed upwards of a 712% performance boost across a LAMP stack using Dolphin Express. While MySQL and Dolphin have for years demonstrated the performance benefits of Dolphin Express across clustered environments, this demonstrates how the combination of Dolphin Express' low latency software and interconnect hardware can mean significant improvements for most single-node applications as well. Dolphin is now calling out to the MySQL community to assist us in validating these performance improvements in real-world application environments. Dolphin invites users that are interested in improving the performance of their applications to contact us for an opportunity to test Dolphin Express. Dolphin will provide the software and hardware for selected users to conduct performance testing with their single- or multi-node MySQL or other application environments. Dolphin requires no obligations to buy, but solely for testers to report the performance results of their tests. Those who wish to keep their Dolphin Express products after our testing period will be eligible for a substantial partner discount. More information can be found at http://www.dolphinics.com/solutions/mysql.html Please e-mail me ([EMAIL PROTECTED]) to express your interest in participating in this test. Best regards Hugo = Hugo Kohmann | Dolphin Interconnect Solutions AS | E-mail: P.O. Box 150 Oppsal| [EMAIL PROTECTED] N-0619 Oslo, Norway| Web: Tel:+47 23 16 71 83| http://www.dolphinics.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql performance report
Guys my mysqlreport says : Buffer used 205.00k of 16.00M %Used: 1.25 Current 2.04M%Usage: 12.76 Write hit 100.00% Read hit 97.90% __ Questions ___ Total 5.08k16.6/s * Com_ 2.68k 8.8/s %Total: 52.74* QC Hits 1.84k 6.0/s 36.20 DMS 528 1.7/s 10.39 COM_QUIT 40 0.1/s0.79 -Unknown 6 0.0/s0.12 Slow (1)0 0/s0.00 %DMS: 0.00 Log: ON - the com_ is spinning and taking a lot of time/resource from my mysql (or seem like it does) Any idea what might be causing this or if this is bad? or just normal? This web-site is under development and not much hits coming in so low DMS makes senseright? -- Sulochan Acharya
Re: FreeBSD MySQL Performance Tunning suggestions???
Hi all ! VeeJay wrote: [[...]] At my job, I am going to build a Web Server with 1. FreeBSD 7.0-RELEASE amd64 2. Apache 2.2.8 3. PHP 4.4.8 (or may be PHP5, what do you suggest?) Server's hardware configuration is as follow: 2 x Quad Core Xeon E5450 3.0GHz,2x6MB,1333FSB 16GB (8x2GB Dual Rank DIMMs) 667MHz FBD 6 x 450GB SAS 15k 3.5 HD Hot Plug PERC 6/i, Integrated Controller Card x6 backplane PE2950 III C5 MSS R10 Add-in PERC 5/i / 6/i 1 S TCP/IP Offload Engine 2P Broadcom TCP/IP Offload Engine functionality (TOE) Not Enabled For FreeBSD 7.0-RELEASE amd64 Which MySQL 5.0 would be used ? 1. FreeBSD 7.x (x86_64) or 2. FreeBSD 6.x (x86) Your machine has 16 GB of RAM. If you ever want to use really large caches in the MySQL server process, you need to use a 64 bit binary, called x86_64 in the MySQL package file names. I have done some googling and made these configuration files for Apache and MySQL? Apache: httpd.conf-start [[... file snipped ...]] MySQL: my.cnfstart [[... file snipped ...]] Is there something you can tune? You cannot tune without knowing which bottleneck you should widen, and how much resources still are available. IMNSHO, you need to start with some configuration, put load onto it (as representative as possible), then watch the system's behavior (take measurements !), and only then determine which part you want to improve. Example: Database caches are good to reduce disk I/O and so to increase performance, but you will not increase caches if your system is already paging heavily (= your RAM is too small). There is no need to change any parameter unless a) its current setting restricts your performance, and you have sufficient resources to raise the limit, or b) its current setting allocates more resources than needed here, which could be used better at some other place. You need some initial run and measurement to check that. HTH, Jörg -- Joerg Bruehe, MySQL Build Team, [EMAIL PROTECTED] Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FreeBSD MySQL Performance Tunning suggestions???
Hi,
FreeBSD 7 should offer much better performance for MySQL. The FreeBSD
kernel developers have found ways to relieve some of the kernel
bottlenecks which permit multithreaded applications to operate much
better.
Regards,
Antony.
On 3 Jun 2008, at 03:43, VeeJay wrote:
Hi Guys
I need some performance tuning suggestions/help from you.
At my job, I am going to build a Web Server with
1. FreeBSD 7.0-RELEASE amd64
2. Apache 2.2.8
3. PHP 4.4.8 (or may be PHP5, what do you suggest?)
Server's hardware configuration is as follow:
2 x Quad Core Xeon E5450 3.0GHz,2x6MB,1333FSB
16GB (8x2GB Dual Rank DIMMs) 667MHz FBD
6 x 450GB SAS 15k 3.5 HD Hot Plug
PERC 6/i, Integrated Controller Card x6 backplane
PE2950 III C5 MSS R10 Add-in PERC 5/i / 6/i 1 S
TCP/IP Offload Engine 2P
Broadcom TCP/IP Offload Engine functionality (TOE) Not Enabled
For FreeBSD 7.0-RELEASE amd64 Which MySQL 5.0 would be used ?
1. FreeBSD 7.x (x86_64)
or
2. FreeBSD 6.x (x86)
I have done some googling and made these configuration files for
Apache and
MySQL?
Apache:
httpd.conf-
start
# =
# Basic settings
# =
ServerType standalone
ServerRoot /usr/local/apache
PidFile /usr/local/apache/logs/httpd.pid
ScoreBoardFile /usr/local/apache/logs/httpd.scoreboard
ResourceConfig /dev/null
AccessConfig /dev/null
# =
# Performance settings
# =
Timeout 300
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 5
MaxSpareServers 10
StartServers 5
MaxClients 256
MaxRequestsPerChild 0
# =
# Apache modules
# =
ClearModuleList
AddModule mod_log_config.c
AddModule mod_mime.c
AddModule mod_dir.c
AddModule mod_access.c
AddModule mod_auth.c
AddModule mod_php4.c
AddModule mod_rewrite.c
AddModule mod_security.c
AddModule mod_setenvif.c
# =
# General settings
# =
Port 80
User apache
Group apache
ServerAdmin [EMAIL PROTECTED]
UseCanonicalName Off
ServerSignature Off
HostnameLookups Off
ServerTokens Prod
IfModule mod_dir.c
DirectoryIndex index.html
/IfModule
DocumentRoot /home/apache/www
# =
# Access control
# =
Directory /
Options None
AllowOverride None
Order deny,allow
Deny from all
/Directory
Directory /home/apache/www
Order allow,deny
Allow from all
/Directory
Directory /home/apache/www/vhosts/mydomain.com/public_html
Order allow,deny
Allow from all
/Directory
# =
# MIME encoding
# =
IfModule mod_mime.c
TypesConfig /usr/local/apache/conf/mime.types
/IfModule
DefaultType text/plain
IfModule mod_mime.c
AddEncoding x-compress Z
AddEncoding x-gzip gz tgz
AddType application/x-tar .tgz
AddType application/x-httpd-php .html
/IfModule
# =
# Logs
# =
LogLevel warn
LogFormat %h %l %u %t \%r\ %s %b \%{Referer}i\ \%{User-Agent}i
\
combined
LogFormat %h %l %u %t \%r\ %s %b common
LogFormat %{Referer}i - %U referer
LogFormat %{User-agent}i agent
ErrorLog /var/apache/logs/error_log
CustomLog /var/apache/logs/access_log combined
# =
# Virtual hosts
# =
NameVirtualHost *
VirtualHost *
DocumentRoot /home/apache/www/vhosts/mydomain.com/public_html
ServerName www.mydomain.com
ServerAlias mydomain.com
ErrorLog /var/apache/logs/vhosts/mydomain.com/error_log
CustomLog /var/apache/logs/vhosts/mydomain.com/access_log
combined
IfModule mod_rewrite.c
RewriteEngine on
RewriteRule ^/([a-z]{2})/index.html$ /index.html?topicid=$1
/IfModule
ErrorDocument 400 /page_error.html
ErrorDocument 401 /page_error.html
ErrorDocument 403 /page_error.html
ErrorDocument 404 /page_error.html
ErrorDocument 500 /page_error.html
/VirtualHost
#
# Logging GET/POST requests, defending against
# Cross-Site-Scripting (XSS) and SQL Injection attacks
#
IfModule mod_security.c
AddHandler application/x-httpd-php .html
#Turn the filtering engine On or Off
SecAuditEngine On
# Only log suspicious requests
SecAuditEngine RelevantOnly
SecAuditLog /var/apache/logs/audit_log
SecFilterScanPOST On
SecFilterEngine On
SecFilterDefaultAction deny,log,status:500
Re: MySQL performance on LVM2
http://tldp.org/HOWTO/LVM-HOWTO/whatislvm.html On Tue, May 20, 2008 at 10:13 PM, Moon's Father [EMAIL PROTECTED] wrote: What is LVM? 2008/5/12 MarisRuskulis [EMAIL PROTECTED]: Hello! I'm wondering about MySQL LVM2 preformance, but cant found any comparisions. I know that there is some speed decrease with LVM, something about 30%. But how this decrease impacts overal MySQL performance? Now we are backuping replication slave server with mysqldump w full table locks, this takes some time. I think better solution is to use LVM snapshots, but this performance decrease really scares me. Has anyone some advices on this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn -- obed.org.mx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL performance on LVM2
What is LVM? 2008/5/12 MarisRuskulis [EMAIL PROTECTED]: Hello! I'm wondering about MySQL LVM2 preformance, but cant found any comparisions. I know that there is some speed decrease with LVM, something about 30%. But how this decrease impacts overal MySQL performance? Now we are backuping replication slave server with mysqldump w full table locks, this takes some time. I think better solution is to use LVM snapshots, but this performance decrease really scares me. Has anyone some advices on this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
MySQL performance on LVM2
Hello! I'm wondering about MySQL LVM2 preformance, but cant found any comparisions. I know that there is some speed decrease with LVM, something about 30%. But how this decrease impacts overal MySQL performance? Now we are backuping replication slave server with mysqldump w full table locks, this takes some time. I think better solution is to use LVM snapshots, but this performance decrease really scares me. Has anyone some advices on this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ANNOUNCE: Nagios Plugin for MySQL performance
Hi list, i wrote a plugin for the Nagios monitoring system which allows you to check some performance related aspects of a MySQL database. The -m option tells the plugin the desired mode. Possible keywords are: slave-lag (Check 'Seconds behind master') slave-io-running (Check for 'Slave io running: Yes') slave-sql-running (Check for 'Slave sql running: Yes') threads-connected (Check 'Threads connected') threadcache-hitrate (Check 'Thread cache hitrate') querycache-hitrate(Check 'Query cache hitrate') keycache-hitrate (Check 'MyISAM Key cache hitrate') bufferpool-hitrate(Check 'InnoDB Buffer pool hitrate') tablecache-hitrate(Check 'Table cache hitrate') table-lock-contention (Check 'Table lock contention') temp-disk-tables (Check 'Percent of temp tables created on disk') connection-time (Check 'Seconds to get a connection') slow-queries (Check 'Slow queries'*) qcache-lowmem-prunes (Check 'Query cache entries pruned because of low memory'*) bufferpool-wait-free (Check 'InnoDB Buffer pool waits for clean page available'*) log-waits (Check 'InnoDB log waits because of a too small log buffer'*) Example: check_mysql_perf -m qcache-hitrate OK - Query Cache Hitrate at 93.93%|qcache_hitrate=93.93%;90:;80: check_mysql_perf -m qcache-hitrate -w 95: -c 90: WARNING - Query Cache Hitrate at 93.93%|qcache_hitrate=93.93%;95:;90: Please take into account that you need also the nagios-plugins package (i recommend 1.4.11) for a successful build. The steps are: - tar zxvf nagios-plugins-1.4.11.tar.gz - cd nagios-plugins-1.4.11; ./configure; make - cd .. - tar zxvf check_mysql-perf-1.1.tar.gz - cd check_mysql-perf-1.1 - ./configure --with-officialplugins=/absolute/path/to/nagios-plugins-1.4.11 - make Of course a mysql-dev installation has to be found (or provided with --with-mysql) either in step 2 or step 6. You can find more documentation and the download link at http://www.consol.com/opensource/nagios/check-mysql-perf or if you like the german language http://www.consol.de/opensource/nagios/check-mysql-perf Greetings from Munich, Gerhard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Performance Analysis tools
Bernd Jagla wrote: sar will give you some basic information about what happens on the system... (see e.g.: http://linux.die.net/man/1/sar)... Munin (http://munin.projects.linpro.no/) will generate graphs and stats over time for system usage (cpu, mem load, disk usage etc) and includes good support for mysql graphing throughput, queries, threads and slow queries. Example here: http://munin.ping.uio.no/ping.uio.no/dahl.ping.uio.no.html mark | MARK ADDISON WEB DEVELOPER 200 GRAY'S INN ROAD LONDON WC1X 8XZ UNITED KINGDOM T +44 (0)20 7430 4678 F E [EMAIL PROTECTED] WWW.ITN.CO.UK P Please consider the environment. Do you really need to print this email? -Original Message- |From: thomas Armstrong [mailto:[EMAIL PROTECTED] |Sent: Monday, November 19, 2007 6:42 AM |To: mysql@lists.mysql.com |Subject: MySQL Performance Analysis tools | |Hi. | |Using MySQL on Linux, I'd like to analyze the performance and know how |resources (memory, threads) are used during a period of time. | |Do you know any tool to carry it out? Thank you very much. | |-- |MySQL General Mailing List |For list archives: http://lists.mysql.com/mysql |To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Performance Analysis tools
Hi. Using MySQL on Linux, I'd like to analyze the performance and know how resources (memory, threads) are used during a period of time. Do you know any tool to carry it out? Thank you very much. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Performance Analysis tools
sar will give you some basic information about what happens on the system... (see e.g.: http://linux.die.net/man/1/sar)... -B |-Original Message- |From: thomas Armstrong [mailto:[EMAIL PROTECTED] |Sent: Monday, November 19, 2007 6:42 AM |To: mysql@lists.mysql.com |Subject: MySQL Performance Analysis tools | |Hi. | |Using MySQL on Linux, I'd like to analyze the performance and know how |resources (memory, threads) are used during a period of time. | |Do you know any tool to carry it out? Thank you very much. | |-- |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 Degrades Significantly Over Time
Hi Daniel, We were using a software RAID-5 on top of hardware RAID-5 across 3 4-disk volume groups. (1 LUN from each array volume group built the software RAID-5). So we were able to lose 3 disks in a worst case scenario. It seems to me that neither RAID-1 or RAID-5 can lose more than one disk without losing data, please correct me if I'm wrong. Our data is 70% write/30% read, so the write latency is important. The filesystem is ZFS. Thanks again. Best Regards, Jason On 12/4/06, Daniel da Veiga [EMAIL PROTECTED] wrote: On 12/4/06, Jason J. W. Williams [EMAIL PROTECTED] wrote: Hi Daniel, Thank you very much for your help and advice. After some examination, we discovered a couple of things. It looks like our storage array layout was really bad for the IOPS MySQL was throwing at it, as a result the InnoDB transactions started to back-up under heavy load. Changing the array layout from RAID-5 to RAID-1 as well as moving the logs to their own spindles corrected the issue. Also, moving the InnoDB fsync log flushing interval from every commit to a 2 second interval helped dramatically. We found the storage was the problem by looking at SHOW INNODB STATUS while looking at the SCSI IOP latency. Does this sound reasonable to you? Disk IO is one of innodb's bottleneck anyway, but I doubt this could hurt performance as you suggested, making it unusable. You're the one with access to the system, and thus the only one who can test it and be sure ;) . Making a RAID 5 should increase read performance (if you calculate the best segment size), but the write operations would be not as fast as with a RAID 1, and you're risking data loss if more than one of your disks go away. I never trade security for speed, and if I were you I would check for another option. What's the most frequent operation (read/write) on your tables? Anyway, glad you solved your problem. Just out of curiosity, what is your filesystem? -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- 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 Degrades Significantly Over Time
On 12/4/06, Jason J. W. Williams [EMAIL PROTECTED] wrote: Hi Daniel, Thank you very much for your help and advice. After some examination, we discovered a couple of things. It looks like our storage array layout was really bad for the IOPS MySQL was throwing at it, as a result the InnoDB transactions started to back-up under heavy load. Changing the array layout from RAID-5 to RAID-1 as well as moving the logs to their own spindles corrected the issue. Also, moving the InnoDB fsync log flushing interval from every commit to a 2 second interval helped dramatically. We found the storage was the problem by looking at SHOW INNODB STATUS while looking at the SCSI IOP latency. Does this sound reasonable to you? Disk IO is one of innodb's bottleneck anyway, but I doubt this could hurt performance as you suggested, making it unusable. You're the one with access to the system, and thus the only one who can test it and be sure ;) . Making a RAID 5 should increase read performance (if you calculate the best segment size), but the write operations would be not as fast as with a RAID 1, and you're risking data loss if more than one of your disks go away. I never trade security for speed, and if I were you I would check for another option. What's the most frequent operation (read/write) on your tables? Anyway, glad you solved your problem. Just out of curiosity, what is your filesystem? -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Performance Degrades Significantly Over Time
Hi Daniel, Thank you very much for your help and advice. After some examination, we discovered a couple of things. It looks like our storage array layout was really bad for the IOPS MySQL was throwing at it, as a result the InnoDB transactions started to back-up under heavy load. Changing the array layout from RAID-5 to RAID-1 as well as moving the logs to their own spindles corrected the issue. Also, moving the InnoDB fsync log flushing interval from every commit to a 2 second interval helped dramatically. We found the storage was the problem by looking at SHOW INNODB STATUS while looking at the SCSI IOP latency. Does this sound reasonable to you? Best Regards, Jason On 11/27/06, Daniel da Veiga [EMAIL PROTECTED] wrote: On 11/27/06, Jason J. W. Williams [EMAIL PROTECTED] wrote: Hi, We're running MySQL 5.0.27 under Solaris 10 on both Opteron and UltraSparc T1 machines. The performance on both boxes starts out great when the process is fresh, however over the course of a week of heavy use the performance degrades to the point where its nearly unusable. The Opteron has 2GB of RAM and the T1 has 8GB. A little stumped as to what to look for that might cause performance to degrade over time. Any pointers are greatly appreciated. On a side note, when the Opteron is a slave of the T1, when the T1 has heavy load the Opteron slave falls behind on its replication duties. The whole thing is kind of strange. Thank you again in advance. First, enable (if you don't have it already) logging, without any warnings or errors its kinda complicated to check for a real problem. From what you say, I can assume your server is probably eating memory on dead process or its trying to launch multiple threads to answer requests. Check the logs, check process (show processlist at mysql), check threads (ps on *ix), if there are dead process on the list, check your applications (web or standalone) and see if the connections are being closed correctly, decrease the wait_timeout and interactive_timeout variables to automatically clean this process, but be careful with those options, as they may kill your idle clients too fast. If there are many threads, check the variables that deal with thread launching, and your OS for limits on memory or cpu time. Also, while you're at it: http://www.mysql.com/news-and-events/newsletter/2004-01/a000301.html http://dev.mysql.com/books/hpmysql-excerpts/ch06.html http://www.mysql.com/news-and-events/on-demand-webinars/mysql-performance-tuning.php Go for it. -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- 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]
MySQL Performance Degrades Significantly Over Time
Hi, We're running MySQL 5.0.27 under Solaris 10 on both Opteron and UltraSparc T1 machines. The performance on both boxes starts out great when the process is fresh, however over the course of a week of heavy use the performance degrades to the point where its nearly unusable. The Opteron has 2GB of RAM and the T1 has 8GB. A little stumped as to what to look for that might cause performance to degrade over time. Any pointers are greatly appreciated. On a side note, when the Opteron is a slave of the T1, when the T1 has heavy load the Opteron slave falls behind on its replication duties. The whole thing is kind of strange. Thank you again in advance. Best Regards, Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Performance Degrades Significantly Over Time
On 11/27/06, Jason J. W. Williams [EMAIL PROTECTED] wrote: Hi, We're running MySQL 5.0.27 under Solaris 10 on both Opteron and UltraSparc T1 machines. The performance on both boxes starts out great when the process is fresh, however over the course of a week of heavy use the performance degrades to the point where its nearly unusable. The Opteron has 2GB of RAM and the T1 has 8GB. A little stumped as to what to look for that might cause performance to degrade over time. Any pointers are greatly appreciated. On a side note, when the Opteron is a slave of the T1, when the T1 has heavy load the Opteron slave falls behind on its replication duties. The whole thing is kind of strange. Thank you again in advance. First, enable (if you don't have it already) logging, without any warnings or errors its kinda complicated to check for a real problem. From what you say, I can assume your server is probably eating memory on dead process or its trying to launch multiple threads to answer requests. Check the logs, check process (show processlist at mysql), check threads (ps on *ix), if there are dead process on the list, check your applications (web or standalone) and see if the connections are being closed correctly, decrease the wait_timeout and interactive_timeout variables to automatically clean this process, but be careful with those options, as they may kill your idle clients too fast. If there are many threads, check the variables that deal with thread launching, and your OS for limits on memory or cpu time. Also, while you're at it: http://www.mysql.com/news-and-events/newsletter/2004-01/a000301.html http://dev.mysql.com/books/hpmysql-excerpts/ch06.html http://www.mysql.com/news-and-events/on-demand-webinars/mysql-performance-tuning.php Go for it. -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Performance Degrades Significantly Over Time
Jason, in addition to Daniel's suggestions, I'll throw this out there: I had a somewhat similar problem with a database I used to own, where a handful of very hard-hit tables would become progressively slower over time, despite the fact that (due to daily archiving and purging) they were not growing in size. For me, running OPTIMIZE operations periodically did the trick, keeping the tables performing fast. Daily wasn't sufficient, actually - I ended up optimizing the key tables every other hour, though that was probably more often than needed. I think the tables were becoming fragmented in memory, possibly along with the index data. This was with MyISAM, and I do not know whether performance would have improved with mysqld restarts, as we never really had occasion to restart mysqld except during major upgrades. HTH, Dan On 11/27/06, Jason J. W. Williams [EMAIL PROTECTED] wrote: Hi, We're running MySQL 5.0.27 under Solaris 10 on both Opteron and UltraSparc T1 machines. The performance on both boxes starts out great when the process is fresh, however over the course of a week of heavy use the performance degrades to the point where its nearly unusable. The Opteron has 2GB of RAM and the T1 has 8GB. A little stumped as to what to look for that might cause performance to degrade over time. Any pointers are greatly appreciated. On a side note, when the Opteron is a slave of the T1, when the T1 has heavy load the Opteron slave falls behind on its replication duties. The whole thing is kind of strange. Thank you again in advance. Best Regards, Jason -- 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
One reason you might be seeing a higher number of writes than reads is if MySQL is able to answer queries from the data it has cached in RAM, rather than having to read off disk. I would second Atle's opinion that this setup should be entirely possible with a single database daemon, especially the way you have it segmented into so many tables across so many databases - that will help reduce contention for locks among your many clients. A big reason I would vote for one (or as few as possible anyway) database daemon is sheer simplicity. Anytime you can make system design simpler, it will make for improved reliability, easier maintenance, easier upgrades, etc. It seems you've really done the numbers on planning your data size and growth - good show. You don't talk much about your hardware, except to note RAID 1. That will obviously be an important piece of the puzzle for serving so many clients and so much data. You might consider upgrading to a multi-disk RAID 1+0 array to improve speed, for example. With so many potential client connections to MySQL, you might also take a good hard look at multiprocessor machines if you haven't already. MySQL's site has a good section on optimization: http://dev.mysql.com/doc/refman/5.0/en/optimization.html Another great resource is Jeremy Zawodny's High Performance MySQL from O'Reilly. HTH, Dan Robinson, Eric wrote: All customer sites will use the same application, but each will have its own set of 3 databases. In believe the nature of the application confines users to brief, bursty selects and updates except possibly when they run reports. I have not specifically analyzed reporting, but I ran a 2-hour sampling today of a client site with 25 users during a period of typical workload. Here's the overall disk statistics: % Read Time:5.26 % Write Time: 5.00 Avg Bytes/Read: 2918 Avg Bytes/Write:6563 Avg Read Queue: .05 Avg Write Queue:.013 Avg Disk Secs/Read: .013 Avg Disk Secs/Write:.004 Avg Read Bytes/Sec: 15151 Avg Write Bytes/Sec:66904 Avg Disk Reads/Sec: 3 Avg Disk Writes/Sec:9 The numbers are very interesting. The system writes to disk 3 times more often than it reads, and the writes are more than double the size. Bytes written per second is 4 times higher than bytes read. Yet, on average, reads take longer than writes and they tend to stack up in the queue a little more, which could explain why % Read Time is slightly higher. This is a RAID 1 array. System has plenty of RAM and was not swapping. All in all, the application appears write-heavy, but I don't think anyone can hog all the disk I/O. So, your opinion is that one instance of MySQL with a lot of databases is just as efficient as multiple MySQL instances? (Note: I WILL have to run separate instances in some cases because some customers are using slightly different versions of the application.) --Eric -Original Message- From: Atle Veka [mailto:[EMAIL PROTECTED] Sent: Saturday, June 17, 2006 12:14 PM To: Robinson, Eric Cc: mysql@lists.mysql.com Subject: Re: MySQL Performance Question So, you're looking at 150-300 databases and ~31-62k tables based on your numbers? MySQL should be able to handle that, as should your OS, but the most important part IMO is how your clients will be using their data(bases). What sort of queries, how many, etc. Will it be possible for one client to hog all the disk IO? Ignoring the latter questions, with a properly designed database and tuned queries this doesn't seem like an impossible setup on a single database daemon. Atle - Flying Crocodile Inc, Unix Systems Administrator On Fri, 16 Jun 2006, Robinson, Eric wrote: Our server will be home to 50-100 separate clients. Each client will have their own set of databases that will be accessed by 10-60 users at each client's site. In terms of performance, is it better to have 1 instance of MySQL servicing multiple databases, or multiple instances of MySQL each serving 1 database? Here's some more information to work with: Each client has 3 databases. Database: 500 tables. 13 tables sized 10-100MB. Remaining tables all less that 10MB. (This is the only database that is updated. The others are just for reference.) Main table grows at a rate of a few hundred MB/year. Database2: 50 tables. 3 tables sized 10-100MB. All other tables less than 10MB. No data growth. Database3: 179 tables. 10 tables sized 1-15MB. All other tables less than 1MB. No data growth. --Eric Disclaimer - June 16, 2006 This email and any files transmitted with it are confidential and intended solely for [EMAIL PROTECTED] If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physician Select Management (PSM) or Physician's Managed Care (PMC). Warning: Although the message sender has taken
RE: MySQL Performance Question
Dan, thanks for your comments. I think we agree on how things should be configured. I'll keep the number of daemons to a minimum. The sample data I reported earlier is from an existing production system. The new system that we are deploying will be in a 2-node HA Linux configuration. Each node will be: 2 x Dual-core Xeon 2.8GHz (4 processor cores, total). 8GB RAM RAID 10 array (300GB (usable) I prefer RAID 10 to RAID 0+1 because the former can survive the loss of 2 drives and read/write performance is about the same as RAID 0+1. Thanks again for your input. --Eric Robinson -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Monday, June 19, 2006 6:35 AM To: Robinson, Eric Cc: mysql@lists.mysql.com Subject: Re: MySQL Performance Question One reason you might be seeing a higher number of writes than reads is if MySQL is able to answer queries from the data it has cached in RAM, rather than having to read off disk. I would second Atle's opinion that this setup should be entirely possible with a single database daemon, especially the way you have it segmented into so many tables across so many databases - that will help reduce contention for locks among your many clients. A big reason I would vote for one (or as few as possible anyway) database daemon is sheer simplicity. Anytime you can make system design simpler, it will make for improved reliability, easier maintenance, easier upgrades, etc. It seems you've really done the numbers on planning your data size and growth - good show. You don't talk much about your hardware, except to note RAID 1. That will obviously be an important piece of the puzzle for serving so many clients and so much data. You might consider upgrading to a multi-disk RAID 1+0 array to improve speed, for example. With so many potential client connections to MySQL, you might also take a good hard look at multiprocessor machines if you haven't already. MySQL's site has a good section on optimization: http://dev.mysql.com/doc/refman/5.0/en/optimization.html Another great resource is Jeremy Zawodny's High Performance MySQL from O'Reilly. HTH, Dan Robinson, Eric wrote: All customer sites will use the same application, but each will have its own set of 3 databases. In believe the nature of the application confines users to brief, bursty selects and updates except possibly when they run reports. I have not specifically analyzed reporting, but I ran a 2-hour sampling today of a client site with 25 users during a period of typical workload. Here's the overall disk statistics: % Read Time: 5.26 % Write Time: 5.00 Avg Bytes/Read: 2918 Avg Bytes/Write: 6563 Avg Read Queue: .05 Avg Write Queue: .013 Avg Disk Secs/Read: .013 Avg Disk Secs/Write: .004 Avg Read Bytes/Sec: 15151 Avg Write Bytes/Sec: 66904 Avg Disk Reads/Sec: 3 Avg Disk Writes/Sec: 9 The numbers are very interesting. The system writes to disk 3 times more often than it reads, and the writes are more than double the size. Bytes written per second is 4 times higher than bytes read. Yet, on average, reads take longer than writes and they tend to stack up in the queue a little more, which could explain why % Read Time is slightly higher. This is a RAID 1 array. System has plenty of RAM and was not swapping. All in all, the application appears write-heavy, but I don't think anyone can hog all the disk I/O. So, your opinion is that one instance of MySQL with a lot of databases is just as efficient as multiple MySQL instances? (Note: I WILL have to run separate instances in some cases because some customers are using slightly different versions of the application.) --Eric -Original Message- From: Atle Veka [mailto:[EMAIL PROTECTED] Sent: Saturday, June 17, 2006 12:14 PM To: Robinson, Eric Cc: mysql@lists.mysql.com Subject: Re: MySQL Performance Question So, you're looking at 150-300 databases and ~31-62k tables based on your numbers? MySQL should be able to handle that, as should your OS, but the most important part IMO is how your clients will be using their data(bases). What sort of queries, how many, etc. Will it be possible for one client to hog all the disk IO? Ignoring the latter questions, with a properly designed database and tuned queries this doesn't seem like an impossible setup on a single database daemon. Atle - Flying Crocodile Inc, Unix Systems Administrator On Fri, 16 Jun 2006, Robinson, Eric wrote: Our server will be home to 50-100 separate clients. Each client will have their own set of databases that will be accessed by 10-60 users at each client's site. In terms of performance, is it better to have 1 instance of MySQL servicing multiple databases, or multiple instances of MySQL each serving 1 database? Here's some more information to work with: Each client has 3 databases. Database: 500 tables. 13 tables sized 10-100MB
Re: MySQL Performance Question
So, you're looking at 150-300 databases and ~31-62k tables based on your numbers? MySQL should be able to handle that, as should your OS, but the most important part IMO is how your clients will be using their data(bases). What sort of queries, how many, etc. Will it be possible for one client to hog all the disk IO? Ignoring the latter questions, with a properly designed database and tuned queries this doesn't seem like an impossible setup on a single database daemon. Atle - Flying Crocodile Inc, Unix Systems Administrator On Fri, 16 Jun 2006, Robinson, Eric wrote: Our server will be home to 50-100 separate clients. Each client will have their own set of databases that will be accessed by 10-60 users at each client's site. In terms of performance, is it better to have 1 instance of MySQL servicing multiple databases, or multiple instances of MySQL each serving 1 database? Here's some more information to work with: Each client has 3 databases. Database: 500 tables. 13 tables sized 10-100MB. Remaining tables all less that 10MB. (This is the only database that is updated. The others are just for reference.) Main table grows at a rate of a few hundred MB/year. Database2: 50 tables. 3 tables sized 10-100MB. All other tables less than 10MB. No data growth. Database3: 179 tables. 10 tables sized 1-15MB. All other tables less than 1MB. No data growth. --Eric Disclaimer - June 16, 2006 This email and any files transmitted with it are confidential and intended solely for [EMAIL PROTECTED] If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physician Select Management (PSM) or Physician's Managed Care (PMC). Warning: Although the message sender has taken reasonable precautions to ensure no viruses are present in this email, neither PSM nor PMC can accept responsibility for any loss or damage arising from the use of this email or attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Performance Question
All customer sites will use the same application, but each will have its own set of 3 databases. In believe the nature of the application confines users to brief, bursty selects and updates except possibly when they run reports. I have not specifically analyzed reporting, but I ran a 2-hour sampling today of a client site with 25 users during a period of typical workload. Here's the overall disk statistics: % Read Time:5.26 % Write Time: 5.00 Avg Bytes/Read: 2918 Avg Bytes/Write:6563 Avg Read Queue: .05 Avg Write Queue:.013 Avg Disk Secs/Read: .013 Avg Disk Secs/Write:.004 Avg Read Bytes/Sec: 15151 Avg Write Bytes/Sec:66904 Avg Disk Reads/Sec: 3 Avg Disk Writes/Sec:9 The numbers are very interesting. The system writes to disk 3 times more often than it reads, and the writes are more than double the size. Bytes written per second is 4 times higher than bytes read. Yet, on average, reads take longer than writes and they tend to stack up in the queue a little more, which could explain why % Read Time is slightly higher. This is a RAID 1 array. System has plenty of RAM and was not swapping. All in all, the application appears write-heavy, but I don't think anyone can hog all the disk I/O. So, your opinion is that one instance of MySQL with a lot of databases is just as efficient as multiple MySQL instances? (Note: I WILL have to run separate instances in some cases because some customers are using slightly different versions of the application.) --Eric -Original Message- From: Atle Veka [mailto:[EMAIL PROTECTED] Sent: Saturday, June 17, 2006 12:14 PM To: Robinson, Eric Cc: mysql@lists.mysql.com Subject: Re: MySQL Performance Question So, you're looking at 150-300 databases and ~31-62k tables based on your numbers? MySQL should be able to handle that, as should your OS, but the most important part IMO is how your clients will be using their data(bases). What sort of queries, how many, etc. Will it be possible for one client to hog all the disk IO? Ignoring the latter questions, with a properly designed database and tuned queries this doesn't seem like an impossible setup on a single database daemon. Atle - Flying Crocodile Inc, Unix Systems Administrator On Fri, 16 Jun 2006, Robinson, Eric wrote: Our server will be home to 50-100 separate clients. Each client will have their own set of databases that will be accessed by 10-60 users at each client's site. In terms of performance, is it better to have 1 instance of MySQL servicing multiple databases, or multiple instances of MySQL each serving 1 database? Here's some more information to work with: Each client has 3 databases. Database: 500 tables. 13 tables sized 10-100MB. Remaining tables all less that 10MB. (This is the only database that is updated. The others are just for reference.) Main table grows at a rate of a few hundred MB/year. Database2: 50 tables. 3 tables sized 10-100MB. All other tables less than 10MB. No data growth. Database3: 179 tables. 10 tables sized 1-15MB. All other tables less than 1MB. No data growth. --Eric Disclaimer - June 16, 2006 This email and any files transmitted with it are confidential and intended solely for [EMAIL PROTECTED] If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physician Select Management (PSM) or Physician's Managed Care (PMC). Warning: Although the message sender has taken reasonable precautions to ensure no viruses are present in this email, neither PSM nor PMC can accept responsibility for any loss or damage arising from the use of this email or attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Performance Question
Our server will be home to 50-100 separate clients. Each client will have their own set of databases that will be accessed by 10-60 users at each client's site. In terms of performance, is it better to have 1 instance of MySQL servicing multiple databases, or multiple instances of MySQL each serving 1 database? Here's some more information to work with: Each client has 3 databases. Database: 500 tables. 13 tables sized 10-100MB. Remaining tables all less that 10MB. (This is the only database that is updated. The others are just for reference.) Main table grows at a rate of a few hundred MB/year. Database2: 50 tables. 3 tables sized 10-100MB. All other tables less than 10MB. No data growth. Database3: 179 tables. 10 tables sized 1-15MB. All other tables less than 1MB. No data growth. --Eric Disclaimer - June 16, 2006 This email and any files transmitted with it are confidential and intended solely for [EMAIL PROTECTED] If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physician Select Management (PSM) or Physician's Managed Care (PMC). Warning: Although the message sender has taken reasonable precautions to ensure no viruses are present in this email, neither PSM nor PMC can accept responsibility for any loss or damage arising from the use of this email or attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql performance / ndb 5.1 performance
What exactly is the performance problem you are seeing? Have you checked to make sure all your memory is being utilized? ie, not just grabbed by MySQL, but actually in use? -Sheeri On 5/24/06, Dan Trainor [EMAIL PROTECTED] wrote: Moritz Möller wrote: Hi Dan, there are about 2GB free, so the net size would still be 32 GB. The queries are really optimized, 99.9% of all queries can be satisfied without table scans. Well, I guess I have to give NDB a chance, I hope it will help. The only alternative I come to is to cluster the database on application level (use server userID%numServers), which would be a [insert favourite non-swear-word here] lot of work ;) Moritz -Original Message- From: Dan Trainor [mailto:[EMAIL PROTECTED] Sent: Thursday, May 25, 2006 1:41 AM To: Moritz Möller; mysql@lists.mysql.com Subject: Re: mysql performance Moritz Möller wrote: Hi list, we're running some large high-traffic mysql servers, and are currently reaching the limit of our machines. We're using mysql 4.1 / innodb on debian, ibdata is about 35GB. Hardware is quad xeon dualcore, 8 GB RAM. Disk-io is nearly zero, limiting factor is CPU. The queries run very fast (I seldom see a process that's running longer than a second), but there are too many of them, I guess. As far as I know, NDB keeps the whole database in memory, so with indices and some mem as reserve, we'd need ~48GB (3x16 or something) in total for NDB :( Does someone know other solutions to this? Is NDB the only storage engine supporting clustering? Thanks in advantage, Moritz Hi - That's quite a large database. I, too, have been dealing with what I thought was a large database for this new project. Being 2G, it hardly compares to your database size. Keep in mind, however, that a 36G ibdata file does not necessarily mean that you are using 36G to store data. InnoDB documents from the MySQL site explain ways to compact these files, possibly shrinking the size of ibdata files. Another way to get a better idea of how much data you're actually using is to use the 'SHOW TABLE STATUS' query from within MySQL. Take the InnoDB Free: item under the 'Comment:' column, and subtract this from the total size of the ibdata file(s). This will give you a more accurate representation of how much of that ibdata file you're actually using. I think. (Someone mind correcting me if I'm way off here?) NDB may not be your solution. Even though disk-based storage is included with NDB in 5.1 and beyond, I'm not too sure how this will affect the speed of your operations. I suppose it's worth a try, however. Please take this advise with a grain of salt, as InnoDB is still quite new to me, as well. Other things I've found to speed up large databases are to properly make indexes, and testing them with the EXPLAIN function. This alone has let me to speed up our operations as much as 30% in most cases. Thanks -dant Hi - Well, go ahead and do that and let us know how it turns out. There's a whole mailing list on cluster. Like I said, 5.1 (don't remember specifically which version) has file-based storage for cluster as an option. Good luck with that. Thanks! -dant -- 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 / ndb 5.1 performance
Moritz Möller wrote: Hi Dan, there are about 2GB free, so the net size would still be 32 GB. The queries are really optimized, 99.9% of all queries can be satisfied without table scans. Well, I guess I have to give NDB a chance, I hope it will help. The only alternative I come to is to cluster the database on application level (use server userID%numServers), which would be a [insert favourite non-swear-word here] lot of work ;) Hi Moritz! There is an alternative solution than NdbCluster, and that would be to set up a replicated environment, and have commodity hardware slaves supply the bulk of the SELECT operations, with the 8-core machine used as the master replication database. Your application server or web server would have to point SELECTs to the slaves for reporting purposes, and do writes to the master only. This is a cheap way to get scale-out performance from commodity hardware, and it is pretty customizable as far as the replicationi layout you would want... For instance, you could have your application server direct a certain category of queries to one slave, and another category to another slave, depending on traffic conditions. BTW, how many requests/sec are you averaging, and also, what is the percentage reads to writes in your database? You can get both answers from SHOW STATUS variables. Cheers, Jay -Original Message- From: Dan Trainor [mailto:[EMAIL PROTECTED] Sent: Thursday, May 25, 2006 1:41 AM To: Moritz Möller; mysql@lists.mysql.com Subject: Re: mysql performance Moritz Möller wrote: Hi list, we're running some large high-traffic mysql servers, and are currently reaching the limit of our machines. We're using mysql 4.1 / innodb on debian, ibdata is about 35GB. Hardware is quad xeon dualcore, 8 GB RAM. Disk-io is nearly zero, limiting factor is CPU. The queries run very fast (I seldom see a process that's running longer than a second), but there are too many of them, I guess. As far as I know, NDB keeps the whole database in memory, so with indices and some mem as reserve, we'd need ~48GB (3x16 or something) in total for NDB :( Does someone know other solutions to this? Is NDB the only storage engine supporting clustering? Thanks in advantage, Moritz Hi - That's quite a large database. I, too, have been dealing with what I thought was a large database for this new project. Being 2G, it hardly compares to your database size. Keep in mind, however, that a 36G ibdata file does not necessarily mean that you are using 36G to store data. InnoDB documents from the MySQL site explain ways to compact these files, possibly shrinking the size of ibdata files. Another way to get a better idea of how much data you're actually using is to use the 'SHOW TABLE STATUS' query from within MySQL. Take the InnoDB Free: item under the 'Comment:' column, and subtract this from the total size of the ibdata file(s). This will give you a more accurate representation of how much of that ibdata file you're actually using. I think. (Someone mind correcting me if I'm way off here?) NDB may not be your solution. Even though disk-based storage is included with NDB in 5.1 and beyond, I'm not too sure how this will affect the speed of your operations. I suppose it's worth a try, however. Please take this advise with a grain of salt, as InnoDB is still quite new to me, as well. Other things I've found to speed up large databases are to properly make indexes, and testing them with the EXPLAIN function. This alone has let me to speed up our operations as much as 30% in most cases. Thanks -dant -- Jay Pipes Community Relations Manager, North America, MySQL Inc. Roaming North America, based in Columbus, Ohio email: [EMAIL PROTECTED]mob: +1 614 406 1267 Are You MySQL Certified? http://www.mysql.com/certification Got Cluster? http://www.mysql.com/cluster -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql performance / ndb 5.1 performance
Hi Jay! Writes make up about 17% of the queries, and we average 4000 queries/sec. Querycache hit ratio is about 45% (QC seems to be a topic worth spending some time for... 45% does not look so good). We already tested replication, changing the database logic in the scripts to use the slaves until the first update operation, then use the master only (so the SELECT-after-UPDATE operations succeed). Main problem was that the slaves got out of sync after large updates (LOAD DATA INFILE for example), which we solved by doing large updates in small chunks (UPDATE LIMIT n while rows_matched n), but still, every 2-3 days the slaves got completely out of sync (duplicate key and other) :( Moritz -Original Message- From: Jay Pipes [mailto:[EMAIL PROTECTED] Sent: Thursday, May 25, 2006 6:37 PM To: Moritz Möller Cc: 'Dan Trainor'; mysql@lists.mysql.com Subject: Re: mysql performance / ndb 5.1 performance Moritz Möller wrote: Hi Dan, there are about 2GB free, so the net size would still be 32 GB. The queries are really optimized, 99.9% of all queries can be satisfied without table scans. Well, I guess I have to give NDB a chance, I hope it will help. The only alternative I come to is to cluster the database on application level (use server userID%numServers), which would be a [insert favourite non-swear-word here] lot of work ;) Hi Moritz! There is an alternative solution than NdbCluster, and that would be to set up a replicated environment, and have commodity hardware slaves supply the bulk of the SELECT operations, with the 8-core machine used as the master replication database. Your application server or web server would have to point SELECTs to the slaves for reporting purposes, and do writes to the master only. This is a cheap way to get scale-out performance from commodity hardware, and it is pretty customizable as far as the replicationi layout you would want... For instance, you could have your application server direct a certain category of queries to one slave, and another category to another slave, depending on traffic conditions. BTW, how many requests/sec are you averaging, and also, what is the percentage reads to writes in your database? You can get both answers from SHOW STATUS variables. Cheers, Jay -Original Message- From: Dan Trainor [mailto:[EMAIL PROTECTED] Sent: Thursday, May 25, 2006 1:41 AM To: Moritz Möller; mysql@lists.mysql.com Subject: Re: mysql performance Moritz Möller wrote: Hi list, we're running some large high-traffic mysql servers, and are currently reaching the limit of our machines. We're using mysql 4.1 / innodb on debian, ibdata is about 35GB. Hardware is quad xeon dualcore, 8 GB RAM. Disk-io is nearly zero, limiting factor is CPU. The queries run very fast (I seldom see a process that's running longer than a second), but there are too many of them, I guess. As far as I know, NDB keeps the whole database in memory, so with indices and some mem as reserve, we'd need ~48GB (3x16 or something) in total for NDB :( Does someone know other solutions to this? Is NDB the only storage engine supporting clustering? Thanks in advantage, Moritz Hi - That's quite a large database. I, too, have been dealing with what I thought was a large database for this new project. Being 2G, it hardly compares to your database size. Keep in mind, however, that a 36G ibdata file does not necessarily mean that you are using 36G to store data. InnoDB documents from the MySQL site explain ways to compact these files, possibly shrinking the size of ibdata files. Another way to get a better idea of how much data you're actually using is to use the 'SHOW TABLE STATUS' query from within MySQL. Take the InnoDB Free: item under the 'Comment:' column, and subtract this from the total size of the ibdata file(s). This will give you a more accurate representation of how much of that ibdata file you're actually using. I think. (Someone mind correcting me if I'm way off here?) NDB may not be your solution. Even though disk-based storage is included with NDB in 5.1 and beyond, I'm not too sure how this will affect the speed of your operations. I suppose it's worth a try, however. Please take this advise with a grain of salt, as InnoDB is still quite new to me, as well. Other things I've found to speed up large databases are to properly make indexes, and testing them with the EXPLAIN function. This alone has let me to speed up our operations as much as 30% in most cases. Thanks -dant -- Jay Pipes Community Relations Manager, North America, MySQL Inc. Roaming North America, based in Columbus, Ohio email: [EMAIL PROTECTED]mob: +1 614 406 1267 Are You MySQL Certified? http://www.mysql.com/certification Got Cluster? http://www.mysql.com/cluster -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http
mysql performance
Hi list, we're running some large high-traffic mysql servers, and are currently reaching the limit of our machines. We're using mysql 4.1 / innodb on debian, ibdata is about 35GB. Hardware is quad xeon dualcore, 8 GB RAM. Disk-io is nearly zero, limiting factor is CPU. The queries run very fast (I seldom see a process that's running longer than a second), but there are too many of them, I guess. As far as I know, NDB keeps the whole database in memory, so with indices and some mem as reserve, we'd need ~48GB (3x16 or something) in total for NDB :( Does someone know other solutions to this? Is NDB the only storage engine supporting clustering? Thanks in advantage, Moritz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql performance
Moritz Möller wrote: Hi list, we're running some large high-traffic mysql servers, and are currently reaching the limit of our machines. We're using mysql 4.1 / innodb on debian, ibdata is about 35GB. Hardware is quad xeon dualcore, 8 GB RAM. Disk-io is nearly zero, limiting factor is CPU. The queries run very fast (I seldom see a process that's running longer than a second), but there are too many of them, I guess. As far as I know, NDB keeps the whole database in memory, so with indices and some mem as reserve, we'd need ~48GB (3x16 or something) in total for NDB :( Does someone know other solutions to this? Is NDB the only storage engine supporting clustering? Thanks in advantage, Moritz Hi - That's quite a large database. I, too, have been dealing with what I thought was a large database for this new project. Being 2G, it hardly compares to your database size. Keep in mind, however, that a 36G ibdata file does not necessarily mean that you are using 36G to store data. InnoDB documents from the MySQL site explain ways to compact these files, possibly shrinking the size of ibdata files. Another way to get a better idea of how much data you're actually using is to use the 'SHOW TABLE STATUS' query from within MySQL. Take the InnoDB Free: item under the 'Comment:' column, and subtract this from the total size of the ibdata file(s). This will give you a more accurate representation of how much of that ibdata file you're actually using. I think. (Someone mind correcting me if I'm way off here?) NDB may not be your solution. Even though disk-based storage is included with NDB in 5.1 and beyond, I'm not too sure how this will affect the speed of your operations. I suppose it's worth a try, however. Please take this advise with a grain of salt, as InnoDB is still quite new to me, as well. Other things I've found to speed up large databases are to properly make indexes, and testing them with the EXPLAIN function. This alone has let me to speed up our operations as much as 30% in most cases. Thanks -dant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql performance / ndb 5.1 performance
Hi Dan, there are about 2GB free, so the net size would still be 32 GB. The queries are really optimized, 99.9% of all queries can be satisfied without table scans. Well, I guess I have to give NDB a chance, I hope it will help. The only alternative I come to is to cluster the database on application level (use server userID%numServers), which would be a [insert favourite non-swear-word here] lot of work ;) Moritz -Original Message- From: Dan Trainor [mailto:[EMAIL PROTECTED] Sent: Thursday, May 25, 2006 1:41 AM To: Moritz Möller; mysql@lists.mysql.com Subject: Re: mysql performance Moritz Möller wrote: Hi list, we're running some large high-traffic mysql servers, and are currently reaching the limit of our machines. We're using mysql 4.1 / innodb on debian, ibdata is about 35GB. Hardware is quad xeon dualcore, 8 GB RAM. Disk-io is nearly zero, limiting factor is CPU. The queries run very fast (I seldom see a process that's running longer than a second), but there are too many of them, I guess. As far as I know, NDB keeps the whole database in memory, so with indices and some mem as reserve, we'd need ~48GB (3x16 or something) in total for NDB :( Does someone know other solutions to this? Is NDB the only storage engine supporting clustering? Thanks in advantage, Moritz Hi - That's quite a large database. I, too, have been dealing with what I thought was a large database for this new project. Being 2G, it hardly compares to your database size. Keep in mind, however, that a 36G ibdata file does not necessarily mean that you are using 36G to store data. InnoDB documents from the MySQL site explain ways to compact these files, possibly shrinking the size of ibdata files. Another way to get a better idea of how much data you're actually using is to use the 'SHOW TABLE STATUS' query from within MySQL. Take the InnoDB Free: item under the 'Comment:' column, and subtract this from the total size of the ibdata file(s). This will give you a more accurate representation of how much of that ibdata file you're actually using. I think. (Someone mind correcting me if I'm way off here?) NDB may not be your solution. Even though disk-based storage is included with NDB in 5.1 and beyond, I'm not too sure how this will affect the speed of your operations. I suppose it's worth a try, however. Please take this advise with a grain of salt, as InnoDB is still quite new to me, as well. Other things I've found to speed up large databases are to properly make indexes, and testing them with the EXPLAIN function. This alone has let me to speed up our operations as much as 30% in most cases. Thanks -dant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql performance / ndb 5.1 performance
Moritz Möller wrote: Hi Dan, there are about 2GB free, so the net size would still be 32 GB. The queries are really optimized, 99.9% of all queries can be satisfied without table scans. Well, I guess I have to give NDB a chance, I hope it will help. The only alternative I come to is to cluster the database on application level (use server userID%numServers), which would be a [insert favourite non-swear-word here] lot of work ;) Moritz -Original Message- From: Dan Trainor [mailto:[EMAIL PROTECTED] Sent: Thursday, May 25, 2006 1:41 AM To: Moritz Möller; mysql@lists.mysql.com Subject: Re: mysql performance Moritz Möller wrote: Hi list, we're running some large high-traffic mysql servers, and are currently reaching the limit of our machines. We're using mysql 4.1 / innodb on debian, ibdata is about 35GB. Hardware is quad xeon dualcore, 8 GB RAM. Disk-io is nearly zero, limiting factor is CPU. The queries run very fast (I seldom see a process that's running longer than a second), but there are too many of them, I guess. As far as I know, NDB keeps the whole database in memory, so with indices and some mem as reserve, we'd need ~48GB (3x16 or something) in total for NDB :( Does someone know other solutions to this? Is NDB the only storage engine supporting clustering? Thanks in advantage, Moritz Hi - That's quite a large database. I, too, have been dealing with what I thought was a large database for this new project. Being 2G, it hardly compares to your database size. Keep in mind, however, that a 36G ibdata file does not necessarily mean that you are using 36G to store data. InnoDB documents from the MySQL site explain ways to compact these files, possibly shrinking the size of ibdata files. Another way to get a better idea of how much data you're actually using is to use the 'SHOW TABLE STATUS' query from within MySQL. Take the InnoDB Free: item under the 'Comment:' column, and subtract this from the total size of the ibdata file(s). This will give you a more accurate representation of how much of that ibdata file you're actually using. I think. (Someone mind correcting me if I'm way off here?) NDB may not be your solution. Even though disk-based storage is included with NDB in 5.1 and beyond, I'm not too sure how this will affect the speed of your operations. I suppose it's worth a try, however. Please take this advise with a grain of salt, as InnoDB is still quite new to me, as well. Other things I've found to speed up large databases are to properly make indexes, and testing them with the EXPLAIN function. This alone has let me to speed up our operations as much as 30% in most cases. Thanks -dant Hi - Well, go ahead and do that and let us know how it turns out. There's a whole mailing list on cluster. Like I said, 5.1 (don't remember specifically which version) has file-based storage for cluster as an option. Good luck with that. Thanks! -dant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Quick Linux/MySQL performance questions.
Folks, I had some recommendations about operating system last time I posted, and decided to follow it. It's been a couple of weeks running Gentoo Linux 2006.0 SMP 2.6.15-gentoo-r5, with glibc 2.4 (NPTL), gcc 3.4.4, XFS as my FS, deadline scheduler and this has proven to be really stable -- MySQL is 4.0.26 (with gentoo patches, but built on my own), and the machine is a 2x250 Opteron, 2GB RAM, with LSI MegaRAID 320 (15k RPM disks). Although this is perfectly fine, I have some questions that maybe you guys can help me out. 1) Is there a way to see MySQL using both processors? Is SMP helpful in this case? (This server is dedicated to MySQL, only one instance). 2) Is there a way to confirm that MySQL is using NPTL? The glibc 2.4 is NPTL only, but I'm not sure if this is right. 3) My database is entirely MyISAM, reaching almost 35GB, there are 3 large tables with (approx) 6GB each. And this can really slow things down with joins. How can I make this perform better? More RAM? The MYI files are almost twice the size of its respective MYD (lotsof indexes). Thanks a lot your help, guys. Best regards, RV -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Quick Linux/MySQL performance questions.
RV Tec schrieb: Folks, I had some recommendations about operating system last time I posted, and decided to follow it. It's been a couple of weeks running Gentoo Linux 2006.0 SMP 2.6.15-gentoo-r5, with glibc 2.4 (NPTL), gcc 3.4.4, XFS as my FS, deadline scheduler and this has proven to be really stable -- MySQL is 4.0.26 (with gentoo patches, but built on my own), and the machine is a 2x250 Opteron, 2GB RAM, with LSI MegaRAID 320 (15k RPM disks). Although this is perfectly fine, I have some questions that maybe you guys can help me out. 1) Is there a way to see MySQL using both processors? Is SMP helpful in this case? (This server is dedicated to MySQL, only one instance). PS (*nix) should tell you how your processors are used. 2) Is there a way to confirm that MySQL is using NPTL? The glibc 2.4 is NPTL only, but I'm not sure if this is right. Depends on which lib mysql uses in your case. 3) My database is entirely MyISAM, reaching almost 35GB, there are 3 large tables with (approx) 6GB each. And this can really slow things down with joins. How can I make this perform better? More RAM? The MYI files are almost twice the size of its respective MYD (lotsof indexes). Read the Optimization sections of the mysql docs. More RAM will speed it up also. Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Quick Linux/MySQL performance questions.
Buettner, First of all, thanks a lot for your reply! This server has 4 disks to MySQL usage, in two pairs of RAID-1, connected to a single channel (ok, I realize now this means a bottleneck) LSI PCIe card. One RAID1 for MySQL logging and temp space, and the other pair for the database files (MYI/MYD). I was planning a couple of things: 1) Add another LSI card, this time, 2-channel. Put the MYI files on one mount point, and the MYD at the other one -- different channels. 2) Find a way to measure the max size of the tempdir, used by MySQL. Depending on its size, I could use a MFS partition. This could avoid me some Copying to tmp table, I guess. What I'm scared to death, is that our queries are really complex, with lots of left joins and lots of large tables used. Some queries are now reaching 30 minutes to return... we do have slow queries active, and after I'm sure the hardware/OS is OK, we'll nail this and try to get it better. Best regards, RV On Fri, 12 May 2006, Dan Buettner wrote: Good morning RV - On your 3rd question, about how to make things faster: More RAM should help by allowing the server to keep more/all of the indexes in memory, enabling much faster access. Be sure to adjust the cache settings in your my.cnf file after adding RAM. (Keep in mind - some my.cnf memory settings are per database server instance and some are per connection thread instance!) Large databases eat RAM for breakfast. The rest of your hardware setup sounds really quite good. One possibility for some improvement might be to look at adding dedicated fast disks for MySQL temp space, since you are dealing with large datasets. 2 or more small fast disks in a striped setup, especially on their own SCSI channel and ideally with their own hardware RAID RAM cache, may reduce disk and I/O contention if your temp space is currently on the same disks with your data. Of course this will only be helpful if MySQL is actually using disk based temp tables during large queries - check your status output to see. I've done a lot of reading on and experimentation with MySQL performance and attended a MySQL training session on performance tuning, and have learned: once you have reasonable hardware, the biggest thing you can do to improve speed is to optimize your SQL queries, indexes, and data structure. While improving your hardware can give perhaps a factor of 10 performance increase, optimizing your indexes and queries can sometimes give factors of 100's. Enable your slow query log, if you haven't already, and use the slow query tool to start looking at what kinds of queries are taking too long (too long being defined by you as a MySQL variable in number of seconds). Start with the slow queries used most often and see how you can optimize those, by adding or changing indexes for example. Read up on MyISAM performance, particularly when it comes to index creation and usage. Keep in mind that 4.x and 5.x are slightly different animals in this area (MyISAM index usage) and so read the section for your version: http://dev.mysql.com/doc/refman/4.1/en/optimization.html Lots of indexes can be helpful, but MySQL may not be able to use them well depending on how they were created: the order in which you specify columns when creating a multi-column index affects how/whether MySQL can use it for certain queries, for example. Hope this helps. Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Quick Linux/MySQL performance questions. (fwd)
1) Is there a way to see MySQL using both processors? Is SMP helpful in this case? (This server is dedicated to MySQL, only one instance). PS (*nix) should tell you how your processors are used. PS does show me about the CPU usage, but it doesnt tell me which processor, or if they're being used at the same time. Or am I missing a magic PS switch? 2) Is there a way to confirm that MySQL is using NPTL? The glibc 2.4 is NPTL only, but I'm not sure if this is right. Depends on which lib mysql uses in your case. Yeah, I'm aware of that. Since glibc 2.4 has only NPTL, I was wondering if it is possible to MySQL use a threading system of its own. However, what I want to know, is a way to confirm that it has been compiled against NPTL. This appears on my config.log session: --enable-threads=posix Thread model: posix Is that NPTL? I know NPTL is POSIX, but maybe this should say Thread model: NPTL. 3) My database is entirely MyISAM, reaching almost 35GB, there are 3 large tables with (approx) 6GB each. And this can really slow things down with joins. How can I make this perform better? More RAM? The MYI files are almost twice the size of its respective MYD (lotsof indexes). Read the Optimization sections of the mysql docs. More RAM will speed it up also. Thanks. I'll try to bump it up to 4GB to see what happens. Thanks a lot! RV -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Quick Linux/MySQL performance questions.
Good morning RV - On your 3rd question, about how to make things faster: More RAM should help by allowing the server to keep more/all of the indexes in memory, enabling much faster access. Be sure to adjust the cache settings in your my.cnf file after adding RAM. (Keep in mind - some my.cnf memory settings are per database server instance and some are per connection thread instance!) Large databases eat RAM for breakfast. The rest of your hardware setup sounds really quite good. One possibility for some improvement might be to look at adding dedicated fast disks for MySQL temp space, since you are dealing with large datasets. 2 or more small fast disks in a striped setup, especially on their own SCSI channel and ideally with their own hardware RAID RAM cache, may reduce disk and I/O contention if your temp space is currently on the same disks with your data. Of course this will only be helpful if MySQL is actually using disk based temp tables during large queries - check your status output to see. I've done a lot of reading on and experimentation with MySQL performance and attended a MySQL training session on performance tuning, and have learned: once you have reasonable hardware, the biggest thing you can do to improve speed is to optimize your SQL queries, indexes, and data structure. While improving your hardware can give perhaps a factor of 10 performance increase, optimizing your indexes and queries can sometimes give factors of 100's. Enable your slow query log, if you haven't already, and use the slow query tool to start looking at what kinds of queries are taking too long (too long being defined by you as a MySQL variable in number of seconds). Start with the slow queries used most often and see how you can optimize those, by adding or changing indexes for example. Read up on MyISAM performance, particularly when it comes to index creation and usage. Keep in mind that 4.x and 5.x are slightly different animals in this area (MyISAM index usage) and so read the section for your version: http://dev.mysql.com/doc/refman/4.1/en/optimization.html Lots of indexes can be helpful, but MySQL may not be able to use them well depending on how they were created: the order in which you specify columns when creating a multi-column index affects how/whether MySQL can use it for certain queries, for example. Hope this helps. Dan RV Tec wrote: Folks, I had some recommendations about operating system last time I posted, and decided to follow it. It's been a couple of weeks running Gentoo Linux 2006.0 SMP 2.6.15-gentoo-r5, with glibc 2.4 (NPTL), gcc 3.4.4, XFS as my FS, deadline scheduler and this has proven to be really stable -- MySQL is 4.0.26 (with gentoo patches, but built on my own), and the machine is a 2x250 Opteron, 2GB RAM, with LSI MegaRAID 320 (15k RPM disks). Although this is perfectly fine, I have some questions that maybe you guys can help me out. 1) Is there a way to see MySQL using both processors? Is SMP helpful in this case? (This server is dedicated to MySQL, only one instance). 2) Is there a way to confirm that MySQL is using NPTL? The glibc 2.4 is NPTL only, but I'm not sure if this is right. 3) My database is entirely MyISAM, reaching almost 35GB, there are 3 large tables with (approx) 6GB each. And this can really slow things down with joins. How can I make this perform better? More RAM? The MYI files are almost twice the size of its respective MYD (lotsof indexes). Thanks a lot your help, guys. Best regards, RV -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Quick Linux/MySQL performance questions.
On 5/12/06, RV Tec [EMAIL PROTECTED] wrote: Buettner, First of all, thanks a lot for your reply! This server has 4 disks to MySQL usage, in two pairs of RAID-1, connected to a single channel (ok, I realize now this means a bottleneck) LSI PCIe card. One RAID1 for MySQL logging and temp space, and the other pair for the database files (MYI/MYD). I was planning a couple of things: 1) Add another LSI card, this time, 2-channel. Put the MYI files on one mount point, and the MYD at the other one -- different channels. 2) Find a way to measure the max size of the tempdir, used by MySQL. Depending on its size, I could use a MFS partition. This could avoid me some Copying to tmp table, I guess. What I'm scared to death, is that our queries are really complex, with lots of left joins and lots of large tables used. Some queries are now reaching 30 minutes to return... we do have slow queries active, and after I'm sure the hardware/OS is OK, we'll nail this and try to get it better. Best regards, RV On Fri, 12 May 2006, Dan Buettner wrote: Good morning RV - On your 3rd question, about how to make things faster: More RAM should help by allowing the server to keep more/all of the indexes in memory, enabling much faster access. Be sure to adjust the cache settings in your my.cnf file after adding RAM. (Keep in mind - some my.cnf memory settings are per database server instance and some are per connection thread instance!) Large databases eat RAM for breakfast. The rest of your hardware setup sounds really quite good. One possibility for some improvement might be to look at adding dedicated fast disks for MySQL temp space, since you are dealing with large datasets. 2 or more small fast disks in a striped setup, especially on their own SCSI channel and ideally with their own hardware RAID RAM cache, may reduce disk and I/O contention if your temp space is currently on the same disks with your data. Of course this will only be helpful if MySQL is actually using disk based temp tables during large queries - check your status output to see. I've done a lot of reading on and experimentation with MySQL performance and attended a MySQL training session on performance tuning, and have learned: once you have reasonable hardware, the biggest thing you can do to improve speed is to optimize your SQL queries, indexes, and data structure. While improving your hardware can give perhaps a factor of 10 performance increase, optimizing your indexes and queries can sometimes give factors of 100's. Enable your slow query log, if you haven't already, and use the slow query tool to start looking at what kinds of queries are taking too long (too long being defined by you as a MySQL variable in number of seconds). Start with the slow queries used most often and see how you can optimize those, by adding or changing indexes for example. Read up on MyISAM performance, particularly when it comes to index creation and usage. Keep in mind that 4.x and 5.x are slightly different animals in this area (MyISAM index usage) and so read the section for your version: http://dev.mysql.com/doc/refman/4.1/en/optimization.html Lots of indexes can be helpful, but MySQL may not be able to use them well depending on how they were created: the order in which you specify columns when creating a multi-column index affects how/whether MySQL can use it for certain queries, for example. Hope this helps. Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Be aware that Gentoo builds glibc by default twice (read the einfo of the ebuild?) one with linuxthreads and the other with NPTL, you can set USE flags to avoid that (I guess its a matter of compatibility). so, you may be running MySQL with linuxthreads instead. Check it. More RAM, that's my advice, since your system seems pretty powerful. Also check MySQL manual on optimization, and check the variables and the way they are set on your system. -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Quick Linux/MySQL performance questions.
Hope it is useful. I agree, you may want to look at adding another card and disks, for speed and to segregate the various operations (temp, logging, data). Splitting up your MYD and MYI files may help, though if you have enough RAM to keep indexes in memory, maybe you don't need to do that. With the sheer size of your data, I suggest you consider some form of striping with your RAID, not just mirroring. I'm a big fan of RAID 10 personally but if your data is pretty static then RAID 5 gives you the read speed at a lower cost. If you have a lot of write operations then RAID 5 may not be such a good choice. You might be surprised how much you will gain in read speed and therefore MySQL query speed if you go from RAID 1 to say a 6-disk RAID 10 setup. Depends on funds of course. For a good LSI card and 6 small fast internal disks you're probably looking at $2K or so. Depending on what you have now you could put MySQL logging on some inexpensive slower disks and re-use existing disks in a new setup. Good luck! Dan RV Tec wrote: Buettner, First of all, thanks a lot for your reply! This server has 4 disks to MySQL usage, in two pairs of RAID-1, connected to a single channel (ok, I realize now this means a bottleneck) LSI PCIe card. One RAID1 for MySQL logging and temp space, and the other pair for the database files (MYI/MYD). I was planning a couple of things: 1) Add another LSI card, this time, 2-channel. Put the MYI files on one mount point, and the MYD at the other one -- different channels. 2) Find a way to measure the max size of the tempdir, used by MySQL. Depending on its size, I could use a MFS partition. This could avoid me some Copying to tmp table, I guess. What I'm scared to death, is that our queries are really complex, with lots of left joins and lots of large tables used. Some queries are now reaching 30 minutes to return... we do have slow queries active, and after I'm sure the hardware/OS is OK, we'll nail this and try to get it better. Best regards, RV On Fri, 12 May 2006, Dan Buettner wrote: Good morning RV - On your 3rd question, about how to make things faster: More RAM should help by allowing the server to keep more/all of the indexes in memory, enabling much faster access. Be sure to adjust the cache settings in your my.cnf file after adding RAM. (Keep in mind - some my.cnf memory settings are per database server instance and some are per connection thread instance!) Large databases eat RAM for breakfast. The rest of your hardware setup sounds really quite good. One possibility for some improvement might be to look at adding dedicated fast disks for MySQL temp space, since you are dealing with large datasets. 2 or more small fast disks in a striped setup, especially on their own SCSI channel and ideally with their own hardware RAID RAM cache, may reduce disk and I/O contention if your temp space is currently on the same disks with your data. Of course this will only be helpful if MySQL is actually using disk based temp tables during large queries - check your status output to see. I've done a lot of reading on and experimentation with MySQL performance and attended a MySQL training session on performance tuning, and have learned: once you have reasonable hardware, the biggest thing you can do to improve speed is to optimize your SQL queries, indexes, and data structure. While improving your hardware can give perhaps a factor of 10 performance increase, optimizing your indexes and queries can sometimes give factors of 100's. Enable your slow query log, if you haven't already, and use the slow query tool to start looking at what kinds of queries are taking too long (too long being defined by you as a MySQL variable in number of seconds). Start with the slow queries used most often and see how you can optimize those, by adding or changing indexes for example. Read up on MyISAM performance, particularly when it comes to index creation and usage. Keep in mind that 4.x and 5.x are slightly different animals in this area (MyISAM index usage) and so read the section for your version: http://dev.mysql.com/doc/refman/4.1/en/optimization.html Lots of indexes can be helpful, but MySQL may not be able to use them well depending on how they were created: the order in which you specify columns when creating a multi-column index affects how/whether MySQL can use it for certain queries, for example. Hope this helps. Dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Quick Linux/MySQL performance questions.
I'll add make sure logs and data are on separate partitions so you're not doing excessive seeking back and forth. -Sheeri On 5/12/06, Dan Buettner [EMAIL PROTECTED] wrote: Hope it is useful. I agree, you may want to look at adding another card and disks, for speed and to segregate the various operations (temp, logging, data). Splitting up your MYD and MYI files may help, though if you have enough RAM to keep indexes in memory, maybe you don't need to do that. With the sheer size of your data, I suggest you consider some form of striping with your RAID, not just mirroring. I'm a big fan of RAID 10 personally but if your data is pretty static then RAID 5 gives you the read speed at a lower cost. If you have a lot of write operations then RAID 5 may not be such a good choice. You might be surprised how much you will gain in read speed and therefore MySQL query speed if you go from RAID 1 to say a 6-disk RAID 10 setup. Depends on funds of course. For a good LSI card and 6 small fast internal disks you're probably looking at $2K or so. Depending on what you have now you could put MySQL logging on some inexpensive slower disks and re-use existing disks in a new setup. Good luck! Dan RV Tec wrote: Buettner, First of all, thanks a lot for your reply! This server has 4 disks to MySQL usage, in two pairs of RAID-1, connected to a single channel (ok, I realize now this means a bottleneck) LSI PCIe card. One RAID1 for MySQL logging and temp space, and the other pair for the database files (MYI/MYD). I was planning a couple of things: 1) Add another LSI card, this time, 2-channel. Put the MYI files on one mount point, and the MYD at the other one -- different channels. 2) Find a way to measure the max size of the tempdir, used by MySQL. Depending on its size, I could use a MFS partition. This could avoid me some Copying to tmp table, I guess. What I'm scared to death, is that our queries are really complex, with lots of left joins and lots of large tables used. Some queries are now reaching 30 minutes to return... we do have slow queries active, and after I'm sure the hardware/OS is OK, we'll nail this and try to get it better. Best regards, RV On Fri, 12 May 2006, Dan Buettner wrote: Good morning RV - On your 3rd question, about how to make things faster: More RAM should help by allowing the server to keep more/all of the indexes in memory, enabling much faster access. Be sure to adjust the cache settings in your my.cnf file after adding RAM. (Keep in mind - some my.cnf memory settings are per database server instance and some are per connection thread instance!) Large databases eat RAM for breakfast. The rest of your hardware setup sounds really quite good. One possibility for some improvement might be to look at adding dedicated fast disks for MySQL temp space, since you are dealing with large datasets. 2 or more small fast disks in a striped setup, especially on their own SCSI channel and ideally with their own hardware RAID RAM cache, may reduce disk and I/O contention if your temp space is currently on the same disks with your data. Of course this will only be helpful if MySQL is actually using disk based temp tables during large queries - check your status output to see. I've done a lot of reading on and experimentation with MySQL performance and attended a MySQL training session on performance tuning, and have learned: once you have reasonable hardware, the biggest thing you can do to improve speed is to optimize your SQL queries, indexes, and data structure. While improving your hardware can give perhaps a factor of 10 performance increase, optimizing your indexes and queries can sometimes give factors of 100's. Enable your slow query log, if you haven't already, and use the slow query tool to start looking at what kinds of queries are taking too long (too long being defined by you as a MySQL variable in number of seconds). Start with the slow queries used most often and see how you can optimize those, by adding or changing indexes for example. Read up on MyISAM performance, particularly when it comes to index creation and usage. Keep in mind that 4.x and 5.x are slightly different animals in this area (MyISAM index usage) and so read the section for your version: http://dev.mysql.com/doc/refman/4.1/en/optimization.html Lots of indexes can be helpful, but MySQL may not be able to use them well depending on how they were created: the order in which you specify columns when creating a multi-column index affects how/whether MySQL can use it for certain queries, for example. Hope this helps. Dan -- 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: Quick Linux/MySQL performance questions. (fwd)
On Fri, 2006-05-12 at 10:59 -0400, RV Tec wrote: Yeah, I'm aware of that. Since glibc 2.4 has only NPTL, I was wondering if it is possible to MySQL use a threading system of its own. However, what I want to know, is a way to confirm that it has been compiled against NPTL. This appears on my config.log session: --enable-threads=posix Thread model: posix Is that NPTL? I know NPTL is POSIX, but maybe this should say Thread model: NPTL. NPTL stands for Native POSIX Thread Library, so yeah, it's using NPTL. -- Pat Adams Digital Darkness Promotions Check out the Dallas Music Wiki http://digitaldarkness.com/tiki signature.asc Description: This is a digitally signed message part
Re: ~Mysql performance~
Hi, The Server2 is not a slave. Seperate inserts were done on two different mysql servers. Thanks, Abdul. On Fri, 2006-04-28 at 12:01 -0400, Kishore Jalleda wrote: On 4/28/06, Mohammed Abdul Azeem [EMAIL PROTECTED] wrote: Hi, I have 2 mysql servers with version 5.0.15-standard-log running on redhat es4 installed on 2 different geographic locations. The default storage engine used is innodb on both the servers. I run an insert query on both the servers that inserts 25,00,000 records. first server takes 7.5 hrs, while the second server takes around 17 hrs for the same process. However the configuration file my.cnf is the same for both the machines. The first server has a 1GB RAM and a single processor. While the second server has a 2 GB RAM and it has a dual processor as well. If we go by the hardware specs the second server should take less time as it has a better hardware than the first one. Iam really puzzled . Can anyone help me out in fixing the issue ? Thanks, Abdul. This email has been Scanned for Viruses! www.newbreak.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] Is the server2 a slave of server1, and is the same insert on server2 run directly on it or is itpart of a replicated query from server1. This came to my mind as u mentioned the two servers are geographically seperated .. Kishore Jalleda http://kjalleda.googlepages.com/projects This email has been Scanned for Viruses! www.newbreak.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ~Mysql performance~
Hi, The insert queries are run from the localhost on both the machines. -Abdul On Fri, 2006-04-28 at 10:28 -0700, Atle Veka wrote: On Fri, 28 Apr 2006, Mohammed Abdul Azeem wrote: Hi, I have 2 mysql servers with version 5.0.15-standard-log running on redhat es4 installed on 2 different geographic locations. The default storage engine used is innodb on both the servers. I run an insert query on both the servers that inserts 25,00,000 records. first server takes 7.5 hrs, while the second server takes around 17 hrs for the same process. Are these queries run from localhost or from a remote location? It's very unusual for an IDE based system to outperform SCSI. Atle - Flying Crocodile Inc, Unix Systems Administrator This email has been Scanned for Viruses! www.newbreak.com This email has been Scanned for Viruses! www.newbreak.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
~Mysql performance~
Hi, I have 2 mysql servers with version 5.0.15-standard-log running on redhat es4 installed on 2 different geographic locations. The default storage engine used is innodb on both the servers. I run an insert query on both the servers that inserts 25,00,000 records. first server takes 7.5 hrs, while the second server takes around 17 hrs for the same process. However the configuration file my.cnf is the same for both the machines. The first server has a 1GB RAM and a single processor. While the second server has a 2 GB RAM and it has a dual processor as well. If we go by the hardware specs the second server should take less time as it has a better hardware than the first one. Iam really puzzled . Can anyone help me out in fixing the issue ? Thanks, Abdul. This email has been Scanned for Viruses! www.newbreak.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ~Mysql performance~
Hi, I have 2 mysql servers with version 5.0.15-standard-log running on redhat es4 installed on 2 different geographic locations. The default storage engine used is innodb on both the servers. I run an insert query on both the servers that inserts 25,00,000 records. first server takes 7.5 hrs, while the second server takes around 17 hrs for the same process. However the configuration file my.cnf is the same for both the machines. The first server has a 1GB RAM and a single processor. While the second server has a 2 GB RAM and it has a dual processor as well. If we go by the hardware specs the second server should take less time as it has a better hardware than the first one. Iam really puzzled . Can anyone help me out in fixing the issue ? How does the harddisk setup looks like on both servers? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ~Mysql performance~
Hi, The following is the hard disk specs for both the servers: server one: ( whose performance is good ) --- class: HD bus: IDE detached: 0 device: hda driver: ignore desc: ST3200822A physical: 16383/16/63 logical: 24321/255/63 - class: HD bus: IDE detached: 0 device: hdb driver: ignore desc: ST3200822A physical: 16383/16/63 logical: 24321/255/63 This has 2 SATA hard disks of 200 GB capacity. There is no RAID configured. server two: ( whose performance is poor ) -- class: HD bus: SCSI detached: 0 device: sda driver: ignore desc: Seagate ST3146707LC host: 0 id: 0 channel: 0 lun: 0 generic: sg0 - class: HD bus: SCSI detached: 0 device: sdb driver: ignore desc: Seagate ST3146707LC host: 0 id: 1 channel: 0 lun: 0 generic: sg1 This has 2 SCSI hard disks of capacity 146 GB each. There is no RAID configured. Thanks, Abdul. On Fri, 2006-04-28 at 12:52 +0200, living liquid | Christian Meisinger wrote: Hi, I have 2 mysql servers with version 5.0.15-standard-log running on redhat es4 installed on 2 different geographic locations. The default storage engine used is innodb on both the servers. I run an insert query on both the servers that inserts 25,00,000 records. first server takes 7.5 hrs, while the second server takes around 17 hrs for the same process. However the configuration file my.cnf is the same for both the machines. The first server has a 1GB RAM and a single processor. While the second server has a 2 GB RAM and it has a dual processor as well. If we go by the hardware specs the second server should take less time as it has a better hardware than the first one. Iam really puzzled . Can anyone help me out in fixing the issue ? How does the harddisk setup looks like on both servers? This email has been Scanned for Viruses! www.newbreak.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ~Mysql performance~
Hi, I would like to make one correction. The server one has 2 IDE hard disks and not SATA hard disks. Thanks, Abdul. On Fri, 2006-04-28 at 16:33 +0530, Mohammed Abdul Azeem wrote: Hi, The following is the hard disk specs for both the servers: server one: ( whose performance is good ) --- class: HD bus: IDE detached: 0 device: hda driver: ignore desc: ST3200822A physical: 16383/16/63 logical: 24321/255/63 - class: HD bus: IDE detached: 0 device: hdb driver: ignore desc: ST3200822A physical: 16383/16/63 logical: 24321/255/63 This has 2 SATA hard disks of 200 GB capacity. There is no RAID configured. server two: ( whose performance is poor ) -- class: HD bus: SCSI detached: 0 device: sda driver: ignore desc: Seagate ST3146707LC host: 0 id: 0 channel: 0 lun: 0 generic: sg0 - class: HD bus: SCSI detached: 0 device: sdb driver: ignore desc: Seagate ST3146707LC host: 0 id: 1 channel: 0 lun: 0 generic: sg1 This has 2 SCSI hard disks of capacity 146 GB each. There is no RAID configured. Thanks, Abdul. On Fri, 2006-04-28 at 12:52 +0200, living liquid | Christian Meisinger wrote: Hi, I have 2 mysql servers with version 5.0.15-standard-log running on redhat es4 installed on 2 different geographic locations. The default storage engine used is innodb on both the servers. I run an insert query on both the servers that inserts 25,00,000 records. first server takes 7.5 hrs, while the second server takes around 17 hrs for the same process. However the configuration file my.cnf is the same for both the machines. The first server has a 1GB RAM and a single processor. While the second server has a 2 GB RAM and it has a dual processor as well. If we go by the hardware specs the second server should take less time as it has a better hardware than the first one. Iam really puzzled . Can anyone help me out in fixing the issue ? How does the harddisk setup looks like on both servers? This email has been Scanned for Viruses! www.newbreak.com This email has been Scanned for Viruses! www.newbreak.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ~Mysql performance~
On 4/28/06, Mohammed Abdul Azeem [EMAIL PROTECTED] wrote: Hi, I have 2 mysql servers with version 5.0.15-standard-log running on redhat es4 installed on 2 different geographic locations. The default storage engine used is innodb on both the servers. I run an insert query on both the servers that inserts 25,00,000 records. first server takes 7.5 hrs, while the second server takes around 17 hrs for the same process. However the configuration file my.cnf is the same for both the machines. The first server has a 1GB RAM and a single processor. While the second server has a 2 GB RAM and it has a dual processor as well. If we go by the hardware specs the second server should take less time as it has a better hardware than the first one. Iam really puzzled . Can anyone help me out in fixing the issue ? Thanks, Abdul. This email has been Scanned for Viruses! www.newbreak.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Is the server2 a slave of server1, and is the same insert on server2 run directly on it or is itpart of a replicated query from server1. This came to my mind as u mentioned the two servers are geographically seperated .. Kishore Jalleda http://kjalleda.googlepages.com/projects
Re: ~Mysql performance~
On Fri, 28 Apr 2006, Mohammed Abdul Azeem wrote: Hi, I have 2 mysql servers with version 5.0.15-standard-log running on redhat es4 installed on 2 different geographic locations. The default storage engine used is innodb on both the servers. I run an insert query on both the servers that inserts 25,00,000 records. first server takes 7.5 hrs, while the second server takes around 17 hrs for the same process. Are these queries run from localhost or from a remote location? It's very unusual for an IDE based system to outperform SCSI. Atle - Flying Crocodile Inc, Unix Systems Administrator -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql performance problems.
As others have suggested , turn your slow query log on in my.cnf , and set your long-query_time, and you can view your slow queries in the *.log file in your data dir, and then try to optimize them, you could also try mytop ( http://jeremy.zawodny.com/mysql/mytop/) , and check your queries in real time..., also check SHOW FULL PROCESSLIST to see what state the query's are in . Kishore Jalleda http://kjalleda.googlepages.com/projects On 3/29/06, Jacob, Raymond A Jr [EMAIL PROTECTED] wrote: After a 23days of running mysql, I have a 3GB database. When I use an application called base(v.1.2.2) a web based intrusion detection analysis console, the mysqld utilization shoots up to over 90% and stays there until the application times out or is terminated. Question: Have I made some error in configuration? When I don't run the application base, mysqld utilization is between 30-50%. Question: What hardware do I need to speed up queries? Question: How do determine if the query is the problem? Data: I used my-large.cnf as the basis of my.cnf. Hardware and OS info: ... FreeBSD 6.0-RELEASE-p5 #0: ... CPU: Intel Pentium III (997.46-MHz 686-class CPU) Origin = GenuineIntel Id = 0x68a Stepping = 10 Features=0x383fbffFPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE,MCA,CMOV,PAT,PSE36,MMX,FXSR,SSE real memory = 1073676288 (1023 MB) avail memory = 1041784832 (993 MB) Observations: Disk Space used: du -am /var/db/mysql | sort -nr | head -20 5259mysql/ 3055mysql/snort 2184mysql/snort_archive 1546mysql/snort_archive/data.MYD 1546mysql/snort/data.MYD 560 mysql/snort/acid_event.MYI 311 mysql/snort/acid_event.MYD 132 mysql/snort_archive/event.MYI 132 mysql/snort/event.MYI 116 mysql/snort_archive/iphdr.MYI 116 mysql/snort/iphdr.MYI 112 mysql/snort_archive/iphdr.MYD 112 mysql/snort/iphdr.MYD 74 mysql/snort_archive/event.MYD 74 mysql/snort/event.MYD 42 mysql/snort_archive/data.MYI 42 mysql/snort/data.MYI 40 mysql/snort_archive/icmphdr.MYI 40 mysql/snort/icmphdr.MYI 35 mysql/snort_archive/icmphdr.MYD ... snort is 3GB snort_archive is 2GB(snort_archive acid and base tables have not been built that is why snort archive is smaller) When the application searches the database, the mysqld utilization goes up to over 90% until the application times out. top last pid: 44263; load averages: 0.95, 0.89, 0.76 up 25+23:49:4416:07:17 49 processes: 2 running, 47 sleeping Mem: 173M Active, 608M Inact, 186M Wired, 29M Cache, 111M Buf, 1660K Free Swap: 2048M Total, 156K Used, 2048M Free PID USERNAME THR PRI NICE SIZERES STATETIME WCPU COMMAND 31890 mysql 15 200 103M 79032K kserel 768:38 93.46% mysqld 49138 www 1 40 17432K 12848K accept 0:23 0.00% httpd 46759 www 1 200 16584K 12084K lockf0:21 0.00% httpd 46764 www 1 40 16632K 12072K accept 0:21 0.00% httpd 46763 www 1 40 16580K 12012K accept 0:20 0.00% httpd 46760 www 1 40 17452K 12872K accept 0:19 0.00% httpd 46762 www 1 40 16568K 12000K accept 0:19 0.00% httpd 46761 www 1 40 16608K 12088K sbwait 0:17 0.00% httpd 68456 www 1 40 16572K 11980K accept 0:17 0.00% httpd 68457 www 1 40 16724K 11824K accept 0:17 0.00% httpd 68458 www 1 40 16980K 11920K accept 0:17 0.00% httpd Processes that run in the background: I run an update process in the background with hope that if I process the alerts from the snort table on a regular basis.o I won't have process a large number( 44,000) alerts first thing in the morning. The update process inserts records into the acid table that result from the join of certain fields from the snort tables. (Schema at http://www.andrew.cmu.edu/user/rdanyliw/snort/acid_db_er_v102.html ) rabid# cat /var/log/base-update.2006-03-28.log 2006-03-28, 00:05:00, Added 3808 alert(s) to the Alert cache 2006-03-28, 01:05:00, Added 5855 alert(s) to the Alert cache 2006-03-28, 02:05:00, Added 4096 alert(s) to the Alert cache 2006-03-28, 03:05:00, Added 4473 alert(s) to the Alert cache 2006-03-28, 04:05:00, Added 4378 alert(s) to the Alert cache 2006-03-28, 05:05:00, Added 4087 alert(s) to the Alert cache 2006-03-28, 06:05:00, Added 5163 alert(s) to the Alert cache 2006-03-28, 07:05:00, Added 4789 alert(s) to the Alert cache 2006-03-28, 08:05:00, Added 4411 alert(s) to the Alert cache 2006-03-28, 09:05:00, Added 4830 alert(s) to the Alert cache 2006-03-28, 10:05:00, Added 4739 alert(s) to the Alert cache 2006-03-28, 11:05:00, Added 5360 alert(s) to the Alert cache 2006-03-28, 12:05:00, Added 7305 alert(s) to the Alert cache 2006-03-28, 13:05:00, Added 8481 alert(s) to the Alert cache 2006-03-28, 14:05:00, Added 60731 alert(s) to the Alert cache 2006-03-28, 15:05:00, Added 44328
Re: mysql performance problems.
Is tat query is the problem ? Then turn on your slow queies and try optimizing those slow queries ? Post your queries and table description for further help :) --Praj On Wed, 29 Mar 2006 12:33:20 -0500 Jacob, Raymond A Jr [EMAIL PROTECTED] wrote: After a 23days of running mysql, I have a 3GB database. When I use an application called base(v.1.2.2) a web based intrusion detection analysis console, the mysqld utilization shoots up to over 90% and stays there until the application times out or is terminated. Question: Have I made some error in configuration? When I don't run the application base, mysqld utilization is between 30-50%. Question: What hardware do I need to speed up queries? Question: How do determine if the query is the problem? Data: I used my-large.cnf as the basis of my.cnf. Hardware and OS info: ... FreeBSD 6.0-RELEASE-p5 #0: ... CPU: Intel Pentium III (997.46-MHz 686-class CPU) Origin = GenuineIntel Id = 0x68a Stepping = 10 Features=0x383fbffFPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE,MCA,CMOV,PAT,PSE36,MMX,FXSR,SSE real memory = 1073676288 (1023 MB) avail memory = 1041784832 (993 MB) Observations: Disk Space used: du -am /var/db/mysql | sort -nr | head -20 5259 mysql/ 3055 mysql/snort 2184 mysql/snort_archive 1546 mysql/snort_archive/data.MYD 1546 mysql/snort/data.MYD 560 mysql/snort/acid_event.MYI 311 mysql/snort/acid_event.MYD 132 mysql/snort_archive/event.MYI 132 mysql/snort/event.MYI 116 mysql/snort_archive/iphdr.MYI 116 mysql/snort/iphdr.MYI 112 mysql/snort_archive/iphdr.MYD 112 mysql/snort/iphdr.MYD 74mysql/snort_archive/event.MYD 74mysql/snort/event.MYD 42mysql/snort_archive/data.MYI 42mysql/snort/data.MYI 40mysql/snort_archive/icmphdr.MYI 40mysql/snort/icmphdr.MYI 35mysql/snort_archive/icmphdr.MYD ... snort is 3GB snort_archive is 2GB(snort_archive acid and base tables have not been built that is why snort archive is smaller) When the application searches the database, the mysqld utilization goes up to over 90% until the application times out. top last pid: 44263; load averages: 0.95, 0.89, 0.76 up 25+23:49:44 16:07:17 49 processes: 2 running, 47 sleeping Mem: 173M Active, 608M Inact, 186M Wired, 29M Cache, 111M Buf, 1660K Free Swap: 2048M Total, 156K Used, 2048M Free PID USERNAME THR PRI NICE SIZERES STATETIME WCPU COMMAND 31890 mysql 15 200 103M 79032K kserel 768:38 93.46% mysqld 49138 www 1 40 17432K 12848K accept 0:23 0.00% httpd 46759 www 1 200 16584K 12084K lockf0:21 0.00% httpd 46764 www 1 40 16632K 12072K accept 0:21 0.00% httpd 46763 www 1 40 16580K 12012K accept 0:20 0.00% httpd 46760 www 1 40 17452K 12872K accept 0:19 0.00% httpd 46762 www 1 40 16568K 12000K accept 0:19 0.00% httpd 46761 www 1 40 16608K 12088K sbwait 0:17 0.00% httpd 68456 www 1 40 16572K 11980K accept 0:17 0.00% httpd 68457 www 1 40 16724K 11824K accept 0:17 0.00% httpd 68458 www 1 40 16980K 11920K accept 0:17 0.00% httpd Processes that run in the background: I run an update process in the background with hope that if I process the alerts from the snort table on a regular basis.o I won't have process a large number( 44,000) alerts first thing in the morning. The update process inserts records into the acid table that result from the join of certain fields from the snort tables. (Schema at http://www.andrew.cmu.edu/user/rdanyliw/snort/acid_db_er_v102.html ) rabid# cat /var/log/base-update.2006-03-28.log 2006-03-28, 00:05:00, Added 3808 alert(s) to the Alert cache 2006-03-28, 01:05:00, Added 5855 alert(s) to the Alert cache 2006-03-28, 02:05:00, Added 4096 alert(s) to the Alert cache 2006-03-28, 03:05:00, Added 4473 alert(s) to the Alert cache 2006-03-28, 04:05:00, Added 4378 alert(s) to the Alert cache 2006-03-28, 05:05:00, Added 4087 alert(s) to the Alert cache 2006-03-28, 06:05:00, Added 5163 alert(s) to the Alert cache 2006-03-28, 07:05:00, Added 4789 alert(s) to the Alert cache 2006-03-28, 08:05:00, Added 4411 alert(s) to the Alert cache 2006-03-28, 09:05:00, Added 4830 alert(s) to the Alert cache 2006-03-28, 10:05:00, Added 4739 alert(s) to the Alert cache 2006-03-28, 11:05:00, Added 5360 alert(s) to the Alert cache 2006-03-28, 12:05:00, Added 7305 alert(s) to the Alert cache 2006-03-28, 13:05:00, Added 8481 alert(s) to the Alert cache 2006-03-28, 14:05:00, Added 60731 alert(s) to the Alert cache 2006-03-28, 15:05:00, Added 44328 alert(s) to the Alert cache 2006-03-28, 15:50:00, Added 13742 alert(s) to the Alert cache 2006-03-28, 15:55:00, Added 607 alert(s) to the Alert cache 2006-03-28, 16:00:00, Added 938 alert(s) to the Alert cache 2006-03-28, 16:05:00, Added 1348
mysql performance problems.
After a 23days of running mysql, I have a 3GB database. When I use an application called base(v.1.2.2) a web based intrusion detection analysis console, the mysqld utilization shoots up to over 90% and stays there until the application times out or is terminated. Question: Have I made some error in configuration? When I don't run the application base, mysqld utilization is between 30-50%. Question: What hardware do I need to speed up queries? Question: How do determine if the query is the problem? Data: I used my-large.cnf as the basis of my.cnf. Hardware and OS info: ... FreeBSD 6.0-RELEASE-p5 #0: ... CPU: Intel Pentium III (997.46-MHz 686-class CPU) Origin = GenuineIntel Id = 0x68a Stepping = 10 Features=0x383fbffFPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE,MCA,CMOV,PAT,PSE36,MMX,FXSR,SSE real memory = 1073676288 (1023 MB) avail memory = 1041784832 (993 MB) Observations: Disk Space used: du -am /var/db/mysql | sort -nr | head -20 5259mysql/ 3055mysql/snort 2184mysql/snort_archive 1546mysql/snort_archive/data.MYD 1546mysql/snort/data.MYD 560 mysql/snort/acid_event.MYI 311 mysql/snort/acid_event.MYD 132 mysql/snort_archive/event.MYI 132 mysql/snort/event.MYI 116 mysql/snort_archive/iphdr.MYI 116 mysql/snort/iphdr.MYI 112 mysql/snort_archive/iphdr.MYD 112 mysql/snort/iphdr.MYD 74 mysql/snort_archive/event.MYD 74 mysql/snort/event.MYD 42 mysql/snort_archive/data.MYI 42 mysql/snort/data.MYI 40 mysql/snort_archive/icmphdr.MYI 40 mysql/snort/icmphdr.MYI 35 mysql/snort_archive/icmphdr.MYD ... snort is 3GB snort_archive is 2GB(snort_archive acid and base tables have not been built that is why snort archive is smaller) When the application searches the database, the mysqld utilization goes up to over 90% until the application times out. top last pid: 44263; load averages: 0.95, 0.89, 0.76 up 25+23:49:4416:07:17 49 processes: 2 running, 47 sleeping Mem: 173M Active, 608M Inact, 186M Wired, 29M Cache, 111M Buf, 1660K Free Swap: 2048M Total, 156K Used, 2048M Free PID USERNAME THR PRI NICE SIZERES STATETIME WCPU COMMAND 31890 mysql 15 200 103M 79032K kserel 768:38 93.46% mysqld 49138 www 1 40 17432K 12848K accept 0:23 0.00% httpd 46759 www 1 200 16584K 12084K lockf0:21 0.00% httpd 46764 www 1 40 16632K 12072K accept 0:21 0.00% httpd 46763 www 1 40 16580K 12012K accept 0:20 0.00% httpd 46760 www 1 40 17452K 12872K accept 0:19 0.00% httpd 46762 www 1 40 16568K 12000K accept 0:19 0.00% httpd 46761 www 1 40 16608K 12088K sbwait 0:17 0.00% httpd 68456 www 1 40 16572K 11980K accept 0:17 0.00% httpd 68457 www 1 40 16724K 11824K accept 0:17 0.00% httpd 68458 www 1 40 16980K 11920K accept 0:17 0.00% httpd Processes that run in the background: I run an update process in the background with hope that if I process the alerts from the snort table on a regular basis.o I won't have process a large number( 44,000) alerts first thing in the morning. The update process inserts records into the acid table that result from the join of certain fields from the snort tables. (Schema at http://www.andrew.cmu.edu/user/rdanyliw/snort/acid_db_er_v102.html ) rabid# cat /var/log/base-update.2006-03-28.log 2006-03-28, 00:05:00, Added 3808 alert(s) to the Alert cache 2006-03-28, 01:05:00, Added 5855 alert(s) to the Alert cache 2006-03-28, 02:05:00, Added 4096 alert(s) to the Alert cache 2006-03-28, 03:05:00, Added 4473 alert(s) to the Alert cache 2006-03-28, 04:05:00, Added 4378 alert(s) to the Alert cache 2006-03-28, 05:05:00, Added 4087 alert(s) to the Alert cache 2006-03-28, 06:05:00, Added 5163 alert(s) to the Alert cache 2006-03-28, 07:05:00, Added 4789 alert(s) to the Alert cache 2006-03-28, 08:05:00, Added 4411 alert(s) to the Alert cache 2006-03-28, 09:05:00, Added 4830 alert(s) to the Alert cache 2006-03-28, 10:05:00, Added 4739 alert(s) to the Alert cache 2006-03-28, 11:05:00, Added 5360 alert(s) to the Alert cache 2006-03-28, 12:05:00, Added 7305 alert(s) to the Alert cache 2006-03-28, 13:05:00, Added 8481 alert(s) to the Alert cache 2006-03-28, 14:05:00, Added 60731 alert(s) to the Alert cache 2006-03-28, 15:05:00, Added 44328 alert(s) to the Alert cache 2006-03-28, 15:50:00, Added 13742 alert(s) to the Alert cache 2006-03-28, 15:55:00, Added 607 alert(s) to the Alert cache 2006-03-28, 16:00:00, Added 938 alert(s) to the Alert cache 2006-03-28, 16:05:00, Added 1348 alert(s) to the Alert cache 2006-03-28, 16:10:02, Added 687 alert(s) to the Alert cache 2006-03-28, 16:15:00, Added 1168 alert(s) to the Alert cache 2006-03-28, 16:20:00, Added 1760 alert(s) to the Alert cache 2006-03-28, 16:25:00, Added 814 alert(s) to the Alert cache 2006-03-28, 16:30:01, Added 617 alert(s) to the Alert cache
Re: mysql performance problems.
Jacob, Raymond A Jr wrote: After a 23days of running mysql, I have a 3GB database. When I use an application called base(v.1.2.2) a web based intrusion detection analysis console, the mysqld utilization shoots up to over 90% and stays there until the application times out or is terminated. Question: Have I made some error in configuration? When I don't run the application base, mysqld utilization is between 30-50%. Question: What hardware do I need to speed up queries? Question: How do determine if the query is the problem? Data: I used my-large.cnf as the basis of my.cnf. Hardware and OS info: ... FreeBSD 6.0-RELEASE-p5 #0: ... CPU: Intel Pentium III (997.46-MHz 686-class CPU) Origin = GenuineIntel Id = 0x68a Stepping = 10 Features=0x383fbffFPU,VME,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE,MCA,CMOV,PAT,PSE36,MMX,FXSR,SSE real memory = 1073676288 (1023 MB) avail memory = 1041784832 (993 MB) Observations: Disk Space used: du -am /var/db/mysql | sort -nr | head -20 5259mysql/ 3055mysql/snort 2184mysql/snort_archive 1546mysql/snort_archive/data.MYD 1546mysql/snort/data.MYD 560 mysql/snort/acid_event.MYI 311 mysql/snort/acid_event.MYD 132 mysql/snort_archive/event.MYI 132 mysql/snort/event.MYI 116 mysql/snort_archive/iphdr.MYI 116 mysql/snort/iphdr.MYI 112 mysql/snort_archive/iphdr.MYD 112 mysql/snort/iphdr.MYD 74 mysql/snort_archive/event.MYD 74 mysql/snort/event.MYD 42 mysql/snort_archive/data.MYI 42 mysql/snort/data.MYI 40 mysql/snort_archive/icmphdr.MYI 40 mysql/snort/icmphdr.MYI 35 mysql/snort_archive/icmphdr.MYD ... snort is 3GB snort_archive is 2GB(snort_archive acid and base tables have not been built that is why snort archive is smaller) When the application searches the database, the mysqld utilization goes up to over 90% until the application times out. top last pid: 44263; load averages: 0.95, 0.89, 0.76 up 25+23:49:4416:07:17 49 processes: 2 running, 47 sleeping Mem: 173M Active, 608M Inact, 186M Wired, 29M Cache, 111M Buf, 1660K Free Swap: 2048M Total, 156K Used, 2048M Free PID USERNAME THR PRI NICE SIZERES STATETIME WCPU COMMAND 31890 mysql 15 200 103M 79032K kserel 768:38 93.46% mysqld 49138 www 1 40 17432K 12848K accept 0:23 0.00% httpd 46759 www 1 200 16584K 12084K lockf0:21 0.00% httpd 46764 www 1 40 16632K 12072K accept 0:21 0.00% httpd 46763 www 1 40 16580K 12012K accept 0:20 0.00% httpd 46760 www 1 40 17452K 12872K accept 0:19 0.00% httpd 46762 www 1 40 16568K 12000K accept 0:19 0.00% httpd 46761 www 1 40 16608K 12088K sbwait 0:17 0.00% httpd 68456 www 1 40 16572K 11980K accept 0:17 0.00% httpd 68457 www 1 40 16724K 11824K accept 0:17 0.00% httpd 68458 www 1 40 16980K 11920K accept 0:17 0.00% httpd Processes that run in the background: I run an update process in the background with hope that if I process the alerts from the snort table on a regular basis.o I won't have process a large number( 44,000) alerts first thing in the morning. The update process inserts records into the acid table that result from the join of certain fields from the snort tables. (Schema at http://www.andrew.cmu.edu/user/rdanyliw/snort/acid_db_er_v102.html ) rabid# cat /var/log/base-update.2006-03-28.log 2006-03-28, 00:05:00, Added 3808 alert(s) to the Alert cache 2006-03-28, 01:05:00, Added 5855 alert(s) to the Alert cache 2006-03-28, 02:05:00, Added 4096 alert(s) to the Alert cache 2006-03-28, 03:05:00, Added 4473 alert(s) to the Alert cache 2006-03-28, 04:05:00, Added 4378 alert(s) to the Alert cache 2006-03-28, 05:05:00, Added 4087 alert(s) to the Alert cache 2006-03-28, 06:05:00, Added 5163 alert(s) to the Alert cache 2006-03-28, 07:05:00, Added 4789 alert(s) to the Alert cache 2006-03-28, 08:05:00, Added 4411 alert(s) to the Alert cache 2006-03-28, 09:05:00, Added 4830 alert(s) to the Alert cache 2006-03-28, 10:05:00, Added 4739 alert(s) to the Alert cache 2006-03-28, 11:05:00, Added 5360 alert(s) to the Alert cache 2006-03-28, 12:05:00, Added 7305 alert(s) to the Alert cache 2006-03-28, 13:05:00, Added 8481 alert(s) to the Alert cache 2006-03-28, 14:05:00, Added 60731 alert(s) to the Alert cache 2006-03-28, 15:05:00, Added 44328 alert(s) to the Alert cache 2006-03-28, 15:50:00, Added 13742 alert(s) to the Alert cache 2006-03-28, 15:55:00, Added 607 alert(s) to the Alert cache 2006-03-28, 16:00:00, Added 938 alert(s) to the Alert cache 2006-03-28, 16:05:00, Added 1348 alert(s) to the Alert cache 2006-03-28, 16:10:02, Added 687 alert(s) to the Alert cache 2006-03-28, 16:15:00, Added 1168 alert(s) to the Alert cache 2006-03-28, 16:20:00, Added 1760 alert(s) to the Alert cache 2006-03-28, 16:25:00, Added 814 alert(s) to the Alert cache 2006-03-28, 16:30:01, Added
Bad MySQL performance with LEFT JOINS in combination with BIGINT(16) Keys
Hi, I just ran into a very strange problem. I have two simple tables with BIGINT(16) PKs. The primary table has 7500 records and the secondars has 15000. If I execute: SELECT * FROM lad JOIN snd ON lad.id = snd.lad_id The query takes 8 seconds. If I execute this one: SELECT * FROM lad LEFT JOIN snd ON lad.id = snd.lad_id I quit the query after 5 minutes. Why is this so? Chritofer Dutz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bad MySQL performance with LEFT JOINS in combination with BIGINT(16) Keys
Christofer Dutz wrote: Hi, I just ran into a very strange problem. I have two simple tables with BIGINT(16) PKs. The primary table has 7500 records and the secondars has 15000. If I execute: SELECT * FROM lad JOIN snd ON lad.id = snd.lad_id The query takes 8 seconds. If I execute this one: SELECT * FROM lad LEFT JOIN snd ON lad.id = snd.lad_id I quit the query after 5 minutes. Why is this so? Chritofer Dutz Christofer, You'll need to provide the list with more information for anyone to be able to help you. What version of MySQL are you running? Can you paste the output of show create table snd; show create table lad; Also, do you have other processes using the server that could have locked one of the tables? When the query is taking a long time, try opening a second connection to the server and running show full processlist; to see what else is going on. Regards, Devananda vdv -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bad MySQL performance with LEFT JOINS in combination with BIGINT(16)Keys
Ok ... here all the information I could find: CREATE TABLE `disco_lad` ( `id` bigint(16) unsigned NOT NULL auto_increment, `import_datum` date default NULL, `import_zeit` time default NULL, `gst` smallint(4) unsigned default '0', `gst_bez` varchar(40) default NULL, `betrieb` tinyint(2) unsigned default '0', `auft_nr` bigint(10) unsigned default NULL, `auft_referenz` varchar(40) default NULL, `lad_datum` date default NULL, `lad_von_zeit` time default NULL, `lad_bis_zeit` time default NULL, `lad_land` char(3) default NULL, `lad_plz` varchar(6) default NULL, `lad_lon_deg` double default NULL, `lad_lat_deg` double default NULL, `lad_ort` varchar(40) default NULL, `lad_str` varchar(40) default NULL, `lad_fix` tinyint(1) default '0', `frankatur` varchar(20) default NULL, `name_vers` varchar(40) default NULL, `name_auftg` varchar(40) default NULL, `pnr_vers` varchar(10) default NULL, `pnr_auftg` varchar(10) default NULL, `pnr_ladeort` varchar(10) default NULL, `lad_gew` int(6) unsigned default '0', `lad_meter` float(3,2) unsigned default '0.00', `lad_fracht` float(5,2) unsigned default '0.00', `lad_lkw_typ` varchar(30) default NULL, `lad_art` varchar(20) default NULL, `lad_gg` tinyint(1) default '0', `lad_tausch` tinyint(1) default '0', `lad_last` mediumint(6) default '0', `entlad_datum` date default NULL, `entlad_von_zeit` time default NULL, `entlad_bis_zeit` time default NULL, `entlad_land` char(3) default NULL, `entlad_plz` varchar(6) default NULL, `entlad_lon_deg` double default NULL, `entlad_lat_deg` double default NULL, `entlad_ort` varchar(40) default NULL, `entlad_str` varchar(40) default NULL, `entlad_fix` tinyint(1) default '0', `lad_hinweis` varchar(200) default NULL, `pnr_empf` varchar(10) default NULL, `pnr_entladeort` varchar(10) default NULL, `lad_disp_name` varchar(40) default NULL, `lad_disp_tel` varchar(20) default NULL, `lad_disp_mobil` varchar(20) default NULL, `lad_disp_fax` varchar(20) default NULL, `lad_disp_email` varchar(30) default NULL, `lad_disp_kontakt` tinyint(1) default '0', `export_gst` smallint(4) unsigned default '0', `export_betrieb` tinyint(2) unsigned default '0', `export_datum` date default NULL, `export_zeit` time default NULL, `export_disp_name` varchar(40) default NULL, `export_disp_tel` varchar(20) default NULL, `export_disp_mobil` varchar(20) default NULL, `export_disp_fax` varchar(20) default NULL, `export_disp_email` varchar(30) default NULL, `export_ref_auft` varchar(10) default NULL, `state` tinyint(3) unsigned default NULL, `modify_data` datetime default NULL, `modify_user` varchar(20) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 CREATE TABLE `disco_snd` ( `lad_id` bigint(16) unsigned NOT NULL default '0', `gst` smallint(4) unsigned default NULL, `betrieb` tinyint(2) unsigned default NULL, `auft_nr` bigint(10) unsigned default NULL, `snd_gew` mediumint(6) unsigned default NULL, `snd_meter` decimal(3,2) default NULL, `snd_signum` varchar(20) default NULL, `snd_inhalt` varchar(40) default NULL, `lademittel_bez` varchar(40) default NULL, `lademittel_code` smallint(4) unsigned default NULL, `lademittel_anz` mediumint(5) unsigned default NULL, `snd_gg_schl` varchar(6) default NULL, `snd_gg_bez` varchar(40) default NULL, `state` tinyint(3) unsigned default NULL, `modify_date` datetime default NULL, `modify_user` varchar(20) default NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COMMENT='InnoDB free: 107520 kB' I am using a MySQL 4.1.13 on a Redhat Linux server and accessing it using the new version of the JDBC driver from a windows XP client. While performing the query I used the MySQL Admin-Tool to check the server Load and the load shown there was only minimal. The internet-connection can't be the problem, since The query returns the same results and one is fast and the other one realy slow. Chris Devananda wrote: Christofer Dutz wrote: Hi, I just ran into a very strange problem. I have two simple tables with BIGINT(16) PKs. The primary table has 7500 records and the secondars has 15000. If I execute: SELECT * FROM lad JOIN snd ON lad.id = snd.lad_id The query takes 8 seconds. If I execute this one: SELECT * FROM lad LEFT JOIN snd ON lad.id = snd.lad_id I quit the query after 5 minutes. Why is this so? Chritofer Dutz Christofer, You'll need to provide the list with more information for anyone to be able to help you. What version of MySQL are you running? Can you paste the output of show create table snd; show create table lad; Also, do you have other processes using the server that could have locked one of the tables? When the query is taking a long time, try opening a second connection to the server and running show full processlist; to see what else is going on. Regards, Devananda vdv -- MySQL General Mailing List For list archives:
Re: Bad MySQL performance with LEFT JOINS in combination with BIGINT(16)Keys
Looks like you don't have index on `lad_id` field in second table. So when you do the first query, mysql first does a full scan of 2nd table, then does eqref lookup for 1st table, which is rather fast operation. But when you do the left join, it scans first table and for each value of id does a scan of second table, which is performance killer. -- Alexey Polyakov -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb thread concurrency size [mysql performance tuning]
Clyde Lewis wrote: Hey guys, I'm looking for any best practices or a formula that is commonly used in setting the value for thread concurrency in the configuration file. I have 24 instances running on a sun 2900 server with 32GB or ram. Here is a sample of my configuration file. The best practice for setting innodb_thread_concurrency is # of resources per instance * 2, so if you had 2 CPUs and 2 hard drives you wanted to use for a MySQL instance, set it to 8, which is the default. Sometimes the *2 factor could be *1 to get the best system performance. If you want to throttle all of your instances so no one instance hogs the system, you might set the innodb_thread_concurrency down to 2. If you wanted one to use all of your system resources, then set it much higher. If you ever need more involved assistance from the MySQL Professional Services team, you might look at these packages: http://www.mysql.com/consulting/packaged/performance.html http://www.mysql.com/consulting/packaged/rapidresponse.html Regards, Josh -- Josh Chamas Director, Professional Services MySQL Inc., www.mysql.com Get More with MySQL! www.mysql.com/consulting #use this line mfor mysql 4.1 old-passwords server-id = 2216 net_buffer_length=65536 net_read_timeout=120 net_write_timeout=180 key_buffer=64M max_allowed_packet=1M table_cache=2048 sort_buffer=1M record_buffer=1M myisam_sort_buffer_size=16M max_connections=2500 thread_cache=8 # Try number of CPU's*2 thread_concurrency=4 query_cache_size=256M query_cache_limit=128K #only availble in 4.1 innodb_file_per_table innodb_buffer_pool_size=500M innodb_additional_mem_pool_size=25M innodb_log_archive=0 innodb_log_files_in_group=3 innodb_log_file_size=100M innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=2 innodb_file_io_threads=4 innodb_lock_wait_timeout=30 #innodb_flush_method=fdatasync #innodb_fast_shutdown=1 innodb_thread_concurrency=5 transaction-isolation = READ-UNCOMMITTED [mysqld140] bind-address=xxx.xxx.xxx.xxx old-passwords mysqld=/usr/local/mysql/bin/mysqld_safe pid-file=/p01/abq/mysqladmin/abq_pid basedir=/usr/local/mysql datadir=/p01/abq/mysqldata socket=/p01/abq/mysqladmin/mysql.sock port=3306 local-infile=1 user=mysql tmpdir = /tmp/abq/ log = /p01/abq/mysqllogs log-bin = /p01/abq/mysqllogs/abq-bin log-err = /p01/abq/mysqllogs/abq.err log-slow-queries = /p01/abq/mysqllogs/abq_slow_query.log innodb_file_per_table set-variable = innodb_buffer_pool_size=500M set-variable = innodb_additional_mem_pool_size=25M innodb_data_home_dir = /p01/abq/mysqldata/innodb/ innodb_data_file_path = ibdata1_abq:100M:autoextend:max:4096M #.._log_arch_dir must be the same #as .._log_group_home_dir innodb_log_group_home_dir = /p01/abq/mysqladmin/iblogs innodb_log_arch_dir = /p01/abq/mysqladmin/iblogs -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Too Many Queries Have Writing To Net Status [mysql performance tuning]
Kishore Jalleda wrote: Hi All, We have a production Database, running mysql 4.0.17 , on a Poweredge 2650 with 3 GB RAM, and dual Xeon 2.4 GHZ, the server averages between 100 and 200 qps ( ,also and the CPU/MEM load is pretty low and is extremely fast except for once in a while may be 1 in 1 queries take mote than 3 seconds to execute (slow_query_time), and the status of the query is always writing to net Initially the NIC was an Intel 10/100, then the slow queries were a lot more, then I started using the Broadcom Gigabit port that comes with the 2650, and recently I also started load balancing the traffic between the two Broadcomm NIC'S using the Basp Module, so that I effectively have a 200 MBPS full duplex link( the switch connected has only FE ports), with this setting the slow queries have decreased a lot, but they do come up once in a while.. There is no lock time in any of them, I have tried using both InnoDB and MyISAM, but they dont seem to go away, so I was wondering why there are still few queries which still have writing to Net in the status line and sometimes take 30-50 seconds to execute, note that if the queries are run in any mysql client they run in less than 0.005 seconds, so I am pretty sure its the network thats the bottleneck, and buying a Gigabit capable switch is not an option now, also sometimes the traffic on the server goes upto 35MBPS. So why is mysql not sending the results back to the client and sometimes waiting soo long, also while mysql is waiting for a long query( in the Writing to Net status) hundreds of other queries are executed extrelmely fast. I am not very familiar with how mysql handles network packets, and why its holding back ... If the network is a bottleneck, certainly this could result in long Writing to Net status. One could imagine network congestion causing repeated TCP/IP retries, etc. It might just be that your server is heavily loaded and certain threads are not getting enough resources to finish processing. Things like this are usually far to complex to diagnose simply, and if you want expert help, I would recommend you engage with our professional services team with either of these packaged consulting solutions: http://www.mysql.com/consulting/packaged/performance.html http://www.mysql.com/consulting/packaged/rapidresponse.html Regards, Josh -- Josh Chamas Director, Professional Services MySQL Inc., www.mysql.com Get More with MySQL! www.mysql.com/consulting -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mySQL Performance Problems - Help !!
Hi all, I'm pretty new to the list, so please be kind :) I'm having serious problems with our core mysql server. We are running a Dell Poweredge 2850 with dual Xeon 3.0 processors, RAID 5 and 1Gb memory. There are 3 main databases running on this machine, one is a freeradius database, one is for vpopmail and the other is for cacti (MRTG based tool). At the moment, we find the suddenly, our mail server or cacti server will establish several hundred connections to the database that just seem to be sit in an unauthenticated state. E.G. | 6452 | unauthenticated user | xxx.xxx.xxx.xxx:40923 | NULL | Connect | NULL | login | NULL | | 6453 | unauthenticated user | xxx.xxx.xxx.xxx:40924 | NULL | Connect | NULL | login | NULL | The server starts dragging it's heals and mail and cacti virtually stop altogether. There does not seem to be any unusual CPU / I/O or memory usage when this happens. Can anyone point me in the right direction here ? I'm more than happy to provide any configuration information that is required - but this is killing us and I need to know what is going on. We have run this server for several years (recently we upgraded the hardware without problems) and we have never experienced anything like this. I am at my wits end and not having had any formal training in mySQL servers, I simply don't know what I should be looking at next. Please - if anyone can give me any insight at all on this it would be MOST appreciated. Warm regards, Callum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mySQL Performance Problems - Help !!
my.cnf add this: skip-name-resolve under [mysqld] On 8/29/05, Callum McGillivray [EMAIL PROTECTED] wrote: Hi all, I'm pretty new to the list, so please be kind :) I'm having serious problems with our core mysql server. We are running a Dell Poweredge 2850 with dual Xeon 3.0 processors, RAID 5 and 1Gb memory. There are 3 main databases running on this machine, one is a freeradius database, one is for vpopmail and the other is for cacti (MRTG based tool). At the moment, we find the suddenly, our mail server or cacti server will establish several hundred connections to the database that just seem to be sit in an unauthenticated state. E.G. | 6452 | unauthenticated user | xxx.xxx.xxx.xxx:40923 | NULL | Connect | NULL | login | NULL | | 6453 | unauthenticated user | xxx.xxx.xxx.xxx:40924 | NULL | Connect | NULL | login | NULL | The server starts dragging it's heals and mail and cacti virtually stop altogether. There does not seem to be any unusual CPU / I/O or memory usage when this happens. Can anyone point me in the right direction here ? I'm more than happy to provide any configuration information that is required - but this is killing us and I need to know what is going on. We have run this server for several years (recently we upgraded the hardware without problems) and we have never experienced anything like this. I am at my wits end and not having had any formal training in mySQL servers, I simply don't know what I should be looking at next. Please - if anyone can give me any insight at all on this it would be MOST appreciated. Warm regards, Callum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Best Wishes, Xiaodong -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mySQL Performance Problems - Help !!
If you suddenly are spiking in unauthenticated connections, you may be the target of a network attack. This could be just a random probe, you may be a random target or someone may be targeting you. Although if someone were specifically targeting you, you would probably be down. I would check where the connections are coming from, especially if this amount of traffic is not typical. Perhaps you can set a filter on your router to limit the number of connections from that IP range or deny that range altogether. Normally you don't allow direct access to MySQL from the outside, access is usually done through SendMail/ Postifx or Apache (PHP, Perl, etc), but your requirements may be different. On Aug 29, 2005, at 4:58 AM, Callum McGillivray wrote: Hi all, I'm pretty new to the list, so please be kind :) I'm having serious problems with our core mysql server. We are running a Dell Poweredge 2850 with dual Xeon 3.0 processors, RAID 5 and 1Gb memory. There are 3 main databases running on this machine, one is a freeradius database, one is for vpopmail and the other is for cacti (MRTG based tool). At the moment, we find the suddenly, our mail server or cacti server will establish several hundred connections to the database that just seem to be sit in an unauthenticated state. E.G. | 6452 | unauthenticated user | xxx.xxx.xxx.xxx:40923 | NULL | Connect | NULL | login | NULL | | 6453 | unauthenticated user | xxx.xxx.xxx.xxx:40924 | NULL | Connect | NULL | login | NULL | The server starts dragging it's heals and mail and cacti virtually stop altogether. There does not seem to be any unusual CPU / I/O or memory usage when this happens. Can anyone point me in the right direction here ? I'm more than happy to provide any configuration information that is required - but this is killing us and I need to know what is going on. We have run this server for several years (recently we upgraded the hardware without problems) and we have never experienced anything like this. I am at my wits end and not having had any formal training in mySQL servers, I simply don't know what I should be looking at next. Please - if anyone can give me any insight at all on this it would be MOST appreciated. Warm regards, Callum -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BLOB in mysql ----- performance issue.
Hi, 2005/8/22, Kane Wilson [EMAIL PROTECTED]: Thanx , as i understood , i have to keet the raw data in folders and has to be stored that relavent file path's into mysql database, so can u kindly tel me , how it is possible to get the file path ( absolute ot realtive ) of that certain files which are uploaded in to the database from PHP ? what is the function i have to use ? It depends on your application, but you usually choose yourself the location where to move the uploaded file. Usually uploaded file are stored in /tmp temporally : http://uk.php.net/manual/en/function.move-uploaded-file.php Your application move itself the file (hence know the path) and you insert the path in your DB. so that's meant i dont try to hard code file path in my program , This is not actually mysql question ...sorry for asking such out of list questions. Thanx in advance , Kane. Pooly [EMAIL PROTECTED] wrote: 2005/8/20, Kane Wilson : hi Friends, I have a WAP portal which is running based on mysql database. That's meant that contents has been stored in the data base. wallpapers , themes, ringtones, games ...etc data type as BLOB. upto now now problems with the content retrieving. WAP site hosted in REDHAT Linux BOX. I will leave my question by point form , 1. http://www.zend.com/zend/trick/tricks-sept-2001.php according to info of this link , it says there is performance issue when using BLOB for data storing and retriving in mysql and he has recomended some statndard file sharing / accessing methods to use . for instance samba or NFS - in linux. is this happening in mysql ? does it has a solution in mysql ? what could be the best way to use mysql to store / retrive my contents via the wap site ? I'm using mysql bcause , keeping data in a database is very easy to do a site search , rather than keeping contents in folders . The approach generally used is to store in your DB, information concerning your file (its type : ringtones, themes, wallpaper), its size, keywords and everything you find useful, and then keep the file on disk (and having its path sotred in the DB as well). You move all load from serving the files (ringtones, wallpaper) to the FS which does it very well _and_ can be cached by proxies, browser (less load for the server). So you an still do a site search since you keep all the useful information in your DB, but raw data stay on disk. For example if you need to send a ringtone which is in your DB, the browsersend the request, Apache receive it, load PHP/Perl, which then connect to the DB... If the file is on disk, you remove the last 2 operations which willmakethe whole operation faster. -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: BLOB in mysql ----- performance issue.
2005/8/20, Kane Wilson [EMAIL PROTECTED]: hi Friends, I have a WAP portal which is running based on mysql database. That's meant that contents has been stored in the data base. wallpapers , themes, ringtones, games ...etc data type as BLOB. upto now now problems with the content retrieving. WAP site hosted in REDHAT Linux BOX. I will leave my question by point form , 1. http://www.zend.com/zend/trick/tricks-sept-2001.php according to info of this link , it says there is performance issue when using BLOB for data storing and retriving in mysql and he has recomended some statndard file sharing / accessing methods to use . for instance samba or NFS - in linux. is this happening in mysql ? does it has a solution in mysql ? what could be the best way to use mysql to store / retrive my contents via the wap site ? I'm using mysql bcause , keeping data in a database is very easy to do a site search , rather than keeping contents in folders . The approach generally used is to store in your DB, information concerning your file (its type : ringtones, themes, wallpaper), its size, keywords and everything you find useful, and then keep the file on disk (and having its path sotred in the DB as well). You move all load from serving the files (ringtones, wallpaper) to the FS which does it very well _and_ can be cached by proxies, browser (less load for the server). So you an still do a site search since you keep all the useful information in your DB, but raw data stay on disk. For example if you need to send a ringtone which is in your DB, the browsersend the request, Apache receive it, load PHP/Perl, which then connect to the DB... If the file is on disk, you remove the last 2 operations which willmakethe whole operation faster. -- Pooly Webzine Rock : http://www.w-fenec.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
BLOB in mysql ----- performance issue.
hi Friends, I have a WAP portal which is running based on mysql database. That's meant that contents has been stored in the data base. wallpapers , themes, ringtones, games ...etc data type as BLOB. upto now now problems with the content retrieving. WAP site hosted in REDHAT Linux BOX. I will leave my question by point form , 1. http://www.zend.com/zend/trick/tricks-sept-2001.php according to info of this link , it says there is performance issue when using BLOB for data storing and retriving in mysql and he has recomended some statndard file sharing / accessing methods to use . for instance samba or NFS - in linux. is this happening in mysql ? does it has a solution in mysql ? what could be the best way to use mysql to store / retrive my contents via the wap site ? I'm using mysql bcause , keeping data in a database is very easy to do a site search , rather than keeping contents in folders . 2. i have kept two databases in my mysql servers . for example abc , xyz. for the database abc , we are doing the content uploads. xyz for content downloads. in abc .there are more fields . a,b,c,d,e,f,g,.s in xyz there are more and different fields .aa,bb.cc,dd,eess so i wanted to transfer that contents get stored in abc to xyz. not all fields . few from abc to xyz. how it could be done ? can somebody help me ? Thanx in advance, Kane. - Do you Yahoo!? Read only the mail you want - Yahoo! Mail SpamGuard.
