Please Help what is wrong with the setting

MySQL & MariaDB Other MySQL® Questions
1

I have 10 different web sites access to sql database and the hosting company is saying that there is a problem with the indexes these are the configuration below.

Will you please tell me how to fix it I have 15 days either i fix the problem or i will be thrown from the hosting company.

thank you

Luca

Server variables and settings
Variable Session value / Global value
back log 50
basedir /
binlog cache size 32,768
bulk insert buffer size 8,388,608
character set client utf8
(Global value) latin1
character set connection utf8
(Global value) latin1
character set database latin1
character set results utf8
(Global value) latin1
character set server latin1
character set system utf8
character sets dir /usr/share/mysql/charsets/
collation connection utf8_unicode_ci
(Global value) latin1_swedish_ci
collation database latin1_swedish_ci
collation server latin1_swedish_ci
concurrent insert ON
connect timeout 5
datadir /var/lib/mysql/
date format %Y-%m-%d
datetime format %Y-%m-%d %H:%i:%s
default week format 0
delay key write ON
delayed insert limit 100
delayed insert timeout 300
delayed queue size 1,000
expire logs days 0
flush OFF
flush time 0
ft boolean syntax + -><()~*:“”&|
ft max word len 84
ft min word len 4
ft query expansion limit 20
ft stopword file (built-in)
group concat max len 1,024
have archive NO
have bdb NO
have blackhole engine NO
have compress YES
have crypt YES
have csv NO
have example engine NO
have geometry YES
have innodb YES
have isam NO
have merge engine YES
have ndbcluster NO
have openssl NO
have query cache YES
have raid NO
have rtree keys YES
have symlink YES
init connect
init file
init slave
innodb additional mem pool size 1,048,576
innodb autoextend increment 8
innodb buffer pool awe mem mb 0
innodb buffer pool size 209,715,200
innodb data file path ibdata1:10M:autoextend
innodb data home dir
innodb fast shutdown ON
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 1,048,576
innodb log file size 5,242,880
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 table locks ON
innodb thread concurrency 8
interactive timeout 28,800
join buffer size 3,141,632
key buffer size 629,145,600
key cache age threshold 300
key cache block size 1,024
key cache division limit 100
language /usr/share/mysql/english/
large files support ON
lc time names en_US
license GPL
local infile ON
locked in memory OFF
log OFF
log bin OFF
log error
log slave updates OFF
log slow queries OFF
log update OFF
log warnings 1
long query time 10
low priority updates OFF
lower case file system OFF
lower case table names 0
max allowed packet 1,048,576
max binlog cache size 4,294,967,295
max binlog size 1,073,741,824
max connect errors 10
max connections 45
max delayed threads 20
max error count 64
max heap table size 16,777,216
max insert delayed threads 20
max join size 4,294,967,295
max length for sort data 1,024
max prepared stmt count 16,382
max relay log size 0
max seeks for key 4,294,967,295
max sort length 1,024
max tmp tables 32
max user connections 15
max write lock count 4,294,967,295
myisam data pointer size 4
myisam max extra sort file size 2,147,483,648
myisam max sort file size 2,147,483,647
myisam recover options OFF
myisam repair threads 1
myisam sort buffer size 8,388,608
myisam stats method nulls_unequal
net buffer length 16,384
net read timeout 30
net retry count 10
net write timeout 60
new OFF
old passwords OFF
open files limit 4,096
pid file /var/lib/mysql/alexis.internetwebserver.net.pid
port 3,306
preload buffer size 32,768
prepared stmt count 0
protocol version 10
query alloc block size 8,192
query cache limit 20,971,520
query cache min res unit 4,096
query cache size 209,715,200
query cache type ON
query cache wlock invalidate OFF
query prealloc size 8,192
range alloc block size 2,048
read buffer size 5,238,784
read only OFF
read rnd buffer size 262,144
relay log purge ON
relay log space limit 0
rpl recovery rank 0
secure auth OFF
server id 0
skip external locking ON
skip networking OFF
skip show database OFF
slave net timeout 3,600
slave transaction retries 0
slow launch time 2
socket /var/lib/mysql/mysql.sock
sort buffer size 5,242,872
sql mode
sql notes ON
sql warnings ON
storage engine MyISAM
sync binlog 0
sync frm ON
sync replication 0
sync replication slave id 0
sync replication timeout 0
system time zone EDT
table cache 512
table type MyISAM
thread cache size 50
thread stack 196,608
time format %H:%i:%s
time zone SYSTEM
tmp table size 33,554,432
tmpdir
transaction alloc block size 8,192
transaction prealloc size 4,096
tx isolation REPEATABLE-READ
version 4.1.22-standard
version comment MySQL Community Edition - Standard (GPL)
version compile machine i686
version compile os pc-linux-gnu
wait timeout 28,800

Variable Value
Flush_commands 37
Slow_queries 2,873
Begin Handler
Variable Value
Handler_commit 561
Handler_delete 646 k
Handler_discover 0
Handler_read_first 3,513 k
Handler_read_key 777 M
Handler_read_next 387 M
Handler_read_prev 9,935 k
Handler_read_rnd 54 M
Handler_read_rnd_next 1,331 M
Handler_rollback 6,583
Handler_update 9,574 k
Handler_write 107 M
Begin Query cache
Variable Value
Flush query cache
Qcache_free_blocks 4,553
Qcache_free_memory 23 M
Qcache_hits 3,704.61 M
Qcache_inserts 121 M
Qcache_lowmem_prunes 2,604 k
Qcache_not_cached 4,470 k
Qcache_queries_in_cache 78 k
Qcache_total_blocks 164 k
Begin Threads
Variable Value
Show processes
Slow_launch_threads 0
Threads_cached 37
Threads_connected 10
Threads_created 46
Threads_running 5
Threads_cache_hitrate_% 100.00%
Begin Binary log
Variable Value

Binlog_cache_disk_use 0
Binlog_cache_use 0
Begin Temporary data
Variable Value
Created_tmp_disk_tables 246 k
Created_tmp_files 8,352
Created_tmp_tables 781 k
Begin Delayed inserts
Variable Value
Delayed_errors 0
Delayed_insert_threads 1
Delayed_writes 66 k
Not_flushed_delayed_rows 0
Begin Key cache
Variable Value

Key_blocks_not_flushed 0
Key_blocks_unused 539 k
Key_blocks_used 19 k
Key_read_requests 2,115 M
Key_reads 3,888 k
Key_write_requests 6,565 k
Key_writes 3,366 k
Key_buffer_fraction_% 12.21%
Key_write_ratio_% 51.27%
Key_read_ratio_% 0.18%
Begin Joins
Variable Value
Select_full_join 246 k
Select_full_range_join 719
Select_range 318 k
Select_range_check 6,395
Select_scan 4,591 k
Begin Replication
Variable Value
Show slave hosts Show slave status
Rpl_status NULL
Slave_open_temp_tables 0
Slave_retried_transactions 0
Slave_running OFF
Begin Sorting
Variable Value
Sort_merge_passes 4,174
Sort_range 250 k
Sort_rows 60 M
Sort_scan 1,162 k
Begin Tables
Variable Value
Flush (close) all tables Show open tables
Open_tables 512
Opened_tables 1,102 k
Table_locks_immediate 247 M
Table_locks_waited 100 k
Begin
Variable Value
Open_files 981
Open_streams 0

2

You should fix Your tables and queries.

Slow_queries 2,873

Almost 3k of the slow queries (at least 10 seconds of execution)

Handler_read_rnd 54 M

The number of requests to read a row based on a fixed position. This is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don’t use keys properly.

Handler_read_rnd_next 1,331 M

The number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.

3

Yes sir thank you for your help but one problem I dont know how to fix them can you please help me little more.

regards

Lucas

I got the cache size larger and play some I dont event know what I did but i got this

Select_full_join 7,745
Select_full_range_join 25
Select_range 16 k T
Select_range_check 80

Handler_read_rnd 2,000 k

4

You need to create indexes on your tables.
Especially for your JOIN queries.

Do you know about indexes?
Otherwise read here to get some more information: http://20bits.com/articles/interview-questions-database-inde xes/.

So what you need to do is:

  1. Find out which queries that are a problem.
    If you have a small site then you probably don’t have that many queries and then you can just search for them in the source code.
    If you have a large site then I suggest the MySQL slow-query-log[/url] if you can use it for your webhosting company.
    And start by looking at which tables that contain a lot of records. Very often on a site you only have a couple of tables that really contains a lot of records, the rest is usually quite small, so focus you energy with indexes for these large tables where they will be put to most use.

    3.

Check the design of your database.
Either SHOW CREATE TABLE yourTable; or SHOW INDEXES yourTable or using some GUI.

Start with adding indexes for the columns that are part of the JOIN condition.
This is most important because it is causing a lot of load on your database as it looks like.
So if you have a query that look like:

SELECT …FROM yourTableAINNER JOIN yourTableB ON yourTableA.id = yourTableB.parent_id

Then you should index both yourTable.id and yourTableB.parent_id.
Example:

ALTER TABLE yourTableA ADD INDEX yourTableA_ix_id(id);ALTER TABLE yourTableB ADD INDEX yourTableB_ix_parentid(parent_id);

If you start with this you can at least get rid of the table scans with the joins which very fast puts a huge load on your server.

Then you can start looking at the rest of your queries that are just like “… WHERE x = 3;” and verify that the x column is indexed so that the query does not have to scan the entire table to find the matching rows.

When you have done this you will gain much more speed in your case than any server variables will ever give you.

Good luck!
5

can i give you login name and the password )

I got lost sorry but I will try i dont want to mess things up

I greatly appreciated for your help.

Luca…

6

Are You aware that this will take time and it may be connected with serious application rebuild ?

Is there some sort of query log or debug ? It would be easier to work on ready queries.

7

No I am not

8

So, is there a query list or there is php mess like:
$query = "select columns from ".$prefix.“table “.$where.” “.$orderby.” limit “.$start.”,”.$stop ;
$result = mysql_query( $query ) ;
?

And what type of webpages are there ? Handmade or made by other people (like phpbb, phpnuke, etc) ?

9

thank you for all your help

lucas

10

And by looking at your site I guess that you are running osCommerce, correct?

When I look at the sql code in the install directory I can say that indexes looks to be very scarce in the default database for osCommerce. Basically they only seem to have a primary key and some tables have one more index but that’s all.
And looking at the PHP code then yes Januzi it looks like it’s a php mess. :wink:

My suggestion is that you either figure out how to use the slow query log and turn it on to find which queries that are worst or hire someone to optimize it for you.

I don’t think that you at this stage need to rewrite any php code, but I do think that you will have to create quite a few indexes in your database to solve your problem.

11

so how am i gonna do that )

sorry

Lucas

12

What about:
http://www.google.se/search?q=mysql+optimization+consultant& amp;ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:en-US :official&client=firefox-a :wink:

I googled a bit more for you, read this article:
[URL=“http&#58;&#47;&#47;Speed / Performance Optimizations - General Discussions - osCommerce Community Forum”]Forums - osCommerce Community Forum

Then you can apparently install this plugin if you don’t want to use the mysql slow query log:
[URL=“http&#58;&#47;&#47;www.oscommerce.com/community/contributions,2575”]http://www.oscommerce.com/community/contributions,2575[/URL]

I would have been glad to help you but I’m going on vacation in less than 36 hours and I can’t really squeeze in the 4-8 hours I think will take to get it somewhat under control.

13

Thank you I greatly appreciated

Lucas

14

Okay I did the easiest way and I install the contribution will you please check the botton.

thank you

I don’t know what to do with that I am sorry.
I attached a file if you can please take a look

Regards

Luca

15

Those are queries only from one display of the front page ?
Disable products counter. Oscommerce is running query for every category, the more categories and subcategories the worst situation. (There was other solution at the oscommerce forums, maybe You’ll find it)

As for tax rate, there is only one tax rate ? I changed friends shop so the query “select sum(tax_rate) as tax_rate from tax_rates …” is running only once at the begin of the script. 100 products = 1 tax query

16

where else the queries is gonna be ?

Thank you

Lucas

how can i disable the products counter?

17

Configuration (Your shop), 4th option from the bottom (“show categories amount” or something like that)

18

okay the number that shows sorry

i got that and I deleted all the taxes I dont do taxes

thank you

19

Create second query log and post it.

20

so what is the different now ? )

now only 136 query