Re: remote mysql is too slow

2012-04-13 Thread Karl E. Jorgensen
On Wed, Apr 11, 2012 at 09:23:09PM +0100, Bob Proulx wrote:
 Karl E. Jorgensen wrote:
  Bob Proulx wrote:
   But at some point the daemon is going to need to write a file to disk.
   That data will get cached at that time.  Or are you saying that mysql
   is using or should using O_DIRECT and avoid the cache explicitly?
  
  No - although that might help (marginally) too.  The point is that
  caching of data in MySQL will be more efficient than caching in the
  kernel disk cache; less layers and whatnot.
 
 The point I was trying to make (and apparently failing miserably) is
 that unless the mysql daemon is really trying hard to avoid it then as
 soon as it writes a file it will end up in the filesystem buffer
 cache.

ah. The miserable failing was at the receiving end - sorry :-)

 In any case, from the original 'free' output it isn't possible to know
 if the filesystem buffer cache is due to mysql or due to other
 activity across the filesystem.  Such as backup for example.  It could
 have come from any other activity.  There could be files being copied
 to or from the system.  Could be anything.

Agreed

 Normally filesystem buffer cache is a good healthy indication.
 Because if the operating system becomes starved for memory it will
 free filesystem buffer cache in order to obtain it.  If a system has
 no files cached and lots of truly free ram it may indicate that some
 process is frequently consuming a lot of memory, forcing all buffers
 to be flushed, and then exiting causing that memory to be free memory.
 Free memory as opposed to working-for-you memory in the buffer cache.
 Normally I would say memory used for filesystem buffer cache is good
 healthy use of the memory.  And when I say normally here I am not
 talking about a database daemon which will of course have special
 needs.

Definitely special needs.

-- 
Karl E. Jorgensen
IT Operations


-- 
To UNSUBSCRIBE, email to debian-user-requ...@lists.debian.org 
with a subject of unsubscribe. Trouble? Contact listmas...@lists.debian.org
Archive: http://lists.debian.org/20120413144316.GG11957@hawking



Re: remote mysql is too slow

2012-04-11 Thread Bob Proulx
Karl E. Jorgensen wrote:
 Jumping back in (late) in this thread...
 Bob Proulx wrote:
   # free -m
total   used   free sharedbuffers cached
   Mem:  7986   7913 73  0224   6133
   -/+ buffers/cache:   1554   6431
   Swap: 3813  0   3813
  
  Shows 8G of ram.  Good.  Shows no swap used.  Also good.  (But not
  necessarily bad if some swap is used.  So if you see some swap being
  used that isn't necessarily a problem.)
 
 Mostly good. Yes. But 6133 Mb Cached is not what I would expect - this
 indicates that the linux kernel is doing the caching - rather than
 MySQL. And the MySQL cache for this stuff is (almost) always more
 efficient.

But at some point the daemon is going to need to write a file to disk.
That data will get cached at that time.  Or are you saying that mysql
is using or should using O_DIRECT and avoid the cache explicitly?

Bob


signature.asc
Description: Digital signature


Re: remote mysql is too slow

2012-04-11 Thread Karl E. Jorgensen
On Wed, Apr 11, 2012 at 07:18:18AM +0100, Bob Proulx wrote:
 Karl E. Jorgensen wrote:
  Jumping back in (late) in this thread...
  Bob Proulx wrote:
# free -m
 total   used   free sharedbuffers 
cached
Mem:  7986   7913 73  0224   
6133
-/+ buffers/cache:   1554   6431
Swap: 3813  0   3813
   
   Shows 8G of ram.  Good.  Shows no swap used.  Also good.  (But not
   necessarily bad if some swap is used.  So if you see some swap being
   used that isn't necessarily a problem.)
  
  Mostly good. Yes. But 6133 Mb Cached is not what I would expect - this
  indicates that the linux kernel is doing the caching - rather than
  MySQL. And the MySQL cache for this stuff is (almost) always more
  efficient.
 
 But at some point the daemon is going to need to write a file to disk.
 That data will get cached at that time.  Or are you saying that mysql
 is using or should using O_DIRECT and avoid the cache explicitly?

No - although that might help (marginally) too.  The point is that
caching of data in MySQL will be more efficient than caching in the
kernel disk cache; less layers and whatnot.

-- 
Karl E. Jorgensen
IT Operations


-- 
To UNSUBSCRIBE, email to debian-user-requ...@lists.debian.org 
with a subject of unsubscribe. Trouble? Contact listmas...@lists.debian.org
Archive: http://lists.debian.org/20120411145523.GE30103@hawking



Re: remote mysql is too slow

2012-04-11 Thread Bob Proulx
Karl E. Jorgensen wrote:
 Bob Proulx wrote:
  But at some point the daemon is going to need to write a file to disk.
  That data will get cached at that time.  Or are you saying that mysql
  is using or should using O_DIRECT and avoid the cache explicitly?
 
 No - although that might help (marginally) too.  The point is that
 caching of data in MySQL will be more efficient than caching in the
 kernel disk cache; less layers and whatnot.

The point I was trying to make (and apparently failing miserably) is
that unless the mysql daemon is really trying hard to avoid it then as
soon as it writes a file it will end up in the filesystem buffer
cache.

In any case, from the original 'free' output it isn't possible to know
if the filesystem buffer cache is due to mysql or due to other
activity across the filesystem.  Such as backup for example.  It could
have come from any other activity.  There could be files being copied
to or from the system.  Could be anything.

Normally filesystem buffer cache is a good healthy indication.
Because if the operating system becomes starved for memory it will
free filesystem buffer cache in order to obtain it.  If a system has
no files cached and lots of truly free ram it may indicate that some
process is frequently consuming a lot of memory, forcing all buffers
to be flushed, and then exiting causing that memory to be free memory.
Free memory as opposed to working-for-you memory in the buffer cache.
Normally I would say memory used for filesystem buffer cache is good
healthy use of the memory.  And when I say normally here I am not
talking about a database daemon which will of course have special
needs.

Bob


signature.asc
Description: Digital signature


Re: remote mysql is too slow

2012-04-10 Thread Karl E. Jorgensen
Jumping back in (late) in this thread...

On Mon, 2012-04-09 at 19:51 +0100, Bob Proulx wrote:

 J. Bakshi wrote:
  Karl E. Jorgensen wrote:
   J. Bakshi wrote:
I have been provided a muscular linux server to use as a Mysql server
in our organization. The server is located just beside the web server
and within the same network. This dedicated server has 8GB RAM, i5 
processors
and running mysql as service. No apache, php . nothing. All 
resources are
dedicated to mysql only.
 
 That does sound big and muscular and should do a good job of providing
 database services.
 
The BIG hitch is; when we connect with this box the web sites become 
too slow.
 
 Unfortunately subjective descriptions such as too slow are not
 useful descriptions.  One person's very fast is another person's too
 slow.  Objective benchmark data is needed in order to make forward
 progress.  Also when you change something to improve the performance
 if you don't know how much you changed things you might actually make
 something worse without knowing it.
 
 The other suggestions that people gave you were good.  They were
 better than anything I could suggest about mysql specifically.  But
 for performance tuning in general I strongly recommend that you use or
 create a benchmark that illustrates the type of operation you are
 trying to optimize.  Benchmarks are best when they can be shared with
 other people so that they can recreate your environment.  It might be
 useful to create a benchmark using synthetic data (created fake data)
 so that others can share your environment and recreate it.
 
 Then collect data on that benchmark.  Then make performance tuning
 changes.  Then run the benchmarkmark again and determine if your
 change improved things and if so by how much.
 
The sql connection becomes little faster but still it is considerably
slow; specially with such a muscular dedicated linx box just for Mysql.
Is there anything else which I can add/configure to make the network 
latecy
small or any such mechanism to make the query fast ?
 
 Please show us data that tells us how slow is slow and how fast is
 fast.
 
 I am not a mysql performance expert.  I won't be able to help too
 much.  Sorry.  But I can tell you that if you don't have actual data
 on the existing performance then you also won't know if you have
 improved it or if you have made it worse or if you haven't changed
 anything.  Benchmarking when performance tuning is critically
 important.
 
  # free -m
   total   used   free sharedbuffers cached
  Mem:  7986   7913 73  0224   6133
  -/+ buffers/cache:   1554   6431
  Swap: 3813  0   3813
 
 Shows 8G of ram.  Good.  Shows no swap used.  Also good.  (But not
 necessarily bad if some swap is used.  So if you see some swap being
 used that isn't necessarily a problem.)


Mostly good. Yes. But 6133 Mb Cached is not what I would expect - this
indicates that the linux kernel is doing the caching - rather than
MySQL. And the MySQL cache for this stuff is (almost) always more
efficient.

If you use InnoDB tables, you can increase innodb_buffer_pool to e.g.
4Gb more.

If are using MyISAM tables, increase key_buffer_size (affects caching of
indexes) and probably others - I don't use MyISAM tables much...



 
  # vmstat 5 10
  procs ---memory-- ---swap-- -io -system-- 
  cpu
   r  b   swpd   free   buff  cache   si   sobibo   in   cs us sy id 
  wa
   0  0  0  56328 230440 629967600257466  0  0 98 
   2
   0  0  0  55700 230440 629974400 022  226  272  0  0 99 
   1
   0  0  0  55964 230440 629985600 0   314  348  388  0  0 94 
   5
   0  0  0  55452 230440 629995600 061  304  364  0  0 97 
   2
   0  1  0  55592 230440 630042400 0   271  199  257  0  0 96 
   4
   0  0  0  54584 230440 630090800 0   338  342  428  0  0 92 
   8
   0  0  0  54800 230440 630107200 077  119  133  0  0 98 
   2
   0  0  0  53964 230440 630153200 0   617  267  327  0  0 95 
   4
   0  0  0  54468 230440 630154400 0   296  119  116  0  0 99 
   0
   0  0  0  54212 230440 630164800 0   183  361  435  0  0 95 
   4
 
 The 'si' and 'so' fields show zero swap-in / swap-out rate.  That is
 good.  A very low rate of 1 or 2 also would not be a problem.  When
 machines start to swap excessively you will see rate numbers like 10
 or 20 in that field and that would be a bad indication.
 
 Bob




Re: remote mysql is too slow

2012-04-10 Thread J. Bakshi
On Tue, 10 Apr 2012 12:24:47 +0100
Karl E. Jorgensen karl.jorgen...@nice.com wrote:

snap


  
   # free -m
total   used   free sharedbuffers cached
   Mem:  7986   7913 73  0224   6133
   -/+ buffers/cache:   1554   6431
   Swap: 3813  0   3813
  
  Shows 8G of ram.  Good.  Shows no swap used.  Also good.  (But not
  necessarily bad if some swap is used.  So if you see some swap being
  used that isn't necessarily a problem.)
 
/snap

 
 Mostly good. Yes. But 6133 Mb Cached is not what I would expect - this
 indicates that the linux kernel is doing the caching - rather than
 MySQL. And the MySQL cache for this stuff is (almost) always more
 efficient.
 
 If you use InnoDB tables, you can increase innodb_buffer_pool to e.g.
 4Gb more.
 

I did, but them mysqltuner warned me that the max memory usage is dangerously 
high
and I need to install more physical memory. So I reduce it to 2G.

 If are using MyISAM tables, increase key_buffer_size (affects caching of
 indexes) and probably others - I don't use MyISAM tables much...
 

Yes, I have MyISAM tables. And provided 250M to key_buffer_size. 


-- 
To UNSUBSCRIBE, email to debian-user-requ...@lists.debian.org 
with a subject of unsubscribe. Trouble? Contact listmas...@lists.debian.org
Archive: http://lists.debian.org/20120411103641.192bd...@shiva.selfip.org



Re: remote mysql is too slow

2012-04-09 Thread J. Bakshi
On Thu, 05 Apr 2012 11:36:38 +0100
Karl E. Jorgensen karl.jorgen...@nice.com wrote:

 Hi
 
 On Thu, 2012-04-05 at 08:13 +0100, J. Bakshi wrote:
 
  Hello,
  
  I have been provided a muscular linux server to use as a Mysql server
  in our organization. The server is located just beside the web server
  and within the same network. This dedicated server has 8GB RAM, i5 
  processors
  and running mysql as service. No apache, php . nothing. All resources 
  are
  dedicated to mysql only.
  
  Mysql version - mysql  Ver 14.14 Distrib 5.1.49, for debian-linux-gnu 
  (x86_64)
  
  The BIG hitch is; when we connect with this box the web sites become too 
  slow.
  I have added the following at my.cnf under [mysqld] section
  
  ` ` ` ` `
  skip_external_locking
  skip_name_resolve
  skip_host_cach
  
  ` ` ` ` ` `
  
  
  The sql connection becomes little faster but still it is considerably
  slow; specially with such a muscular dedicated linx box just for Mysql.
  Is there anything else which I can add/configure to make the network latecy
  small or any such mechanism to make the query fast ?
 
 
 Although you say all resources are dedicated to mysql only, I guess
 they're not.
 
 First step: run mysqltuner - this will give some rough recommendations
 which will usually point people in the right direction.  Beware that
 mysqltuner will always recommend setting innodb_buffer_pool_size big
 enough to keep all InnoDB tables in memory - which is usually overkill.
 

I run the mysqltuner directly on the remote mysql server; and here is the
result

```
 General Statistics --
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.49-3-log
[OK] Operating on 64-bit architecture

 Storage Engine Statistics ---
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 4777)
[--] Data in InnoDB tables: 3G (Tables: 5543)
[--] Data in MEMORY tables: 0B (Tables: 136)
[!!] Total fragmented tables: 5562

 Performance Metrics -
[--] Up for: 3d 23h 55m 27s (1M q [4.523 qps], 81K conn, TX: 23B, RX: 469M)
[--] Reads / Writes: 74% / 26%
[--] Total buffers: 2.2G global + 20.4M per thread (150 max threads)
[OK] Maximum possible memory usage: 5.2G (66% of installed RAM)
[OK] Slow queries: 2% (39K/1M)
[OK] Highest usage of available connections: 18% (28/150)
[OK] Key buffer size / total MyISAM indexes: 2.0G/268.5M
[!!] Key buffer hit rate: 78.2% (5M cached / 1M reads)
[OK] Query cache efficiency: 42.0% (327K cached / 778K selects)
[!!] Query cache prunes per day: 1993
[OK] Sorts requiring temporary tables: 0% (48 temp sorts / 9K sorts)
[!!] Temporary tables created on disk: 39% (91K on disk / 230K total)
[OK] Thread cache hit rate: 99% (28 created / 81K connections)
[!!] Table cache hit rate: 6% (16K open / 248K opened)
[OK] Open file limit used: 36% (11K/32K)
[OK] Table locks acquired immediately: 99% (695K immediate / 695K locks)
[!!] InnoDB data size / buffer pool: 3.6G/8.0M

 Recommendations -
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
When making adjustments, make tmp_table_size/max_heap_table_size equal
Reduce your SELECT DISTINCT queries without LIMIT clauses
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size ( 128M)
tmp_table_size ( 100M)
max_heap_table_size ( 100M)
table_cache ( 16000)
innodb_buffer_pool_size (= 3G)



Please note, every day mysql optimization as well as repairing is done
by a cron at night. I have also tried with the suggestion multiple
times before but every time it gives some new suggestion. I have 8GB
physical RAM in this server and here is some statistics

`
# free -m
 total   used   free sharedbuffers cached
Mem:  7986   7913 73  0224   6133
-/+ buffers/cache:   1554   6431
Swap: 3813  0   3813
`

And here is the vmstat result with 10 count and 5 sec delay

`

# vmstat 5 10
procs ---memory-- ---swap-- -io -system-- cpu
 r  b   swpd   free   buff  cache   si   sobibo   in   cs us sy id wa
 0  0  0  56328 230440 629967600257466  0  0 98  2
 0  0  0  55700 230440 629974400 022  226  272  0  0 99  1
 0  0  0  55964 230440 629985600 0   314  348  388  0  0 94  5
 0  0  0  55452 230440 629995600 061  304  364  0  0 97  2
 0  1  0  55592 230440 630042400 0   271  199  257  0  0 

Re: remote mysql is too slow

2012-04-09 Thread Joe
On Mon, 9 Apr 2012 12:36:30 +0530
J. Bakshi baksh...@gmail.com wrote:

 `
 
 Is it possible to suggest something to tweak the server / mysql to get
 a fast remote mysql box ?
 

Almost certainly, but it's more likely that people who use mysql very
intensively will be better able to help you than general Debian users.

Have you considered seeking help in mysql forums? The vast majority of
heavy mysql users will be running it on Linux or a BSD, and there's not
really anything Debian-specific about mysql.

-- 
Joe


-- 
To UNSUBSCRIBE, email to debian-user-requ...@lists.debian.org 
with a subject of unsubscribe. Trouble? Contact listmas...@lists.debian.org
Archive: http://lists.debian.org/20120409093142.63369...@jretrading.com



Re: remote mysql is too slow

2012-04-09 Thread Bob Proulx
J. Bakshi wrote:
 Karl E. Jorgensen wrote:
  J. Bakshi wrote:
   I have been provided a muscular linux server to use as a Mysql server
   in our organization. The server is located just beside the web server
   and within the same network. This dedicated server has 8GB RAM, i5 
   processors
   and running mysql as service. No apache, php . nothing. All resources 
   are
   dedicated to mysql only.

That does sound big and muscular and should do a good job of providing
database services.

   The BIG hitch is; when we connect with this box the web sites become too 
   slow.

Unfortunately subjective descriptions such as too slow are not
useful descriptions.  One person's very fast is another person's too
slow.  Objective benchmark data is needed in order to make forward
progress.  Also when you change something to improve the performance
if you don't know how much you changed things you might actually make
something worse without knowing it.

The other suggestions that people gave you were good.  They were
better than anything I could suggest about mysql specifically.  But
for performance tuning in general I strongly recommend that you use or
create a benchmark that illustrates the type of operation you are
trying to optimize.  Benchmarks are best when they can be shared with
other people so that they can recreate your environment.  It might be
useful to create a benchmark using synthetic data (created fake data)
so that others can share your environment and recreate it.

Then collect data on that benchmark.  Then make performance tuning
changes.  Then run the benchmarkmark again and determine if your
change improved things and if so by how much.

   The sql connection becomes little faster but still it is considerably
   slow; specially with such a muscular dedicated linx box just for Mysql.
   Is there anything else which I can add/configure to make the network 
   latecy
   small or any such mechanism to make the query fast ?

Please show us data that tells us how slow is slow and how fast is
fast.

I am not a mysql performance expert.  I won't be able to help too
much.  Sorry.  But I can tell you that if you don't have actual data
on the existing performance then you also won't know if you have
improved it or if you have made it worse or if you haven't changed
anything.  Benchmarking when performance tuning is critically
important.

 # free -m
  total   used   free sharedbuffers cached
 Mem:  7986   7913 73  0224   6133
 -/+ buffers/cache:   1554   6431
 Swap: 3813  0   3813

Shows 8G of ram.  Good.  Shows no swap used.  Also good.  (But not
necessarily bad if some swap is used.  So if you see some swap being
used that isn't necessarily a problem.)

 # vmstat 5 10
 procs ---memory-- ---swap-- -io -system-- cpu
  r  b   swpd   free   buff  cache   si   sobibo   in   cs us sy id wa
  0  0  0  56328 230440 629967600257466  0  0 98  2
  0  0  0  55700 230440 629974400 022  226  272  0  0 99  1
  0  0  0  55964 230440 629985600 0   314  348  388  0  0 94  5
  0  0  0  55452 230440 629995600 061  304  364  0  0 97  2
  0  1  0  55592 230440 630042400 0   271  199  257  0  0 96  4
  0  0  0  54584 230440 630090800 0   338  342  428  0  0 92  8
  0  0  0  54800 230440 630107200 077  119  133  0  0 98  2
  0  0  0  53964 230440 630153200 0   617  267  327  0  0 95  4
  0  0  0  54468 230440 630154400 0   296  119  116  0  0 99  0
  0  0  0  54212 230440 630164800 0   183  361  435  0  0 95  4

The 'si' and 'so' fields show zero swap-in / swap-out rate.  That is
good.  A very low rate of 1 or 2 also would not be a problem.  When
machines start to swap excessively you will see rate numbers like 10
or 20 in that field and that would be a bad indication.

Bob


signature.asc
Description: Digital signature


remote mysql is too slow

2012-04-05 Thread J. Bakshi
Hello,

I have been provided a muscular linux server to use as a Mysql server
in our organization. The server is located just beside the web server
and within the same network. This dedicated server has 8GB RAM, i5 processors
and running mysql as service. No apache, php . nothing. All resources are
dedicated to mysql only.

Mysql version - mysql  Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (x86_64)

The BIG hitch is; when we connect with this box the web sites become too slow.
I have added the following at my.cnf under [mysqld] section

` ` ` ` `
skip_external_locking
skip_name_resolve
skip_host_cach

` ` ` ` ` `


The sql connection becomes little faster but still it is considerably
slow; specially with such a muscular dedicated linx box just for Mysql.
Is there anything else which I can add/configure to make the network latecy
small or any such mechanism to make the query fast ?

The server has very little load as observer from top and iostat
even during peak hours.

Thanks


-- 
To UNSUBSCRIBE, email to debian-user-requ...@lists.debian.org 
with a subject of unsubscribe. Trouble? Contact listmas...@lists.debian.org
Archive: http://lists.debian.org/20120405124336.73d6c...@shiva.selfip.org



Re: remote mysql is too slow

2012-04-05 Thread Juan Sierra Pons
2012/4/5 J. Bakshi baksh...@gmail.com:
 Hello,

 I have been provided a muscular linux server to use as a Mysql server
 in our organization. The server is located just beside the web server
 and within the same network. This dedicated server has 8GB RAM, i5 processors
 and running mysql as service. No apache, php . nothing. All resources are
 dedicated to mysql only.

 Mysql version - mysql  Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (x86_64)

 The BIG hitch is; when we connect with this box the web sites become too slow.
 I have added the following at my.cnf under [mysqld] section

 ` ` ` ` `
 skip_external_locking
 skip_name_resolve
 skip_host_cach

 ` ` ` ` ` `


 The sql connection becomes little faster but still it is considerably
 slow; specially with such a muscular dedicated linx box just for Mysql.
 Is there anything else which I can add/configure to make the network latecy
 small or any such mechanism to make the query fast ?

 The server has very little load as observer from top and iostat
 even during peak hours.

 Thanks


 --
 To UNSUBSCRIBE, email to debian-user-requ...@lists.debian.org
 with a subject of unsubscribe. Trouble? Contact listmas...@lists.debian.org
 Archive: http://lists.debian.org/20120405124336.73d6c...@shiva.selfip.org

Hi

My 5 cents: :)

 * Do you have anything in your mysql's slow query log?
entries look like (see below) and can point you in the right
direction. As maybe is a SQL bad programming problem, lock between
tables, etc
.
# Wed Apr  4 13:08:27 2012
# Query_time: 1.494178  Lock_time: 0.00 Rows_sent: 0  Rows_examined: 0
use elsotani_X;
SET timestamp=1333566507;
CREATE TABLE IF NOT EXISTS `wp_translate_langs` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(25) NOT NULL,
`main` tinyint(1) NOT NULL default 0,
`order` tinyint(1) NOT NULL default 0,
`icon` varchar(20) NOT NULL default '',
PRIMARY KEY  (`id`)
)

 * Is your DNS server responding quickly? Just a quick check.
add the IPs  server names to /etc/hosts in mysql server and clients
and see if performance is better. I have seen that you have
skip_name_resolve and  skip_host_cache parameters set, but this
solves (workaround) only the servers' side not the clients

 * Anything wrong in your apache's logs?

Keep us informed about your progresses.

I hope it helps :)

Best regards

--
Juan Sierra Pons j...@elsotanillo.net
Linux User Registered: #257202   http://www.elsotanillo.net
GPG key = 0xA110F4FE
Key Fingerprint = DF53 7415 0936 244E 9B00  6E66 E934 3406 A110 F4FE
--


--
To UNSUBSCRIBE, email to debian-user-requ...@lists.debian.org
with a subject of unsubscribe. Trouble? Contact listmas...@lists.debian.org
Archive: 
http://lists.debian.org/CABS=y9uPRH_6V3W8EGjhEymWh3zP07e5Mwxn=izsceac0bk...@mail.gmail.com



Re: remote mysql is too slow

2012-04-05 Thread Karl E. Jorgensen
Hi

On Thu, 2012-04-05 at 08:13 +0100, J. Bakshi wrote:

 Hello,
 
 I have been provided a muscular linux server to use as a Mysql server
 in our organization. The server is located just beside the web server
 and within the same network. This dedicated server has 8GB RAM, i5 processors
 and running mysql as service. No apache, php . nothing. All resources are
 dedicated to mysql only.
 
 Mysql version - mysql  Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (x86_64)
 
 The BIG hitch is; when we connect with this box the web sites become too slow.
 I have added the following at my.cnf under [mysqld] section
 
 ` ` ` ` `
 skip_external_locking
 skip_name_resolve
 skip_host_cach
 
 ` ` ` ` ` `
 
 
 The sql connection becomes little faster but still it is considerably
 slow; specially with such a muscular dedicated linx box just for Mysql.
 Is there anything else which I can add/configure to make the network latecy
 small or any such mechanism to make the query fast ?


Although you say all resources are dedicated to mysql only, I guess
they're not.

First step: run mysqltuner - this will give some rough recommendations
which will usually point people in the right direction.  Beware that
mysqltuner will always recommend setting innodb_buffer_pool_size big
enough to keep all InnoDB tables in memory - which is usually overkill.

if vmstat reports that CPU usage is low, and disk IO is low, this
points towards locking contention - which is entirely application
dependent. Hope you haven't used MyISAM tables when you intended to use
InnoDB tables :-)

if vmstat reports high cpu usage, and low disk IO, then the database
is probably in memory, and you need to look at one or more of:

  * queries scanning lots of rows (e.g. missing indexes)
  * for InnoDB tables: if innodb_buffer_pool is too small, but the
server has lots of memory, the linux kernel will cache disk
blocks rather than MySQL doing it. Which translates to high CPU
load and lots of cache hits. But in this case it is more
efficient to let MySQL do the caching rather than the kernel.

 
 The server has very little load as observer from top and iostat
 even during peak hours.
 
 Thanks
 
 


attachment: face-smile.png

Re: remote mysql is too slow

2012-04-05 Thread Chris Davies
2012/4/5 J. Bakshi baksh...@gmail.com:
 I have been provided a muscular linux server to use as a Mysql server
 in our organization. [...]

 The BIG hitch is; when we connect with this box the web sites become
 too slow.

Is this in direct comparison to some other database or server, or is it
more of a black and white (it's too slow) statement?

If it's slower than some other database system you're going to need to
look at your network topology and system configuration and compare like
for like as best you can.

If it's a simple statement of fact you probably need to look at
your application architecture. Are you - and should you be - using
memcached? Have you got indexes on the right table columns? Are you
using ISAM when you should be using InnoDB (or v.v.)?


Juan Sierra Pons j...@elsotanillo.net wrote:
 * Do you have anything in your mysql's slow query log?

This is a good place to check, but it doesn't preclude you checking that
all your frequent database queries and updates are using indexes.

Chris


-- 
To UNSUBSCRIBE, email to debian-user-requ...@lists.debian.org 
with a subject of unsubscribe. Trouble? Contact listmas...@lists.debian.org
Archive: http://lists.debian.org/dka059x1s1@news.roaima.co.uk