Re: [Bacula-users] Low request Mysql
Hi, I try to optimize mysql with some infos find on the web, but that not better :s For the backup, it take 11 min to save datas, but 1 hour to update mysql table... My my.cnf : [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid query_cache_size=32M tmp_table_size=256M max_heap_table_size=256M thread_cache_size=4 table_cache=512 key_buffer_size=256M On Mon, 26 Mar 2012 12:13:28 -0400, John Drescher wrote: On Mon, Mar 26, 2012 at 12:09 PM, Nicolas wrote: good idea ! i'll try it asap and let you know. Also its best if you can have the database on a different raid than the storage or put the database on an SSD. John M. Drescher -- Nicolas http://www.shivaserv.fr [2] Links: -- [1] mailto:nico...@shivaserv.fr [2] http://www.shivaserv.fr -- This SF email is sponsosred by: Try Windows Azure free for 90 days Click Here http://p.sf.net/sfu/sfd2d-msazure___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Low request Mysql
Oups, I made a mistake on my conf, parameters are not on [mysqld]... On Tue, 27 Mar 2012 09:45:54 +0200, Nicolas wrote: Hi, I try to optimize mysql with some infos find on the web, but that not better :s For the backup, it take 11 min to save datas, but 1 hour to update mysql table... My my.cnf : [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid query_cache_size=32M tmp_table_size=256M max_heap_table_size=256M thread_cache_size=4 table_cache=512 key_buffer_size=256M On Mon, 26 Mar 2012 12:13:28 -0400, John Drescher wrote: On Mon, Mar 26, 2012 at 12:09 PM, Nicolas wrote: good idea ! i'll try it asap and let you know. Also its best if you can have the database on a different raid than the storage or put the database on an SSD. John M. Drescher -- Nicolas http://www.shivaserv.fr [2] -- Nicolas http://www.shivaserv.fr [3] Links: -- [1] mailto:nico...@shivaserv.fr [2] http://www.shivaserv.fr [3] http://www.shivaserv.fr -- This SF email is sponsosred by: Try Windows Azure free for 90 days Click Here http://p.sf.net/sfu/sfd2d-msazure___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Low request Mysql
On Tue, 27 Mar 2012 10:28:01 +0200, Nicolas wrote: Oups, I made a mistake on my conf, parameters are not on [mysqld]... On Tue, 27 Mar 2012 09:45:54 +0200, Nicolas wrote: Hi, I try to optimize mysql with some infos find on the web, but that not better :s For the backup, it take 11 min to save datas, but 1 hour to update mysql table... My my.cnf : [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid query_cache_size=32M tmp_table_size=256M max_heap_table_size=256M thread_cache_size=4 table_cache=512 key_buffer_size=256M On Mon, 26 Mar 2012 12:13:28 -0400, John Drescher wrote: On Mon, Mar 26, 2012 at 12:09 PM, Nicolas wrote: good idea ! i'll try it asap and let you know. Also its best if you can have the database on a different raid than the storage or put the database on an SSD. John M. Drescher -- Nicolas http://www.shivaserv.fr [2] Hello, Each database server is different. If you want to perform some tuning, you can help yourself with tuning-primer.sh + mysqltuner.pl. They provide good metrics to start with. If you are facing table locking issue, perhaps you should migrate to InnoDB (if you use Mysql 5.1, use the InnoDB Plugin, not the embedded one) HTH. Jérôme Blion (Lyon, France) -- This SF email is sponsosred by: Try Windows Azure free for 90 days Click Here http://p.sf.net/sfu/sfd2d-msazure ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Low request Mysql
I tried, but no changes.. Mysqltuner give it for performance : Performance Metrics - [--] Up for: 2h 40m 21s (171K q [17.787 qps], 23 conn, TX: 31M, RX: 22M) [--] Reads / Writes: 0% / 100% [--] Total buffers: 572.0M global + 2.7M per thread (151 max threads) [OK] Maximum possible memory usage: 987.2M (49% of installed RAM) [OK] Slow queries: 0% (1/171K) [OK] Highest usage of available connections: 1% (2/151) [OK] Key buffer size / total MyISAM indexes: 256.0M/130.9M [OK] Key buffer hit rate: 99.3% (2M cached / 16K reads) [OK] Query cache efficiency: 56.9% (95 cached / 167 selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 9% (2 temp sorts / 21 sorts) [!!] Temporary tables created on disk: 28% (11 on disk / 38 total) [OK] Thread cache hit rate: 91% (2 created / 23 connections) [OK] Table cache hit rate: 74% (52 open / 70 opened) [OK] Open file limit used: 8% (99/1K) [OK] Table locks acquired immediately: 100% (237 immediate / 237 locks) [!!] Connections aborted: 8% On Tue, 27 Mar 2012 11:55:41 +0200, Jérôme Blion wrote: On Tue, 27 Mar 2012 10:28:01 +0200, Nicolas wrote: Oups, I made a mistake on my conf, parameters are not on [mysqld]... On Tue, 27 Mar 2012 09:45:54 +0200, Nicolas wrote: Hi, I try to optimize mysql with some infos find on the web, but that not better :s For the backup, it take 11 min to save datas, but 1 hour to update mysql table... My my.cnf : [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid query_cache_size=32M tmp_table_size=256M max_heap_table_size=256M thread_cache_size=4 table_cache=512 key_buffer_size=256M On Mon, 26 Mar 2012 12:13:28 -0400, John Drescher wrote: On Mon, Mar 26, 2012 at 12:09 PM, Nicolas wrote: good idea ! i'll try it asap and let you know. Also its best if you can have the database on a different raid than the storage or put the database on an SSD. John M. Drescher -- Nicolas http://www.shivaserv.fr [1] [2] Hello, Each database server is different. If you want to perform some tuning, you can help yourself with tuning-primer.sh + mysqltuner.pl. They provide good metrics to start with. If you are facing table locking issue, perhaps you should migrate to InnoDB (if you use Mysql 5.1, use the InnoDB Plugin, not the embedded one) HTH. Jérôme Blion (Lyon, France) -- This SF email is sponsosred by: Try Windows Azure free for 90 days Click Here http://p.sf.net/sfu/sfd2d-msazure [2] ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net [3] https://lists.sourceforge.net/lists/listinfo/bacula-users [4] -- Nicolas http://www.shivaserv.fr [5] Links: -- [1] http://www.shivaserv.fr [2] http://p.sf.net/sfu/sfd2d-msazure [3] mailto:Bacula-users@lists.sourceforge.net [4] https://lists.sourceforge.net/lists/listinfo/bacula-users [5] http://www.shivaserv.fr -- This SF email is sponsosred by: Try Windows Azure free for 90 days Click Here http://p.sf.net/sfu/sfd2d-msazure___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Low request Mysql
On Tue, 27 Mar 2012 13:10:02 +0200, Nicolas wrote: I tried, but no changes.. Mysqltuner give it for performance : Performance Metrics - [--] Up for: 2h 40m 21s (171K q [17.787 qps], 23 conn, TX: 31M, RX: 22M) [--] Reads / Writes: 0% / 100% [--] Total buffers: 572.0M global + 2.7M per thread (151 max threads) [OK] Maximum possible memory usage: 987.2M (49% of installed RAM) [OK] Slow queries: 0% (1/171K) [OK] Highest usage of available connections: 1% (2/151) [OK] Key buffer size / total MyISAM indexes: 256.0M/130.9M [OK] Key buffer hit rate: 99.3% (2M cached / 16K reads) [OK] Query cache efficiency: 56.9% (95 cached / 167 selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 9% (2 temp sorts / 21 sorts) [!!] Temporary tables created on disk: 28% (11 on disk / 38 total) [OK] Thread cache hit rate: 91% (2 created / 23 connections) [OK] Table cache hit rate: 74% (52 open / 70 opened) [OK] Open file limit used: 8% (99/1K) [OK] Table locks acquired immediately: 100% (237 immediate / 237 locks) [!!] Connections aborted: 8% On Tue, 27 Mar 2012 11:55:41 +0200, Jérôme Blion wrote: On Tue, 27 Mar 2012 10:28:01 +0200, Nicolas wrote: Oups, I made a mistake on my conf, parameters are not on [mysqld]... On Tue, 27 Mar 2012 09:45:54 +0200, Nicolas wrote: Hi, I try to optimize mysql with some infos find on the web, but that not better :s For the backup, it take 11 min to save datas, but 1 hour to update mysql table... My my.cnf : [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid query_cache_size=32M tmp_table_size=256M max_heap_table_size=256M thread_cache_size=4 table_cache=512 key_buffer_size=256M On Mon, 26 Mar 2012 12:13:28 -0400, John Drescher wrote: On Mon, Mar 26, 2012 at 12:09 PM, Nicolas wrote: good idea ! i'll try it asap and let you know. Also its best if you can have the database on a different raid than the storage or put the database on an SSD. John M. Drescher -- Nicolas http://www.shivaserv.fr [1] [2] Hello, Each database server is different. If you want to perform some tuning, you can help yourself with tuning-primer.sh + mysqltuner.pl. They provide good metrics to start with. If you are facing table locking issue, perhaps you should migrate to InnoDB (if you use Mysql 5.1, use the InnoDB Plugin, not the embedded one) HTH. Jérôme Blion (Lyon, France) Hello, Please don't obfuscate anything. tuning-primer provides more metrics data. Best regards. Jerome Blion. -- This SF email is sponsosred by: Try Windows Azure free for 90 days Click Here http://p.sf.net/sfu/sfd2d-msazure ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Low request Mysql
Hi, tuning-primer send me some infos, I'll try this night some parameters to see :) thanks Nicolas http://www.shivaserv.fr Le 27 mars 2012 à 16:23, Jérôme Blion jerome.bl...@free.fr a écrit : On Tue, 27 Mar 2012 13:10:02 +0200, Nicolas wrote: I tried, but no changes.. Mysqltuner give it for performance : Performance Metrics - [--] Up for: 2h 40m 21s (171K q [17.787 qps], 23 conn, TX: 31M, RX: 22M) [--] Reads / Writes: 0% / 100% [--] Total buffers: 572.0M global + 2.7M per thread (151 max threads) [OK] Maximum possible memory usage: 987.2M (49% of installed RAM) [OK] Slow queries: 0% (1/171K) [OK] Highest usage of available connections: 1% (2/151) [OK] Key buffer size / total MyISAM indexes: 256.0M/130.9M [OK] Key buffer hit rate: 99.3% (2M cached / 16K reads) [OK] Query cache efficiency: 56.9% (95 cached / 167 selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 9% (2 temp sorts / 21 sorts) [!!] Temporary tables created on disk: 28% (11 on disk / 38 total) [OK] Thread cache hit rate: 91% (2 created / 23 connections) [OK] Table cache hit rate: 74% (52 open / 70 opened) [OK] Open file limit used: 8% (99/1K) [OK] Table locks acquired immediately: 100% (237 immediate / 237 locks) [!!] Connections aborted: 8% On Tue, 27 Mar 2012 11:55:41 +0200, Jérôme Blion wrote: On Tue, 27 Mar 2012 10:28:01 +0200, Nicolas wrote: Oups, I made a mistake on my conf, parameters are not on [mysqld]... On Tue, 27 Mar 2012 09:45:54 +0200, Nicolas wrote: Hi, I try to optimize mysql with some infos find on the web, but that not better :s For the backup, it take 11 min to save datas, but 1 hour to update mysql table... My my.cnf : [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid query_cache_size=32M tmp_table_size=256M max_heap_table_size=256M thread_cache_size=4 table_cache=512 key_buffer_size=256M On Mon, 26 Mar 2012 12:13:28 -0400, John Drescher wrote: On Mon, Mar 26, 2012 at 12:09 PM, Nicolas wrote: good idea ! i'll try it asap and let you know. Also its best if you can have the database on a different raid than the storage or put the database on an SSD. John M. Drescher -- Nicolas http://www.shivaserv.fr [1] [2] Hello, Each database server is different. If you want to perform some tuning, you can help yourself with tuning-primer.sh + mysqltuner.pl. They provide good metrics to start with. If you are facing table locking issue, perhaps you should migrate to InnoDB (if you use Mysql 5.1, use the InnoDB Plugin, not the embedded one) HTH. Jérôme Blion (Lyon, France) Hello, Please don't obfuscate anything. tuning-primer provides more metrics data. Best regards. Jerome Blion. -- This SF email is sponsosred by: Try Windows Azure free for 90 days Click Here http://p.sf.net/sfu/sfd2d-msazure ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users -- This SF email is sponsosred by: Try Windows Azure free for 90 days Click Here http://p.sf.net/sfu/sfd2d-msazure ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Low request Mysql
Is it normal that the request is very long or is it a bug ? Server is with Xeon 2Ghz CPU. Neither. You need to tune your database server. John -- This SF email is sponsosred by: Try Windows Azure free for 90 days Click Here http://p.sf.net/sfu/sfd2d-msazure ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Low request Mysql
What do you want to say by tune my database server ? On Mon, 26 Mar 2012 10:57:21 -0400, John Drescher wrote: Is it normal that the request is very long or is it a bug ? Server is with Xeon 2Ghz CPU. Neither. You need to tune your database server. John -- Nicolas http://www.shivaserv.fr [1] Links: -- [1] http://www.shivaserv.fr -- This SF email is sponsosred by: Try Windows Azure free for 90 days Click Here http://p.sf.net/sfu/sfd2d-msazure___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Low request Mysql
What do you want to say by tune my database server ? Look into mysql guides for improving database performance. By default most distributions optimize their databases for machines with a ridiculously low amount of ram like 32MB. Yes even in 2012 I see my.cfg files that are optimized for 32 MB of ram. You need to adjust your database parameters to better match your system. John -- This SF email is sponsosred by: Try Windows Azure free for 90 days Click Here http://p.sf.net/sfu/sfd2d-msazure ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Low request Mysql
good idea ! i'll try it asap and let you know. Nicolas http://www.shivaserv.fr Le 26 mars 2012 à 17:20, John Drescher dresche...@gmail.com a écrit : What do you want to say by tune my database server ? Look into mysql guides for improving database performance. By default most distributions optimize their databases for machines with a ridiculously low amount of ram like 32MB. Yes even in 2012 I see my.cfg files that are optimized for 32 MB of ram. You need to adjust your database parameters to better match your system. John -- This SF email is sponsosred by: Try Windows Azure free for 90 days Click Here http://p.sf.net/sfu/sfd2d-msazure ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Low request Mysql
On Mon, Mar 26, 2012 at 12:09 PM, Nicolas nico...@shivaserv.fr wrote: good idea ! i'll try it asap and let you know. Also its best if you can have the database on a different raid than the storage or put the database on an SSD. John M. Drescher -- This SF email is sponsosred by: Try Windows Azure free for 90 days Click Here http://p.sf.net/sfu/sfd2d-msazure ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Low request Mysql
On Mon, Mar 26, 2012 at 12:09 PM, Nicolas nico...@shivaserv.fr wrote: good idea ! i'll try it asap and let you know. BTW, Please excuse me for being very short on the replies today. I am not feeling at all well today. The pollen / changing whether has made it very difficult to breathe. John -- This SF email is sponsosred by: Try Windows Azure free for 90 days Click Here http://p.sf.net/sfu/sfd2d-msazure ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Low request Mysql
On 26/03/12 17:21, John Drescher wrote: BTW, Please excuse me for being very short on the replies today. I am not feeling at all well today. The pollen / changing whether has made it very difficult to breathe. If it's pollen, a good dustmask/halfmask works far better than any drug available - and the only side effect is looking a little odd. You'll usually notice the change within 15 minutes. Class P3/FFP3 for Europe, N100 for North America. Lower values don't trap fine enough particles (Allergen pollens are in the 5-15um range) (*) Sometimes called Mechanical filter respirators Valved ones are preferred. It makes exhaling a little easier if you wear spectacles. -- This SF email is sponsosred by: Try Windows Azure free for 90 days Click Here http://p.sf.net/sfu/sfd2d-msazure ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Low request Mysql
don't worry for your responses :) where are you ? I'm in france Nicolas http://www.shivaserv.fr Le 26 mars 2012 à 19:32, Alan Brown a...@mssl.ucl.ac.uk a écrit : On 26/03/12 17:21, John Drescher wrote: BTW, Please excuse me for being very short on the replies today. I am not feeling at all well today. The pollen / changing whether has made it very difficult to breathe. If it's pollen, a good dustmask/halfmask works far better than any drug available - and the only side effect is looking a little odd. You'll usually notice the change within 15 minutes. Class P3/FFP3 for Europe, N100 for North America. Lower values don't trap fine enough particles (Allergen pollens are in the 5-15um range) (*) Sometimes called Mechanical filter respirators Valved ones are preferred. It makes exhaling a little easier if you wear spectacles. -- This SF email is sponsosred by: Try Windows Azure free for 90 days Click Here http://p.sf.net/sfu/sfd2d-msazure ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Low request Mysql
If it's pollen, a good dustmask/halfmask works far better than any drug available - and the only side effect is looking a little odd. You'll usually notice the change within 15 minutes. I may try this at home. At work I will have to tough this one out.. John -- This SF email is sponsosred by: Try Windows Azure free for 90 days Click Here http://p.sf.net/sfu/sfd2d-msazure ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
Re: [Bacula-users] Low request Mysql
don't worry for your responses :) where are you ? I'm in france Pittsburgh, PA, USA. John -- This SF email is sponsosred by: Try Windows Azure free for 90 days Click Here http://p.sf.net/sfu/sfd2d-msazure ___ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
